Query Spamming

[benu302000]

I would like to create a query that allows the ability to search for some number of different parameters of data (Through User Input) but without spamming the user with parameter requests. Is there anyway to design a query so that a user can perhaps choose what criteria to search by?

 

[kjv1611]

Create a form with text boxes or combo boxes for the user to update, then have a button, or use one of the controls' after update events to run the code to create the query.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PHV]

Do a search for query by form

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[benu302000]

Thats not quite what I need. I intend to use the query as the source for a form I've already created

 

[KenReay]

Hi

OK, in that case, advice given thus far is still sound

Use a form to gather the query parameters, build the sql string on the fly, and pass it to the form you have already built, then use it to reset the forms recordsource, something like

Me.Recordsource = Form!MyCriteriaForm!txtSQL

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[benu302000]

Ok, I'm trying to work around the whole query thing by using the "where" parameter for docmd.openform

I have a bunch of fields on the criteria form, and in this block I am attempting specify which records to bring up in an already created form, and it seems to work, kinda. It doesn't throw any errors, but it still just keeps bringing up the whole table instead of just the parts I want. Need some fresh eyes, its been a long day.

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Search Form Result"

If Not (Me.Billet_Number = Null) Then
stLinkCriteria = stLinkCriteria & "[Billet Number]=" & Me![Billet Number]
End If

If Not (Me.Org_Code = Null) Then
If Not (stLinkCriteria = Null) Then
stLinkCriteria = stLinkCriteria & " and "
End If
stLinkCriteria = stLinkCriteria & "[org code]=" & Me![Org_Code]
End If

If Not (Me.Org_Title = Null) Then
If Not (stLinkCriteria = Null) Then
stLinkCriteria = stLinkCriteria & " and "
End If
stLinkCriteria = stLinkCriteria & "[org title]=" & Me![Org_Title]
End If

If Not (Me.Series = Null) Then
If Not (stLinkCriteria = Null) Then
stLinkCriteria = stLinkCriteria & " and "
End If
stLinkCriteria = stLinkCriteria & "[series]=" & Me![Series]
End If

If Not (Me.Grade = Null) Then
If Not (stLinkCriteria = Null) Then
stLinkCriteria = stLinkCriteria & " and "
End If
stLinkCriteria = stLinkCriteria & "[grade]=" & Me![Grade]
End If

If Not (Me.Step = Null) Then
If Not (stLinkCriteria = Null) Then
stLinkCriteria = stLinkCriteria & " and "
End If
stLinkCriteria = stLinkCriteria & "[step]=" & Me![Step]
End If

If Not (Me.FPL = Null) Then
If Not (stLinkCriteria = Null) Then
stLinkCriteria = stLinkCriteria & " and "
End If
stLinkCriteria = stLinkCriteria & "[fpl]=" & Me![FPL]
End If

If Not (Me.Title = Null) Then
If Not (stLinkCriteria = Null) Then
stLinkCriteria = stLinkCriteria & " and "
End If
stLinkCriteria = stLinkCriteria & "[title]=" & Me![Title]
End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

 

[PHV]

Replace this kind of syntax:
If Not (Me.Billet_Number = Null)
By this:
If Not IsNull(Me.Billet_Number)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244