HELP!!! Combobox search w/ query linked to form. 1

[sirchris]

I am trying to set up a form with 2 comboboxes and a textbox, plus a query to use the data from these boxes to search the database.
I have tried the following SQL statement for my query:

SELECT Teacher.TeacherID, Teacher.TeacherName
FROM Teacher
WHERE (((Teacher.[TeacherName ])=[Forms]![frmSearch]![cboTeacher]));

However this just opens an input box that displays "Forms!frmSearch!cboTeacher"

How can I take data from the comboboxes and textbox on the form and then use this for a query?

 

[Rick39]

Try:

SELECT Teacher.TeacherID, Teacher.TeacherName
FROM Teacher
WHERE (((Teacher.TeacherName)=[Forms]![frmSearch]![cboTeacher]));

 

[scriverb]

It sounds like you are trying to run this query with having first opened your form and entered data into your control cboTeacher. If you just open the query from the design grid it WILL NOT open the form for you to prompt for the input. You must open the form first and then after picking from the ComboBox you then have to have a Command Button with code like the following to execute your query:[COLOR=blue[

DoCmd.SetWarnings false
DoCmd.OpenQuery "qryYourQueryName"
DoCmd.SetWarnings True[COLOR=black]

Let me know if this solves the problem for you.

Bob Scriver

 

[sirchris]

This now opens the Query results, but doesn't actually show the data, it opens a table that displays:

TeacherID - TeacherName
0 -

 

[scriverb]

The query has run but the query didn't find any records that matched the data that you have entered in the ComboBox. This can be the result of the fact that the Bound Column of the combobox may be different from the visual selection being displayed. What is the RowSource of the Combobox. Table, Query, etc. How many columns? What are the widths of the columns. Which column is the Bound Columnm.

The combobox is called cboTeacher. And you are comparing the value to the TeacherName field in the table. Are you actually picking the TeacherName in the ComboBox? and if so then is the TeacherID one of the other columns of the combobox?

These are all the things to look for when trying to solve this problem.

Bob Scriver

 

[sirchris]

Okay, this is now working thanks but I have a couple more questions;

I am using 3 comboboxes on the form to use in the query now, how can I use wildcards in these? I was thinking of using If statements to direct the program to a suitable query, but this seemed too long winded. How can I get the comboboxes to input suitable wildcards for SQL?

Also, how could I display the query results in another form I have set up with textboxes and comboboxes

Thanks

 

[scriverb]

Are you referring to using a wildcard if the user elects not to make an entry meaning that they do not want to be selective for that field?


Bob Scriver

 

[sirchris]

Yes

 

[scriverb]

When you say "wildcard" just what do you want to do with the entry? Are you going to allow the user to leave the combobox empty? Do you want to perform a Like "*name*" type criteria selection?

Bob Scriver

 

[sirchris]

I was going to allow the user to leave the combobox empty

 

[scriverb]

See my posts related to this in this thread: thread701-524731

I have provided specific coding down in the thread to do just as you are wanting.

Let me know if this helps.

Bob Scriver