CurrentDb.Execute Update

[jsummerville]

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??

[Nwtech75]

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.

[jsummerville]

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:


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.

[Nwtech75]

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.

[jonh]

Dim db As Database

Set db = CurrentDb

Dbs.Execute

Turn option explicit on

[jsummerville]

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

[jonh]

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.

[jsummerville]

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?

Capture.JPG

[jonh]

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.

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

edit

Also, if STUDENT_ID is a number data type, you need to remove the quotes from the value.

[jsummerville]

Thank you jonh this worked for me!