How to display Query results on a Form?

[harrisharris]

HI,

I want to design a form that alows me to select a customer and then show all the records for that customer.

Thanks in advance

chris

 

[gwinn7]

Suggestion...

Create a form with a Combo-Listbox and set the RecordSource property of the Combo to the Customer database. Set the Combo box so that there are two columns. The first column will be your record identifier. The second column will be the friendly name of the customer. Example:

With cboCustomer
.RecordSource = "SELECT ID, Name FROM Customer;"
.ColumnCount = 2
.BoundColumn = 1
.ColumnWidths = 0;1
End With

In the "cboCustomer_BeforeUpdate" event apply something similar to below...

Private Sub cboCustomer_BeforeUpdate(Cancel As Integer)
<control or form object>.RowSource = _
&quot;SELECT * FROM Orders WHERE OrderCustomerID = &quot; & Me.cboCustomer&quot;
<control or form object>.Refresh ' Or Requery!
End Sub

HOW IT WORKS:

You set the bound column property in the Combo-List box so that the Customer ID can be reused in the 'BeforeUpdate' event.

When the user selects the customer from the list, the 'BeforeUpdate' event is triggered. The code in the event procedure sets the Rowsource property of the control, Listbox or Subform, to show the ORDERS in the contents.

The Requery or Refresh method allows the control/subform (whichever you use to display the records!) to repaint the records in the control for viewing.

Is this helpful?