Use ComboBox selection to Update Fields (1 Viewer)

sshinshaw

Registered User.
Local time
Yesterday, 19:22
Joined
Jun 17, 2016
Messages
10
I am a newbie so go easy on me. I want to use the selection made in the combo box to Update a table. Currently the after they select the ID number in the combo box and it then fills in a few text boxes with info about the ID number.

I want to have an update button that can be clicked and it will then update a table, called Prior, where the location equals the ID number selected to a field in one of the text boxes, Me.txtBranchCenterID .

Private Sub cboAtmCenterID_Click()
Me.txtLocationName = Me.cboAtmCenterID.Column(9)
Me.txtServBranch = Me.cboAtmCenterID.Column(3)
Me.txtBranchNumber = Me.cboAtmCenterID.Column(2)
Me.txtBranchCenterID = Me.cboAtmCenterID.Column(14)
End Sub


Thanks for the help.
 

Ranman256

Well-known member
Local time
Yesterday, 22:22
Joined
Apr 9, 2015
Messages
4,339
build your update query to make changes to the table based on the items in the form.
then run the query on btn click.
Code:
 sub btnSave_click()
    docmd.openquery "quUpdateData"
 end sub

the query will use paths from the form, (use the builder tool)
update tbl set [SerBranch] = forms!frmMyForm!txtServBranch , ...
 

sneuberg

AWF VIP
Local time
Yesterday, 19:22
Joined
Oct 17, 2014
Messages
3,506
I think I'm missing or unclear about some information. The code would be as follows, but I've indicate the missing info in red.


CurrentDb.Execute "UDDATE [Prior] SET [the name of the field to be updated] = " & Me.txtBranchCenterID & "WHERE [Some Field] = " & Me.SomeTextBox

Please provide the missing information and we also need to know if the fields involve are numbers, texts or dates. Also this might be the wrong way to do this. Could you tell us a little more about this project and why you need this update.
 

sshinshaw

Registered User.
Local time
Yesterday, 19:22
Joined
Jun 17, 2016
Messages
10
build your update query to make changes to the table based on the items in the form.
then run the query on btn click.
Code:
 sub btnSave_click()
    docmd.openquery "quUpdateData"
 end sub
the query will use paths from the form, (use the builder tool)
update tbl set [SerBranch] = forms!frmMyForm!txtServBranch , ...

This is what I entered...

UPDATE [tbl Assets - Prior] SET [tbl Assets - Prior].[Location ID] = [forms]![frmUpdateDepartmentLocation]![txtBranchCenterID]
WHERE ((([tbl Assets - Prior].[Location ID])=[Forms]![frmUpdateDepartmentLocation]![cboAtmCenterID]));


It is pulling the correct field but is not updating it with the new number.
 

sshinshaw

Registered User.
Local time
Yesterday, 19:22
Joined
Jun 17, 2016
Messages
10
I think I'm missing or unclear about some information. The code would be as follows, but I've indicate the missing info in red.


CurrentDb.Execute "UDDATE [Prior] SET [the name of the field to be updated] = " & Me.txtBranchCenterID & "WHERE [Some Field] = " & Me.SomeTextBox

Please provide the missing information and we also need to know if the fields involve are numbers, texts or dates. Also this might be the wrong way to do this. Could you tell us a little more about this project and why you need this update.

When I tried it this way

CurrentDb.Execute "update [tbl Assets - Prior] set [Location ID] = " & Me.txtBranchCenterID & "where [Location ID] = " & Me.cboAtmCenterID

It gives me this error

Syntax error (missing operator) in query expression '209101where
[Location ID] = 101300'


*The numbers you see are the correct two ID numbers.
 

moke123

AWF VIP
Local time
Yesterday, 22:22
Joined
Jan 11, 2013
Messages
3,852
you need a space between the " and Where

Code:
Me.txtBranchCenterID & " where [Location ID]
 

sshinshaw

Registered User.
Local time
Yesterday, 19:22
Joined
Jun 17, 2016
Messages
10
Ok that fixed that error; but, now it is giving me data type mismatch in criteria expression. The table that the txtbox is pulling from has a data type of Text and the tbl column I am trying to update has a data type of text. Is there something else I'm missing?
 

sneuberg

AWF VIP
Local time
Yesterday, 19:22
Joined
Oct 17, 2014
Messages
3,506
I forgot to suggest to put a dbFailOnError in that like;

CurrentDb.Execute "UPDATE [tbl Assets - Prior] SET [Location ID] = " & Me.txtBranchCenterID & " WHERE [Location ID] = " & Me.cboAtmCenterID, dbFailOnError

That will raise errors for duplicate keys and integrity violations.
 

sneuberg

AWF VIP
Local time
Yesterday, 19:22
Joined
Oct 17, 2014
Messages
3,506
Ok that fixed that error; but, now it is giving me data type mismatch in criteria expression. The table that the txtbox is pulling from has a data type of Text and the tbl column I am trying to update has a data type of text. Is there something else I'm missing?
You need single quotes like:


Code:
CurrentDb.Execute "UPDATE [tbl Assets - Prior] SET [Location ID] = '" & Me.txtBranchCenterID & "' WHERE [Location ID] = '" & Me.cboAtmCenterID & "'", dbFailOnError
 
Last edited:

sneuberg

AWF VIP
Local time
Yesterday, 19:22
Joined
Oct 17, 2014
Messages
3,506
Sorry make that:

Code:
CurrentDb.Execute "UPDATE [tbl Assets - Prior] SET [Location ID] = '" & Me.txtBranchCenterID & "' WHERE [Location ID] = '" & Me.cboAtmCenterID & "'", dbFailOnError
 

sneuberg

AWF VIP
Local time
Yesterday, 19:22
Joined
Oct 17, 2014
Messages
3,506
One more I forgot to mention. Since your fields are text you need to escape single quotes if there are not other mechanisms to keep them out of your data. For example if txtBranchCenterID has a value of "Momma's Branch" you will get an syntax error when the update statement is executed. To represent a single quote within a string you put in two single quotes so the literal "Momma's Branch" would be written "Momma''s Branch". That's two single quotes between the "a" and "s". You can replace single quotes in a string with the Replace function. Since we do this a lot we put a public function in a module for this. The function is:

Code:
Public Function ESQ(str As String) As String
 
ESQ = Replace(str, "'", "''")
   
End Function

To use this in your code it would be like:

Code:
CurrentDb.Execute "UPDATE [tbl Assets - Prior] SET [Location ID] = '" & ESQ(Me.txtBranchCenterID) & "' WHERE [Location ID] = '" & ESQ(Me.cboAtmCenterID) & "'", dbFailOnError

Alternatively if you do this the way Ranman256 suggested you don't have to worry about this issue or more precisely you don't have to worry about delimiters at all. Access takes care of it. I don't do it that way because when the SQL is in the code it's easier to see what the code is doing and after a while having all those extra querydefs creates a lot of clutter. Another consideration is that when it is in code you can trap the errors the dbFailOnError might produce.
 

sshinshaw

Registered User.
Local time
Yesterday, 19:22
Joined
Jun 17, 2016
Messages
10
That should not be an issue for this data but good to know for the future. Thanks again.
 

sneuberg

AWF VIP
Local time
Yesterday, 19:22
Joined
Oct 17, 2014
Messages
3,506
If there's anyway a single quote can get in the data,e.g., typo no matter how improbable you should escape them. We neglected to do that in one of our projects and when the requirement change and data was being imported from spreadsheets, bam it happened. It not good when your customers get bizarre errors like this.
 

Users who are viewing this thread

Top Bottom