I need to take a ComboBox selection and use it to feed a query.

Hi there,

I have created a form with a combobox named combo0 and a command button named command2. Here is a snippet of code using the selected entry on the combobox to open a recordset.

Private Sub Command2_Click()
    ' Check for a selection
    If IsNull(Me.Combo0) Then
        MsgBox "Please select a value from the combo box"
        Exit Sub
    End If
   
    Dim rst1 As Recordset
   
    Set rst1 = CurrentDb.OpenRecordset("SELECT * FROM tUser WHERE UserID = " & Me.Combo0 & ";")
   
    If rst1.RecordCount > 0 Then
        MsgBox "This box indicates records were found"
    End If
   
    Set rst1 = Nothing
   
End Sub

Sorry, I cannot get this to work with a query?

thanks

Sorry, I cannot get this to work with a query?

thanks

hi ataripirate ,

1) do u have an existing query?
2) Or do u want to create the sql-statement on the spot?

1) existing query :
suppose the name of the combobox on the form is 'cboMyData' and the form is named 'MyForm'. Then in your query paste this :
[Forms]![MyForm]![cboMyData]

2) create the sql statement:
dim strSQL as string
'If YourField is numeric
strSQL = "SELECT * FROM YourTable WHERE YourField = " & Me.cboMyData

'If YourField is text
strSQL = "SELECT * FROM YourTable WHERE YourField = '" & Me.cboMyData & "'"

hope this helps
Ricky

In your query desing grid "Cirteria" line put the path to your combo box seletion: Forms![FORMNAME]![COMBOBOX].  I have used this for 26 query criteria in one query, but I have found that you get no results in the resultant query unless you do one of two things: 1) Every field that you are performing criteria on must be a required field (ie some data even a default of "*" works - that is what I do when there is no data in the field->preventing nulls. 2)You can't specifically put an ISNull criteria on the or line of the query design for the same field you are looking for Combo box criteria.  I did not like this method though because you would then get all the "NULL" recirds in addition to the one you were looking for.  I would sugget number one - it hasn't failed me yet.

Thanks


Forms![FORMNAME]![COMBOBOX], I have this method, when I click the run query button, I get the results, they have the headers and nothing else.  Is this because I have holes in my data?

thanks

Thanks


Forms![FORMNAME]![COMBOBOX], I have this method, when I click the run query button, I get the results, they have the headers and nothing else.  Is this because I have holes in my data?

thanks

u mean that there is not even an empty row? (white row)

if so, can u post the sql from the query here?
In your query, goto sql-view, and copy everything and paste it here. There might be a problem with the join.

cheers
Ricky

btw MissEd,

i see you are new to EE, so i would like to welcome u to this great community.
In the access topic area we have an unwritten rule: we, the experts dont submit answers, only comments. This way the question dont get locked and dissapears from the list, thus resulting in other experts not visiting the question. Thus again resulting in less help for the questioner.

Since this is an unwritten rule, u could not be aware of it.

Again, i would like to welcome u and hope u can help a lot of people coming for help here,

cheers
Ricky

It is now showing a white blank under the headings, I took out the "is null" in the criteria.  Now all I get is a blank record?


thanks

Yes that is exactly what it means. Just as an experiment. Put IsNull in the OR line of the criteria.  You should get back all of the records which have a null value in that field and "ANY" that would have matched the crieria you chose in you combo box.  

For some reason queries do not like to search fields which have NULL values when you are using a FORMS!.... as criteria.  Depending on the number of records you have in this table you could populate the nulls with a value say "None" put this in you drop list to chose from should you ever want to look for nulls again.  What I would do is run a query and pull up all the nulls and auto-fill all of these nulls with None. (you could do an update query thatfinds all the nulls and updates the field with "None".  Then you would want to set a default on your table design to all new records for that field would have None as the defualt.  You could go a step further and make it a required field and never have to worry about it being blank (Null).  Of course, I don't know your situation but in mine I have a database where every field is required text or date.   For the text fields I use "None" or "." As an addded benfit I am now able to force a user to change any field I want based on their actions simply by setting focus and doing and acCut operation - leaving the field blank and thus requiring entry before the record can be saved.  But I digress (har har).  I hope this helps if i can do anything else let me know.

Do A test on your link to the criteria on your form. I have had some Strange happenings on occasion.  In you query design grid put an actual value "Texas" that you are looking for. run the query.  note the nuber of records. Now change the query again to refernce the combo box on your form pick the same "Texas" from you drop list? are you getting the same records?  If you are getting records with "Texas" in the design grid and no records when you chose "Texas" on your form then the problem is your Null Records.  

I put the "is null" as an or, same problem.  I replaced the criteria with an actual value on the form and did the query from that  item.  It worked, I put back the criteria to the combo box and it did not work?


thanks

I put the "is null" as an or, same problem.  I replaced the criteria with an actual value on the form and did the query from that  item.  It worked, I put back the criteria to the combo box and it did not work?


thanks

That was it!  Thanks to all for your input.