CODEWITHSUNDEEP
vbams-access
Mark Buikema
Mark Buikema

Reputation: 2540

Access Form show other table's fields of selected combobox item

I am new to Microsoft Access. This is the structure I have:

Table Suppliers with fields Name, Address, Zip, PhoneNumber
Table Purchases with fields Supplier, Address, Zip, PhoneNumber (and some other irrelevant ones)

The Supplier field in the Purchases table should contain a reference to a record in the Suppliers table.

Now I am making a form so the user can enter Purchases records. It has a combobox to choose the Supplier and some text fields for Address, Zip, PhoneNumber. What I want is that when the user selects a Supplier, the values for the Address, Zip, PhoneNumber will be copied from the selected Supplier to the corresponding text fields in the form.

How can I achieve this result using Visual Basic code?

Upvotes: 1

Views: 1742

Answers (1)

TheSmileyCoder
TheSmileyCoder

Reputation: 160

First a word of caution. Copying date is sometimes the wrong approach, as it breaks relational integrity of your data. That said, sometimes there can be perfectly valid reasons for doing so. I won't go further into that here.

One of the simplest ways is to ensure you have the data available in the recordsource of your Supplier combobox. They do NOT need to be visible, you can set the column width to 0 to hide them. Lets pretend your recordsource is:

SELECT Suppliers.[Name], Suppliers.Address, Suppliers.Zip, Suppliers.PhoneNumber from Suppliers

Now in the combobox AfterUpdate event we copy the data.

Private Sub SupplierCombo_AfterUpdate()
  If IsNull(Me.SupplierCombo) Then
    Me.txtAddress=Null
    Me.txtZip=Null
    Me.txtPhoneNumber=Null
  Else
    Me.txtAddress=me.SupplierCombo.Column(1)
    Me.txtZip=me.SupplierCombo.Column(2)
    Me.txtPhoneNumber=me.SupplierCombo.Column(3)
  End If

End Sub

If your textboxes are bound, you are done. If they are unbound, you will also need code in the forms Current event to ensure the textboxes update as you move from 1 record to the next.

Hope that Helps

Upvotes: 3

Related Questions