Use ComboBox selection to Update Fields

[sshinshaw]

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]

build your update query to make changes to the table based on the items in the form.
then run the query on btn click.

 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]

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]

build your update query to make changes to the table based on the items in the form.
then run the query on btn click.

 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]

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]

you need a space between the " and Where

Me.txtBranchCenterID & " where [Location ID]

 

[sshinshaw]

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]

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]

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:

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

 

[sneuberg]

Sorry make that:

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

 

[sshinshaw]

Thanks so much! Worked great.

 

[sneuberg]

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:

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

To use this in your code it would be like:

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]

That should not be an issue for this data but good to know for the future. Thanks again.

 

[sneuberg]

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.