How do I identify one or more form records that have the same value in a given field

[Nick Cooper]

I generate an omnibus invoice for one or more completed taxi bookings, comprising details and costs taken from each selected job/booking record, but all sharing just the one Invoice Number (the next in the stack). This action ticks a temporary check-box in each chosen record. Once the invoice is dispatched I want to be able to search those records that are still checked and add the one invoice number (identified by DMax + 1) to the currently blank 'invoice number' field in each of them, as well as the date of dispatch Date() to the currently blank date field, all for future reference. I have been trying to do this with a macro. I open the Bookings table that feeds the form (via query) but I'm having probs identifying the checked records. Also not sure, if I crack that, how I can add the fresh data back into the relevant records.

[liimra]

Possible Solution

If you want to apply this using a macro, why don't you use the "setvalue" command. I mean to set the value you want in the field you want where the criteria you desire is met (two blank fields). Honestly, I don't know why some argue about this command but for me it is extremely useful.

Hope this helps if any,

Regards,
Ali

[Nick Cooper]

Thanks - SetValue seems to be just what I need, but when I test it I find that it acts on the first record but stops there. I need to pass through all records to see whether they are checked and add the invoice number accordingly. My macro design is as follows (the "5" and "10" are just testers to check the functionality at this stage).
Item: [Forms]![Bookings]![Inv No]
Expression: IIf([Forms]![Bookings]![Is Selected]=True,"5","10")

[liimra]

Solution/

Thanks Nick. Glad we are on the right track. Now, we need to use the loop function so we can achieve what you need.

The solution consists of three steps:

1) Create a Query based on the invoice table. Under criteria in Query Design View, Type "Is Null" without quotes. Note that in SQl view the statement is (replace names where necassary).

SELECT TableName.FieldName1, TableName.FieldName2
FROM TableName
WHERE (((TableName.FieldName1) Is Null));

Of course you can modify it in the way you want depending on your needs. For example you can add more criteria (Invoice No, Date..etc).

2) Create a Multiple Item Form based on the just created Query.

3) Create a button and attach the following code to it:

Do
FieldName = DMax("FieldName", "TableOrQuery") + 1
Recordset.MoveNext
Loop Until Recordset.EOF

That is it, you are done. Furthermore, if you want not to get any error when there are no records, you can add If statement at the beginning like : If RecordCount = 0
Msg Box "No Record" Else..


Here is my favorite part and one thing I like about Access & VBA.

You can create a button that will do it for you (Open the form, update records, close back the record, give message of success or no result when there are no records). This will make your database looks extremely professional because all what the user would see is that button.

I have attached a database with the example.

The form with the button is the one displayed at opening. Once you click on the button "update records", All records will be updated. If the table (invoice table) contains no records without invoice number, you will get the message "no new records". If there are new records with no invoice number, the records will be updated with the required number (DMax + 1) and you will get the message "records updated".

The table has some missing invoice numbers; so first time you click "update records" the records will be updated. Next time it won't as all records will have invoice numbers.


It is simple and works great. I hope it will help you with this issue as well with future issues.

Regards,
Ali

[Nick Cooper]

Thanks for your very comprehensive reply and attachment Ali which I have been studying carefully. One problem is that mine is already quite a large and involved database and I would like to implement your proposals as closely to the current structure as possible. Also, this is a multiple invoice covering more than one job but all sharing the same 'next in the stack' Inv No. I have now created a query which displays all records (jobs) to be included on the next invoice, also a field (Next Inv No) which calculates the next Inv No using DMax on all those currently allocated. I have converted that query to a Multiple Item Form but the following code within it stops at the 3rd line giving "runtime error 2448 - You can't assign a value to this object":

Private Sub Form_Open(Cancel As Integer)
Do
Forms![Invoice Query]![Inv No] = Forms![Invoice Query]![Next Inv No]
Forms![Invoice Query].Recordset.MoveNext
Loop Until Forms![Invoice Query].Recordset.EOF
DoCmd.Close acForm, "[Invoice Query]"
End Sub

Any ideas? Maybe it is my notation that is poor - I'm new to VBA.

Thanks
Nick

[liimra]

Solution//

You are welcome Nick. We are almost there. I don't have the database and the structure but I strongly believe that you are getting this error because you are placing the new field you created inside the form in a hidden state (May be in the head of the form and then you changed visibility of the head to "NO"). IF this is the case, just change the visibility to "Yes".

Even if you use the DMAX inside a field in the multiple items form, it will still gather the last entered value. Means (DMax + 1, DMax + 1 + 1, DMax + 1 +1 +1 and so on).
There is an easy way to overcome this. Use the "Setvalue" Function to set the value of the field in the multiple item form *on the load event*. Thus, you get a constant number.

I have copied more records into the table (14,400) and it is pretty fast I think. Kindly find what I just explained in the attached database. Three Variables are included: (1)The Form is now displayed as Hidden, so it won't be noticed.(2)The Invoice Number is now constant for all invoices. (3) More Records.

Your Code should be alright now (after placing the "Next Inv No" field in a visible area).

Private Sub Form_Load  'Use Form_Load
Forms![Invoice Query]![Inv No] = Forms![Invoice Query]![Next Inv No]
Forms![Invoice Query].Recordset.MoveNext
Loop Until Forms![Invoice Query].Recordset.EOF
DoCmd.Close acForm, "[Invoice Query]"
End Sub

Hint:It is better not to use space when naming objects. One simple advantage is that you won't need to enclose instances in brackets and your code would be:

Private Sub Form_Load  'Use Form_Load
Forms!InvoiceQuery!InvNo] = Forms!InvoiceQuery!NextInv No
Forms!InvoiceQuery.Recordset.MoveNext
Loop Until Forms!InvoiceQuery.Recordset.EOF
DoCmd.Close acForm, "InvoiceQuery"
End Sub

Keep in mind that if "Next Inv No" is not set as fixed number, every record will be given the next DMAX number.


Best Regards,
Ali

[NeoPa]

Assuming you have the invoice number available in a long variable (lngInvNo) and also that you want to reset the selections on the selected records after updating them with the invoice number and date, then the following code (changing names where necessary) should create the SQL for you :

Dim strSQL As String
Dim lngInvNo As Long

lngInvNo = Nz(DMax("[InvoiceNumber]", "YourTable"), 0) + 1
strSQL = "UPDATE [YourTable] " & _
         "SET    [Selected] = False " & _
         "     , [InvoiceNumber] = " & lngInvNo & _
         "     , [DispatchDate] = Date() " & _
         "WHERE ([Selected])"
Call CurrentDb.Execute(strSQL)

[Nick Cooper]

Thanks Liimra for all the assistance you have given. I have learnt much from your input, particularly about improving the user inteface, but the code from NeoPa worked off the shelf and could be incorporated directly into my existing database design with minimal modification. I'm very grateful to you both for your time.
Cheers
Nick

[liimra]

Welcome

You are welcome Nick. Happy it worked for you and glad I have added some value to this post and hope it will be helpful for others in future.

Please choose NeoPa's answer as "best answer" so this post appears as solved (more helpful for others).

Regards,
Ali

[NeoPa]

I've been away, but I'm glad to return and find this all resolved nicely :) Good for you Nick.