I'm relatively new to access and am having troubles designing a form that allows you to search a table. My table has 10 fields, including a custname field and datepurch field. Most custnames have made multiple purchases but only one purchase on any selected date. I'm trying to design a form whereby the user can pull up a unique record by selecting first the custname which would then narrow the search and show only the datepurch field for that custname. After selecting both the custname and the datepurch fields the other 8 fields in the form would then be populated to show that one specific record. I have tried using two combo boxes and linking them but have not been successful. Can anyone recommend an online tutorial or a coding example that would help. Thanks.
search using form with two fields
- Thread starter ndmark
- Start date
boblarson
Smeghead
- Local time
- , 18:28
- Joined
- Jan 12, 2001
- Messages
- 32,059
I have a cascading combo box example that perhaps might help.
http://downloads.btabdevelopment.com/Samples/combos/CascadingCombosSample.zip
http://downloads.btabdevelopment.com/Samples/combos/CascadingCombosSample.zip
boblarson
Smeghead
- Local time
- , 18:28
- Joined
- Jan 12, 2001
- Messages
- 32,059
Can you post your db here?
How to upload a database to the forum
How to upload a database to the forum
boblarson
Smeghead
- Local time
- , 18:28
- Joined
- Jan 12, 2001
- Messages
- 32,059
Not just the table, but what you also have already for a form.
ColinEssex
Old registered user
- Local time
- Today, 01:28
- Joined
- Feb 22, 2002
- Messages
- 9,349
You should only have the customer names in the main table indexed with an Autonumber to give a unique number. The rest of the information should be in separate tables linkled to the Customer table.
Sounds to me like you have the same customer name many times in just one table (like a spreadsheet) which is incorrect as this will lead to problems when querying the data (as you have found out)
Col
Sounds to me like you have the same customer name many times in just one table (like a spreadsheet) which is incorrect as this will lead to problems when querying the data (as you have found out)
Col
That's the problem that I inherited - that all of the data is in one table. You're right that I'm having a problem with the queries as a result.
Will post the database with the form by tomorrow, I just have to clean it up.
That's why I was wondering if there was an example where all of the data is just in one table.
Will post the database with the form by tomorrow, I just have to clean it up.
That's why I was wondering if there was an example where all of the data is just in one table.
Attached is the modified database. Using just the one table, I just want to have a form that allows the user to fist pick the customer name and then the next combo box would only show the puchasedates for that customer. After the user selects the purchase date, the rest of the form would then be filled with the data from that record to allow the user to modify just that record.
Hello,
You could try this.
On Form1 set Combo18 row source to
'SELECT sales.CustName FROM sales GROUP BY sales.CustName;'
column count to 1, column width to nothing, after update to
Me.Requery
Me!CmbPurchDate = ""
Me!CmbPurchDate.Requery
Set CmbPurchDate row source to
'SELECT sales.PurchDate FROM sales WHERE (((sales.CustName)=Forms!Form1!Combo18));'
column count to 1, column width to nothing, after update to
Me.Requery
Set the Form1 record source to
'SELECT sales.CustName, sales.PurchDate, sales.Rec, sales.Office, sales.Status, sales.Saaat, sales.NOTES, sales.Num, sales.Num2, sales.mww, sales.ma FROM sales WHERE (((sales.CustName)=[Forms]![Form1]![Combo18]) AND ((sales.PurchDate)=[Forms]![Form1]![CmbPurchDate]));'
Hope this helps.
Art
You could try this.
On Form1 set Combo18 row source to
'SELECT sales.CustName FROM sales GROUP BY sales.CustName;'
column count to 1, column width to nothing, after update to
Me.Requery
Me!CmbPurchDate = ""
Me!CmbPurchDate.Requery
Set CmbPurchDate row source to
'SELECT sales.PurchDate FROM sales WHERE (((sales.CustName)=Forms!Form1!Combo18));'
column count to 1, column width to nothing, after update to
Me.Requery
Set the Form1 record source to
'SELECT sales.CustName, sales.PurchDate, sales.Rec, sales.Office, sales.Status, sales.Saaat, sales.NOTES, sales.Num, sales.Num2, sales.mww, sales.ma FROM sales WHERE (((sales.CustName)=[Forms]![Form1]![Combo18]) AND ((sales.PurchDate)=[Forms]![Form1]![CmbPurchDate]));'
Hope this helps.
Art
Art - Thanks for your help. The only question I have is your instruction for Combo18 says to set column count to 1 but column width to nothing. When I did that, nothing showed up in the box, but after changing the width to 1" then the selections appeared. Did I do something wrong and got lucky when I changed it to 1"? Thanks again, you saved me a lot of time.
Similar threads
