Trouble with update query (1 Viewer)

D

dkmoreland

Registered User.
Local time
Today, 01:33
Joined
Dec 6, 2017
Messages
129
I have a couple of questions, please.

First, I am trying to use an update query to update a field in a second table. Here's the setup:
New record is being added to the Input table - the job number field is populated and when the "Job Complete" checkbox is clicked, the Update Job Status form is opened. Once the user chooses the status and clicks Continue, I want to update the Job Status field for that job number in a second table (Job Info).

Here's my query - I'm getting a syntax error and I don't know where I have messed it up. FWIW - I inherited this database so I am not responsible for the funky field names. :D


Code: 
CurrentDb.Execute "UPDATE [Job Info] INNER JOIN [Input] ON [Job Info].[Job#] = [Input].[Job#] & SET [Job Info].[Job Status] = [Forms]![Update Job Status form]![cboJobStatus] WHERE (([Input].[Job#]=[Job Info].[Job#]))"

Here's the other question - how do I break a code statement into multiple lines? I tried the [space] _ but I must have done it in the wrong place.

Thanks in advance for any and all input.
 
isladogs

isladogs

MVP / VIP
Local time
Today, 09:33
Joined
Jan 14, 2017
Messages
18,212
Holy funky code names Batman ....

If its text, add text delimiters
Code: 
CurrentDb.Execute "UPDATE [Job Info] INNER JOIN [Input] ON [Job Info].[Job#] = [Input].[Job#] & _
SET [Job Info].[Job Status] = [COLOR="Red"]'" & [/COLOR][Forms]![Update Job Status form]![cboJobStatus] [COLOR="red"]& "' [/COLOR] & _
WHERE (([Input].[Job#]=[Job Info].[Job#]))"

If number field, then:
Code: 
CurrentDb.Execute "UPDATE [Job Info] INNER JOIN [Input] ON [Job Info].[Job#] = [Input].[Job#] & _
SET [Job Info].[Job Status] = [COLOR="Red"]" & [/COLOR][Forms]![Update Job Status form]![cboJobStatus] [COLOR="red"]& " [/COLOR] & _
WHERE (([Input].[Job#]=[Job Info].[Job#]))"

As you can see, use & _ to break the line

EDIT: Just read this properly - can't see why you need the WHERE clause as the INNER JOIN ensures that is true
So its:
Code: 
CurrentDb.Execute "UPDATE [Job Info] INNER JOIN [Input] ON [Job Info].[Job#] = [Input].[Job#] & _
SET [Job Info].[Job Status] = [COLOR="Red"]'" & [/COLOR][Forms]![Update Job Status form]![cboJobStatus] [COLOR="red"]& "'[/COLOR]"

or the number equivalent
Code: 
CurrentDb.Execute "UPDATE [Job Info] INNER JOIN [Input] ON [Job Info].[Job#] = [Input].[Job#] & _
SET [Job Info].[Job Status] = [COLOR="Red"]" & [/COLOR][Forms]![Update Job Status form]![cboJobStatus] [COLOR="red"] & "[/COLOR]"
 
Last edited:
D

dkmoreland

Registered User.
Local time
Today, 01:33
Joined
Dec 6, 2017
Messages
129
That superfluous WHERE clause was brought to you by the Department of Redundancy Department.

Any, I tried this and I'm getting a syntax error. Here's the code I entered:
Code: 
Private Sub CmdContinue_Click()

   CurrentDb.Execute "UPDATE [Job Info] INNER JOIN [Input] ON [Job Info].[Job#] = [Input].[Job#] & _
SET [Job Info].[Job Status] = '" & [Forms]![Update Job Status form]![cboJobStatus] & "'"


End Sub

Have I made a typo?
 
isladogs

isladogs

MVP / VIP
Local time
Today, 09:33
Joined
Jan 14, 2017
Messages
18,212
Department of Redundancy Department.
Click to expand...
:)

Assuming the default column in cboJobStatus is a text field, it looks OK.
It's a long time since I had a query with a # field but should be ok using [] brackets.
Which part is it highlighting as having an error?

You could try this:

Code: 
Private Sub CmdContinue_Click()

   CurrentDb.Execute "UPDATE [Job Info] INNER JOIN [Input] ON [Job Info].[Job#] = [Input].[Job#] & _
SET [Job Info].[Job Status] = '" & [Forms]![Update Job Status form].[cboJobStatus] & "';"

End Sub
 
D

dkmoreland

Registered User.
Local time
Today, 01:33
Joined
Dec 6, 2017
Messages
129
The debugger lands right here and highlights this:

Code: 
Private Sub CmdContinue_Click()

The only thing in that subroutine is what I posted last time. I'm kind of at a loss.
 
isladogs

isladogs

MVP / VIP
Local time
Today, 09:33
Joined
Jan 14, 2017
Messages
18,212
Suggest you post the relevant parts of your db & i'll take a look
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:33
Joined
Feb 28, 2001
Messages
27,167
The last time I saw a break at an event entry point, it was because I had done something to drastically change the form and I had to go back to the relevant event property, double click in that box, and (essentially) re-link to the extant code. That's not the only time I've seen that, but that was what fixed it the last time.

By any chance did you change the name of the control associated with that event?
 
J

JHB

Have been here a while
Local time
Today, 10:33
Joined
Jun 17, 2012
Messages
7,732
dkmoreland said:
..
Any, I tried this and I'm getting a syntax error. Here's the code I entered:
Code: 
Private Sub CmdContinue_Click()

   CurrentDb.Execute "UPDATE [Job Info] INNER JOIN [Input] ON [Job Info].[Job#] = [Input].[Job#] & _
SET [Job Info].[Job Status] = '" & [Forms]![Update Job Status form]![cboJobStatus] & "'"


End Sub
Have I made a typo?
Click to expand...
You've forgotten the " at the end of the first line and in the start of the next line.
Code: 
CurrentDb.Execute "UPDATE [Job Info] INNER JOIN [Input] ON [Job Info].[Job#] = [Input].[Job#] [B][COLOR=Red]" [/COLOR][/B]& _
[B][COLOR=Red]"[/COLOR][/B]SET [Job Info].[Job Status] = '" & [Forms]![Update Job Status form]![cboJobStatus] & "'"
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:33
Joined
Jan 20, 2009
Messages
12,852
ridders said:
As you can see, use & _ to break the line
Click to expand...

Yes that does work but strictly speaking, Line Continuation is not "& _" (ampersand, space, underscore) but simply " _" (space, underscore).

I also highly recommend placing the ampersand at the beginning of the next line along with any spaces required in the SQL.
For example:
Code: 
strSQL = "SELECT somefield" _
       & " FROM sometable" _
       & " WHERE somecondition"

Debug.Print strSQL

CurrentDb.Execute strSQL, dbFailOnError

As you can see the inclusion of the ampersands, string delimiters and the space between key words and object names are much more easily verified with this layout.

With any complex SQL command it is worth loading it into a variable so the exact command to be Executed can be read from the variable if there are any difficulties. That is the best way to recognise typos.

Unless you are happy for the command to silently fail under all conditions, it is best to include Execute's second argument so errors are raised.
 
isladogs

isladogs

MVP / VIP
Local time
Today, 09:33
Joined
Jan 14, 2017
Messages
18,212
Thanks JHB. The missing " at the end of line 1 was my fault. Sorry.:eek:

Agree with galaxiom about putting the space at the start of a line though personally I prefer the & at the end.
The important thing is to be consistent so you shouldn't make mistakes (ahem)!

The point about adding dbfailonerror was also important
 
M

Mark_

Longboard on the internet
Local time
Today, 01:33
Joined
Sep 12, 2017
Messages
2,111
I would also take a look at your naming conventions.

Special characters and spaces in field names can cause issues with some back ends and require you use brackets.

I'd also preface your tables and queries with something that reminds you exactly what they are used for AND allows you to quickly identify which is which.

For myself, I either prepend or append what type of variable I'm using if it isn't text. I normally preface dates with Dt and append ID or No for AutoNumber and Numerics.

While not required, it certainly makes it easier to identify if I misuse a variable or need to format for use in SQL.
 
D

dkmoreland

Registered User.
Local time
Today, 01:33
Joined
Dec 6, 2017
Messages
129
Sorry I have not responded to this for so long. Life is crazy. Thanks to everyone who weighed in on this, the query is working now. However, I forgot something important - I need to check for duplicates.

I added a DCount and another msgbox and modified the code to this:

Code: 
Private Sub CmdNewNCR_Click()
  Dim lngNCR As Long
    lngNCR = DMax("[NCR#]", "[Non Conformance]")
   ' Debug.Print lngNCR
    
    If (([Forms]![Input Form]![Accept/Recheck]) = "Recheck") Then
       [B]If DCount(ID, [Non Conformance], [Forms]![Input]![ID]) = 0 Then[/B]
            CurrentDb.Execute "INSERT INTO [Non Conformance] ( ID, [NCR#])" & _
            " SELECT " & [Forms]![Input Form]![ID] & " AS NewID, " & (lngNCR + 1) & " AS NewNCR" & _
            " FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID" & _
            " WHERE ((([Non Conformance].ID) Is Null));"
            
        DoCmd.Close
        MsgBox "NCR " & (lngNCR + 1) & " has been created."
        
        ElseIf DCount(ID, [Non Conformance], [Forms]![Input]![ID]) > 0 Then
            MsgBox ("An NCR already exists for this record ID")
        End If
    End If

End Sub

However, it is crashing on the bolded line with an error that says "can't find the field '|1' referred to in your expression".

How have I hosed this up? Any and all suggestions are appreciated. Also, I realize that it is entirely likely I have buggered up the IF, Elseif code so I would also appreciate some education on that matter.
 
D

dkmoreland

Registered User.
Local time
Today, 01:33
Joined
Dec 6, 2017
Messages
129
I fixed the Dcount problem - my syntax was all funky and my form name was not right. So here's the version of code that actually works.

Code: 
Private Sub CmdNewNCR_Click()
  Dim lngNCR As Long
    lngNCR = DMax("[NCR#]", "[Non Conformance]")
   ' Debug.Print lngNCR
    
    If (([Forms]![Input Form]![Accept/Recheck]) = "Recheck") Then
        If DCount("ID", "Non Conformance", "[Forms]![Input Form]![ID]") = 0 Then
            CurrentDb.Execute "INSERT INTO [Non Conformance] ( ID, [NCR#])" & _
            " SELECT " & [Forms]![Input Form]![ID] & " AS NewID, " & (lngNCR + 1) & " AS NewNCR" & _
            " FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID" & _
            " WHERE ((([Non Conformance].ID) Is Null));"
            
        DoCmd.Close
        MsgBox "NCR " & (lngNCR + 1) & " has been created."
        
        ElseIf DCount("ID", "Non Conformance", "[Forms]![Input Form]![ID]") > 0 Then
            MsgBox ("An NCR already exists for this record ID")
        End If
    End If

End Sub

Now, I need to do two other things in this routine - I need to add another field (Job#) in the Update query so I did this:

Code: 
CurrentDb.Execute "INSERT INTO [Non Conformance] ( ID, [NCR#], [Job#])" & _
            " SELECT " & [Forms]![Input Form]![ID] & " AS NewID, " & (lngNCR + 1) & " AS NewNCR" & _
            " [Forms]![Input Form]![Job#] AS NewJob & _
            " FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID" & _
            " WHERE ((([Non Conformance].ID) Is Null));"

But, I'm getting a syntax error. Suggestions?

The other thing I need to do is change the msgbox to show which NCR # has been found for that ID. Since I used Dcount to count the records, how can I tell the message box which record to look at?
 
Last edited:
isladogs

isladogs

MVP / VIP
Local time
Today, 09:33
Joined
Jan 14, 2017
Messages
18,212
You omitted a comma, end quotes & delimiters for Job#
Assuming all your variables are numbers, try this:

CurrentDb.Execute "INSERT INTO [Non Conformance] ( ID, [NCR#], [Job#])" & _
" SELECT " & [Forms]![Input Form]![ID] & " AS NewID, " & (lngNCR + 1) & " AS NewNCR," & _
" " & [Forms]![Input Form]![Job#] & " AS NewJob" & _
" FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID" & _
" WHERE ((([Non Conformance].ID) Is Null));"
Click to expand...

or if Job# is text then

CurrentDb.Execute "INSERT INTO [Non Conformance] ( ID, [NCR#], [Job#])" & _
" SELECT " & [Forms]![Input Form]![ID] & " AS NewID, " & (lngNCR + 1) & " AS NewNCR," & _
" '" & [Forms]![Input Form]![Job#] & "' AS NewJob" & _
" FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID" & _
" WHERE ((([Non Conformance].ID) Is Null));"
Click to expand...

I expect its been mentioned before but special characters like # should NEVER be used in field names

Also ... if I've correctly understood what you mean

Code: 
MsgBox "An NCR already exists for record with ID = " & lngNCR

The brackets are superfluous in that line
 
M

Mark_

Longboard on the internet
Local time
Today, 01:33
Joined
Sep 12, 2017
Messages
2,111
For myself, I would DIM a STRING, fill the STRING with your SQL, then use the STRING instead of the excessive amount you are passing to CurrentDb.Execute.

This will allow you to use one of several techniques to review your SQL prior to it being executed, thus allowing you to more easily validate what you will pass.

Ridders code thus becomes

Code: 
DIM asSQL as STRING
asSQL = "INSERT INTO [Non Conformance] ( ID, [NCR#], [Job#])" & _
" SELECT " & [Forms]![Input Form]![ID] & " AS NewID, " & (lngNCR + 1) & " AS NewNCR," & _
" '" & [Forms]![Input Form]![Job#] & "' AS NewJob" & _
" FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID" & _
" WHERE ((([Non Conformance].ID) Is Null));"

'debug.Print asSQL
'   OR
'msgbox "SQL IS " & asSQL
'   OR
'Me.TextBoxToShowSQL = asSQL

CurrentDb.Execute asSQL

You would then see something similar to the below prior to the execute;

INSERT INTO [Non Conformance] ( ID, [NCR#], [Job#]) SELECT 1 AS NewID, 2 AS NewNCR,'3' AS NewJob FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID WHERE ((([Non Conformance].ID) Is Null));


Far easier to read and verify than the code used to build the string.
 
D

dkmoreland

Registered User.
Local time
Today, 01:33
Joined
Dec 6, 2017
Messages
129
ridders said:
I expect its been mentioned before but special characters like # should NEVER be used in field names
Click to expand...

Yes, I know. I mentioned in the original post that I inherited this message with the funky field names.

Thanks for your help - I knew I had missed something simple.
 
D

dkmoreland

Registered User.
Local time
Today, 01:33
Joined
Dec 6, 2017
Messages
129
Yes - I know about the field names. l mentioned in the original post that I inherited these funky field names. Changing them at this point would be a monumental task.


So, the update query is working now but my If statement to check for dupes is failing every time. It finds dupes where there are none and l cannot see why.

Code: 
Private Sub CmdNewNCR_Click()
  Dim lngNCR As Long
    lngNCR = DMax("[NCR#]", "[Non Conformance]")
      
  Dim asSQL As String
  asSQL = "INSERT INTO [Non Conformance] ( ID, [NCR#], [Job #])" & _
    " SELECT " & [Forms]![Input Form]![ID] & " AS NewID, " & (lngNCR + 1) & " AS NewNCR," & _
    " '" & [Forms]![Input Form]![Job #] & "' AS NewJob" & _
    " FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID" & _
    " WHERE ((([Non Conformance].ID) Is Null));"
    
    
    If (([Forms]![Input Form]![Accept/Recheck]) = "Recheck") Then
        If DCount("ID", "Non Conformance", "[Forms]![Input Form]![ID]") = 0 Then
            CurrentDb.Execute asSQL
            DoCmd.Close
            MsgBox "NCR " & (lngNCR + 1) & " has been created."
        ElseIf DCount("ID", "Non Conformance", "[Forms]![Input Form]![ID]") > 0 Then
            MsgBox ("An NCR already exists for this record ID")
        End If
    End If

End Sub
 
isladogs

isladogs

MVP / VIP
Local time
Today, 09:33
Joined
Jan 14, 2017
Messages
18,212
So, the update query is working now but my If statement to check for dupes is failing every time. It finds dupes where there are none and l cannot see why.
Click to expand...

That's because the syntax is wrong
It should be DCount(Field name, table/query name, filter criteria)

The criteria section "[Forms]![Input Form]![ID]" isn't filtering anything

It should be something like
Code: 
DCount("*", "Non Conformance", "ID = " & [Forms]![Input Form]![ID])
assuming ID is a number field

So that could be:

Code: 
Private Sub CmdNewNCR_Click()
  Dim lngNCR As Long
    lngNCR = DMax("[NCR#]", "[Non Conformance]")
      
  Dim asSQL As String
  asSQL = "INSERT INTO [Non Conformance] ( ID, [NCR#], [Job #])" & _
              " SELECT " & [Forms]![Input Form]![ID] & " AS NewID, " & (lngNCR + 1) & " AS NewNCR," & _
              " '" & [Forms]![Input Form]![Job #] & "' AS NewJob" & _
                " FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID" & _
    " WHERE ((([Non Conformance].ID) Is Null));"
    
    If (([Forms]![Input Form]![Accept/Recheck]) = "Recheck") Then
        [COLOR="red"]If DCount("*", "Non Conformance", "ID = " & [Forms]![Input Form]![ID]) = 0 Then
[/COLOR]            CurrentDb.Execute asSQL
            DoCmd.Close
            MsgBox "NCR " & (lngNCR + 1) & " has been created."
        [COLOR="Red"]Else 
            MsgBox "An NCR already exists for record with ID = " & lngNCR
[/COLOR]        End If
    End If

End Sub

BUT the If section isn't really necessary as the aSQL code will only append a record if it doesn't exist.
So if you can do without your MsgBox statements you could just do something like

Code: 
Private Sub CmdNewNCR_Click()
  Dim lngNCR As Long
    lngNCR = DMax("[NCR#]", "[Non Conformance]")
      
    If (([Forms]![Input Form]![Accept/Recheck]) = "Recheck") Then
            CurrentDb.Execute  "INSERT INTO [Non Conformance] ( ID, [NCR#], [Job #])" & _
              " SELECT " & [Forms]![Input Form]![ID] & " AS NewID, " & (lngNCR + 1) & " AS NewNCR," & _
              " '" & [Forms]![Input Form]![Job #] & "' AS NewJob" & _
              " FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID" & _
              " WHERE ((([Non Conformance].ID) Is Null);"
            DoCmd.Close
    End If

End Sub
 
D

dkmoreland

Registered User.
Local time
Today, 01:33
Joined
Dec 6, 2017
Messages
129
it's all working the way I want it to now - many, many thanks to everyone that provided input. As always, I learned a lot from this exchange.

Let's call this one solved!

Here's how my final code looks, in case it can help anybody in the future.
Code: 
Private Sub CmdNewNCR_Click()
  Dim lngNCR As Long
    lngNCR = DMax("[NCR#]", "[Non Conformance]")
    
  Dim lngRecID As Long
  lngRecID = [Forms]![Input Form]![ID]

      
  Dim asSQL As String
  asSQL = "INSERT INTO [Non Conformance] ( ID, [NCR#], [Job #])" & _
    " SELECT " & [Forms]![Input Form]![ID] & " AS NewID, " & (lngNCR + 1) & " AS NewNCR," & _
    " '" & [Forms]![Input Form]![Job #] & "' AS NewJob" & _
    " FROM [Input] LEFT JOIN [Non Conformance] ON Input.ID = [Non Conformance].ID" & _
    " WHERE ((([Non Conformance].ID) Is Null));"
    
    
    If (([Forms]![Input Form]![Accept/Recheck]) = "Recheck") Then
        If DCount("*", "Non Conformance", "ID = " & [Forms]![Input Form]![ID]) = 0 Then
            CurrentDb.Execute asSQL
            DoCmd.Close
            MsgBox "NCR " & (lngNCR + 1) & " has been created."
        ElseIf DCount("*", "Non Conformance", "ID = " & [Forms]![Input Form]![ID]) > 0 Then
            MsgBox ("An NCR already exists for record" & " " & (lngRecID))
            DoCmd.Close
        End If
    End If

End Sub
 
isladogs

isladogs

MVP / VIP
Local time
Today, 09:33
Joined
Jan 14, 2017
Messages
18,212
Just to repeat - in case anyone else does want to use it ....

The line:
Code: 
ElseIf DCount("*", "Non Conformance", "ID = " & [Forms]![Input Form]![ID]) > 0 Then

should just be
Code: 
Else

REASON: If the value isn't zero, it HAS to be >0. There is no other possibility
 
You must log in or register to reply here.

Similar threads

ZKHADI
Multiple Items related to Customer Issue!
Replies
11
Views
476
XPS35
XPS35
J
update field
Replies
4
Views
276
Pat Hartman
P
N
Enter Parameter Value Error
Replies
11
Views
347
Gasman
Gasman
P
Multiple criteria on DCount including date field
2
Replies
31
Views
1,155
cheekybuddha
cheekybuddha
J
Running total by date
Replies
4
Views
473
GaP42
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom