469,077 Members | 1,471 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,077 developers. It's quick & easy.

Record lookup with 2 Combo boxes

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 :)
Oct 3 '10 #1
10 5257
Mr Key
132 100+
Hi!
See the attached database in the post to follow
Oct 3 '10 #2
Mr Key
132 100+
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
Attached Files
File Type: zip 2CombosonForms.zip (31.1 KB, 511 views)
Oct 3 '10 #3
BarbQb
31
Hi Mr Key, thanks for the help. I only have Access 2003 so I am not able to open the attached DB.
Oct 4 '10 #4
Mr Key
132 100+
Let me send it in 2003 format.mdb
Attached Files
File Type: zip 2CombosonForms1.zip (32.5 KB, 191 views)
Oct 4 '10 #5
BarbQb
31
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Toy_AfterUpdate()
  2.  
  3. Me.Refresh
  4.  
  5.     ' Find the record that matches the control.
  6.     Dim rs As Object
  7.  
  8.     Set rs = Me.Recordset.Clone
  9.     rs.FindFirst "[TOY_ID] = '" & Me![Toy] & "'"
  10.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  11. End Sub

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Customer_Click()
  2. Me.Refresh
  3. End Sub
Oct 4 '10 #6
Mr Key
132 100+
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.
Oct 4 '10 #7
BarbQb
31
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Control_AfterUpdate()
  2. Me.Refresh
  3. ' Find the record that matches the control.
  4. Dim rs As Object
  5. Set rs = Me.Recordset.Clone
  6. rs.FindFirst "[Product_ID] = '" & Me![Product] & "'"
  7. If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  8. End Sub
Oct 5 '10 #8
Mr Key
132 100+
Yes its, what important here is a REFRESH part of a form. No refresh no records update no solution.
Gooday!
Oct 5 '10 #9
NeoPa
32,154 Expert Mod 16PB
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.
Oct 5 '10 #10
Mr Key
132 100+
Yep, nice article
Oct 6 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by William Wisnieski | last post: by
2 posts views Thread by jim | last post: by
2 posts views Thread by Ausclad | last post: by
7 posts views Thread by Ausclad | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.