Solved Listbox & Subform Help (1 Viewer)

[kylejf91]

I am working on a Property Management database. I am having trouble with one small thing. It's insignificant to me, because I can work around it, but if this would be used by others, I want to make sure they can't cause errors in data.

I have a main form (frmTenants_Tenancy) which displays someone's tenancy. On that form is a listbox (lstExistingTenants) which shows the tenants under that tenancy. Clicking on one of them brings up their information on a subform (sfrmTenants_Tenant) through master/child link by TenantID. This will also requery another listbox (lstParkingAgreements) which shows a list of all vehicles parked by that tenant. Clicking on one of the vehicles in that listbox will display vehicle/agreement information in another subform (sfrmTenants_Parking) through master/child link by ParkingID.

My problem right now is that when I click a tenant in lstExistingTenants and then click a vehicle in lstParkingAgreements, the vehicle details will display...but if I then click another tenant in lstExistingTenants, the vehicle details don't go away, regardless of whether the 2nd tenant has parking agreements. It only changes when I click another agreement.

I would like the Parking subform to essentially refresh to blank when the tenant is changed, so that there's no possibility of information being put in under the wrong tenant. I have tried requerying the subform, tried me.refresh, tried setting focus to the subform and going to a new record (this doesn't work because it interferes with a beforeupdate event to handle unsaved changes).

Any advice?

 

[kylejf91]

Here are two pictures showing what I mean.

The first one, when Jane Tester is selected, shows her vehicle also selected.
The second one, with John Tester selected, shows that Jane's vehicle is still showing in the parking subform, despite John's vehicle not selected in lstParkingAgreements.

 

[ebs17]

I would like the Parking subform to essentially refresh to blank

You get an empty recordset with

With Me.SubformContainer.Form
   .Filter = "False"
   .FilterOn = True
End With

Or you set the criteria to False in the data source of the subform.

SELECT * FROM TabQuery WHERE False

 

[MajP]

My guess is you have code that fires when you click on a vehicle. That same code needs to be called when you change a tenant. You need to requery both the vehicles and the vehicles details.

 

[kylejf91]

You get an empty recordset with

With Me.SubformContainer.Form
   .Filter = "False"
   .FilterOn = True
End With

This worked! Thank you so much! I put this code into the AfterUpdate of lstExistingTenants. Initially, it caused the sfrmTenants_Parking to always be blank, even when a vehicle was chosen. But I just put the opposite of this code into the AfterUpdate of lstParkingAgreements and now it does exactly what I wanted

My guess is you have code that fires when you click on a vehicle. That same code needs to be called when you change a tenant. You need to requery both the vehicles and the vehicles details.

No code for updating the subform based on vehicle selected in the listbox - it's just done via the Master/Child data link.