String error in SQL MS Access

Question

I'm not sure if my title is 100% accurate, but I think that is the problem in my code. I got this code working, but when I made changes to other portions of my code and it stopped working. Here's the full SQL:

UPDATE CFRRR    
  SET assignedto = " & GetNextAssignee("program", "Language", "username") & ", 
    assignedby = '" 
    & Forms!Supervisor!NavigationSubform!assignedby.Value 
    & "', Dateassigned = #" 
    & Now & "#, actiondate = #" 
    & Now & "#, Workername = '" 
    & DLookup("username", "attendance", "userID = " 
    & GetNextAssignee("program", "Language", "username")) 
    & "', WorkerID = " & DLookup("userID", "attendance", "userID = " 
    & GetNextAssignee("program", "Language", "username")) 
    & " WHERE CFRRRID = " 
    & rs!CFRRRID

The error I am getting is here:

Workername = '" 
& DLookup("username", "attendance", "userID = " 
& GetNextAssignee("program", "Language", "username"))

This is the output I am getting:

UPDATE CFRRR SET assignedto = 6, 
assignedby = '33', 
Dateassigned = #5/17/2015 7:46:40 PM#, 
actiondate = #5/17/2015 7:46:40 PM#, 
Workername = 'Valentino', 
WorkerID = 7 WHERE CFRRRID = 40

This is the output I should be getting:

UPDATE CFRRR SET assignedto = 6, 
assignedby = '33', 
Dateassigned = #5/17/2015 7:46:40 PM#, 
actiondate = #5/17/2015 7:46:40 PM#, 
Workername = 'John', 
WorkerID = 6 WHERE CFRRRID = 40

And here:

WorkerID = "
& DLookup("userID", "attendance", "userID = "
& GetNextAssignee("program", "Language", "username"))

Here is the GetNextAssignee code for reference:

Public Function GetNextAssignee(program As String, 
                              language As String, 
                              username As String) As Long
        Dim db As dao.Database
        Dim rs As dao.Recordset
        Dim strSQL As String
        Set db = CurrentDb
        strSQL = "SELECT TOP 1 
                   userID 
                 FROM attendance as a, 
                 CFRRR WHERE a.Status = 'Available' 
                 AND a.Programs LIKE CFRRR.program  
                 AND a.Language = CFRRR.language 
                 ORDER BY TS ASC, userID, CFRRRID"
        Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
        If Not rs.BOF And Not rs.EOF Then
      strSQL = "UPDATE attendance 
                SET TS= " & DMax("[TS]", "attendance") + 1 
                & " WHERE [userID]=" & rs!userID 
                & " AND [Status]=""Available"""
            db.Execute strSQL, dbFailOnError
            GetNextAssignee = rs!userID
        Else
            GetNextAssignee = 0
        End If

        rs.Close
        db.Close
        Set rs = Nothing
        Set db = Nothing

    End Function

Hi @Sachu I'm actually not getting an error message. I believe that I know the issue, I just don't know how to fix it. So it seems like GetNextAssignee is going through that code 3 times, because it's referenced in the code AssignNullProjects 3 times which is why I am getting 3 different values. How do I get it so it only goes to GetNextAssignee once and then go to AssignNullProjects, before looping again? — Lilly
– Lilly, Commented May 18, 2015 at 3:01
can u edit your question with a sample out put u r looking for and output what you r getting now. — Sachu
– Sachu, Commented May 18, 2015 at 3:11
Thanks @Sachu I updated my question to shows the outputs I am getting and what I should get. Like I said in my comment above, I think it's because it is looping through GetNextAssignee 3 times instead of 1, at least that what makes sense and what it is doing when I'm stepping through the code. — Lilly
– Lilly, Commented May 18, 2015 at 3:21
can't you run GetNextAssignee seperately before the query and save the output in a variable and use that variable inside the query? — Sachu
– Sachu, Commented May 18, 2015 at 3:32

Sachu · Accepted Answer · 2015-05-18 04:34:38Z

1

Change your code as below

Dim i as long

and inside your while loop of AssignNullProjects change the query like this

If Not rs.BOF And Not rs.EOF Then
        While Not rs.EOF

i = GetNextAssignee("program", "Language", "username")

 strSQL = "UPDATE CFRRR SET assignedto = " & i & ",
 assignedby = '" & Forms!Supervisor!NavigationSubform!assignedby.Value 
& "', Dateassigned = #" & Now & "#, actiondate = #" 
& Now & "#, Workername = '" 
& _DLookup("username", "attendance", "userID = " & i)
 & "', WorkerID = " & i & " WHERE CFRRRID = " & rs!CFRRRID
Debug.Print strSQL
 db.Execute strSQL, dbFailOnError
  rs.MoveNext
        Wend
    End If

answered May 18, 2015 at 4:34

Sachu

7,7549 gold badges59 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

String error in SQL MS Access

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related