Update Statement with a listbox Filtering & Combobaox

[wilder20]

Thank you for your help,

I am trying to update records in a table with the update statement, By using a unbound Listbox [SN] that shows serial#(s) (stored as text) (able to select Multi) primary key is hiding, there is a third column that shows item discerption (text), then there is a unbound Combobox [Loc] (stored as text, primary key hiding) that shows locations. Both are located on table [Master].

The user will select one or many SN’s then select the location that they will be stored and hit the submit button.

[Master] – is the table
[Loc] – is the field saved as text, field to be updated with what is selected in [ToLocSlct]
[SNSlctToLoc] – Listbox saved as text, multi select listing serial #’s, will select the records that needs to be updated
[ToLocSlct] – Combobox saved as text, listing locations

Below are all the ways I have tried and I am missing something or in the wrong place. Please help and describe what I did wrong so that I can learn what I am doing wrong.

Any suggestion will be helpful, and thank you again

[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]CurrentDb.Execute "UPDATE [Master] SET [Loc] = [ToLocSlct] WHERE [SN] '" = Me.SNSlctToLoc.itemselected & "'"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]CurrentDb.Execute "UPDATE [Master] SET [Loc] = ToTLocSlct.Value WHERE [SN] = '" & Me.SNSlctToLoc.Column() & "'"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]CurrentDb.Execute "UPDATE [Master] SET [Loc]='" & (Me.SNSlctToLoc.Column()) & "'"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]CurrentDb.Execute "UPDATE [Master] SET [Loc]='" & Me.SNSlctToLoc.Column().Value & "'"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]CurrentDb.Execute "UPDATE [Master] SET [Loc] = ToTLocSlct " WHERE [SN] = & Me.SNSlctToLoc[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]CurrentDb.Execute "UPDATE Master SET Loc = ToTLocSlct " & "WHERE SN = Me.SNSlctToLoc.Value"[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]CurrentDb.Execute "UPDATE [Master] SET [Loc]= ToTLocSlct " & "WHERE [SN] = Me.SNSlctToLoc.Value"[/SIZE][/FONT]

 

[Ranman256]

You don't need multi select,
Just execute 1 update query on the double click.

And you don't need to pull extra columns,use the key.
The key selected is used to update from the source table that has all the columns for the target column.

So the dbl-click runs: docmd.openQuery "quUpdMyTbl"

 

[wilder20]

I did think about the update query, but I could not think of how to filter down to only show the records that need to be updated or am I thinking how a update query works that it updates all records that show in a update query. this will happen on a month by month bases that a end user that knows nothing about computers other than clicking buttons and selecting items, trying to make it simple and more automated for the end user.

 

[arnelgp]

Dim snNos As String
Dim v As Variant
For Each v In [SNSlcToLoc].ItemsSelected
    snNos = snNos & "," & [SNSlcToLoc].ItemData(v)
Next
If snNos<>"" Then 
    snNos = Mid(snNos, 2)
    Currentdb.Execute "update [Master] Set [Loc] = " & [ToLocSlct] & " " & _
                      "where [sn] in (" & snNos & ");"
End If

 

[wilder20]

Thank you, I received an run-time error '3075':
Syntax error (missing operator) in query expression 'Raeford, NC'

It shows the error on the CurrentDb.Execute line

I do not understand string programming that well, I usually stay away from them for that reason, I am a self taught VBA and access Novice.

 

[arnelgp]

change to this:

 Currentdb.Execute "update [Master] Set [Loc] = " & Chr(34) & [ToLocSlct] & Chr(34) & " " & _
                      "where [sn] in (" & snNos & ");"

 

[wilder20]

Sorry to say that did not work, received another run-time error '3075'
Syntax error (messing operator) in query expression '[LSN] in (1A1256B)'

1a is a SN

 

[arnelgp]

ok, sorry, all your fields are Text:

Dim snNos As String
Dim v As Variant
For Each v In [SNSlcToLoc].ItemsSelected
    snNos = snNos & "," & Chr(34) & [SNSlcToLoc].ItemData(v) & Chr(34)
Next
If snNos<>"" Then 
    snNos = Mid(snNos, 2)
    Currentdb.Execute "update [Master] Set [Loc] = " & [ToLocSlct] & " " & _
                      "where [sn] in (" & snNos & ");"
End If

 

[wilder20]

I appreciate the help, and any thing that helps to get this working the way I am things would be great.

I received another runtime error 3144

Syntax error in UPDATE Statement

FYI, if I select a different location I receive a different error code of runtime error 3075

Syntax error (missing operator) in query expression '24 MEU'

Just wondering is there another way to accomplish what I am trying to do?

 

[arnelgp]

try uploading a sample db with your form and master table.

 

[wilder20]

Attached is as requested, and thank you for all the help.

 

[arnelgp]

here is your db.

 

[wilder20]

Thank you for your assistance/work, creating the message box and it works great. I will now continue creating the rest of the db. I will have to learn how to work with strings they allow for more flexibility.

 

[wilder20]

Basically the listbox shows what equipment is available to be transferred, the db does not know what equipment is being transferred there is not a way to filter it out because a person decides what equipment is being transferred. lets say there is 300 parachutes in the db 50 of them may (filtered out) for maint, repacking, or already transferred that leaves the available parachutes to transfer then the person decides which ones that are left to select from. The rigger may choose out of the 250 left but there are different types of parachutes to choose from but he may want to choose 10 low level static line, 20 multi-mission parachute system, and 5 tandem parachute all to one location, how is the db able to filter that out.