Cycling numbers and too few variables error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to use the code below to run a query that looks at a series
of numbers for work lists (1,3,4,6) and assigns them in a cycle. It checks
to see if any of the numbers were not used in the previous cycle to evenly
distribute records to a user to work. When i run this code through the form
it gives an error message stating 'too few variables. expected 1.' Any idea
where i'm going wrong here? Thanks!!!

Private Function SetQueueRouting()
Dim db As DAO.Database
Dim rstQueuesToCycle As DAO.Recordset
Dim rstLastQueueCycleSequence As DAO.Recordset
Dim strCycledQueues As String
Dim strReceivingQueue As String
Dim blnQueueNotInLastCycle As Boolean
blnQueueNotInLastCycle = False
strCycledQueues = "SELECT * FROM tbl_Queue2Associate " & _
"WHERE (((tbl_Queue2Associate.cycled_ind)=True) AND
((tbl_Queue2Associate.Queue) Is Not Null));"
Set db = CurrentDb
Set rstQueuesToCycle = db.OpenRecordset(strCycledQueues, dbOpenSnapshot)
Set rstLastQueueCycleSequence = db.OpenRecordset("qryGetQueueRoutingCycle",
dbOpenSnapshot)
rstQueuesToCycle.MoveLast
If rstQueuesToCycle.RecordCount <> 0 Then 'if records exist
rstQueuesToCycle.MoveFirst 'go to first record
Do Until rstQueuesToCycle.EOF Or blnQueueNotInLastCycle = True 'loop
through cycled Queues
rstLastQueueCycleSequence.FindFirst "Queue_Key='" &
rstQueuesToCycle!queue & "'" 'check if a Queue was in the last cycle
If rstLastQueueCycleSequence.NoMatch Then 'if queue was not in last
cycle
blnQueueNotInLastCycle = True 'then set indicator to true and
exit the loop
Exit Do
End If
rstQueuesToCycle.MoveNext 'if queue was in last cycle then check the
next queue
Loop
If blnQueueNotInLastCycle = True Then 'sets the routing to either the
queue not already in the cycle
strReceivingQueue = rstQueuesToCycle!queue
Else
rstLastQueueCycleSequence.MoveFirst 'or the queue at the beginning
of the last cycle
strReceivingQueue = Nz(rstLastQueueCycleSequence!Queue_Key, "null")
End If

SetQueueRouting = strReceivingQueue

End If
'On Error Resume Next
rstLastQueueCycleSequence.Close
rstQueuesToCycle.Close
db.Close
'Set rstQueueSequence = Nothing
Set rstQueuesToCycle = Nothing
Set db = Nothing
End Function
 
strCycledQueues = "SELECT * FROM tbl_Queue2Associate " & _
"WHERE cycled_ind=True AND Queue Is Not Null));"
Set rstQueuesToCycle = db.OpenRecordset(strCycledQueues, _
dbOpenSnapshot)
Click to expand...
Set rstLastQueueCycleSequence = _
db.OpenRecordset("qryGetQueueRoutingCycle", dbOpenSnapshot)
Click to expand...


You don't tell us which of these two queries is the one that fails.

(a) Does tbl_queue2associate really have columns called cycled_ind
and queue? The commonest reason for the "parameter error" error
is a misspelled field name.

(b) Does the querydef qryGetQueueRoutingCycle normally open
legally when run from VBA? Remember that parameters like
Forms!MyForm!MyTextbox work when the qdf is called from the GUI
but not when run directly by the db engine under VBA.

Just incidentally:
rstQueuesToCycle.MoveLast
If rstQueuesToCycle.RecordCount <> 0 Then 'if records exist
rstQueuesToCycle.MoveFirst 'go to first record

'loop through cycled Queues
Do Until rstQueuesToCycle.EOF Or blnQueueNotInLastCycle = True
Click to expand...



It's easier, faster and kinder to the network and to your computer memory
to replace all this code with

Do While rstQueuesToCycle.EOF
If blnQueueNotInLastCycle = True Then Exit Do

