Full Version: Do I use autolookup?
UtterAccess Forums > Microsoft® Access > Access Forms
I have a question and I'm hoping that you guys can provide me with some insight. I asked a similar question before, but I decided to simplify things a little bit but I still can't figure this part out.
I have 2 tables:
- Tracking Table. It's for tracking cases ordered by clients. Has many fields, including Client ID and various address fields
-Client Addresses table. I use this to store the addresses of my clients. It has Client ID (primary key), Company Name, and various address fields
I have a form whose record source is the Tracking table. On one part of the form I have a drop down list of my Client ID's. The source for this is a SQL statement: SELECT [Client Addresses].[Client Code] FROM [Client Addresses];
Below this drop-down list, I have the various address fields.
What I would like is that when I choose the Client ID for the address fields to automatically populate with the relevant information from the Client Addresses table and store it in the Tracking table.
Do I have to use autolookup? And if so, how do I do that? I'm very much a novice at doing this kind of thing, so any and all help would be greatly appreciated. thanks
You shouldn't be storing the addresses in both tables. Doing so violates the forms of normalization and generally will cause headaches down the road. You could display the address data, but you only store the PK of the client address table, as a Foreign Key, in your tracking table.
Store the client id in the cases table, not the entire address. Put the name of this field in the Control Source for the combo box and put the number of the column in the Bound Column. It might be easier to use the wizard and make a new one since the wizard asks you what field to use and sets it up for you.
That is autolookup?
Ok, so I've changed my Tracking table to only store the Client ID from the Client address table. But how do I get the address fields in my form to automatically populate when I select the client ID from the drop-down box?
You put them in the combo box along with the client id. They will populate automatically on existing records.
Edited by: projecttoday on Mon Jan 9 14:06:34 EST 2006.
I don't understand what you mean by that. How does that work?
Use the wizard! It asks you everything. The wizard should start when you drop a combobox on the form.
I've got that part already. I have a drop-down box that lists all my Client ID's. Now what I need is when a client ID is selected for all the address fields for that client to automatically populate.
Use the Columns property of the combo box and unbound text boxes. In each unbound text box put a different part of the address. Then use:
where x is the number of the hidden column containing the desired data. Access Help or an Advanced Search of Utter Access should show you in more detail.
Oooooohh.... verrry interesting. Let me try that out.... thanks!
Jack Cowley
Another way to show the data is to create a query base on the two tables. Drag the fields you want to see from your other table and then drag the address fields from the Client table. If the ClientID is a combo box on the form then selecting the client will show their addresses and add only the ClientID to your second table.
My 3 cents worth...
Jack, could you please go into a little more detail as to what you're talking about? I'm not exactly following.
You can do it that way. The form is bound to a query which joins both tables. When you make a selection in the combo box, the corresponding fields on the form are populated. Start by binding your form both to the clients table and the CASES table. then add a combo box bound to the clients table.
Edited by: projecttoday on Mon Jan 9 15:10:58 EST 2006.
To create a query that joins clients to cases, just go into your query design view and click on Add Table and select clients. a line should appear between client id in cases and client id in clients.

FOr you could use the

Edited by: projecttoday on Mon Jan 9 15:13:50 EST 2006.
Edited by: projecttoday on Mon Jan 9 15:15:22 EST 2006.
You'll have to pardon my ignorance, but how do you bind a form to more than one table?
Let me ask you this: do you really need to display the address? It's true in the combo box the address doesn't show until you click but shouldn't that be enough to let you make the correct client selection as you're entering your case?
You bind the form to more than 1 table with the query in the record source.
Oh... my... god... You're right!!
Odidn't realize it, but it isn't necessary at all for me to display the address!
You're a friggin' genius!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.