CODEWITHSUNDEEP
sqlms-accessvba
RSD
RSD

Reputation: 25

MS Access running SQL Update Error 3464

So I have been searching on here but can't seem to find an answer that makes my code work. I know I'm just missing something but can't figure it out.

I am Running MS Access 2013 and trying to run the follow SQL "Update" in VBA but keep getting

runtime error 3464 : Data Type Mismatch

Function ChangeTaskRank(NewRank As Integer, PriorityLevel As Integer, UserGroup As String)

Dim db As DAO.Database
Dim tableDF As DAO.TableDef

Dim sqlString As String
Dim currentRank As Integer
Dim nextRank As Integer

Set db = CurrentDb()
Set tableDF = db.TableDefs("Task_List")

SelectTaskQueryDB PriorityLevel, UserGroup

currentRank = TempVars!tmp_Task_MaxRank

Do While (currentRank >= NewRank)

    nextRank = currentRank + 1

    db.Execute "UPDATE Task_List " & _
    "SET [Task_Rank]= " & nextRank & " " & _
    "WHERE (([Task_Rank] = " & currentRank & ") AND ([Task_Priority] = " & PriorityLevel & ") AND ([Task_UserGroup] = '" & UserGroup & "'));"


    'Update
    'SET Task_Rank = (currentRank + 1)
    'WHERE (([Task_Priority] = PriorityLevel) AND ([Task_UserGroup] = UserGroup));

    currentRank = currentRank - 1

Loop

End Function

What I am trying to do with code is run a loop that will change the Rank of a task when a new one is added (IE: you have task rank 1 to 5 and add a new task at 3, the code will change the task rank of 3,4,5 to 4,5,6.)

Database info: 

Table name: Task_List
Field Name: Task_Rank (Integer)

Filters:

[Task_Rank] = currentRank (Integer)
[Task_Priority] = PriorityLevel (Integer)
[Task_UserGroup] = UserGroup (String)

Any help would be great as this is driving me nuts lol. Thanks in advance.

Upvotes: 2

Views: 150

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider a parameterized SQL query with a conditional IIF to avoid any looping, quote enclosures, and string concatenation.

SQL (save as a saved query)

PARAMETERS NewRankParam LONG, PriorityLevelParam LONG, UserGroupParam TEXT;
UPDATE Task_List  t
SET [Task_Rank]= IIF(t.[Task_Rank] >= NewRankParam, t.[Task_Rank] + 1, t.[Task_Rank])
WHERE ((t.[Task_Priority] = PriorityLevelParam) AND (t.[Task_UserGroup] = UserGroupParam));

VBA

Function ChangeTaskRank(NewRank As Integer, PriorityLevel As Integer, UserGroup As String)

    Dim db As DAO.Database
    Dim qdef As QueryDef

    Set db = CurrentDb
    Set qdef = db.QueryDefs("mySavedQuery")

    qdef!NewRankParam = NewRank
    qdef!PriorityLevelParam = PriorityLevel
    qdef!UserGroupParam = UserGroup

    qdef.Execute dbFailOnError

    Set db = Nothing
    Set qdef = Nothing

End Function

Upvotes: 2

Related Questions