Auto Populate Fields

[chansda]

In my form "Transmit," I have a combo box that obtains values for a user to select from a table called "Merge." When the user selects a perticular value (in this case "XYZ Corp" from my "Merge" table) in the combo box, I want another text box or combo box (not sure what to use to accomplish my goal) to auto populate the corresponding "zip code" for the company selected.

Any ideas??

 

[statsman]

With the form in design view, right click the combo/list box and select
Build Event, then Code Builder.

Select BeforeUpdate from the top right corner.

Enter the following code between the 2 existing lines.

Private Sub Combo55_BeforeUpdate(Cancel As Integer)

[fieldname] = ([Combo55],#)

End Sub
The [fieldname] is the name of the field you want to copy the data to.
Combo55 is the number of the combo box you're using.
The # refers to the column in the combo box. The columns start with number 0 (zero) in the left most column and increases by 1 for each column you move to the right. The third column from the left would therefore be column 2.

If you wished to enter the data from the fourth column from the left in the combo box into a field on your form named CustID you would enter:

[CustID] = ([Combo55],3)


Each column you wish to copy when you select a record in the combo box needs its own line of code.

If you created the combo/list box using the wizard, you had the option of saving one of the columns into a field at that time. If you did this, you don't have to use the code to save that column, it’s already done.

 

[chansda]

Thanks Statsman!

So, I gave it a shot and inserted as instructed ...

Private Sub Combo55_BeforeUpdate(Cancel As Integer)

[Text119] = ([Combo106],2)

End Sub

.. but got the following error "Complie error: Syntax error" Your thoughts?

 

[missinglinq]

There's two problems with the code:

Private Sub Combo55_BeforeUpdate(Cancel As Integer)

[fieldname] = ([Combo55],#)

End Sub

BeforeUpdate

is the wrong event to use, and

[fieldname] = ([Combo55],#)

is not correct syntax, at least not in Access VBA.

It should be in the AfterUpdate event for the combobox, like this:

Private Sub CompanyCombo_AfterUpdate()
 Me.ZipField = Me.CompanyCombo.Column([B]X[/B])
End Sub

where X is the column number for the column holding the Zipcode, as statsman indicated. IF the fields in the combobox, running from left-to-right were

Company, Address, Zipcode

the code would be

Private Sub CompanyCombo_AfterUpdate()
 Me.ZipField = Me.CompanyCombo.Column([B]2[/B])
End Sub

 

[chansda]

Thanks much missinglinq! Worked out perfectly!

 

[missinglinq]

Glad you got it working!