Search Forms Advise

[q582gmzhi]

Hi,

I have the following forms setup:

frm_Main (Switchboard)
frm_Customers (Form)
frm_SalesDetailsSub (SubForm on frmCustomers showing basic details of items they have bought)

On frm_Main I can search for Customers by Surname using the following code behind a search button on the form:

On Error GoTo Err_Handler

Dim stQry
Dim stDocName As String

stQry = SearchSurname() 'Query containing tblCustomers
stDocName = "frm_Customers"

DoCmd.OpenForm stDocName, acNormal, stQry, "((([tbl_Customers].[Customer_LName])like[Customer Surname?]& ""*""))"

Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
Resume Next
End If

This works fine, however I also want to search by items on the subform frm_SalesDetailsSub by using another button on frm_Main. I have tried code like the one shown below.

On Error GoTo Err_Handler

Dim stQry
Dim stDocName As String

stQry = SearchSaleItem() 'Query containing tblCustomers and tblSales Items linked via ID
stDocName = "frm_Customers"

DoCmd.OpenForm stDocName, acNormal, stQry, "((([tbl_Sales_Details].[Sales_Details])like [Item Description?]& ""*""))"

Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
Resume Next
End If

I have tried lots of different variations of code, but can't seem to crack it.

Any advice is welcome.

Daz.....

 

[Oldsoftboss]

Searching is such a problem.

Imagine if you searched here...

http://www.access-programmers.co.uk/forums/showthread.php?t=118820

Dave

 

[q582gmzhi]

Hi,

I have already searched and found this but it not what I am after, because you are searching from a form which as a subform attached.

I want to search from a main form (switchboard)

When I search from the switchboard form for say ItemA:

It open up the Customers form which as a subform that shows this customers sales details. Basically I want it to return all the customers who bought ItemA.

My db is made up of:

tbl_Customers (viewed via Customers form)
tbl_Sales_Details (viewed via Subform within Customers form showing sales details for this customer)

Hope that helps

Daz....

 

[Oldsoftboss]

I have another Db that I will hack a bit to reflect what you are trying to do.

One thing I may suggest is a third table for sale items. Then on the sales details the item MUST be picked from a combo box. This will eliminate the problem of incorrect codes being entered

EG:
29CTD4500
29 CTD4500
29-CTD4500
29CTD-4500

You get the idea.
Each of the above will return a different set of sales, even though the product is the same.

Dave

 

[Oldsoftboss]

Maybe you could post a cut down version of your Db


Dave


.

 

[q582gmzhi]

Dave,

I have attached a cut down version.

Search By Surname is working OK

Search by Item button on the front does not work (trying to search for a sale item and display all customer who bought a particular item)

Thanks

Daz.....

 

[Oldsoftboss]

Will have a look and get back this evening...

 

[Oldsoftboss]

Not perfect, but I am sure you will get the idea.
Have created a new table to hold sale items (as suggested above) and based the search on that.

Dave

 

[q582gmzhi]

Thanks,

I will have a look this evening.

Daz....