Record value from 3rd open form to table bound to 1st form

[Zydeceltico]

Good stuff. I've been busy with some yard work, and I see Gasman has given great advice. Now you're cookin'.

Re your last post:

And if that is the case then why are there double quote around Me.txtMiscThoughts outside of the ampersands in this line:

Code:
" SET MiscThoughts = '" & Me.txtMiscThoughts & "' , " & _

instead of reading like this:
Code:
" SET MiscThoughts = " & " Me.txtMiscThoughts " & " , " & _

I'm just curious how the the syntax for the string variables works.

This is a string " SET MiscThoughts = '" . When it is processed, the single quote will be the left end delimiter of the value of & Me.txtMiscThoughts which is a text datatype. The & is simply a concatenation symbol. During the rendering process where "Access" deciphers the vba, the value in
Me.txtMiscThoughts gets concatenated to the SET MiscThoughts = '. The & "' in &"' , " forms the right delimiter(the closing quote) of the string. The comma in that &"' , " separates the assignments in the SET statement.

The second example does NOT put the quotes around the string value in Me.txtMiscThoughts.
I said it was tricky, and the debug.print lets you check for errors before ever executing the SQL.

Thanks Jdraw. That's a great explanation. And I meant to tell you that the videos you shared earlier were also super helpful. The Immediate window and I are becoming well acquainted.

So I've tried migrating the concept over to the real db ad truly thought I had it down but I'm stymied. I'm getting an error message saying I have a syntax error in my SQL (3144) but for the life of me I cannot find it even using the Immediate window.

Here's the entire code for the "Save and Close" button:

Private Sub cmdSaveClose_Click()
   ' This procedure checks to see if the data on the form has
   ' changed. If the data has changed, the procedure prompts the
   ' user to continue with the save operation or to cancel it. Then
   ' the action that triggered the BeforeUpdate event is completed.

   Dim ctl As Control
   Dim strSQL As String
  
    strSQL = "UPDATE tblInspectionEvent " & _
        " SET Notes = '" & Me.txtNotes & "' , " & _
        " Photos = '" & Me.txtLinkToPhotos & "' , " & _
        " OilCanning = '" & Me.cboCanningYesNo & "' , " & _
        " CanningStopLine = '" & Me.cboCanningLineStop & "' , " & _
        " CoatingIssues = '" & Me.cboCoatingIssueYesNo & "' , " & _
        " CoatingStopLine = '" & Me.cboCoatingIssueLineStop & "' , " & _
        " WHERE InspectionEvent_PK = " & Me.txtInspectionEvent_ID & " ;"
        
        Debug.Print strSQL
        
   On Error GoTo Err_BeforeUpdate

   ' The Dirty property is True if the record has been changed.
   If Me.Dirty Then
      ' Prompt to confirm the save operation.
      If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
              "Save Record") = vbNo Then
         Me.Undo
         DoCmd.Close
      End If
      Else
        CurrentDb.Execute strSQL, dbFailOnError
        DoCmd.Close
'        strFormName.cmdAddPhotos.Enabled = False
   End If

Exit_BeforeUpdate:
   Exit Sub

Err_BeforeUpdate:
   MsgBox Err.Number & " " & Err.Description
   Resume Exit_BeforeUpdate
End Sub

The Immediate window returns this:
UPDATE tblInspectionEvent SET Notes = 'test test' , Photos = '' , OilCanning = 'no' , CanningStopLine = 'no' , CoatingIssues = 'no' , CoatingStopLine = 'no' , WHERE InspectionEvent_PK = 692 ; which looks correct to me.

I've been working on this for a couple of hours trying various combinations of endings but for the life of me I'm becoming more blind to it because the Immediate window looks like it is returning what I think it is supposed to be returning including the numeric value for InspectionEvent_PK . And Photos should be an empty string because I am not entering anything into it. It will eventually be a hyperlink to a folder.

Are you able to discern why this isn't working?

Thanks,

Tim

Are you able to see something I'm missing?

 

[jdraw]

Remove the comma before the WHERE and try again.

 

[Gasman]

No comma needed ',' after the last field being set?

https://docs.microsoft.com/en-us/of...ference/update-statement-microsoft-access-sql

 

[Zydeceltico]

Remove the comma before the WHERE and try again.

<sigh> so many commas.

I changed the line to read: " CoatingStopLine = '" & Me.cboCoatingIssueLineStop & "' " & _
.......and yes - it works!

Ach du lieber.

Thanks guys!

I was going blind trying to figure that one out. I have learned an enormous amount with this "little" challenge.

 

[Zydeceltico]

I was going blind trying to figure that one out. I have learned an enormous amount with this "little" challenge.

I see now that there should not be a comma before the WHERE statement in the Immediate window.

So much to learn.

 

[Gasman]

I see now that there should not be a comma before the WHERE statement in the Immediate window.

So much to learn.

Perhaps, but Google is your friend.

 

[Zydeceltico]

Perhaps, but Google is your friend.

Sometimes...................sometimes Google is my friend......................

Other times?............................................... one wonders

 

[jdraw]

If you format the update along this

Update tblTUpdate
Set
Field1 = "value1",
FieldNum = someNumber,
AnotherField ="someValue"
WHERE
Bob ="is Your uncle";

Where this is just a list of things, separated by commas
Field1 = "value1",
FieldNum = someNumber,
AnotherField ="someValue" <---the end of the list So no comma

 

[Zydeceltico]

If you format the update along this

Update tblTUpdate
Set
Field1 = "value1",
FieldNum = someNumber,
AnotherField ="someValue"
WHERE
Bob ="is Your uncle";

Where this is just a list of things, separated by commas
Field1 = "value1",
FieldNum = someNumber,
AnotherField ="someValue" <---the end of the list So no comma

That's a good model for me to follow. It's literally how one would write a list of things in a sentence. Won't forget that one.

 

[Zydeceltico]

Hi Guys -

This post would likely be better placed in a new post but - especially you jdraw - are already super familiar with what I'm about to ask relevant to the rest of the prior discussion.

Following the model you helped me work out in the CampingTrip mockup db, I've moved on to the Select Case portion of the sub.

Remembering that I'm capturing the name of the second form when opening the second form from the first form and putting it in a global variable ( gFromForm ), it appears that the value is not being passed all the way through to the third form for some reason. It works just fine in the mockup but following the same process in the real db it is not working.

Here is the code I have on Form1 that opens Form2 and places the string value of the name of Form2 in gFromForm. Notice the MsgBox that shows me that it is indeed making it this far......................................

(time has passed since I began writing this post and I am proud to tell you both that I figured out the issue on my own. Just thought you might like to hear that for a change.)

 

[jdraw]

Great accomplishment! And what was the solution????

 

[Zydeceltico]

Great accomplishment! And what was the solution????

global variable name typo.

 

[Zydeceltico]

global variable name typo.

Many MsgBoxes later..................I was like.................duh

 

[jdraw]

We've all been in the "too close to the issue to see the spelling problem"!
A good lesson for future activity.

 

[Gasman]

global variable name typo.

So do you have Option Explicit at the top of all modules?

 

[Zydeceltico]

So do you have Option Explicit at the top of all modules?

I do now.