combobox on a subform

[Blair]

Hi I am fairly new at access, I have searched through all of the discussion
posts and found a few useful threads. However I'm not sure how to connect
what I've read to my problem.

I have a main form that is based on the table containing monitoring
information for a site. Each monitoring occasion has a unique Id an info
such as the date and basic info is collected. Each monitoring record is
linked to a unique property. But there can be many monitoring occurences for
each site.

I then have a subform which is where the detailed info for the monitoring
reports is. These reports can be one of three different types of reports.
There can be many reports for each monitoringID. Each report has a unique id
and also has the monitorID.

Okay... My subform then has a combo box which lets you pick the land parcel
that the monitor report is based on. Each Property can be made of several
land parcels. I want the combo box for the landID to be populated with only
the landIDs that belong to the property that is being monitored.

Confused? I have a propertyID table that only contains the legal land
descriptions. Each legal land ID has a unique id and is related to the
propertyis in the property table. So I need to get the legal land id from
the legal land tables that match with the property id in the monitoring
table. I wrote a query that works alone but I can't seem to get this to work
for the combo box. here's the query:

SELECT tblLegalLand.LegalLandID FROM tblLegalLand WHERE
tblLegalLand.PropertyID = (SELECT tblMonitoring.PropertyID FROM tblMonitoring
WHERE tblMonitoring.MonitorID = tblMonitorHealthReport.MonitorID);


How do I get this query to work so that these values populate a combobox?
Any help would be greatly appreciated. Thanks in advance....

 

[Al Camp]

Blair,
If the subform record contains a PropertyID, and you want to filter you
combo results accordingly, just use the PropertyID value in the subform
record as the filter.
Using full addressing... and using your own names... add this criteria to
the PropertyID field in your combo query...
=Forms!frmYourMainName!frmYourSubFormName.Form!PropertyID
On the AfterUpdate event of PropertyID, you'll need to Requery the combo
to make sure they are in synch.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Blair]

Hi Al, thanks for your response. My subform does not contain the PropertyID.
Instead the subform has a MonitorID which links to the main form (the
Monitoring form). This Monitoring form has the MonitorID and PropertyID.
The land location table also has the PropertyID. So the subform has the same
monitoringID as the main form. I'm just not sure how to access the Property
ID on the main form to use as the filter. If I use my query that I wrote it
always asks me for the MonitorID. Any suggestions?

Thanks,
Blair

 

[Al Camp]

Hmmm... not usre, but I'm a bit concerned that your filtering a combo with
a value not contained in the record itself... but.... that could be OK I
guess.
Try...
=Forms!frmYourMainForm!PropertyID
as a filter for the PropertyID field in the combo query.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Blair]

Thanks for the response. I'm starting to wonder if it is possible myself. I
have tried a million different things and I always get asked to input the
parameter for PropertyID.
I tried your suggestion and I also tried:
=Forms!frmYourMainForm!Form.PropertyID
Which I got from another post.

Does anyone know if you can filter a combo box based on a value from another
form or table? Thanks

Blair