referencing subform entry in selection query

[Pielewuiter]

Hi,
I have a database for biological monitoring data of birds and mammals. The monitoring is done along line transects of 1000m, with 'point stations' every 200m (so 6 stations per transect).
There is a table for the transects ('Transects') containing the following fields:
'Transect ID', 'Transect name', etc...
There is a separate table for the transect point stations ('Stations'), containing the fields:
'Station ID', 'Transect ID' (referring to the Transect table).

There is an overarching form ('Input sessions2') for data input info (date of input, etc.),
in the first subform (Visit Info1) the visited transect is specified from a combo listing all possible transect ID from the Transects-table.
In the sub-subform (Mammal observations), all observed mammals are entered. The station where it was observed, is selected from a combo (MobsTransect) listing all Station ID from the Stations-table.
However, I want the choice of stations in the MobsTransect combo to be limited to the stations that are actually located on the transect specified in the first subform (Visit Info1)... in other words, only stations that have the specified transect as 'Transect ID' in the Stations-table should be selected.

I used several methods to get this selection, but I assume I make the same mistake in all of them...

the Row Source specified as follows:
SELECT [Station ID] FROM Stations WHERE [Transect ID] = [Forms]![Input sessions2].[Visit Info1].Form![Transect] ORDER BY [Station ID];

The sql looked as follows:
SELECT Stations.[Station ID]
FROM Stations
WHERE (((Stations.[Transect ID])=[Forms]![Input sessions2].[Visit Info1].[Form]![Transect]))


ORDER BY Stations.[Station ID];

It keeps on asking to specify the parameter '[Forms]![Input sessions2].[Visit Info1].Form![Transect]', so I suppose the reference to the subform (Visit Info1) is not correct... but I can't figure out what is wrong..

Any thoughts on this?

(I tried to upload the empty database, but it's a complicated one and too big to send..)
-

[HiTechCoach]

If you look at the sub form's property sheet, what is the value in Name property? Note: This is different than the name of the form shown in the sub form control.

[Pielewuiter]

The value of the Name property for the subform is 'Visit Info1'...
for the combo in the subform, it is 'Transect'

[HiTechCoach]

I use this synatrax:

Forms!MainformName!SubformControlName.Form!Control Name

so try:

 
WHERE (((Stations.[Transect ID])=Forms![Input sessions2]![Visit Info1].Form![Transect]))

What is the data type of Stations.[Transect ID]?

[Pielewuiter]

hm..
I've tried to use your syntax... but still it prompts the same thing...
the data time of Stations.[Transect ID] is Text
seems not a very obvious problem...

[HiTechCoach]

How are you using this SQL?

Are you using it in VBA code to open a DAO or ADO recordset?

[Pielewuiter]

Due my rather limited skills in Access, I've build a query in design mode, and then switched to SQL mode to adjust it (because that makes more sense to me, although I'm not really any more skilled in SQL... :/). In the row source of the combo I refer to this query (named 'Query1').
I'm not familiar with the terms of DAO or ADO recordsets... I suppose Access has a default for regular .mdb files?

[HiTechCoach]

On what form is the combo box that is uing this query as the row source.

If not on the form Input sessions2, is the form Input sessions2 open when you open the form with the combo box?

I've build a query in design mode, and then switched to SQL mode to adjust it (because that makes more sense to me, although I'm not really any more skilled in SQL... :/).

Interesting ...
I am very skilled in reading/writing SQL statements. I could write ever query in notepad which is basically the same as SQL view. But I never do if I can avoid it. I really do prefer the Query Design mode. I only use the SQL view when I must. IMHO, the Query Designer Mode for most things can real make you a lot more efficient. This is one of major things I cover when training people to be Access Power Users or developers.

[Pielewuiter]

the combobox is on the sub-subform (called 'Mammal observations' - see first post), and so I suppose both the overarching form (Input sessions2) and the contained subform (Visit Info1 - that in turn contains the sub-subform) are all considered opened when I'm working on the sub-subform...?

about the sql... it's just that I'm a real novice in Access, and I recently built a little background in programming (albeit Python)... so it feels more difficult to get to know the Access method - which is probably much easier once you know how it works - than to use the scripting logic...

[HiTechCoach]

Did you get this figured out?

[Pielewuiter]

Hm no.. it's still not working.. the problem is I don't have very good internet connection and I don't have an Access 2007 manual at hand.

[HiTechCoach]

Would it be possible to post a sample database that has the issue?

[Pielewuiter]

Here's the database...
The overarching form is called 'Input sessions2'.
The combobox that has the problem is located in the 'Mammal observation' sub-subform (caption='Station ID', name='Closest Transect Station'). The values in this combobox should be limited to the stations that are located on the Transect specified in the combobox (name='Transect', caption= 'Visited transect) on the subform 'Visit Info1'.
I hope this makes sense once you've opened it...

[HiTechCoach]

There is not a form with the object name of 'Input sessions2' in the navigation pane. Did you mean 'Input sessions'

[HiTechCoach]

You are using the caption not the actual form name.

Try changing:

 
[Forms]![Input sessions2].[Visit Info1].[Form]![Transect]

to match the actaul form name like this

 
[Forms]![Input sessions].[Visit Info1].[Form]![Transect]

You will also need to use the after update event for combo box control
[Forms]![Ipout sessions].[Visit Info1].[Form]![Transect] to requiry the combo box located in the 'Mammal observation' sub-subform (caption='Station ID', name='Closest Transect Station').