Update Statement with a listbox Filtering & Combobaox (1 Viewer)

wilder20

New member
Local time
Yesterday, 17:47
Joined
Sep 14, 2019
Messages
10
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

Code:
[/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

Well-known member
Local time
Yesterday, 20:47
Joined
Apr 9, 2015
Messages
4,339
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

New member
Local time
Yesterday, 17:47
Joined
Sep 14, 2019
Messages
10
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

..forever waiting... waiting for jellybean!
Local time
Today, 08:47
Joined
May 7, 2009
Messages
19,169
Code:
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

New member
Local time
Yesterday, 17:47
Joined
Sep 14, 2019
Messages
10
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

..forever waiting... waiting for jellybean!
Local time
Today, 08:47
Joined
May 7, 2009
Messages
19,169
change to this:
Code:
 Currentdb.Execute "update [Master] Set [Loc] = " & Chr(34) & [ToLocSlct] & Chr(34) & " " & _
                      "where [sn] in (" & snNos & ");"
 

wilder20

New member
Local time
Yesterday, 17:47
Joined
Sep 14, 2019
Messages
10
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

..forever waiting... waiting for jellybean!
Local time
Today, 08:47
Joined
May 7, 2009
Messages
19,169
ok, sorry, all your fields are Text:
Code:
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

New member
Local time
Yesterday, 17:47
Joined
Sep 14, 2019
Messages
10
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?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:47
Joined
May 7, 2009
Messages
19,169
try uploading a sample db with your form and master table.
 

wilder20

New member
Local time
Yesterday, 17:47
Joined
Sep 14, 2019
Messages
10
Attached is as requested, and thank you for all the help.
 

Attachments

  • ParachuteMaintanance (12 Sept 19) - Copy.accdb
    1.1 MB · Views: 78

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:47
Joined
May 7, 2009
Messages
19,169
here is your db.
 

Attachments

  • ParachuteMaintanance (12 Sept 19) - Copy.zip
    113.5 KB · Views: 93

wilder20

New member
Local time
Yesterday, 17:47
Joined
Sep 14, 2019
Messages
10
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:47
Joined
Feb 19, 2002
Messages
42,981
but I could not think of how to filter down to only show the records that need to be updated

Arnel seems to have fixed your problem but how does the user identify what records need to be updated? This could quite likely have been simplified if you had used a query with a WHERE clause to select only the records you want to take action with.
 

wilder20

New member
Local time
Yesterday, 17:47
Joined
Sep 14, 2019
Messages
10
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:47
Joined
Feb 19, 2002
Messages
42,981
If the status of a parachute is in the table as well as its type (low level static line, multi-mission parachute system, tandem parachute), then criteria can be used to let the computer pick the specific parachutes. If there are 20 available (based on their status) tandem parachutes and you need 3, then does it really matter if serial numbers 1, 2, 4 are chosen or 37, 88, and 103? The picker isn't making decisions on the type. That should be specified in the order.
 

Users who are viewing this thread

Top Bottom