Expected: end of statement

[annie52]

I'm getting an Expected: end of statement error message when I try to compile this code.

strSQL = "UPDATE ReqnWatch SET DLATicketNum = " _
& Forms!frmReqnWatch!frmDLATicket.Form!DLATicketNum _
& "WHERE (JD)=" & Forms!frmReqnWatch!JD _
& "(Serial) =" & Forms!frmReqnWatch!Serial;

I'm just learning how to write SQL statements and they're killing me. I'm not sure when to use parentheses, single quotes, double quotes, double-single quotes, etc. If anyone has clear guidance or knows of something I can read that will help me understand better, I'll be so grateful.

 

[lameid]

strSQL = "UPDATE ReqnWatch SET DLATicketNum = " _
    & Forms!frmReqnWatch!frmDLATicket.Form!DLATicketNum _
    & "WHERE (JD)=" & Forms!frmReqnWatch!JD _
    & "(Serial) =" & Forms!frmReqnWatch!Serial [Red]& "[/Red];[Red]"[/Red]

The trick is working with SQL embedded in VBA code as you have done here. As for learning SQL, it is perhaps easies to use the query designer make a query and switch to SQL view to get the syntax you want. Beyond that I love the Access Developer Handbook series for all things Access. Those books are published by Sybex. I can't remember the title but there is a book published by Wrox that covers ANSI SQL, both 1989 and 1992. Almost every database uses some variation or enhancement to ANSI SQL. So the question is what is different.

Access both in SQL and VBA Code can use either single or double quotes. They are equal. When you have a string in either that is delimited by either a single or double quote, if the delimeter used is in the string you have to double it up to be in there. Both of the below examples are examples of a one double quote string in code.

'[red]"[/red]'
"[red]""[/red]"

Check out the thread below for more on this topic...
thread705-1546743

Parenthesis.... Generally you use them just like in math. You can also take them out like in math. Access likes to put a lot of extra parenthesis into SQL where clauses. Beyond that they can be needed as part of syntax like in an Update statement.

One more thing to know Square Brackets - []. These go around table and field names that Access can't not get confused about... Delitemter charcters, operands, and white space are just some off the top of my head. If any of that is in your table or field name, use a pair around the table or field name where necessary. You can always use them so don't hesitate to use them liberally.

 

[TheAceMan1]

How are ya annie52 . . .

I'm a stickler for easy reading of code (particularly my own). So what I do is format my code for just that ... easy reading! Any VBA programmer wants to easily parse thru their code when troubleshooting. This is really what formatting code is all about.

[blue]You don't make your code easily readable to others ... [purple]You make your code easily readable to you![/purple] ... after all ... you are the programmer![/blue]

One of the techniques used is setting up objects to [blue]take care of those long names[/blue]. In enough cases you may add a few extra un-needed lines, but the easy reading more than makes up for the diference.

Moving on to your SQL, I always format for major SQL keywords to be on the left, and indent so they stand out. I also put ampersands [blue]&[/blue] on the right for an even better view. Consider your SQL below, rewritten with objects for easy reading:

[blue]strSQL = "UPDATE ReqnWatch SET DLATicketNum = " _
    & Forms!frmReqnWatch!frmDLATicket.Form!DLATicketNum _
    & "WHERE (JD)=" & Forms!frmReqnWatch!JD _
    & "(Serial) =" & Forms!frmReqnWatch!Serial;

[purple]Becomes![/purple]

   Dim frm As Form, sfrm As Form, SQL As String
   
   Set frm = Forms!frmReqnWatch
   Set sfrm = frm!frmDLATicket.Form

   SQL = "UPDATE [ReqnWatch] " & _
         "SET [DLATicketNum] = " & sfrm!DLATicketNum & " " & _
         "WHERE ([JD] = " & frm!JD & ") AND (" & _
                "[Serial] = " & frm!Serial & ");"

   Set sfrm = Nothing
   Set frm = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[annie52]

Thank you both for the help.

TheAceMan1, I definitely see what you mean about the easier read.

Now, I see that I'm still getting the error 3058 that I reported in another thread (Index or Primary Key cannot contain a Null value). I can't figure out how to fix this.

The query behind my subform contains the DLATicketNum from my ReqnWatch table. It has a many-to-one relationship with the DLATickets table. When the user enters a DLATicketNumber that already exists in the DLATickets table into the subform, the subform automatically populates the data. However, when I requery, I get the error and the DLATicketNum is not updated in the ReqnWatch table. This is not a required or indexed field so I don't get it. Three fields (DODAAC, JD, and Serial) in the ReqnWatch table are set as the primary key. I have a default value for the DODAAC so the user does not have to enter the information in the form. Could this be my problem?

 

[lameid]

What if any are the record scurces of your forms?

When are you running the code / what are you intending to do?

 

[TheAceMan1]

annie52 . . .

Unless you post the rest of the code using [blue]strSQL[/blue] my best guess is that you did not end some statement. Examples:

[tt]If Then Do Select Case
[blue]End If Loop EndSelect[/blue][/tt]

There are others.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[annie52]

I wasn't ignoring y'all; I just never had the opportunity to get back until now.

It turns out that I actually had a many-to-many relationship between the two tables. Once I realized that, I created a junction table which left me with two one-to-many relationships. Everything runs well now.