I am having trouble using a command button with a built VBA expression to update 3 fields in a record. VBA highlight my entire expression. Can someone help me??
Printable View
I am having trouble using a command button with a built VBA expression to update 3 fields in a record. VBA highlight my entire expression. Can someone help me??
Can you provide your code so that we can see what you are trying to do? Also need to know if you are using a bound or unbound form.
Code is listed below:Quote:
Originally Posted by Nwtech75
Private Sub Command32_Click()
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb
Dbs.Execute "UPDATE tblTicketInventory " _
& "SET [STUDENT_ID] = '" & Me.txtNetID & "', [LAST_NAME] = '" & Me.txtLastName & "', [FIRST_NAME] = '" & Me.txtFirstName & "'" _
& "WHERE [MONTH] = '" & Me.ListsMonth & "' AND [SHOW] = '" & Me.Cmbassignshow & "' AND [EVENT] = '" & Me.assignevent & "' AND [LOCATION] = '" & Me.assignlocation & "' AND [SECTION] = '" & Me.assignsection & "' AND [ROW] = '" & Me.assignrow & "' AND [SEAT] = '" & Me.assignseat & "' AND [DATE] = #" & Me.assigndate & "# AND [TIME] = #" & Me.assigntime & "#;"
MsgBox "Ticket Assigned!"
End Sub
The Code will update a ticket inventory table after we have already entered the ticket into it. We leave the Sttudent ID Last Name and First Name blank until we issue the ticket. the code is a part of a cmdbutton that is a part of the form to assign a ticket to a person.
A lot of this depends on if you are creating a new record or editing an existing record, it sounds like you are updating. In that case I am not seeing where you have opened the record first. Can you post a photo of the form that you are working with, either filled out or as it would appear when you open it? I could be wrong as I myself am still learning, but when I update tables I create the dbase connection and define the table.
Dim db As Database
Set db = CurrentDb
Dbs.Execute
Turn option explicit on
Hello jonh here is my code now. what is the syntax to turn option explicit on??
Private Sub Command32_Click()
Dim db As Database
Set db = CurrentDb
Dbs.Execute "UPDATE tblTicketInventory " _
& "SET [STUDENT_ID] = '" & Me.txtNetID & "', [LAST_NAME] = '" & Me.txtLastName & "', [FIRST_NAME] = '" & Me.txtFirstName & "'" _
& "WHERE [MONTH] = '" & Me.ListsMonth & "' AND [SHOW] = '" & Me.Cmbassignshow & "' AND [EVENT] = '" & Me.assignevent & "' AND [LOCATION] = '" & Me.assignlocation & "' AND [SECTION] = '" & Me.assignsection & "' AND [ROW] = '" & Me.assignrow & "' AND [SEAT] = '" & Me.assignseat & "' AND [DATE] = #" & Me.assigndate & "# AND [TIME] = #" & Me.assigntime & "#;"
MsgBox "Ticket Assigned!"
End Sub
Your database variable is db.
You are executing sql against dbs. dbs does not exist.
Option Explicit tells you when variables are not declared.
To turn it on simply put Option Explicit at the top of each module.
So the VBA is still giving me errors. I am trying to update a record's fields that has already been saved. For example" I take ticket inventory in and put it into to a table", next I want that VBA to execute an update of specific fields in one record of the ticket inventory table. Should I take a different approach instead of an update ticket form?
Attachment 18195
You need to add spaces at the beginning or end of lines in your sql.
When you build complex strings use a variable. Then you can easily print the variable for checking.
editCode:dim sql as string
sql = "update something set this=that, those='these' what='ever'"
debug.print sql '<--- PRINT TO IMMEDIATE WINDOW FOR CHECKING
db.execute sql
Also, if STUDENT_ID is a number data type, you need to remove the quotes from the value.
Thank you jonh this worked for me!