Access VBA Update query syntax

[Carl23]

There are two tables Nursing_Note and Temp_Nursing_No te. Once the form Temp_Nursing_No te is completed the user enters a part of their password then hits a command button that triggers an UPDATE query. The code is giving me a syntax error, please help.
Thanks,
Carl23

Private Sub Command35_Click()
Dim strSql As String
    strSql = "UPDATE [Nursing_Note] SET [Temp_Nursing_Note].[Nursing_Note_Number] = [Nursing_Note]![Temp_Note_Number],"
    strSql = strSql & " [Temp_Nursing_Note].[Resident_Num] = [Nursing_Note]![Resident_Num],"
    strSql = strSql & " [Temp_Nursing_Note].[Subject] = [Nursing_Note]![Subject],"
    strSql = strSql & " [Temp_Nursing_Note].[Nursing_Note_Data] = [Nursing_Note]![Nursing_Note_Data],"
    strSql = strSql & " [Temp_Nursing_Note].[Date] = [Nursing_Note]![Date], Temp_Nursing_Note.[Time] = [Nursing_Note]![Time],"
    strSql = strSql & " [Temp_Nursing_Note].[Staff] = [Nursing_Note]![Staff], Temp_Nursing_Note.Nurse4digitpw = [Nursing_Note]![Nurse4digitpw],"
    strSql = strSql & " WHERE ((Right$(Password,4) = [Temp_Nursing_Note.Nurse4digitpw]);"
DoCmd.RunSQL strSql
End Sub

[Rabbit]

1) You say UPDATE [Nursing_Note] but you're trying to SET [Temp_Nursing_No te].Obviously one is wrong and one is correct. From your description, I assume you're not trying to set the temp table.

2) Also, you can't reference another table's value unless you're joining to it.

3) Your brackets are incorrect here: [Temp_Nursing_No te.Nurse4digitp w]

4) I suspect point 2 doesn't apply because I think you're trying to reference form controls and not the table. That is not how you reference form controls. It's Forms!formName! controlName. However, that only works in query designer and not through DoCmd.RunSQL. So you can't use that. You need to concatenate the actual value into the string. Don't forget to use the appropriate quotes and escape any within the string.

[Carl23]

Thank you for your help. I have revised my code and am having some success. However, I am having difficulty get the date and time fields to insert. I have changed their field names to DateTemp and TimeTemp. Can you please help me with the formatting of the insert statement for the DateTemp and TimeTemp fields? My current code is:

Private Sub Command35_Click()
MsgBox "Me.Nurse4digitpw:  " & Nurse4digitpw
MsgBox "Me.Staff:  " & Staff
CurrentDb.Execute "INSERT INTO Nursing_Note (Temp_Note_Number , Resident_Num, Nurse4digitpw, Subject) VALUES (" & Me.Nursing_Note_Number & ", " & Me.Resident_Num & ", '" & Me.Nurse4digitpw & "','" & Me.Subject & "')"
MsgBox "Record inserted "
 
 
 
MsgBox "Password did not match staff name  "
End Sub
'Staff
'Subject
'Me.Nursing_Note_Data
 

'Date
 
'Time
'inserting numbers
'CurrentDb.Execute "INSERT INTO TableA (FieldA, FieldB) VALUES (" & TextBoxA.Value & ", " & TextBoxB.Value & ")"
'insert text
'CurrentDb.Execute "INSERT INTO TableA (FieldA, FieldB) VALUES ('" & TextBoxA.Value & "', '" & TextBoxB.Value & "')"
'insert dates
'CurrentDb.Execute "INSERT INTO TableA (FieldA, FieldB) VALUES (#" & Format(TextBoxA.Value,"mm/dd/yyyy"* ) & "#, #" & Format(TextBoxB.Value,"mm/dd/yyyy"* )

Thanks,
Carl23

[NeoPa]

Carl,
You have posted no code that deals with dates or times. Please check out Before Posting (VBA or SQL) Code.

As for general tips and help on dealing with dates and times, see Literal DateTimes and Their Delimiters (#). This should tell you all you need to know.

If, after that, you still need to ask a question on this point then please do so in a separate thread as required by the site rules. This (Dates etc) is an area where many people seem to struggle, but which is fundamentally very logical and, by extension, simple when you have the basics sorted.