Having field automatically complete other fields 2

[althea]

I have two tables with a one to many relationship. In a form, I want the value of one field to automatically complete other fields. For example, if an ID is selected, I need the Name, Phone number and office number fields to be completed. I was thinking of using a macro with "GoToControl" and then "SetValue" but I can't get the expression right. Any suggestions?

 

[willir]

althea

In many situations, with a normalized database, it is not necessary to "copy" the data from table an insert it into another table.

For example, when creating an invoice, the data entry person would select the customer. The invoice would then using the address associated with the customer for the invoice. The invoice would only have a reference, a foreign key in the invoice file that links to the address.

For this situation, rather than populate combo boxes which can be done using the customer number as the reference, I suspect it would be more typical to have a subform that includes the address plus customer number. The subform is linked to the main form with the customer number.

If your needs are different, and / or, your database is not normalized (a design process which includes the removal of unecessary data such as redundent / duplicated data), then review the use of the function [blue]DLookup[/blue] in Access.

Richard

 

[ManojP]

Hi althea,

You can use DLookup Function on AfterUpdate event of the control to retrive other values from the table. Below Code may solve your problem

DLOOKUP("[Name]","[TableName]","[ID]=" & Me.ID
DLOOKUP("[PhNo]","[TableName]","[ID]=" & Me.ID
DLOOKUP("[OffNo]","[TableName]","[ID]=" & Me.ID

HTH

Manoj

 

[althea]

Thanks! The subform is what I needed.

 

[jiejie]

I have a similar question to althea's. I have two combo boxes on a form, one is sourced from a table listing of all states, and the other one is sourced from a query that pulls out the zip codes of the state chosen on the first combo box. I then set a command to rerun that query every time a different state is chosen in the first combo box (at the OnChange event), supposing that the second combo box would only pick up those zip codes that reside within the state chosen. This only works the very first time when the state combo box is populated, but the zip code combo box never update again when I pick a different state. Any ideas?

 

[willir]

jiejie

Two thoughts...

- Search this site and others for "call back function" which will give you an idea on one way to accomplish your task. This code is also in many advance Access books. Also, "Not in List" code may also give you some ideas.


- One thing that is important for this type of activity event is to use the requery method.

For the first comb box, for the After Update event, include

me.YourComboBox2.requery
in your code

This should repopulate the query based on the first combo box provided your code is correct.

Richard