Automatic fill in of data after record found--how, please?

[gusbrunston]

(Beginner) I'm designing a form to enter transactions for clients. I would like for the form to "automatically" show data such as address, city, state, zip, after I've located and entered the appropriate id from a combobox. The box is filled by a select query. That works fine, but I'd love to see the next four or five boxes fill up without having to key in everything. So far, I understand select queries pretty well, and can work with "SELECT DISTINCT ROW" etc.
Thanks for any help.

 

[Telsa]

Seems like you could use the Dlookup function. It will lookup and populate the fields based on the id. You will need to use it for each field you need to autofill.

IT goes a little like this... first create the variable that you will use to populate the field. Also, might be a good idea to create a variable for capturing the ID. Maybe place the code in the OnChange event of your ID field.


Dim intID as Integer ' I'm assuming your ID is an integer
Dim strFName as string

strFName = dlookup("[Yourlookupfield]", "theQueryorTableContainingTheData", "[IDfromTable] = intID&quot

If the ID is text, then you need to put quotes around the variable at end of parameters... like this ..."[IDfromTable] = '" & strID & "'&quot

Does that help??


Mary

 

[gusbrunston]

Thank you. You've pointed me in a direction for study. With more understanding on my part, I'm sure it'll work. Thanks.

 

[NoteSmith]

The secret (without programming) is in the query. Link the two tables with the common field. Use the foreign key in the grid instead of the primary key. Run and test the query by entering an existing customer number into this field. The other fields should fill in.

Create the form using this query as a source. Your transaction table SHOULD not contain any data about the customer except the customer id. (research "rules of normalization".

When you add a combobox lookup field the data will show as soon as you select someone.

If this doesn't make sense, email me at the below address and I will return a simple example.
smithd@dop.state.nj.us