Record lookup with 2 Combo boxes

[BarbQB]

Hi all. I working with an existing access DB at my job and have been asked to add some forms and queries. I've been doing well so far, but this last part of the project has got me stuck.

My question is I am trying to create a form to look up a record based off of the selections in 2 combo boxes. ComboBox1 is a list of customer ID's and ComboBox2 is a list of products based off the selection of the customer ID. ComboBox1 is from the Customers table and ComboBox2 is from the Requirements table.

I would like the fields in my Form to populate with the information from the appropriate record based off of the 2 combo boxes, i.e. Customer is chosen from ComboBox1, ProductID is chosen from ComboBox2 and then the record appears in the textboxes.

I have tried making the form bound to a query or table but when I do this I am either not able to get any records to populate the fields or when i open in the form I can't see any of the controls.

I'm not really sure what I'm doing wrong, but it's pretty frustrating.

Thanks for any help :)

[Mr Key]

Hi!
See the attached database in the post to follow

[Mr Key]

You may use this attached 2Combobox to select your records. COMBO1(Lname)Rowsource=SELECT [Details].[CustomerID], [Details][Lname] FROM Details; and COMBO2 (CustomerID) RowSource=SELECT [Products].[ProductID], [Products].[CustomerID], [Products].[ProductName] FROM Products WHERE Forms!Details!CustomerID=[Products].[CustomerID]; I have include refresh button to look for the latest data in the records. I hope this might solve your problem.
Mark it as answered incase it has or you may ask for more clarifications
You are welcome!!!
Open the attachment below

[BarbQb]

Hi Mr Key, thanks for the help. I only have Access 2003 so I am not able to open the attached DB.

[Mr Key]

Let me send it in 2003 format.mdb

[BarbQb]

Hi Mr Key. For some reason my computer won't open the new attachment, but I was able to get everything working from the Rowsource info you put in your post.

VBA turns out like this:

Private Sub Toy_AfterUpdate()
 
Me.Refresh
 
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[TOY_ID] = '" & Me![Toy] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I put this in for the refresh, I don't know if you had anything different.

Private Sub Customer_Click()
Me.Refresh
End Sub

[Mr Key]

You should have two forms, Main form and its Subform, Combobox 1 at main form and Combobox2 at SubForm, If you choose record source from two different tables without explicity point out the records source; you may endup with no records.
Set your mainform RecordSource to tblCustomer and SubForm to tblProducts and then set the following:
1. Customers_Combo
RowSource=SELECT [Details].[CustomerID], [Details].[Lname] FROM Details; and
AfterUpdate Event is a Macrofor RecordSearch (VBA may do also)
2. Products_Combo
RowSource=SELECT [Products].[ProductID], [Products].[CustomerID], [Products].[ProductName] FROM Products WHERE Forms!Details!CustomerID=[Products].[CustomerID];
and AfterUpdate event SearchforRecords in this SubForm
3. This system will works only once without RefreshButton, You may use any code or Macro to Refresh the records so that to update Products_Combo, otherwise you will endup with no recordschange in the Subform.
4. This approach is valid only if you have form and Subform otherwise you might use other approach to handle this.

[BarbQb]

Thanks again Mr Key.

I was able to get the two combo boxes to work off of one form. The form is bound to the second table.

Below are the steps I took:

ComboBox2 is based off of the selection from ComboBox1

COMBO1 Rowsource
SELECT [Table1].[Customer], [Table1].[CustomerID]
FROM Details;

Make the form bound to Table2

Create 2nd combo box
Choose: I want this to look up a record

Edit Rowsource
COMBO2 RowSource
SELECT [Table2].[ProductID], [Table2].[CustomerID]
FROM Table2
WHERE Forms!FormName!Combo1=[Table2].[CustomerID]
ORDER BY [Table2].[ProductID];


Edit VBA

Private Sub Control_AfterUpdate()
Me.Refresh
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Product_ID] = '" & Me![Product] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

[Mr Key]

Yes its, what important here is a REFRESH part of a form. No refresh no records update no solution.
Gooday!

[NeoPa]

Check out Cascaded Form Filtering. There are examples there too.

PS. Refresh will generally not be of any great help. .Requery is required to get any new records. Check out the difference in Access Help.

[Mr Key]

Yep, nice article