OpenRecordset(strSQL) error 3061-too few parameters

[bsedabres]

Good morning All:

I have the following code attached to a button using the on_click event. It is supposed to look up a rate on a table based on the field name I select from a drop down, matching the size of the circuit to the size already recorded in another table. I am getting a "Too few parameters. Expected 2." error on my OpenRecordset line:

Set rs = CurrentDb().OpenRecordset("SELECT " & strField & " FROM tblBillingRates WHERE Mbps = ' " & txtSizeMB & " ' "

strField is assigned a text value based on the abbrev selected from a drop down and txtSizeMB is stored in a table and loaded into the form. This query should return only one field of one record which I will assign to a text box by:

txtRate = rs(strField)

I use similar code on another section of my form to populate a location code based on a city state combo selected from a drop down and that works fine.

Any help will be appreciated. Thanks!

 

[RobCPA]

This is what I normally do in this situation:

1. In your code, assign the sql statement to a variable such as "sql"

2. Right after assigning the variable, do a "debug.print sql" to send the results to the debug window.

3. Copy and paste the results into a new query - sql design view.

4. Click back to design view or try running the query from there. Either way you should get some better ideas on what is going wrong with the query.

5. Once you fix that query, you can look at the sql code to make the change to the VBA code.

Often, when you see "Too few parameters" it means that the query code is looking for user input because it can't determine the information from the underlying datasets.

 

[bsedabres]

Ok. Thanks for the advice. This is what the query SQL view generated:

SELECT tblATMBillingRates.[UBR-U]
FROM tblATMBillingRates
WHERE (((tblATMBillingRates.Mbps)='10'));

How can I write this SQL in VBA where [UBR-U] is a string field with differing values selected by the user?

 

[bsedabres]

Nevermind. I got it now. Here is the correct syntax:

"SELECT [" & strField & "] FROM tblATMBillingRates WHERE (Mbps = '" & txtSizeMB & "')"

I understand the brackets around the field name since it contains a dash and VB thinks I am trying to subtract, but I don't totally get why parentheses are required around the WHERE clause, but it works!

Thanks again for the help--

 

[RobCPA]

OK, I went through the same exercise based on the last sql code you posted. The problem is the "-" in the field name. Therefore, you need the brackets "[" and "]" in the VBA sql code to work.

sql code should look like:

"SELECT [" & strField & "] FROM tblBillingRates WHERE Mbps = ' " & txtSizeMB & " ' "