Auto Fill Customer Info Based on Drop down box (1 Viewer)

[Zacharyjuniorman]

Here is my situation. I have a table that holds the customer information. This table includes customer id, customer name, customer city, customer state, customer zip, and customer phone.

I created a new form that will be used to track customer complaints. Because we deal with the same customers over and over I wanted to and these fields to a form with other fields from other tables. I want the user to select from a drop down box that is driven from the customer name and create an event that will fill the remaining customer data in. The information must be stored in that form.

Here is the vb code I wrote and it does not seem to work. I am very new to vb so please forgive me.Private Sub cboCustomerName_AfterUpdate()
'Auto Insert customer data based on a value selected in the CustomerName combo box.
Me!CustomerName = Me(CustomerName).Column(1)
Me!CustomerAddress = Me!CustomerAddess
Me!CustomerCity = Me!CustomerCity
Me!CustomerState = Me!CustomerState
Me!CustomerZip = Me!CustomerZip
Me!CustomerPhone = Me!CustomerPhone
End Sub

 

[Matthew Snook]

I think the way to do it in a relational database is to leave the information in the customer table. On your "complaint" form, create a "customer" combo box that will use a lookup: you select the name, and the box returns the customer ID. If you attach the related fields from the customer table, all the correct info will be presented on the form. The only customer info actually stored in the complaint table is the customer ID, the rest resides in the customer table.

Matt

 

[pcdummy]

Here you go:

Table 1:

CustomerID
Address
Etc
Etc

Table 2
ComplaintID
CustomerID
ComplaintETC
ComplaintETC

One to many relationship table 1 customerID to table2 customerID

create query from these two tables (paramater where either id or name (whatever) is criteria (will be an unbound combo box from a form you will create)

create main form unbound

create subform bound to query you just created

on the OnUpdate event of your combo box
Me.SubFormName.Requery

There you go!!

HTH


create combo box on your query