Update ListBox on Record Change in Form

[jrosen12]

I hope someone can help with this. I've been working at it for quite a bit, but I'm not entirely sure how to tackle this.

I have a form with a listbox in it. I want the listbox to update as the record changes.

Let me get a little more in-depth:

The form will be used to identify merchandise that exists in the company to fulfill current POs.

What I want is for the listbox (which displays Store Locations) to update when the PO# updates on the form (so as the record changes).

Currently, the listbox only pulls all the store locations no matter if you hit next record to get a new PO.

The two main fields I am working with are PO# (text box) and Store Locations (the listbox).

How can I get the listbox to update as the record changes on the form?

 

[N1755L]

If you have more coding competence than I do, you might get an idea how to do what you're after by looking at how I used filter criteria.

I have a form onto which I have a Add button. When I click this Add button, the following code is run (I've removed some for brevity):

Dim stDocName As String
Dim stCurrID As String
Dim stLinkCriteria As String

stDocName = "frmFilterNumberAdd"
stCurrID = Me![cboFilterManuB]

stLinkCriteria = "[fFMID_ID]=" & Me![cboFilterManuB]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
Forms![frmFilterNumberAdd]![Text38] = stCurrID
DoCmd.Close acForm, "frmFilterNumberTypeView"

As you can see, I'm opening the form onto which I'm going to add records with the filter criteria "[fFMID_ID]=" along with the current record number (which is then currently in the cboFilterManuB combo box, but you could have an invisible textbox with the current record's ID?).

Then once the second form is opened, I reference the filter value and remove the "[fFMID_ID]=" with "Text38 = Right(Me.Filter, (Len(Me.Filter)) - 11)", which removes the first 11 characters of the filter value, and leaves me with only that record ID that I had on the first form before I opened the current, and then put this record ID in a textbox named Text38.

There might be an easier way to do it, but being a fairly rank amateur Access "builder", my ways of doing things sometimes are over-convoluted. Not sure if this is the case here, but from what you described, I thought that this might give you some ideas to use to code what you're working at.

Likely someone will have a much simpler way to accomplish what you're after, but in the meantime, this might help.

I should have mentioned that the reason I'm suggesting some sort of method as this, is that it seems to me as though you are going to have to filter your list box, so this filtering is likely going to look somewhat like this.