Getting a 3464 Error (1 Viewer)

[brewpedals]

Hi,

Thanks for your help!

I have a Function that is intended to toggle a boolean field in a subforms recordset. If call the function with the commented SQL with a descrete record number, it works perfectly, but does not when I use the declared variable. Is Long the wrong data type for an autonumber?

CODE:
++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function ToggleIFC()

Dim CurRec As Long
' Stores the value of F_Main_subform's current record in the variable CurRec.
CurRec = Form_F_Main_subform.CurrentRecord
' Executes an Update Query to toggle the IFC field in the current record.
' DoCmd.RunSQL "UPDATE PD_Requests SET PD_Requests.IFC = IIf([PD_Requests].[IFC]=Yes,No,IIf([PD_Requests].[IFC]=No,Yes,No))WHERE (((PD_Requests.Request_No)=63));"
DoCmd.RunSQL "UPDATE PD_Requests SET PD_Requests.IFC = IIf([PD_Requests].[IFC]=-1,0,IIf([PD_Requests].[IFC]=0,-1,0))WHERE (((PD_Requests.Request_No)= " & CurRec & " ));"
End Function

 

[Travis]

1. I don't understand why the first query uses yes,no and the second uses -1,0

2. Try this:

Public Function ToggleIFC()

Dim CurRec As Long
Dim strSQL as String
' Stores the value of F_Main_subform's current record in the variable CurRec.
CurRec = Form_F_Main_subform.CurrentRecord
' Executes an Update Query to toggle the IFC field in the current record.
' strSQL = "UPDATE PD_Requests SET PD_Requests.IFC = IIf([PD_Requests].[IFC]=Yes,No,IIf([PD_Requests].[IFC]=No,Yes,No))WHERE (((PD_Requests.Request_No)=63));"
strSQL = "UPDATE PD_Requests SET PD_Requests.IFC = IIf([PD_Requests].[IFC]=-1,0,IIf([PD_Requests].[IFC]=0,-1,0))WHERE (((PD_Requests.Request_No)= " & CurRec & " ));"

Debug.Print strSQL

DoCmd.RunSQL strSQL

End Function

Then copy the SQL statement from the Imediate (Debug) window into the SQL view of a Blank Query. Attempt to run it from there.

 

[brewpedals]

Many Thanks Travis!

It worked great.

The "Debug.Print strSQL" helped my find another mistake - I changed my CurRec declaration to point to the control that holds the primary key. (Da!) CurrentRecord just returned 2 if I selected the second record in the subform and 3 for the third, etc.

Peace.

1. I don't understand why the first query uses yes,no and the second uses -1,0

2. Try this:

Public Function ToggleIFC()

Dim CurRec As Long
Dim strSQL as String
' Stores the value of F_Main_subform's current record in the variable CurRec.
CurRec = Form_F_Main_subform.CurrentRecord
' Executes an Update Query to toggle the IFC field in the current record.
' strSQL = "UPDATE PD_Requests SET PD_Requests.IFC = IIf([PD_Requests].[IFC]=Yes,No,IIf([PD_Requests].[IFC]=No,Yes,No))WHERE (((PD_Requests.Request_No)=63));"
strSQL = "UPDATE PD_Requests SET PD_Requests.IFC = IIf([PD_Requests].[IFC]=-1,0,IIf([PD_Requests].[IFC]=0,-1,0))WHERE (((PD_Requests.Request_No)= " & CurRec & " ));"

Debug.Print strSQL

DoCmd.RunSQL strSQL

End Function

Then copy the SQL statement from the Imediate (Debug) window into the SQL view of a Blank Query. Attempt to run it from there.