ListBox auto update? (1 Viewer)

[SorenLange]

Hello all,

Complete noob here

I have a CustomerT and VesselT with customers and ships, combined through Customer ID in both tables.
In CustomerF, i have a Listbox as shown. It populates with Vessel's that have the corresponding CustomerID's as the CustomerID in CustomerT.
If I change from Customer #1 to Customer #2 or #3, list box does not update.
I have looked at events, but not sure how to auto update this.
Been trying to google, and Videos - it appears it should do automatically, except here in my laptop LOL.
IO found this great Forum, and found a lot of good hints already, yet I am stuck with this autoupdate of my list box with inputs from VesselT
Any hints?
Access 365 Version 2306

See attached screendumps.

 

[cheekybuddha]

Change the RowSource of the listbox slightly.

The WHERE clause references [CustomerT].[CustomerID] which is not included - you actually want to reference the [Forms]![CustomerF]![CustomerID] of the form.

However, I see the listbox has CustomerID as its ControlSource, yet VesselID as the first SELECT'ed column of its RowSource - is the Bound Column bound to VesselID?

 

[SorenLange]

Thanks for your inputs CheekyB

Changed my Where Clause:
Did not solve.
Bound Column said "1" - Should that be CustomerID form VesselT?
Both Tables attached

 

[cheekybuddha]

Bound Column said "1" - Should that be CustomerID form VesselT?

What field in the table that is the RecordSource for the form do you want your listbox to update?

If you are just using the listbox to display the related vessel records, then you probably don't want it bound at all (ie remove the ControlSource of the listbox)

You probably want the RowSource of the listbox to be:

SELECT v.VesselID, v.[Vessel IMO (Vessel Table)], v.[Vessel Name], v.CustomerID FROM VesselT v WHERE v.CustomerID = [CustomerID];

 

[SorenLange]

I removed the ControlSource for the ListBox and updated RowSource:

Maybe i explained it wrong initially.
My form for CustomerF, should list all corresponding ships that has same CustomerID in VesselT.
After saving and closing all, CustomerF Openscustomer with CustomerID = 1, and ListBox lists vessels that has CustomerID = 1 in VesselT as well.
Thus, if i change customer with the arrows down at "Record" to CustomerT.CustomerID = 2, List Box does not update.

I will try look through guides again, seems like they just smashed a listbox on the form, and pointed towards the Table it should use for populating the listbox, and Master/Child took care of that automatically updating when record change to another customer.

Again, complete NOOB here, thus with old PHP/SQL development experience - Access should not be so hard HA HA.

Thanks

 

[cheekybuddha]

Did you also change the RowSource of the listbox as I suggested?

 

[SorenLange]

Did you also change the RowSource of the listbox as I suggested?

Yes sir,
I did, that gives me Vessels with all CustomerID's (All vessels actually)

I changed my RowSource to this:
SELECT [VesselT].[VesselID], [VesselT].[Vessel IMO (Vessel Table)], [VesselT].[Vessel Name], [VesselT].[CustomerID] FROM VesselT WHERE [VesselT].[CustomerID] = [Forms]![CustomerF].[CustomerID];
Which I better understand myself. Am now getting all Vessels with VesselT.CustomerID = 1.
If i change record to #2 customer, it does not update vessels list.

Simple list inserted with Wizzard even.

 

[cheekybuddha]

OK, then in the Current event of your form try adding code:

Private Sub Form_Current()

  Me.List15.Requery
 
End Sub

 

[SorenLange]

Not working.

Created a SubForm instead, working fine, updates when i change record.
Not what i wanted, it's ugly, but does the trick for now LOL
Thanks for the patience!

 

[Mike Krailo]

Created a SubForm instead, working fine, updates when i change record.
Not what i wanted, it's ugly, but does the trick for now LOL

Wait, what's ugly about the subform usage? You do have the ability to turn off the borders, scroll bars, and other attributes of the subform to make it look however you want.