search using form with two fields

ndmark

New member
Local time
Today, 16:25
Joined
Dec 24, 2008
Messages
7
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.
 
Bob - I took a look at your example and noticed that there were three different tables involved. Will it still work if there is only one table? This is the situation that I have. Thanks again for your help.
 
I can post the table but I have to make some changes to the customer names as the information is confidential. I will post it by tomorrow. Thanks again.
 
I can post the table but I have to make some changes to the customer names as the information is confidential. I will post it by tomorrow. Thanks again.

Not just the table, but what you also have already for a form.
 
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
 
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.
 
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.
 

Attachments

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
 
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.
 
If the column width is set to a blank it should display the column the same width as the combo box. I am not sure what happened there.

Glad it worked out for you.

Art :)
 
Art - you're right. I must have changed something else that caused it to be a blank field. It's working great - thanks again.
 

Users who are viewing this thread

Back
Top Bottom