Hi,

I was hoping someone could help me "see the light" with the issue I'm having. I've provided the vba code below. The vba code is looping through a multilist box. There are two text boxes hidden on the form. TextBox1 is a string of item numbers, TextBox2 is a string of location codes that correspond to the item numbers in TextBox1.

Example:

TextBox1: 095716, 167017, 180886, 180887
TextBox2: A404A,A404A,A103A,A102A

Item # 095716 needs to be updated to location A404A .... Item # 180887 needs to be updated to location A102A.

I've been updating the location code by having a user select a location from a combo box and then all items for the location were being displayed in the multilist box. Then based on which items the user selected I used as an expression in my update query to update the location code field selected in the combo box for each item number that appears in TextBox1.

I had to change that design and now display the location code as column 2 in the multiselect box. I want the user to select the row in the multiselect box and then have it update for each item number (column 0) the location code based on column 2 in the multiselect box. I have the two strings (TextBox1, TextBox2), but I don't know how to use the TextBox2 string as update criteria and match it to the item number in TextBox1.

You're describing how you're trying to do something, using listboxes on forms.

With Access, it all starts with the data. Please describe the underlying data/table structure.

It would probably also help us to help you to know which version of Access you're using.

assuming you are trying to pair two strings, then if each can be split based on a comma we get this simple bit of code to give you the paired values.
this assumes both strings have an equal number of commas.

dim a() as string
dim b() as string
dim x as long

a = split(textbox1, ",")
b = split(textbox2,",")

for x = 0 to ubound(a)
msgbox("a: " & a(x) & " b: " & b(x))
next

Jeff B:

I have two tables:

Table 1: master inventory table. Relevant fields to this issue are : [item_number] and [new_location] fields.
Table 2: Initiated Property Transfers table. Relevant fields to this issue are [item_number] and [new_location] fields.


A user updates table #2 via a form that initiates a property transfer.

I then have a property transfer approval form that uses a multiselect list box that displays all [item_numbers] and [new location] for the items that were assigned to a particular user's e-mail address.

I need to update the [item_number] and [new_location] fields in Table #1 based on which [item_number] and [new_location] row(s) the user selects in this multiselect list box.

Summary:
The code I provided in my original post was great, but I had to have a combo box that required the user to select a [new_location] and then have the multiselect list box display [item_number] by location. This was confusing to some users, because they had multiple locations that with items that were pending approval for transfer. I want to have all items in the multiselect list box that are assigned to a user and then have the user select the item(s) from the multiselect box and have the query update table #1 mentioned above. I used Instr to identify the [item_number] requiring the [new_location] to be updated in my original code, because I knew the [new_location] was static in the combo box. Now, the [new_location] is variable depending on which row is selected in the multiselect list box.


I hope that's a little clearer and useful than my original post. Thanks! Any help is appreciated.

And I'm using Access 2007. I know selected "office 2007" from the drop down when I initially created the post, but I'll verify it's selected before I click submit. Does the "preview post" feature reset that?

Ok, so after 2 days I figured out what I was doing wrong. I was over thinking the update query. I simply joined my inventory and property transfers tables based on [Item_Number].

Then I changed my Instr expression to look at the [PropertyTransfers].[ItemNumber] instead of [Inventory].[Item_number]

Then I set the [Inventory].[New_Location] update field in my update query to: =[PropertyTransfers].[New_Location]
(since I was already recording the new location via a property transfer initiation form) <---this is when I "saw the light"

So now the for each [PropertyTransfers].[ItemNumber] = [Inventory].[Item_number] the [Inventory].[New_Location] is updated based on the location that is assigned in the [PropertyTransfers].[New_Location] column that is assigned to the specific [PropertyTransfers].[ItemNumber].


As for the rest of the code I just changed it back to the original which is:



Thanks again to those who tried to help. I appreciate it. Your comments/suggestions made me realize that whatever I was trying to do was overly complicated (due to lack of additional responses lol) and that my issue didn't seem that complicated.