Linking a list box on a subform to the main form. (1 Viewer)

[jimkerry]

I have say information about buildings and repairs to these buildings in a database.

The details of each property (property as in house/ flat etc) are available on the main form. Various works are required from time to time to maintain the condition of the property and quotations received from contractors to carry out this work. A table containing general details of these quotes is linked to each property and a third table contains all the details of each quote.

The quotations are shown in a subform and details of the quotation as a datasheet on the subform.

A listbox on the subform should display the quotes received for each property (and only those relating to that property) and allow me to select one of these quotes to view the information.

My problem is that the list box shows all the quotes received for every property and I can't find how to limit them just to those for the property shown on the main form.

All the tables have one to many relationships linked on each tables unique ID. The database is 2003 but I also have Access 2007. Apologies for not being able to describe this problem any simpler.

Grateful for any help you can offer.

 

[Galaxiom]

Set the Master Link Fields and Child Link Fields in the properties of the subform control.

These cause the subform to track the current record on the mainform.

 

[jimkerry]

Cheers
Thats not the problem. Its the list box on the subform which I want to point to quotes received for a property (house etc). The subform is already linked on Child & Master fields to the main form.

HELP!!

 

[Galaxiom]

You need to edit the RowSource query of the listbox and include a where condition that selects only the records matching a field in the current record. It will be based on the same field as the Master/Child Link.

The OnCurrent Event of the main form will need to trigger a requery of the listbox.

 

[LisaOnMSN]

May I request an example showing the syntax of the WHERE clause used in the RowSource?

I ask because I have nearly the identical situation: No problem creating a listbox populated with everything, but trying to get the syntax correct to populate ONLY those currently related to the item currently in memory on the main form? Not so successful.

Didn't want to start a new post when this is the exact situation I'm encountering.

Lisa

 

[Galaxiom]

Hard to be general as it depends on many things. Unfortunately you are still three posts short of being able to upload a sample of your database.

Can you describe the situation a bit more?

If worst comes to worst then you will have your ten posts soon.

 

[LisaOnMSN]

Thanks so much for replying.

MAIN form frmPark
SUBform frmParkFeature <-- List box on this subform

 

[LisaOnMSN]

keyboard got away from me.

Main form is frmPark, parkID primary key
Subform is frmFeature, featureID primary key, parkID foreign key

goal is to return in the listbox on the subform only the features specific to the park that is currently in memory, so the where clause needs to include = Me.parkID. Sounded straightforward. I can't determine the proper syntax to properly populate the list. Does that make sense?

Lisa

 

[Galaxiom]

The RowSource string does not understand Me. That is a VBA thing.

Try
WHERE whatever = Forms!formname.parkID

 

[LisaOnMSN]

Galaxiom -

Too late last night to properly thank you. I was truly stuck on this issue and your reply got me over the wall. Once I got unblocked on this, all I had to do was requery a list on my subform and suddenly everything is synched up, accurate and working.

Thank you for replying!

Lisa

 

[sunkerala]

Galaxiom -

Too late last night to properly thank you. I was truly stuck on this issue and your reply got me over the wall. Once I got unblocked on this, all I had to do was requery a list on my subform and suddenly everything is synched up, accurate and working.

Thank you for replying!

Lisa

Where did you place the requery,in OnCurrent Event handler of the main form ?

 

[Micron]

sunkerala, you have asked a question of someone who posted that 9 years ago. I will be surprised if you get a reply but who knows, you might get lucky and get an answer. If you have an issue, try searching this forum for an answer, or you can post about your own issue.

 

[sunkerala]

 

[sunkerala]

i have the same issue that jimkerry once had .
i want to populate a listbox in a subform with records that linked to the record id of main form
i tried this but failed, The list box is not get updated. Could anyone pls help
MasterEntryFrm is the main form
IIDTbl is the table where all the values for list box are saved, foreign key of IIDTbl is UnitID
MasterEntryFrm is fetching records from a main Table(Identified_List) in which the primary key is UnitID
IIDFrm is the subform
IIDList is the listbox in Subform
VBA Code

Private Sub Form_Current() 'main form onCurrentEvent

Dim iidQuery As String

iidQuery = "SELECT IIDTbl.IID_ID, IIDTbl.UnitID, IIDTbl.Status, IIDTbl.Identified_Month, IIDTbl.Pipelined_Month, IIDTbl.Started_Month AS [Started/Expansion Completed] FROM IIDTbl WHERE (((IIDTbl.UnitID)= '" & [Forms]![MasterEntryFrm].[UnitID] & "')) ORDER BY IIDTbl.Identified_Month, IIDTbl.Pipelined_Month, IIDTbl.Started_Month;"

Me.[IIDFrm].Form![IIDList].RowSource = iidQuery
Me.[IIDFrm].Form![IIDList].Requery
End Sub

 

[zeroaccess]

I'm not sure a list box is the correct method. If using a subform, just choose datasheet or continuous forms, and use the link Master and Child fields in the subform's properties.

 

[sunkerala]

I'm not sure a list box is the correct method. If using a subform, just choose datasheet or continuous forms, and use the link Master and Child fields in the subform's properties.

Thank you,
I havn't tried datasheet yet. let me try it