Driving Me Nuts - Form Default Value from Another Table?

[Coaltown]

This is probably really simple and something someone asked three days ago, but I've hunted high and low and can't seem to find an answer ...

I have a form which would be more efficient if a certain value were populated from a table unrelated to the table on which the form is based, after the user selects one of the fields on the form. I have tried doing this by populating the form with a query, but since it involves multiple tables, it won't update.

Specifically, the form has to do with ordering cards with sequential numbers, which differ depending on which kind of card it is. Of course the table regarding the cards contains the last used number, but I can't figure out how to get that piece of data onto my form, which is based on the card request table rather than the card info master table. I would like for the last number plus one to pop up as the default value for the starting number of the next shipment once the customer selects which customer and card type. The link is by customer id and card type id.

Any help would be mucho appreciated.

 

[LittleSmudge]

Assuming you have an control on the form called txtCardNumber that is to contain this MaxCardNumber + 1

then
in a suitable event procedure ( - like the GotFocus event for the control that you want to user to 'click in' in order for this thing to happen put - ) :-

If IsNull(txtCardNumber) Then 'This prevents repeat upgrades of the No if user click back in the control a second time.
txtCardNumber = Nz(DMax("CardNo","tblCardData","CustomerId = " & lngCustId & " AND CardTypeId = " & lngCardTypeId),0) + 1
End If

Change tblCardData to your table name
Change CardNo to the field in tblCardData that contains the CardNumber.
Change lngCustId & lngCardTypeId to the appropriate variable names.


'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.