How to create a search form?

[malcprice]

Hi, I wish to create a search form with the following fields

Company
Subsidiary
Application
Customer Type
Country

I would ideally like the user to be able to enter criteria in one, some or all of the fields and then press a button and either view the results in a sub-form or in another form. Is this possible?

If you could help me I would be very grateful.

Many thanks

Malc

 

[KenReay]

Hi

On your form define a text box (say) txtSQL, with visible = false

In the code which opens the form to view the results put code like this:

Dim strWHERE as String
strWhere = ""
If Not IsNull(Company) Then
strWHERE = "Company Like '" & Company & "' "
End If
If Not IsNull(Subsidiary) Then
If Len(strWHere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "Subsidiary Like '" & Subsidiary & "' "
End If
... repeat above IF for each field
txtSQL = "SELECT * FROM tblName " & strWhere
'
then the code to open the view form

in the on open event of the 'view form'

put code
Me.Recordsource = Forms!frmCriteria!txtSQL
Me.Requery

You need to substitute you column names, table names etc

I have assumed in my 'air' code that the column names in the table and on the criteria form are the same, and that they are all text.

I have also assumed that you would be happy with a partial match, hence the use of LIKE if you want an exact match use = instead. Similarly if you want to select if any column matches rather than all, then substitute OR for AND in the where clause.

Good luck










Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[malcprice]

Thanks,

I have two questions.

1) Where do I put the text box on the search form or results form?

2) Opening the results form what is the best way to do this is it ok to use the wizard and create a button or do I need to transfer the criteria which the user has entered in the text across.

Also can I show collums which I am not searching in? or would it be better to add all fields to the search form I wish to display in the results form?

If you can help with this I would be very grateful

Many thanks

Malc

 

[KenReay]

Thanks,

I have two questions.

1) Where do I put the text box on the search form or results form?

this is on the search or criteria form:

On your form define a text box (say) txtSQL, with visible = false



2) Opening the results form what is the best way to do this is it ok to use the wizard and create a button or do I need to transfer the criteria which the user has entered in the text across.

this is in the results form, you do not need to 'pass' the criteria, you reference them from the results form on the criteria for, you leave the critirea form open, if you do not want user to see it set its visible property to false:

then the code to open the view form

in the on open event of the 'view form'

put code
Me.Recordsource = Forms!frmCriteria!txtSQL
Me.Requery



Also can I show collums which I am not searching in? or would it be better to add all fields to the search form I wish to display in the results form?

If you use SELECT * as I induicated, then all columns in the table (or query) will be available for display, not just those searhed on

If you can help with this I would be very grateful

Many thanks

Malc Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[danvlas]

You could try:
ftp://ftp.artrom.ro/SearchForm.zip

It can be used to search/filter any form or datasheet and filter the reports on the fly. Just finished it yesterday.
For Access 2K and XP, make sure you have set the reference to Microsoft DAO 3.6 Object Library.

HTH


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[malcprice]

OK I am a 97 user,

I have tried your code Ken and this is what happens,

Runtime error

The microsoft jet engine control can not find the input table or query "Machine Services Inc" Make sure it exists and spelt correclty.

Here is the open form code I am using is this ok?

Private Sub Detail_Click()
Dim strWHERE As String
strWHERE = ""
If Not IsNull(Company) Then
strWHERE = "Company Like '" & Company & "' "
End If
If Not IsNull(Subsidiary) Then
If Len(strWHERE) > 0 Then
strWHERE = strWHERE & " AND "
End If
strWHERE = strWHERE & "Subsidiary Like '" & Subsidiary & "' "
End If
Dim strWHERE As String
strWHERE = ""
If Not IsNull(Application1) Then
strWHERE = "Application1 Like '" & Application1 & "' "
End If
If Not IsNull(Country) Then
If Len(strWHERE) > 0 Then
strWHERE = strWHERE & " AND "
End If
strWHERE = strWHERE & "Country Like '" & Country & "' "
End Sub

End Sub
Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "search form1"

stLinkCriteria = "[Subsidiary]=" & "'" & Me![Text6] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

Please help or is there another way to do this?

Many thanks

Malc

 

[KenReay]

Hi

well not quite, but see below

Private Sub Detail_Click()
Dim strWHERE As String
strWHERE = ""
If Not IsNull(Company) Then
strWHERE = "Company Like '" & Company & "' "
End If
If Not IsNull(Subsidiary) Then
If Len(strWHERE) > 0 Then
strWHERE = strWHERE & " AND "
End If
strWHERE = strWHERE & "Subsidiary Like '" & Subsidiary & "' "
End If

If Not IsNull(Application1) Then
strWHERE = "Application1 Like '" & Application1 & "' "
End If
If Not IsNull(Country) Then
If Len(strWHERE) > 0 Then
strWHERE = strWHERE & " AND "
End If
strWHERE = strWHERE & "Country Like '" & Country & "' "
End Sub

End Sub
Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "search form1"
' WHY HAVE YOU GOT THIS IN HERE ????????????
stLinkCriteria = "[Subsidiary]=" & "'" & Me![Text6] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command9_Click:
Exit Sub

you also need to code I gave you in the earliuer post in the on open event of the results form

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[MikiH]

Hi

I've done this in the past using a query.

1) Create a unbound form with all the seach field required.

2) Create a query that contains all the fields that you require.

3) Add to criteria of query - if isnull(forms![searchform]![text1]) & "*" & (forms![searchform]![text1])

(need to check this criteria, haven't got application i used this in. If you have problems reply and I'll look it up)

4) Add button to search form to open up new form/report.



The "if isnull" criteria allows the user to enter only the fields they want also the & "*" allows the use of wildcards.



Hope this helps.

Mick

Create a form/report on this query.