which is always true if no records were returned. (BOF works just as well
for this test too. In fact, it looks as though the rstQueuesToCycle could
be opened ForwardOnly which would make your life even easier again.


All the best


Tim F
 
You should tell us which line is causing the error. I expect your
qryGetQueueRoutingCycle has a parameter that is not being provided. Do you
have the SQL view of this query for us?
 
It doesn't specify a line in the VBA code that is the error and i'm not sure
how to convert VBA into SQL. Here's the code for one of the queries that I
think could be the issue. It should be showing the last 4 values so that it
can tell if all numbers in a cycle (1,3,4,6) have been used to assign a
'queue' or 'work list'. thoughts?

SELECT Last(tbl_Email_Data.Count_ID) AS LastOfCount_ID,
tbl_Email_Data.queue_key
FROM tbl_AssocTbl INNER JOIN tbl_Email_Data ON tbl_AssocTbl.Queue =
tbl_Email_Data.queue_key
WHERE (((tbl_AssocTbl.cycled_ind)=True) AND ((tbl_Email_Data.request_type)
Not In ("rt14")))
GROUP BY tbl_Email_Data.queue_key
HAVING (((tbl_Email_Data.queue_key) Is Not Null))
ORDER BY Last(tbl_Email_Data.Count_ID);
 
So far i haven't been able to get the code to run successully from a form or
through VBA. I've double checked the fields and they are all there and
spelled correctly. One query that could be the issue is below. It needs to
look at each 'cycle' and make sure that 1,3,4,6 have all been assigned, if
not assign the missing value, if so start a new cycle. at this point, when i
run the query it is pulling the primary key from my main table but it is not
showing the next 4 records. thoughts?

SELECT Last(tbl_Email_Data.Count_ID) AS LastOfCount_ID,
tbl_Email_Data.queue_key
FROM tbl_AssocTbl INNER JOIN tbl_Email_Data ON tbl_AssocTbl.Queue =
tbl_Email_Data.queue_key
WHERE (((tbl_AssocTbl.cycled_ind)=True) AND ((tbl_Email_Data.request_type)
Not In ("rt14")))
GROUP BY tbl_Email_Data.queue_key
HAVING (((tbl_Email_Data.queue_key) Is Not Null))
ORDER BY Last(tbl_Email_Data.Count_ID);
 
It's difficult to say. You can open a module and set the code to break on
all errors. This will then break as soon as an error is generated. I would
never use Last() in a query like this. Have you tested this to ensure you
get reliable results?
 
You do know that the error: 'too few variables. expected 1' means an
SQL error, not a VBA error? There must be a syntax error somewhere in
your SQL.

HTH,
TC
 
Unfortunately, this code is over my head so the best i've been able to do is
run the code through the form and recieve the non-specific error message.
How would i insert something to isolate the error?
 
No i didn't know that. I wrote this in VBA. Would this mean that there's an
issue in the query i'm calling as it's written in SQL? I don't know how to
view the VBA in SQL if it's even possible.
 
So far i haven't been able to get the code to run successully from a
form or through VBA.
Click to expand...

What code? You have some VBA, you have two SQL queries. We know that one
of the SQL queries is causing an error, but you haven't said which!

I've double checked the fields and they are all
there and spelled correctly.
Okay...

One query that could be the issue isbelow.
Click to expand...


SELECT
Last(tbl_Email_Data.Count_ID) AS LastOfCount_ID,
tbl_Email_Data.queue_key

FROM tbl_AssocTbl
INNER JOIN tbl_Email_Data
ON tbl_AssocTbl.Queue = tbl_Email_Data.queue_key

WHERE tbl_AssocTbl.cycled_ind = True
AND tbl_Email_Data.request_type Not In ("rt14")

GROUP BY tbl_Email_Data.queue_key

HAVING tbl_Email_Data.queue_key Is Not Null

ORDER BY Last(tbl_Email_Data.Count_ID);


This is presumably the qryGetQueueRoutingCycle querydef. I cannot see
what might be wrong with it, but LAST() is a function I very rarely use.
I don't see how it could produce a sensible result unless the recordset
was sorted on the ORDER BY clause before the grouping of the GROUP BY
clause, and I don't think that is true. The function does not seem to
exist like this in any real database language. As I asked above, does
this query [a] run successfully from the UI (double-click it in the
Database window), and give the right answers?
thoughts?
Click to expand...

When you have to jump through hoops like this to get to a fairly simple
fact ("Who has been rostered recently?"), then I start to wonder about a
Major Design Problem...


B Wishes


Tim F
 
i just made the connection that it is a sql code causing the issue. I have
been using the qryGetLast2QueueRoutings for a while. The
qryGetQueueRoutingCycle is a new query i just created so i guess it's that
one. Here's the sql. Any idea what i did wrong? i'm trying to get it to
look at the primary key (Count_ID) in the main table (Email_Data) and pull
the last four to ensure that all numbers have been used in the last cycle.

SELECT TOP 4 tbl_Email_Data.Count_ID, tbl_Email_Data.queue_key
FROM tbl_AssocTbl INNER JOIN tbl_Email_Data ON tbl_AssocTbl.Queue =
tbl_Email_Data.queue_key
WHERE (((tbl_AssocTbl.cycled_ind)=True) AND ((tbl_Email_Data.request_type)
Not In ("rt14")))
GROUP BY tbl_Email_Data.queue_key
ORDER BY Last(tbl_Email_Data.Count_ID);


Tim Ferguson said:
So far i haven't been able to get the code to run successully from a
form or through VBA.
Click to expand...

What code? You have some VBA, you have two SQL queries. We know that one
of the SQL queries is causing an error, but you haven't said which!

I've double checked the fields and they are all
there and spelled correctly.
Okay...

One query that could be the issue isbelow.
Click to expand...


SELECT
Last(tbl_Email_Data.Count_ID) AS LastOfCount_ID,
tbl_Email_Data.queue_key

FROM tbl_AssocTbl
INNER JOIN tbl_Email_Data
ON tbl_AssocTbl.Queue = tbl_Email_Data.queue_key

WHERE tbl_AssocTbl.cycled_ind = True
AND tbl_Email_Data.request_type Not In ("rt14")

GROUP BY tbl_Email_Data.queue_key

HAVING tbl_Email_Data.queue_key Is Not Null

ORDER BY Last(tbl_Email_Data.Count_ID);


This is presumably the qryGetQueueRoutingCycle querydef. I cannot see
what might be wrong with it, but LAST() is a function I very rarely use.
I don't see how it could produce a sensible result unless the recordset
was sorted on the ORDER BY clause before the grouping of the GROUP BY
clause, and I don't think that is true. The function does not seem to
exist like this in any real database language. As I asked above, does
this query [a] run successfully from the UI (double-click it in the
Database window), and give the right answers?
thoughts?
Click to expand...

When you have to jump through hoops like this to get to a fairly simple
fact ("Who has been rostered recently?"), then I start to wonder about a
Major Design Problem...


B Wishes


Tim F
Click to expand...
 
I figured it out. thanks for everyone's help! I am still having issues with
the data access page code that is supposed to do the same thing as the VBA.
Any idea why it's not working?

here it is:

Function FetchQueueValue()
blnQueueNotInLastCycle = False
strCycledQueues = "SELECT * FROM tbl_AssocTbl " & _
"WHERE (((tbl_AssocTbl.cycled_ind)=True) AND
((tbl_AssocTbl.Queue) Is Not Null));"
strLastQueueCycleSequence = "SELECT * FROM qryGetQueueRoutingCycle"
Set rstQueuesToCycle = CreateObject("ADODB.Recordset")
Set rstLastQueueCycleSequence = CreateObject("ADODB.Recordset")
rstQueuesToCycle.Open strCycledQueues, MSODSC.Connection
rstLastQueueCycleSequence.Open strLastQueueCycleSequence, MSODSC.Connection
rstQueuesToCycle.MoveLast
If rstQueuesToCycle.RecordCount <> 0 Then 'if records exist
rstQueuesToCycle.MoveFirst 'go to first record
Do Until rstQueuesToCycle.EOF Or blnQueueNotInLastCycle = True 'loop
through cycled Queues
rstLastQueueCycleSequence.MoveFirst
rstLastQueueCycleSequence.Find "Queue_Key='" &
rstQueuesToCycle.Fields.item("queue").value & "'" 'check if a Queue was in
the last cycle
If rstLastQueueCycleSequence.EOF Then 'if queue was not in last cycle
blnQueueNotInLastCycle = True 'then set indicator to true and
exit the loop
Exit Do
End If
rstQueuesToCycle.MoveNext 'if queue was in last cycle then check the
next queue
Loop
If blnQueueNotInLastCycle = True Then 'sets the routing to either the
queue not already in the cycle
strReceivingQueue = rstQueuesToCycle.Fields.item("queue").value
Else
rstLastQueueCycleSequence.MoveFirst 'or the queue at the beginning
of the last cycle
strReceivingQueue =
rstLastQueueCycleSequence.Fields.item("queue_key").value
End If

FetchQueueValue = strReceivingQueue

End If
rstLastQueueCycleSequence.Close
rstQueuesToCycle.Close
'Set rstQueueSequence = Nothing
Set rstQueuesToCycle = Nothing
End Function
 
Yes, it's coming from the SQL. But it could be the SQL in a query that
you are running, or, the SQL that you are running directly from within
VBA.

This VBA statement will run the specified query. The SQL is stored
within that query:
DoCmd.OpenQuery "MyQueryNameHere"

This VBA code (in lower case) will run the specified SQL (in upper
case):
dim db as database
set db = currentdb()
db.execute "UPDATE MYTABLE SET MYFIELD = MYFIELD + 1"

HTH,
TC
 
Any idea what i did wrong?
Click to expand...

No. See comments above about the LAST() function.

The easiest way to create a SQL query is the access query designer. Just
add the tables, make the joins, select the columns you need and then add
any criteria you need. Test it against known data until you are sure it's
returning the right answers.

At that time you can either (a) save the thing as a Querydef, and call it
using code like you did above

Querydefs("MyNewQuery").Execute dbFailOnError


or (b) select the SQL View of the query and copy-and-paste the whole
command into an inline VBA string:

daoQuery = "SELECT This, That, TheOther " & _
"FROM Somewhere " & _
"etc etc etc"


It all starts, though, with getting a correct query in the first place,
and I can't help you with that from here.

Hope that helps


Tim F
 
Just a tip: you'll get nowhere asking why it's "not working".

There are 50 gazillion ways in which something might "not work". It
might get a compile error, or a runtime error, or it might produce the
wrong result, or /no/ result, or a micture of right & wrong results, or
Access might quit for no reason, or the PC might hang, or gazillions of
other possible problems!

No-one will try to guess what problem you're actually having. You need
to describe that, explicitly. A good approach is to say what you DID,
what you EXPECTED would happen then, and what actually DID happen then.
Then someone can probably help you!

HTH,
TC
 
Back
Top