Information from a row on a table to transfer/show on a form using a single selection

[ScoobyJoe82]

How do you get all the information from a row on a table to transfer/show on a form using a single selection?


I was assigned the task of creating an Access Database to track customer feedback for my company. We call our customers monthly to see how things are going in regards to the use of our product.


I have created a table with the customer name and contact information, one customer in each row with five columns of contact information. What I am trying to do is, on the form select the customer name & by doing so have their contact information appear/come up along with it. I am fairly new to Access & unfortunately am not sure how this would be done.



Please Help!

[stmoong]

There might be another way. What I usually do is this.

1) Create a form based on the table.

2) Delete the key field from the detail section.

3) Create a combo box at the header section to list the key field.
- In the combo box Format property sheet:
- Set the Column Count to 2
- Set the Column Width to 0";5" (feel free to replace 5 with a suitable with to display the cust_name
- Essentially, we have 2 columns, but we hide the first column that contains the cust_id
- By default, Bound Column is 1, so you get the cust_id when you get value using Me.cmbCustomerName

4) Set the form Record Source to the Customer table, eg. tblCustomer.
- In the form Format property sheet, also set Record Selectors and Navigation Buttons to No

5) In the form load event, set the record source for the combo box and the form. Something like this:

With Me.cmbCustomerName
    .RowSource = "SELECT cust_id, cust_name FROM tblCustomer"
    .Value = .ItemData(0)   'Select the first item
    .Requery
End With
 
'Here, we refresh the form detail based on selection of the combo box
Me.Filter = "tblCustomer.cust_id='" & Me.cmbCustomerName & "'"
Me.FilterOn = True
Me.OrderBy = "cust_name"
Me.OrderByOn = True
Me.Requery

6) In the combo box click event, add something similar.

'Here, we refresh the form detail based on selection of the combo box
Me.Filter = "tblCustomer.cust_id='" & Me.cmbCustomerName & "'"
Me.FilterOn = True
Me.OrderBy = "cust_name"
Me.OrderByOn = True
Me.Requery

NOTE: Replace table name and column name with the ones you use.