Hi wondering if someone can suggest the best way to do the following...
I have a combox with customer names, when 1 is selected the name is inserted into a text box on a sub form. As well as that a sql query is run and the customerID is inserted into a table (CustomerOrder).
What I need to do is get the OrderID from the table.
Teddy was, I think, pointing you in the right direction. However, maybe the DMax function will be best suited to the particular problem. I am presuming that the autonumber is incremental and this function will be run immediatley after the insertion, otherwise the following fails. It also assumes you have the CustonerID value in a text box (called txtCustomerID) on a form:
Given the additional criteria, I think the DMax() function would indeed be a more efficient way around the situation. I should add that depending on the timing of the function, you may need to increment the value returned by DMax() by 1. The reason I suggest this, is until you move to the next record in your subform, the record does not exist for the purposes of a dblookup. Therefore you may have to "artificially" determine what that next number is going to be. The statement then becomes: