Syntax error, what am I missing?

[Shenlung]

I know it's something simple like having an apostrophe instead of a quote or something similar, but I am missing it. Every extra pair of eyes helps, we're playing "Where's Waldo" with the error in the code!

   DoCmd.SetWarnings False
    If Me.txtLocation = "IN" And Me.txtRecordType = "M" Then
      DoCmd.RunSQL "UPDATE MedicalRecords SET MedStatus= 'IN', " & _
      "MedOut= Null, " & _
      "MedDue= Null, " & _
      "Memo=" & Me.TxtMemo & ", " & _
      "WHERE SSno='" & _
      Me.txtSSno & "'"

I know the names are all correct, the table is MedicalRecords, and all the set fields are columns on the table; the txt**** are all text fields on the input form. Find the screw up, win a prize!

 

[spikepl]

Too much too fast. That's why.

Do it in steps

Dim strSQL as String
strSQL="UPDATE MedicalRecords SET MedStatus= 'IN', " & _       "MedOut= Null, " & _       "MedDue= Null, " & _       "Memo=" & Me.TxtMemo & ", " & _       "WHERE SSno='" & _       Me.txtSSno & "'
debug.print strSQL
DoCmd.runSQL strSQL

look at the output of debug.print strSQL and the solution should come to you

 

[spikepl]

Besides that, Memo is a reserved word and should not be used as a name for a field.

 

[Shenlung]

Ok, changed Memo over to Notes, changed field to match and form field to txtNotes. Also tried various combinations of apostrophes and quotes to see if I could make it work, this is the code as it stands now, still giving me a syntax error:

Dim strSQL As String

    DoCmd.SetWarnings False
    If Me.txtLocation = "IN" And Me.txtRecordType = "M" Then
      strSQL = "UPDATE MedicalRecords SET MedStatus= 'IN', " & _
      "MedOut=" & vbNullString & ", " & _
      "MedDue=" & vbNullString & ", " & _
      "Notes=" & Me.TxtNotes & ", " & _
      "Modified= #" & Now() & "#, " & _
      "ModifiedBy=" & Me.txtMod & ", " & _
      "WHERE SSno=" & _
      Me.txtSSno & ""
    Debug.Print strSQL

The debug print looks like this after 4 runs with differing apostrophe usage

UPDATE MedicalRecords SET MedStatus= 'IN', MedOut=, MedDue=, Memo='', Modified= '#7/8/2011 10:09:33 AM#', ModifiedBy='CLK', WHERE SSno='123456789'
UPDATE MedicalRecords SET MedStatus= 'IN', MedOut=, MedDue=, Memo='', Modified= '#7/8/2011 10:10:14 AM#', ModifiedBy='CLK', WHERE SSno='123456789'
UPDATE MedicalRecords SET MedStatus= 'IN', MedOut=, MedDue=, Notes='', Modified= '#7/8/2011 10:13:05 AM#', ModifiedBy='CLK', WHERE SSno='123456789'
UPDATE MedicalRecords SET MedStatus= 'IN', MedOut=, MedDue=, Notes=, Modified= #7/8/2011 10:18:44 AM#, ModifiedBy=CLK, WHERE SSno=123456789
UPDATE MedicalRecords SET MedStatus= 'IN', MedOut=, MedDue=, Notes=, Modified= #7/8/2011 10:19:23 AM#, ModifiedBy='CLK', WHERE SSno='123456789'

Added in the Modified and ModifiedBy fields per supervisors request...more crap to make work, I guess.

 

[spikepl]

strings (as detemined by the type of the field set in the table) must be wrapped in '' or chr(34) & myStringvalue & Chr(34) , but NOT numbers. What is ssno?

 

[spikepl]

Plug your
UPDATE MedicalRecords SET MedStatus= 'IN', MedOut=, MedDue=, Memo='', Modified= '#7/8/2011 10:09:33 AM#', ModifiedBy='CLK', WHERE SSno='123456789'

into the query designer (opened in SQLview) and play with it until it stops complaining. That's the fastes way to check for syntax error.

 

[Shenlung]

SSno is a 9 digit number string. It's a plain text text box on the form, a number in the table. Working on forcing the txtSSno value to be a number now.

Here's the code now:

Dim strSQL As String

    DoCmd.SetWarnings False
    If Me.txtLocation = "IN" And Me.txtRecordType = "M" Then
      strSQL = "UPDATE MedicalRecords SET MedStatus= 'IN', " & _
      "MedOut='', " & _
      "MedDue='', " & _
      "Notes='" & Me.TxtNotes & "', " & _
      "Modified= #" & Now() & "#, " & _
      "ModifiedBy='" & Me.txtMod & "' " & _
      "WHERE SSno='" & _
      Me.txtSSno & "'"
    Debug.Print strSQL
    DoCmd.RunSQL strSQL

It generates all the info correctly, but gives a "Data type mismatch in criteria expression" error, which I am pretty sure is caused by the text vs. number issue in the SSno field on form and table. The SSno on the table is also the primary key since there will never be any duplicates.

Also, the data type mismatch doesn't pop in the SQLView query designer, only in the actual vba code when trying to run the DoCmd.RunSQL.

 

[spikepl]

if SSNO is a NUMBER in the table, then no quotes around it in your sql string.

Remeber that the WHOLE of your SQL string made in VBA is a text string, so a "fielldName=" & number works for numbers, no matter whether you type eg 5 or type txtBoxWithMyNumber. For strings you need quotes, no matter where they come from. It is the field type that determines it.

 

[Shenlung]

So this should work then :

Dim strSQL As String

    DoCmd.SetWarnings False
    If Me.txtLocation = "IN" And Me.txtRecordType = "M" Then
      strSQL = "UPDATE MedicalRecords SET MedStatus= 'IN', " & _
      "MedOut='', " & _
      "MedDue='', " & _
      "Notes='" & Me.TxtNotes & "', " & _
      "Modified= #" & Now() & "#, " & _
      "ModifiedBy='" & Me.txtMod & "' " & _
      "WHERE SSno=" & Me.txtSSno & ""
    Debug.Print strSQL
    DoCmd.RunSQL strSQL

It works beautifully, but it pops a blank Msgbox; and if I click into the table for everything to update, it stops working altogether and gives me a "Invalid SQL Statement; expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE" Running the ssn through the form again seems to have a 50/50 shot of generating the error again, or working as expected.

So, the question is : why is the blank box popping up, and why is it deciding to ignore the update command for no apparent reason?

Also, thanks for all the help so far, this issue has been perplexing me for almost 2 weeks now, and I was getting nowhere with it.

EDIT : Figured out the blank box, it was a remnant from the previous coders stuff, cut it out and the box is gone.

 

[spikepl]

does it also do it if you extract the strSQL, plug it into the query designer and run THAT query?

 

[Shenlung]

EDIT : RErevious post, It stopped doing it as soon as I plugged Me.Refresh into the end code, I think it had something to do with the table not updating. I clicked on the table initially to see why there had been no changes, then the changes showed up and that's when it spit the error... the refresh makes the data appear automatically now, and i haven't seen the error again on 50+ iterations.

Ok, final code looks like the following:

Option Compare Database

Private Sub Form_Activate()
  Me.txtSSno = Nothing
  Me.txtName = Nothing
  Me.TxtNotes = Nothing
  Me.txtSSno.SetFocus
End Sub

Private Sub txtSSno_AfterUpdate()
Dim rsTemp As ADODB.Recordset
  
  On Error Resume Next
  Err.Clear
  Set rsTemp = New ADODB.Recordset
  rsTemp.Open "SELECT * from MedicalRecords WHERE SSno=" & Me.txtSSno & "", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
  If Err.Number <> 0 Then
    MsgBox "SSno not on file.  Click on Menu, then Add a new record, and return here.", vbInformation, "Missing Number!"
    Exit Sub
  End If
  rsTemp.MoveFirst
  Me.txtName = rsTemp.Fields("lastname") & ", " & rsTemp.Fields("firstname") & "; [" & rsTemp.Fields("ssno") & "] "
  rsTemp.Close
  Set rsTemp = Nothing
