I'm having a little bit of a dilema. I don't even know if it can be done, but I thought I'd give it a shot.

I'm pretty new to Access so I'll try to be as clear as I can be, but I think this will get confusing. I have Multiple tables and a form for adding records. The master form has "Case Details" and updates a "Case Details" table. I have a subform that I'm working with, it updates (when I say update I'm refering to writing a new record) the Client information. This is all a database thats been around for almost 10 years and I'm just getting introduced too.

What I'm trying to do (on the Client subform) is have the "Last Name", "First Name", "DOB", and etc fields autopopulate if the "Client ID" number is in the list. (I have another table named "ClientLookup" that I have some client info in. I plan on making this a list of all clients eventually. Right now it's just a limited control group.) However, heres the catch, I don't want them to be restricted to that data. Say if John Doe is client #999 and his address is 123 Main St, I'd like to type in 999 in the client field and get "123 Main St" for an address from the "Client Lookup" table, but I don't want it to be 'written in stone'. More like a auto populated "suggestion", because clients address may change.

I'm assuming that once I figure out how to do it for name, it will work for the rest of the fields I want to populate.

Also, another twist on this, some clients do not have numbers or they arn't known at the time of data entry. So, a null value in the "Client ID" field has to allow for the rest of the fields to be writeable (if that makes sense).

So far, I've played around with combo boxs; tried linking the ID and last name fields (only gives 1-1 relationships as far as I've gotten); and played around with the DLookup function, but I'm sure I don't have a grasp on the power of each of these to solve my problem. Any suggestions, tips, or instructions would be very helpful, and thank you in advance.



Software/Hardware used:
Microsoft Access 2003