Find Query results based on Dropdown Box choices

[MilinPCH]

Hi,

I have a form that is tied to a query. on the form I have two dropdown boxes - one lists all the possible "names" and the other lists all the possible "ranks" I want the user to be able to select a value in each dropdown to search on, then click a button which will find records matching "name & Rank" in the query - displaying the query (where user-selected name And user-selected Rank = these results)

This is what I've tried to piece together, but I get a "misuse of null" error. Maybe I'm close??

thanks!

Private Sub Command9_Click()

Dim stDocName As String
Dim stOne As String
Dim stTwo As String
Dim strsql As String

stDocName = "Query1"
stOne = CmboName.Value
stTwo = CmboRank.Value

strsql = "select Name from Query1" & "where stOne = " & Name & "Select Rank from Query1" & "where stName = " & Rank

DoCmd.FindRecord "Query1", acViewNormal, , strsql

 

[Qaroven]

Well how 'bout the form's query just filtering for the values in the dropdowns (ie where name=Forms!Form1!Name and rank=Forms!Form1!Rank or similar).

It may give problems in that case when first opening, so stick something in the form_load to assign initial values to the dropdowns.

Oh and would need either a button, or afterupdate subs or similar to do a requery on the form.
Ben
+61 403 395 052

 

[Katerine]

Hi MilinPCH,
Try the following SQL:

strsql = "SELECT [Name] from Query1 " & _
"WHERE [Name] = " & stOne & _
" AND [Rank] = " & stTwo

Please let me know if I totally misunderstood your intentions It looks as though that may be what you want.

Also, please note that if "Name" is a text value, you'd need to change WHERE [Name] = " & stOne to WHERE [Name] = '" & stOne & "'" (note: "'", in case it's not very visible, is a double-quote, followed by a single quote, followed by a double-quote. '" is a single quote followed by a double-quote). Same goes for Rank as well.

Also note that this won't work if either of the combo boxes have nothing selected. You might want to disable command button until something is selected in both boxes.

Please let me know if you have any questions.

 

[MilinPCH]

ok Katerine, I guess I'm stupid....

My searching criterias are text, so I'm trying to put in the "'" but it keeps seeing the ' as a comment and commenting the rest of my code???????????

Milin

 

[Katerine]

Sorry about the confusion there.. I'll try to break it down to make it a little more clear:

&quot;WHERE [Name] = '&quot; <-- single quote, then double-quote, so there's a quote inside the SQL quotation
& stOne & &quot;'&quot; <--double quote, then single-quote, then double-quote, to create a string with just a ' inside it.

Hope that helps

 

[MilinPCH]

I am trying to follow the &quot; and ' stuff, but don't totally understand it yet...this is what I've got so far and it gives me a Type Mismatch error. Also, am I running the correct DoCmd to have my query results pop up ?
Thanks for your help!

Dim stDocName As String
Dim stOne As String
Dim stTwo As String
Dim strsql As String
stDocName = &quot;Query1&quot;
stOne = CmboName.Value
stTwo = CmboRank.Value

strsql = &quot;SELECT [Name] from Query1 &quot; & _
&quot;WHERE [Name] = '&quot; & stOne & _
&quot;'&quot; And [Rank] = &quot;'&quot; & stTwo

DoCmd.FindRecord &quot;Query1&quot;, acViewNormal, , strsql

 

[Katerine]

Oh. I'm sorry about the inconvenience with this.. I just re-read your original question and realized there's a much better way to set the current record:

--- Begin Code ---
Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = Str(Me!SupplierID)

rst.FindFirst &quot;[Name] = '&quot; & CmboName.Value & _
&quot;' AND [Rank] = '&quot; & CmboRank.Value & &quot;'&quot;
If rst.NoMatch Then
MsgBox &quot;Record not found&quot;
Else
Me.Bookmark = rst.Bookmark
End If

rst.Close
--- End Code ---

There is a possibility that this won't work if one or both of these fields are unindexed. Please let me know if it complains.

Re: quotes - Assuming that CmboName.Value = &quot;Ted Smith&quot; and CmboRank.Value = &quot;Commander of the World&quot;, you want the finished quote (when it's done computing) to look exactly like this:
[Name] = 'Ted Smith' AND [Rank] = 'Commander of the World'

Note the single quotes. These are necessary for the comparisons to work. The &quot;&&quot; signs serve to concatinate strings. There should, in all, be four single quotes in your string (one each for begin-name, end-name, begin-rank, and end-rank).