deletion query

[ethan.geerdes]

 Update Advisory_Messages
Set Status = "Assigned"
Where Message Number = Me.Combo14

The result of the code above is "Compile Error: Variable not defined."

 

[Galaxiom]

Dim strSQL As String
  
strSQL = "Update Advisory_Messages" _
         & " Set Status = 'Assigned'" _
         & " Where [Message Number] =" &  Me.Combo14
  
Currentdb.Execute strSQL, dbFailOnError

 

[ethan.geerdes]

Private Sub Command16_Click()

Dim strSQL As String
  
strSQL = "Update Advisory_Messages" _
         & " Set Status = 'Assigned'" _
         & " Where [Message Number] =" & Me.Combo14
         

 
 'Dim olApp As Outlook.Application
  ' Dim objMail As Outlook.MailItem
   ' Set olApp = Outlook.Application
   
    'Set objMail = olApp.CreateItem(olMailItem)

    'With objMail
        '.Subject = "Test Message"
        '.Body = "Test"
        '.Recipients.To = Me.Combo10.Column(5)
        '.Recipients.ResolveAll
       ' .Display
   
  
 End Sub

So I got a syntax error so I started commenting out the lines to try and isolate it, anyways, I got it to run but it doesn't update the record. Any ideas?

 

[sneuberg]

Where did

Currentdb.Execute strSQL, dbFailOnError

go? That's what makes it happen

 

[ethan.geerdes]

So to get it to work I took out the line

Currentdb.Execute strSQL, dbFailOnError

I inserted it back in and I got the error "Syntax Error (missing operator) in Query Expression [Message Number] = Me.Combo14"

 

[sneuberg]

If Message Number is text the combo should be in single quotes like

Dim strSQL As String
  
strSQL = "Update Advisory_Messages" _
         & " Set Status = 'Assigned'" _
         & " Where [Message Number] = '" &  Me.Combo14 & "'"
  
Currentdb.Execute strSQL, dbFailOnError

 

[jdraw]

I agree with sneuberg. You need single quotes to enclose the text string.
The Currentdb.Execute strSQL, dbFailOnError is what actually runs the query. So it is critical.

 

[Galaxiom]

I got the error "Syntax Error (missing operator) in Query Expression [Message Number] = Me.Combo14"

As others have said, if [Message Number] is a text field then the value needs to be in single quotes. (BTW It would be a very poor choice of name to use the word "Number" in the name of a text field.)

However I doubt this would cause that error. Off the top of my head I would expect the error: "Type mismatch in query expression". I suspect there is no value being returned from Combo14.

Add this line before the Execute so you can see what is going to the Execute in the Immediate window:

Debug.Print strSQL

 

[ethan.geerdes]

If Message Number is text the combo should be in single quotes like

Dim strSQL As String
  
strSQL = "Update Advisory_Messages" _
         & " Set Status = 'Assigned'" _
         & " Where [Message Number] = '" &  Me.Combo14 & "'"
  
Currentdb.Execute strSQL, dbFailOnError

So I got this to work actually but then I messed around with the code a little bit to try and update the date. From a couple examples, All I had to do was throw the next column and value in under the

& " Set Status = 'Assigned'" _

So here is what I have currently

strSQL = "Update Fleet_Advisory_Messages" _
         & " Set Status = 'Assigned',"
         & " Date Assigned = Now()" _
         & " Where [FAM Number] = '" & Me.Combo14 & "'"
         
                
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
 
' Dim olApp As Outlook.Application
   'Dim objMail As Outlook.MailItem
   ' Set olApp = Outlook.Application
   
   ' Set objMail = olApp.CreateItem(olMailItem)

   ' With objMail
     '   .Subject = " Advisory Assignment"
        '.To =
    '    .Body = "Text"
   '     .Display
  '  End With
      
 End Sub

 

[sneuberg]

Try enclosing the now function in pound signs like:

strSQL = "Update Fleet_Advisory_Messages" _
         & " Set Status = 'Assigned',"
         & " Date Assigned = #" & Now() & "#" _
         & " Where [FAM Number] = '" & Me.Combo14 & "'"

 

[Minty]

You will need to put square brackets around Date Assigned as well.
Tip of the day for all newcomers - DON'T use spaces or weirdo characters in field or table names.

 

[jdraw]

You should avoid variable and field and control names with embedded spaces.
You need to enclose such names in square brackets [].
So Date Assigned needs to be [Date Assigned].

Now() gives you date and time.
Date() gives you date only.

Depends on your needs.

Good luck.

 

[ethan.geerdes]

Thank you. Is there a guide or anything on using SQL in VBA?

 

[sneuberg]

Thank you. Is there a guide or anything on using SQL in VBA?

Here's one

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

 

[jdraw]

Yes. A good reference in my view is from Martin Green. It starts here
There is a link within each section to the next.

 

[ethan.geerdes]

 & " [Date Assigned] = #" & Now() & "#" "

Compile Error: Syntax Error. Any more ideas?

 

[jdraw]

Try
& " [Date Assigned] = #" & Now() & "#"

I removed the trailing "

 

[ethan.geerdes]

Still giving me the compile syntax error. I'm reading through those guides that you guys gave me.

 

[jdraw]

Please post all the code in the event you are using.

 

[sneuberg]

Also a screen shot of the table in design view might help us.