Here's how I have it set up, but I keep getting the run time error 3061 - Too few parameters. Expected 3.

cmbCutOrder, cmbStyle and cmbColor are populated combo boxes on a form.

I get the same error when I use a query referencing the combo boxes and try to use that as my recordset. When I run that query, it returns data. I'm totally stumped!

Dim db As Database
Dim rsOrder As Recordset
Set db = CurrentDb

Set rsOrder = db.OpenRecordset("SELECT * FROM tblCutOrderSubs WHERE (((tblCutOrderSubs.intCutOrderID)=cmbCutOrder) AND ((tblCutOrderSubs.intPMID)=cmbStyle) AND ((tblCutOrderSubs.intColorID)=cmbColor))")

Thanks for any help you can offer!

Maybe one of the values you use in the where clause is null.

Peter

Nah - they're all populated.

I tried it again replacing the field names with the values and it worked. I also tried replacing the combo boxes with text boxes and referencing them instead (just in case) and got the same message.

I keep thinking it's gotta be something with how I'm referencing the combos in the statement.

matt k <mfkeough (AT) gmail (DOT) com> wrote in
news:ae83651b-eca5-49d2-8fd2-0fd5712c7fd8 (AT) googlegroups (DOT) com:

It is how you are referencing the comboboxes

if the values are integer try.

Set rsOrder = db.OpenRecordset("SELECT * FROM tblCutOrderSubs
WHERE (((tblCutOrderSubs.intCutOrderID)= " & cmbCutOrder & ")
AND ((tblCutOrderSubs.intPMID)= " & cmbStyle & ")
AND ((tblCutOrderSubs.intColorID)= " & cmbColor & "))")
(one line)
if they are string values, you will need extra quotemarks.


--
Bob Q.
PA is y I've altered my address.

That did it! Thank you so much for your help!

Hey Bob - I copied your text and it worked like a charm. I just read through this again trying to figure it out, but can't figure out the logic behind why there are... & " ...after the combos. Can I pick your brain as to what makes it work?

Please quote a bit of the message you are replying to for context. Not
everyone has a newsreader that keeps all the messages sorted in
conversation-order.
matt k wrote:
PMFJI
Your code was attempting to perform what is called "dynamic sql", which
means "creating string containing a sql statement and passing that statement
as-is to the database to run"
The problem with the way you were doing it is that the combo box references
were not in scope for the process running the sql statement. In other words,
it could not "see" the open form as you would expect it to do when testing
it in the query builder. The difference when running in the query builder is
that Access intervenes and resolves these types of references (actually,
they are "parameters"), which allows the query to run in that environment.

In order to make it work, your code had to do what Access would normally do
for you in the query builder: it has to concatenate the _values_ from your
combo boxes into the string so the resulting statement could be run by the
database engine without needing to refer to open forms. It might help you to
visualize it if I rearrange Bob's code a little, to make it a little more
obvious as to what those ampersands are doing:

dim sql as string
sql="SELECT * FROM tblCutOrderSubs " & _
WHERE (((tblCutOrderSubs.intCutOrderID)= " & _
cmbCutOrder & ")" & _
" AND ((tblCutOrderSubs.intPMID)= " & cmbStyle & ")" & _
" AND ((tblCutOrderSubs.intColorID)= " & cmbColor & "))"
debug.print sql
Set rsOrder = db.OpenRecordset(sql)

It's good practice to get used to assigning sql statements to string
variables, especially when using concatenation to build them as above. This
greatly eases the task of debugging problems since you can see the resulting
statement in the Immediate window. The goal is to create a resulting
statement that you can copy and paste into a query builder's sql view and
run as-is. Running the the query builder sometimes gives you better error
messages than what you get from the VBA code.

matt k <mfkeough (AT) gmail (DOT) com> wrote in
news:2731f94a-0a4a-4b8e-98ff-027e4fdc17b6 (AT) googlegroups (DOT) com:

Basically the query's where clause needs to have the value in the
combobox, not a reference to the combobox. The quotes and ampersands,
which I put both before and after the combo box, makes VBA put the
value from the combo box into the string, Otherwise you get the name of
the combobox, which the query doesn't know how to get the value from.


"...(tblCutOrderSubs.intCutOrderID)= " & cmbCutOrder & " AND.... "

--
Bob Q.
PA is y I've altered my address.

When trying to explain this, I often just pointed out the following and that
tends to "light" the "light bulb"

As yourself the follows:

What would happen if you wanted to search for a color called cucumber, or
even better how about a color called:

cmbColor


AND intColorID = cmbColor


So how would we search for a color called cmbColor? In other words, is that
what we are typing in a color, or some "expression" that references to a
control on the form?

How do you "choose" between wanting to search for cmbColor, or the value
"inside" of cmbColor?

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
PleaseNoSpam_kallal (AT) msn (DOT) com

"Albert D. Kallal" <PleaseNOSpamkallal (AT) msn (DOT) com> wrote in
news:kRGOr.7357$Zl3.318 (AT) newsfe06 (DOT) iad:

Thank you, professor. Explaining the "why" is often my shortcoming.
It is a lot more difficult than just explaiining the 'how'..

--
Bob Q.
PA is y I've altered my address.