Solved

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

Posted on 2001-06-26
16
281 Views
Last Modified: 2012-08-13
I have a form with a combo box, and a run query button.

I want to take the selection of the combo box as the criteria for the query.  I have tried to create an expression from the form data, but get no results, I can add a text box and do it from that, but I want it to search on a selection not a set field.

thanks
0
Comment
Question by:ataripirate
  • 8
  • 4
  • 3
  • +1
16 Comments
 

Expert Comment

by:MissEd
ID: 6227979
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
0
 

Author Comment

by:ataripirate
ID: 6228251
Sorry, I cannot get this to work with a query?

thanks
0
 

Author Comment

by:ataripirate
ID: 6228274
Sorry, I cannot get this to work with a query?

thanks
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6228302
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
0
 
LVL 1

Expert Comment

by:pscholl
ID: 6228309
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.
0
 

Author Comment

by:ataripirate
ID: 6228420

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
0
 

Author Comment

by:ataripirate
ID: 6228440

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
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6228469
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
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 12

Expert Comment

by:Paurths
ID: 6228484
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
0
 

Author Comment

by:ataripirate
ID: 6228618
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
0
 
LVL 1

Expert Comment

by:pscholl
ID: 6228631
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.
0
 
LVL 1

Expert Comment

by:pscholl
ID: 6228653
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.  
0
 

Author Comment

by:ataripirate
ID: 6228832
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
0
 

Author Comment

by:ataripirate
ID: 6228850
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
0
 
LVL 12

Accepted Solution

by:
Paurths earned 50 total points
ID: 6228908
i suggest u also check your combobox.
U need to put the criteria in your query in the correct field.
Suppose u have 2 fields in a table, AutoID and State.
Your combobox on the form shows the states. (Texas, Oregon, ...) but actually the first field in the combobox is the AutoID field which u cant see because its width is set to 0.
Now if u put the criteria in your query in the field 'States' u will never have a result b/c it is comparing numbers with text. If u put the criteria in your AutoID field in the query everything shows up just fine.

so: question, how many fields does your combobox hold?

u might need to change it in your query.
0
 

Author Comment

by:ataripirate
ID: 6228944
That was it!  Thanks to all for your input.


0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now