search combo box, need code 4 after update to fill rest of form...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with a subform. I placed a combo box for search by Customer's
name. Cust_ID is PK. If the combo box on the form is unbound, how do I get
the form to update if an existing customer is found in the search combo box?
If Joe Smith exists, the rest of the form and subform would fill in his
current info.

The subform contains Cust_Add since there may be more than one address for
each customer. The foreign Key there is the Cust_ID from Customer table.
Thank you for all your help,
Ivan.
 
I have a form with a subform. I placed a combo box for search by Customer's
name. Cust_ID is PK. If the combo box on the form is unbound, how do I get
the form to update if an existing customer is found in the search combo box?
If Joe Smith exists, the rest of the form and subform would fill in his
current info.
Click to expand...

Try deleting the combo box, then be sure the magic wand icon is
selected in the toolbox, and re-add the combo box. Take the option
"Use this combo box to find an existing record".

Or... do it yourself, in several steps:

- Create a combo box cboFindCustomer based on a Query selecting
Cust_ID and the customer name, sorted; perhaps something like

SELECT Cust_ID, [LastName] & ", " & [FirstName] & " " & [Phone]
FROM Customers ORDER BY LastName, FirstName;

to distinguish customers who happen to have the same name (you may
have some other disambiguating field than Phone).

- Set its Control Source to blank (unbound), its Bound Column to 1 (so
it returns the Cust_ID when you select a record), and its ColumnWidths
property to

0;1.5

to conceal the ID and display the text.

- In the Combo's AfterUpdate event put code like

Private Sub cboFindCustomer_AfterUpdate
Dim rs As Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Cust_ID] = " & Me!cboFindCustomer
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Oops! This customer not found", vbOKOnly
End If
End Sub

The subform contains Cust_Add since there may be more than one address for
each customer. The foreign Key there is the Cust_ID from Customer table.
Click to expand...

If the Master/Link Field of the subform is the Cust_ID, it will
automatically display the addresses for the main form's customer
information.

John W. Vinson[MVP]
 
Back
Top