Changing Query Criteria through a form

[bwyrwitzke]

I'm still new to MS Access and VB and am struggling to get my search page working. Does anyone know how to to return records that match multiple criteria?

Here's how things are set up...

My table, ModemSpecsTbl, has various fields that are yes/no. Examples of fields include XBand, KaBand, BPSK, QPSK, etc...

The query, ModemSpecsQry, alphabetizes the table, etc

The form, ModemSpecsFrm, shows all the categories from the query

I also have a form SelectFrm, where I would like the user to be able to use check boxes to select criteria he/she would like to see.

Now for the question - How do I change the query criteria on the fly using the SelectFrm? Any help would be appreciated. Thanks.

Bruce

 

[Hayley Baxter]

Are you familiar with Query by Form? I use this alot when searching for multiple criteria. This means you choose your search items in the form and your results then go to the query. I usually have the results dispaly in a report rather than the query. I have a good example of this that I can send onto you.

Hay

 

[bwyrwitzke]

I'm familiar with it, but I'm trying to make this as easy as possible on the user by limiting the fields that can be searched, hence the check boxes. If you think, there's a way to use filter by form that may work, please let me know. Thanks.

Bruce

 

[miguel vasquez]

Hayley, can you please tell how you do that? Thanks!
Are you familiar with Query by Form? I use this alot when searching for multiple criteria. This means you choose your search items in the form
and your results then go to the query. I usually have the results dispaly
in a report rather than the query. I have a good example of this that I
can send onto you.

 

[bwyrwitzke]

Partial answer...

Okay, here's what I did, but I'm still not all the way there.

I used the expression builder in the query criteria section and entered the following:

[Forms]![FilterForm]![CheckX] (I did this for each of the criteria).

Now, my problem is only the records that are an exact match come up. Example, the modem that works with both X- and Ka-band only shows up when both X and Ka are checked off. Any help on this new dilemma would be appreciated. Thanks.

Bruce

 

[Hayley Baxter]

I posted the code I use in this post, have a look at this and see if this helps. If not send me an email for the example as it is too big to attach to the forum. Please remember to tell me which version of access you are using if you want the sample.

http://www.access-programmers.co.uk/forums/showthread.php?threadid=34124

Hay

 

[miguel vasquez]

Hi Hay, I am using Access 2000. From my form I want to call a Customer, so I have a Combo Box, I want to click the customer and get the report.
Is that possible? Thanks!

 

[bwyrwitzke]

Thanks Hayley!

I'll give it a try and let you know how it works. Thanks again.

Bruce

 

[bwyrwitzke]

It works! Here's the code...

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

strSELECT = "s.* "
strFROM = "tblTerminalSpecs s "

If CheckX Then
strWHERE = strWHERE & " AND s.[x-band] =" & True
End If

If CheckKa Then
strWHERE = strWHERE & " AND s.[Ka-band] =" & True
End If

If CheckBPSK Then
strWHERE = strWHERE & " AND s.BPSK =" & True
End If

If CheckQPSK Then
strWHERE = strWHERE & " AND s.QPSK =" & True
End If

If CheckOQPSK Then
strWHERE = strWHERE & " AND s.OQPSK =" & True
End If

If Check8PSK Then
strWHERE = strWHERE & " AND s.[8PSK] =" & True
End If

If Check16QAM Then
strWHERE = strWHERE & " AND s.[16QAM] =" & True
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)

BuildSQLString = True

End Function


Private Sub cmdFind_Click()

Dim strSQL As String

If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If

CurrentDb.QueryDefs("qryTerminalSearch").SQL = strSQL

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTerminalSeachResults"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub