VBA/SQL help: My code to autoassign records to users results in Run-time error 3141

[dpfaff]

I made a database in Access 2010 that has a module called Assignments() which contains a few functions. What I want it to do is populate the “Specialist” field (Column A) into the returned results of the select query “qry_live_records_No_Specialist”, based on the Assignment Percentage field defined in column B of the table “tbl_Specialists”.



I am getting Run-time error 3141: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

I don’t have the skills to debug this (I borrowed the code from another database at work and tweaked it with my own database’s object names). I was hoping someone could help me get this working.

As an alternative, I could drop the entire percentage feature and replace the Assignment Percentage field in “tbl_Specialists” with something to indicate whether to assign records to that specialist, and have it cycle through all included specialists until the last record in the query has had a name added. I don't know how to code it though.

I put the line I get the error on in large red text.

Thanks!
Don

Function assignNewRecords()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngTotUnassign As Long
Dim strSpecialist As String
Dim dblAssnPercent As Double
Dim lngRecsAssign As Long
'***Calculates Total New Unassigned Items***
lngTotUnassign = getTotalUnassigned
'*******************************************
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_Specialists", dbOpenDynaset)
With rs
Do Until .EOF
    strSpecialist = ![Specialist]
    
    dblAssnPercent = ![Assignment Percentage]
    
    If dblAssnPercent > 0 Then
        '***Calculates Number of Items to be Assigned******
        lngRecsAssign = lngTotUnassign * dblAssnPercent
        '**************************************************
        
        '***Assign Requests********************************
        Call updateInitials(lngRecsAssign, strSpecialist)
        '**************************************************
    Else
        lngRecsAssign = 0
    End If
.MoveNext
Loop
End With

End Function
Function updateInitials(lngRecsAssign As Long, strSpecialist As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT TOP " & lngRecsAssign & " * " & _
        "FROM [qry_live_records_No_Specialist] " & _
        "WHERE [Specialist] Is Null "
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With rs
Do Until .EOF
    .Edit
    ![Specialist] = strSpecialist
    .Update
.MoveNext
Loop
End With
End Function
Function getTotalUnassigned() As Long
getTotalUnassigned = DCount("[BIS ID]", "qry_live_records_No_Specialist")
End Function
Function percentageAssigned() As Double
percentageAssigned = DSum("[Assignment Percentage]", "tbl_Specialists")
End Function

qry_live_records_No_Specialist:

Specialist	BIS ID
	1395576
	1395580
	1395581
	1395582

tbl_Specialists:

Specialist	Assignment Percentage
John	0.25%
Mary	0.25%
Lucy	0.50%
Henry	0.00%

[lfpm062010]

Does "lngRecsAssign" always returns 0? "SELECT TOP 0 ...." will cause that error.

[dpfaff]

Thanks for the reply.

I think lngRecAssign will always return some results and some zeros, because I have the names of all my team members listed on tbl_specialists,but only 2 to 3 people will have records assigned to them on a given week; so there will always be specialists with 0.00% Assignment Percentage.

[June7]

Guidelines on debugging techniques in link at bottom of my post.

Step debug. Does the updateInitials function ever get called when lngRecsAssign = 0?

Maybe don't use the * wildcard. Since Specialist is the only field used, just retrieve that one field.

[lfpm062010]

If that is the case, I would add some code to check to see if it will be zero. Then either notifiy user (Msgbox) that it is zero or return one record. Something like this,

Function updateInitials(lngRecsAssign As Long, strSpecialist As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

If lngRecsAssign = 0 then
lngRecsAssign = 1 'Option #1
'OR
Msgbox "lngRecsAssign is " & lngRecsAssign & "), there is nothing to select." 'Option #2
Exit Sub 'Option #2 leave the function and nothing below will be executed.
End If

strSQL = "SELECT TOP " & lngRecsAssign & " * " & _
"FROM [qry_live_records_No_Specialist] " & _
"WHERE [Specialist] Is Null "
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With rs
Do Until .EOF
.Edit
![Specialist] = strSpecialist
.Update
.MoveNext
Loop
End With
End Function

[dpfaff]

Hi June, I had attempted the Debugging steps in your link but I know so little I'm not sure if the commands don't apply for Access 2010 (F8 doesn't do anything) or if I'm adding the Debug.print statement incorrectly (I added it to my code, ran it, and nothing appeared to happen).

When I step into the code, it errors out at the line I indicated. I think the problem is with the strSQL statement?

strSQL = "SELECT TOP " & lngRecsAssign & " * " & _
        "FROM [qry_live_records_No_Specialist] " & _
        "WHERE [Specialist] Is Null "
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

[June7]

Only issues I see with the SQL already noted. Test a literal value instead of the variable in the SQL. Eliminate the wildcard as suggested.

What is value of lngRecsAssign when the code errors? Hover cursor over the variable during step debug. Set break points at strategic locations.

F8 should work. I use Access 2010. However, on my HP the functions are not primary on those keys, the Windows controls are. Had to press fn key and F8 simultaneously until I reversed this designation in my computer settings.

You really need to get an understanding of debugging if you want to use code.

[dpfaff]

I agree, I need to understand how to debug and code. Problem is I don't currently know much at all, which is why I'm asking for help here I didn't write this code, I pulled it from a database and modified the object names for my database. I can't lean on anyone at work because they're tied up with bigger projects, which is why I've serving this sort of "junior analyst" role changing processes from Excel to Access. I'm not a VBA or SQL person by any means.

I tried this but doubt I have the correct syntax for replacing the variable as I'm getting the same error:

strSQL = "SELECT TOP " & lngRecsAssign & " [Specialist] " & _
        "FROM [qry_live_records_No_Specialist] " & _
        "WHERE [Specialist] Is Null "
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

[dpfaff]

Ok I figured out the F8 part.

lngRecsAssign = 0 when it errors out. Which I don't understand, because qry_live_records_No_Specialist returns 4 records, each with a BIS ID.

[June7]

I haven't used TOP much and on closer examination, the wildcard should work.

Try:

strSQL = "SELECT TOP 15 * " & _

or some reasonable number

I really don't understand why the query uses TOP. Seems like really want all records where Specialist Is Null.


Look at the calc that generates lngRecsAssign (lngRecsAssign = lngTotUnassign * dblAssnPercent).

Step debug through the first function. Does this calc result in 0?

[dpfaff]

I haven't used TOP much and on closer examination, the wildcard should work.

Try:

strSQL = "SELECT TOP 15 * " & _

or some reasonable number

I really don't understand why the query uses TOP. Seems like really want all records where Specialist Is Null.


Look at the calc that generates lngRecsAssign (lngRecsAssign = lngTotUnassign * dblAssnPercent).

Step debug through the first function. Does this calc result in 0?

Ok I changed it to TOP 11 (there are 11 specialists on the table) and it no longer errors out. However, it is assigning 100% the blank records to the first specialist with a % listed (even though the table specifies 25%).

Getting there!

The lngRecsAssign = lngTotUnassign * dblAssnPercent calculation does result in 0.

[June7]

I guess that would be because one or both of the terms is 0. Why is that? Follow the code. Step through all the functions as code runs.

[dpfaff]

Ok, thanks guys! I'll spend some time debugging and see what I can figure out, and follow-up here later.

[dpfaff]

Ok good news, the code is now working. I put the SELECT TOP code back to this:

strSQL = "SELECT TOP " & lngRecsAssign & " * " & _
        "FROM [qry_live_records_No_Specialist] " & _
        "WHERE [Specialist] Is Null "

And realized my percentages were entered onto tbl_Specialists incorrectly (e.g. 0.33% rather than 33%).

Anyway, it's not perfect, depending on how many unassigned records there are, sometimes I get the 3141 error although it still fills in the Specialist names (sometimes leaving a blank one). As I learn more maybe I can perfect it but it's functional enough for now.