Automatically filling in fields with table info

[gcomp]

I have searched the forum for the amswer to this question and have found something that partially works. I want to input info to a combobox. The table holds partnumber, description,cost and weight. I choose partnumber from the dropdown in the combobox and want description, cost and weight to automatically populate the appropriate fields in the form. I made a query for the appropriate info and used =[partnumber].[Column](1) in the first field and it works fine, but if I try to use the other 2 columns =([partnumber].[Column](2),=[partnumber].[Column](3), nothing shows up! What am I doing wrong?

 

[dkinley]

I am not sure what you mean when you say that you used a query to auto-populate the other controls.

What you can do is in the AfterUpdate property of the combo box control is assign the values to the other controls. E.g.,

Me.txtControl1 = Me.cboComboBox.Column(1)
Me.txtControl2 = Me.cboComboBox.Column(2)
....

HTH,
-dK

 

[boblarson]

If that data exists elsewhere then you likely should NOT be storing it again.

 

[dkinley]

Unless it is tax related.

-dK

 

[khchan3501]

I have searched the forum for the amswer to this question and have found something that partially works. I want to input info to a combobox. The table holds partnumber, description,cost and weight. I choose partnumber from the dropdown in the combobox and want description, cost and weight to automatically populate the appropriate fields in the form. I made a query for the appropriate info and used =[partnumber].[Column](1) in the first field and it works fine, but if I try to use the other 2 columns =([partnumber].[Column](2),=[partnumber].[Column](3), nothing shows up! What am I doing wrong?

I understand what you want, because I'm doing the exact same thing as you.
But I have a problem. It's that if I use this code (see below) more than once in a same form (I mean in different private sub within a same form), it only works on the first part (first private sub), then it doesn't have any response after the second part (private sub) with the similar code.

Private Sub Customer_ID_BeforeUpdate(Cancel As Integer)

Surname = DLookup("Surname", "tblCustomer", "Customer_ID=" & Customer_ID)
FirstName = DLookup("FirstName", "tblCustomer", "Customer_ID=" & Customer_ID)
Address = DLookup("Address", "tblCustomer", "Customer_ID=" & Customer_ID)
Town = DLookup("Town", "tblCustomer", "Customer_ID=" & Customer_ID)
PostCode = DLookup("PostCode", "tblCustomer", "Customer_ID=" & Customer_ID)
PhoneNumber = DLookup("PhoneNumber", "tblCustomer", "Customer_ID=" & Customer_ID)

End Sub

 

[dkinley]

Greetings khchan3501 and welcome to the forums.

The way you are executing DLookups are very unproductive and system taxing. You can preload the data into memory with the query driving the combo box and utilizing the data as I specified in post #2.

It works in one area and not the other? Why are you calling it twice? If you are, then it seems as if you have more inefficiencies being driven in your form design, that is, allowing the user to change their minds halfway through the data edit process.

In the example of code you have provided, you are updating the customer ID, I presume this is not the primary key of the record? If it is, then how are you performing a lookup on a record that hasn't been created?

-dK