Dim strSQL As String

    DoCmd.SetWarnings False
    If Me.txtLocation = "IN" And Me.txtRecordType = "M" Then
      strSQL = "UPDATE MedicalRecords SET MedStatus= 'IN', " & _
      "MedOut='', " & _
      "MedDue='', " & _
      "Notes='" & Me.TxtNotes & "', " & _
      "Modified= #" & Now() & "#, " & _
      "ModifiedBy='" & Me.txtMod & "' " & _
      "WHERE SSno=" & Me.txtSSno & ""
    'Debug.Print strSQL
    DoCmd.RunSQL strSQL
    
    ElseIf Me.txtLocation = "IN" And Me.txtRecordType = "D" Then
      strSQL = "UPDATE MedicalRecords SET DenStatus= 'IN', " & _
      "DenOut='', " & _
      "DenDue='', " & _
      "Notes='" & Me.TxtNotes & "', " & _
      "Modified= #" & Now() & "#, " & _
      "ModifiedBy='" & Me.txtMod & "' " & _
      "WHERE SSno=" & Me.txtSSno & ""
    'Debug.Print strSQL
    DoCmd.RunSQL strSQL
    
    ElseIf Me.txtLocation = "OUT" And Me.txtRecordType = "M" Then
      strSQL = "UPDATE MedicalRecords SET MedStatus= 'OUT', " & _
      "MedOut= #" & Now() & "#, " & _
      "MedDue= #" & DateAdd("d", 14, Now()) & "#, " & _
      "Notes='" & Me.TxtNotes & "', " & _
      "Modified= #" & Now() & "#, " & _
      "ModifiedBy='" & Me.txtMod & "' " & _
      "WHERE SSno=" & Me.txtSSno & ""
    'Debug.Print strSQL
    DoCmd.RunSQL strSQL
      
    ElseIf Me.txtLocation = "OUT" And Me.txtRecordType = "D" Then
      strSQL = "UPDATE MedicalRecords SET DenStatus='OUT', " & _
      "DenOut= #" & Now() & "#, " & _
      "DenDue= #" & DateAdd("d", 14, Now()) & "#, " & _
      "Notes='" & Me.TxtNotes & "', " & _
      "Modified= #" & Now() & "#, " & _
      "ModifiedBy='" & Me.txtMod & "' " & _
      "WHERE SSno=" & Me.txtSSno & ""
    'Debug.Print strSQL
    DoCmd.RunSQL strSQL
      
    ElseIf Me.txtLocation <> "IN" And Me.txtLocation <> "OUT" And Me.txtRecordType = "M" Then
      strSQL = "UPDATE MedicalRecords SET MedStatus= '" & Me.txtLocation & "', " & _
      "MedOut= #" & Now() & "#, " & _
      "MedDue= #" & DateAdd("d", 14, Now()) & "#, " & _
      "Notes= '" & Me.TxtNotes & "', " & _
      "Modified= #" & Now() & "#, " & _
      "ModifiedBy='" & Me.txtMod & "' " & _
      "WHERE SSno=" & Me.txtSSno & ""
    'Debug.Print strSQL
    DoCmd.RunSQL strSQL
        
    ElseIf Me.txtLocation <> "IN" And Me.txtLocation <> "OUT" And Me.txtRecordType = "D" Then
      strSQL = "UPDATE MedicalRecords SET DenStatus='" & Me.txtLocation & "', " & _
      "DenOut= #" & Now() & "#, " & _
      "DenDue= #" & DateAdd("d", 14, Now()) & "#, " & _
      "Notes='" & Me.TxtNotes & "', " & _
      "Modified= #" & Now() & "#, " & _
      "ModifiedBy='" & Me.txtMod & "' " & _
      "WHERE SSno=" & Me.txtSSno & ""
    'Debug.Print strSQL
    DoCmd.RunSQL strSQL
    
    End If
    DoCmd.SetWarnings True
    Me.Refresh
    txtSSno = ""
    Me.txtName = ""
    Me.TxtNotes = ""
    Me.TxtNotes.SetFocus
    Me.txtSSno.SetFocus


    
End Sub

Might not be the most elegant thing on the planet, but it gets the job done. I'm all ears for suggestions on this, feel free.

@spikepl: I cannot say enough good about you, you are the best, thank you for everything!

 

[spikepl]

Glad it works. Just one concern: a number of your text inputs have a space in front, like
=' IN'
this is something that can turn into one heck of a trap down the road, since a leading space is not always obvious. What function has this space? Or is it just the forum that yet again, confusingly, added spaces here and there?

 

[Shenlung]

I'm not seeing any spaces inside the quotes, they are all =space'IN' or =space"IN"... might be the forums lol

My work day is over, I'll check back in the forums next Tuesday, have a great weekend, and thanks again!