referencing subform entry in selection query

[Pielewuiter]

Hi!


it seems I'm not familiar enough with basic Access terminology, because I misunderstood the term 'Control' in your questions... Sorry for that. They make me run here, before I can walk...
Your solution worked.. the combo box list is restricted to the stations on the selected transect. Thank you very much for your help.
Now, next... the After update event...
I've tried the following (which I adapted from an example in the Access help files):

Private Sub Transect_AfterUpdate()

Dim ctlCombo As Control

    ' Return Control object pointing to a combo box.
    Set ctlCombo = Forms![Input sessions]![Visit Info1]![Mammal observations].Form![Closest Transect Station]

    ' Requery source of data for list box.
    ctlCombo.Requery
    
End Sub

But it gives the error 'The field Mammal observations that is being referred at in the expression could not be found' (or something similar - translation from dutch here)...

so again a referencing problem?

[HiTechCoach]

I have always avoided using a sub form on a sub form. I normally always have my sub forms in continuous mode.


This works in your exanmple:

 
 
Private Sub Transect_AfterUpdate()
 
Me.[Mammal observations1].Form![Closest Transect Station].Requery

End Sub

[Pielewuiter]

Thanks very much again HiTechCoach! Your help is very much appreciated. I think I'm exactly where I want to be with the database. There's only one more small thing I haven't been able to figure out...
Besides the mammal observations, there's a subform for the birds observations as well.. I assumed it was straightforward and I could just add a line to the VBA to have it update the bird transect stations as well. As far as I can tell it is just another subform under the Visit Info form, right?
However I got the error:
'The field | that is being referred to in the expression could not be found'..

Private Sub Transect_AfterUpdate()

Me.[Mammal observations1].Form![Closest Transect Station].Requery
Me.[Bird observations1].Form![Station ID].Requery
    
End Sub

[HiTechCoach]

First I change the rowsource of the [Station ID] combo box to be the same as the [Closest Transect Station] combo box.

This worked for me:

Private Sub Transect_AfterUpdate()
   Me.[Mammal observations1].Form![Closest Transect Station].Requery
 
   Me.[Bird observations].Form![Station ID].Requery
End Sub

[Pielewuiter]

Ha, I had the row source of the combo box in the bird form set, so that couldn't be the problem... The only thing different between our solutions seems to be the naming of 'Bird observations'... (spacing and blank lines didn't really matter when I tried it). I don't really get why it needs to be 'Mammal observations1' for the mammal form, and just 'Bird observations' for the bird from...
but, ok... it works! I got it the way I wanted it!
So again, a big thank you for your time and help!
This topic can be closed as far as i'm concerned (although, if you feel like unpuzzling me on the name issue, go ahead!
cheers!
Maarten

[HiTechCoach]

You are getting the form name and the sub form control name confused.

I do not like to use the default naming that Access automatically assigns to objects. It has been my experience that this can get very confusing to people. As you have experienced.

You were not using the sub form control name but the actual form name used in the sub form control

Generically the syntax is:

Me.[SubForm_Control_Name].Form![ComboBox_Control_Name ].Requery

When I was writing this code for you I went to the properly sheet for each the object to the name property and copied and pasted it into the code. This way I am sure I have the correct name.


When it comes to naming conventions, the most important one is the one you use consistently. I started Windows programming with with VB4 and Access 2.0. I took the naming conventions I have used for years prior to Windows programming and expanded it ti handle the new objects. I basically have been using the same naming style for almost 30 years.

I have a good example on my site. See:
Access Naming Conventions

[Pielewuiter]

Thanks for explaining. I think I do understand the difference between the sub form control name and the form name used in the sub form control... the autonaming is probably useful for many, most of the time...

Maarten

[HiTechCoach]

...the autonaming is probably useful for many, most of the time...

Maarten,

I would have to disagree. The auto naming is the cause of many issues. Especially with reports. The auto generated names are either meaningless (example: Text1) or a duplicate of other ejects (control name and field name the same). This makes debugging more difficult when there is a problem. It makes documenting a database more time consuming. Etc., and etc.

The good thing about autonaming is that it does get me more hours of billable time repairing what it does.