Too few parameters... and a personal note to you all from me!

[JPeters]

Seems like Ben Ohara and I are both in a jam today... Read on! ;-) There's a personal note to all of you at the bottom if you finish reading my question...

Okay - with time I've found that Access comes with it's fair share of head-aches. AC97 is giving me a HUGE head-ache this week.

I've got this parameter query. The table the query comes from has about 3300 records. The query sorts it down to about 300 ... when ran in view mode - it works fine in view mode. Now here's my problem. This criteria (parameter) is linked to a form in which they user picks the criteria, hits a button, and the query is set to a recordset, and then Map Point maps the recordset.. Well I get the lovely error message (something like): "Too few parameters: Expected 1."...

Access pretty much doesn't like letting you set Parameter queries that get their criteria from a form ite as recordsets. I need this to work somehow.

Okay, so I've rewriten the query in SQL - same error. I've added querydefs and even managed to get the recordset to populate . . . WITH ONLY 1 RECORD! It's driving me nuts.

Here's a bit of my SQL that gives me 1 record:
strSQL = "SELECT qryWrkAssignState.State, qryWrkAssignState.City, qryWrkAssignState.ZipCode, qryWrkAssignState.NameInsured, qryWrkAssignState.Address, qryWrkAssignState.[assignment#], qryWrkAssignState.assignee FROM qryWrkAssignState WHERE qryWrkAssignState.assignee = '" & Forms!frmMapFieldRepWrkAssign!cmbFieldRepNum & "'"

Set MapRST = MapDBS.OpenRecordset(strSQL, dbOpenSnapshot)

and here's the on that gives me parameter missing:

strSQL = "SELECT qryWrkAssignState.State, qryWrkAssignState.City, qryWrkAssignState.ZipCode, qryWrkAssignState.NameInsured, qryWrkAssignState.Address, qryWrkAssignState.[assignment#], qryWrkAssignState.assignee FROM qryWrkAssignState WHERE (((qryWrkAssignState.assignee)=[Forms]![frmMapFieldRepWrkAssign]![cmbFieldRepNum]));"

Set MapRST = MapDBS.OpenRecordset(strSQL, dbOpenSnapshot)

I've searched and searched for a way to do tihs in tek-tips and I've come up with a few worthless 'work-arounds'. These all return the value of 1 record... I've tried querydefs, parameters, etc. I'm at my wits end. And I need somewhat of a working copy by (hopefully) tomorrow. Any takers? I appreciate it... thanks..

Now read below:

Well, my last day in office is tomorrow at my job... Then I go back to school cuz the summer is over - my summer internship is up .. I'm still an intern at this company, but I'll be working about 1/4th the time ... schedule will be cluttered - school, social life, partying .. partying ... pa... you get the idea..

I won't be on Tek-Tips QUITE as often - I'll still maintain my position in the top experts of this forum by helping you all, of course. And I'll be keeping in touch with both Robert (thornmastr) and hopefully Ben Ohara (oharab) through email if I ever have many problems. Along with many of you, they've been a great help to me this summre. Thanks guys! Goodluck to all of you, I'll be here whenever I can to help!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[grnzbra]

check help for using variables in sql statemtnt. the problem is that the part of the sql statement that should be in quotes ends one space after the equal sign in the WHERE clause.

generally it should be something like

for a numeric value, it should be something like:
WHERE variable = " & textbox

for a text value:
WHERE variable = '" & textbox & "'"

for a date value:
WHERE variable = #" & textbox & "#"

help goes into this extensively

 

[grnzbra]

first look up "variables" in help.
then select SQL statement from the sub catagories. this will take you right to the relevant page

 

[GHolden]

Try...

strSQL = "SELECT qryWrkAssignState.State, qryWrkAssignState.City, qryWrkAssignState.ZipCode, qryWrkAssignState.NameInsured, qryWrkAssignState.Address, qryWrkAssignState.[assignment#], qryWrkAssignState.assignee FROM qryWrkAssignState WHERE (((qryWrkAssignState.assignee)='" & [Forms]![frmMapFieldRepWrkAssign]![cmbFieldRepNum]& "'));"
There are two ways to write error-free programs; only the third one works.

 

[GHolden]

or if [Forms]![frmMapFieldRepWrkAssign]![cmbFieldRepNum]is numeric

strSQL = "SELECT qryWrkAssignState.State, qryWrkAssignState.City, qryWrkAssignState.ZipCode, qryWrkAssignState.NameInsured, qryWrkAssignState.Address, qryWrkAssignState.[assignment#], qryWrkAssignState.assignee FROM qryWrkAssignState WHERE (((qryWrkAssignState.assignee)=" & [Forms]![frmMapFieldRepWrkAssign]![cmbFieldRepNum]& &quot);"
There are two ways to write error-free programs; only the third one works.

 

[JPeters]

Gentlemen,
You know what? I figured it out ... I forgot something so miniscule I barely even noticed. Thanks for your posts guys!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[grnzbra]

I'm wondering what it was with your first SQL statement (the one without the parenthases in the WHERE statement) that caused it to give you only one record in the result.

 

[JPeters]

It was me turning a blind eye to the fact that when I did a recordcount I forgot to go to the last record and was staying on the first record; thus, only one record was returned. I scratched my head and laughed when I figured it out. Sometimes we miss the littlest things... and these can cause us the most grief but also give us the most relief when fixed. Hooray!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------