How to use variables in a VBA Update statement? (1 Viewer)

candyA25 · Apr 10, 2014

Can someone tell me the syntax for using variables in a VBA update statement? I have the following that I want to use to update a record field.

Code:

Dim thisTbl as String
If Answer = vbYes Then
   If MedicationInvNo2 <> "" Then
      thisTbl = "tblMyMedData"
   Else
      thisTbl = "tblMedData"
   End If

   If thisTbl = "tblMyMedData" Then
      DoCmd.RunCommand acCmdUndo
      Me.RecordSource = "tblMyMedData"
      Me.Filter = "[InvNo] = '" & MedicationInvNo & "'"
      Me.FilterOn = True

      DoCmd.SetWarnings (False)

      SQL = "UPDATE " & thisTbl & _
      "SET Deleted = 0 " & _
      "WHERE ((" & thisTbl & ".InvNo)) = (([Forms]![frmMedDataEntry]![InvNo])); "
   Else
      DoCmd.RunCommand acCmdUndo
      Me.RecordSource = "tblMedData"
      Me.Filter = "[InvNo] = '" & MedicationInvNo & "'"
      Me.FilterOn = True

      DoCmd.SetWarnings (False)

      SQL = "UPDATE " & thisTbl & _
      "SET Deleted = 0 " & _
      "WHERE ((' & thisTbl & .InvNo' )) = (([Forms]![frmMedDataEntry]![InvNo])); "
   End If

   DoCmd.RunSQL SQL
   DoCmd.SetWarnings (True)
End If

I'm getting run-time error '3144' when the database tries to run the SQL. So I'm assuming my syntax is wrong (specifically in the WHERE clause)?

spikepl · Apr 10, 2014

Dunno what 3144 is - next time post the error text. In any case:

http://www.baldyweb.com/ImmediateWindow.htm

JHB · Apr 10, 2014

There is missing space between
... thisTbl & _
"SET ...
in both

SQL = "UPDATE " & thisTbl & _
" SET Deleted = 0 " & _
"WHERE ((" & thisTbl & ".InvNo)) = (([Forms]![frmMedDataEntry]![InvNo])); "

Then something is mix and missing in the below line.
"WHERE ((' & thisTbl & .InvNo' )) ...

SQL = "UPDATE " & thisTbl & _
" SET Deleted = 0 " & _
"WHERE ((" & thisTbl & ".InvNo)) = (([Forms]![frmMedDataEntry]![InvNo])); "

It can be shorten to:

SQL = "UPDATE " & thisTbl & _
" SET Deleted = 0 " & _
"WHERE InvNo = [Forms]![frmMedDataEntry]![InvNo]; "

plog · Apr 10, 2014

You really need to clean up your code. There really is no reason for that third If/Else block:

Code:

   If thisTbl = "tblMyMedData" Then
         DoCmd.RunCommand acCmdUndo
         Me.RecordSource = "tblMyMedData"
         ...
         ...
   Else
         DoCmd.RunCommand acCmdUndo
         Me.RecordSource = "tblMedData"
         ...

The only difference between the If and the Else blocks is the Me.RecordSource line and you already have the correct value assigned the thisTbl variable, so you can eliminate that difference.

I take that back, there is another difference between the If and the Else clause. The if has this for the last line:

Code:

"WHERE ((" & thisTbl & ".InvNo)) = (([Forms]![frmMedDataEntry]![InvNo])); "

The Else has this:

Code:

"WHERE ((' & thisTbl & .InvNo' )) = (([Forms]![frmMedDataEntry]![InvNo])); "

Are they suppose to be different? If not, that further strenghtens my case for cleaning up and reducing your code. Less is more.

How to use variables in a VBA Update statement? (1 Viewer)

candyA25

New member

spikepl

Eledittingent Beliped

JHB

Have been here a while

plog

Banishment Pending

Similar threads

Users who are viewing this thread