Auto Fill a Form Field based on a drop down selection

[Guest]

I am new to access and have been searching the messages for an answer. I
have gotten part of the answer but can't the rest of the way...any help is
greatly appreciated!

Have two tables tbl-customer and tbl-partnnumber

The customer table has a three digit customer number that is the primary key
The part numbers table has a part number field that is 7 digits with the
first three based on the customer, that last four are assigned incrementally.

So what I am trying to do is create a form where you select the customer
from a drop down list. Then based on this number, lookup the last number for
that customer number, add 1 and fill it in a the value.

Tbl-Customer
CustNo CustName
101 Customer 1
102 Customer 2
103 Customer 3

Table-partnumber
PartNumber Description
101-0001
101-0002
102-0001
102-0002
102-0003

For example, selecting customer 101 from the drop down would automatically
put 101-0003 in the PartNumber field.

It seems like I may be able to use the Dmax command in the AfterUpdate Event
for the CustomerNo pull down and set its criteria to get the max value for a
given CustNo selected. Does this approach make sense? If so, could someone
help with the syntax? If not, any advice would be appreciated.

Many Thanks!

 

[Larry Linson]

It seems like I may be able to use the Dmax
command in the AfterUpdate Event for the
CustomerNo pull down and set its criteria
to get the max value for a given CustNo
selected. Does this approach make sense?
If so, could someone help with the syntax?
If not, any advice would be appreciated.

The syntax of DMax is well covered in VBA Help, and, if I recall correctly,
even has Intellisense prompting as you type it. To easily reach the Help, in
the module window, type DMax, place or leave the cursor on it, and press F1.
You may need to create a Query that extracts the Customer Number on which to
base the DMax, unless the Customer Number is also carried separately in the
PartNumber table.

The criteria you specify should refer to the extracted CustomerNo in the
Query, and the value for the criteria should refer to your CustomerNo Combo
Box on the form.

Larry Linson
Microsoft Access MVP