By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Access VBA Update query syntax

P: 22
There are two tables Nursing_Note and Temp_Nursing_Note. Once the form Temp_Nursing_Note 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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command35_Click()
  2. Dim strSql As String
  3.     strSql = "UPDATE [Nursing_Note] SET [Temp_Nursing_Note].[Nursing_Note_Number] = [Nursing_Note]![Temp_Note_Number],"
  4.     strSql = strSql & " [Temp_Nursing_Note].[Resident_Num] = [Nursing_Note]![Resident_Num],"
  5.     strSql = strSql & " [Temp_Nursing_Note].[Subject] = [Nursing_Note]![Subject],"
  6.     strSql = strSql & " [Temp_Nursing_Note].[Nursing_Note_Data] = [Nursing_Note]![Nursing_Note_Data],"
  7.     strSql = strSql & " [Temp_Nursing_Note].[Date] = [Nursing_Note]![Date], Temp_Nursing_Note.[Time] = [Nursing_Note]![Time],"
  8.     strSql = strSql & " [Temp_Nursing_Note].[Staff] = [Nursing_Note]![Staff], Temp_Nursing_Note.Nurse4digitpw = [Nursing_Note]![Nurse4digitpw],"
  9.     strSql = strSql & " WHERE ((Right$(Password,4) = [Temp_Nursing_Note.Nurse4digitpw]);"
  10. DoCmd.RunSQL strSql
  11. End Sub
May 21 '12 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,315
1) You say UPDATE [Nursing_Note] but you're trying to SET [Temp_Nursing_Note].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_Note.Nurse4digitpw]

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.
May 21 '12 #2

P: 22
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command35_Click()
  2. MsgBox "Me.Nurse4digitpw:  " & Nurse4digitpw
  3. MsgBox "Me.Staff:  " & Staff
  4. 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 & "')"
  5. MsgBox "Record inserted "
  6.  
  7.  
  8.  
  9. MsgBox "Password did not match staff name  "
  10. End Sub
  11. 'Staff
  12. 'Subject
  13. 'Me.Nursing_Note_Data
  14.  
  15.  
  16. 'Date
  17.  
  18. 'Time
  19. 'inserting numbers
  20. 'CurrentDb.Execute "INSERT INTO TableA (FieldA, FieldB) VALUES (" & TextBoxA.Value & ", " & TextBoxB.Value & ")"
  21. 'insert text
  22. 'CurrentDb.Execute "INSERT INTO TableA (FieldA, FieldB) VALUES ('" & TextBoxA.Value & "', '" & TextBoxB.Value & "')"
  23. 'insert dates
  24. 'CurrentDb.Execute "INSERT INTO TableA (FieldA, FieldB) VALUES (#" & Format(TextBoxA.Value,"mm/dd/yyyy"* ) & "#, #" & Format(TextBoxB.Value,"mm/dd/yyyy"* )
Thanks,
Carl23
May 27 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 27 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.