Field Update

[jmclozano]

I might have a silly question but I have to ask ...

I have one field AccountName in customer table and another field AccountID.

In my form I would like to select from the combo box AccountName during data entry and then have the AccountID automatically update in the Account ID field.

Please let me know if I can do this.

thanks - Jennifer
(newbie)

 

[Alansidman]

Jennifer;
Go to this link and scroll down to the three lessons on Combo boxes.
http://www.datapigtechnologies.com/AccessMain.htm

Alan

 

[pr2-eugin]

You can do this using some bit of coding maybe use the AfterUpdate event of the ComboBox with the combination of DLookUp..

But a better solution would be to a simple restructuring of your table maybe? I am not sure how your table is structured, but my guess is as follows..
tblCustomers:
custID
accNo
accName
(..all other related fields of customers..)

You may or may not have the following table, but based on your description, I think the combo box is getting its value from somewhere, might as well be this table.. (Again this is just a guess)
tblAccounts:
accNo
accName

If you do not have a seperate table as such I would suggest to change the design as..
tblCustomers:
custID - PK
accNo - FK
(..all other related fields of customers..)

tblAccounts:
accNo - PK
accName

So tblCustomer is related to the tblAccounts with a common accNo, so if you have this structure..
(a) There would be no coding to gather the information, changing the Query of the combo box, setting the column widths, bound column information would be enough..
(b) A perfect Relationship would be created thus eliminating the need to store ID and Account name in the Customer table.

If you do need to see the Account name, all you need ot do is a simple JOIN.. I hope this helps.. Post back if you have any troubles..