Auto-Populate an Access form using a table of values

5 pts.
Tags:
Access 2003
Access forms
Microsoft Access
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following