DLookup & subforms

[geosharp]

Hey,
Right, after googling a lot last week and trwsling through the first few pages of Dlookup search results here, i'm at a copmplete loss....
I'm building a school reports database. To avoid having empty boxes for options (the kids choose 2 or 3 out of a possible 7 or 8) i'm doing some code to extract their options and build a custom query on the fly when a button is clicked.

I got it all working fine using DLookups, but then to pretty the whole thing up and make it usable I put the form in a navigation form as a subform.....and now the code breaks

To try and cut down on the chance of the erro being in a different line and access just highlighting the DLookup line, i've commented out nearly everything, leaving me with the following:

Public Sub Command0_Click()
'This creates a database object, then 2 query objects
Dim db As DAO.Database
Dim qdf As QueryDef
Dim Report_Data As QueryDef
    
'This sets the db object as the current database, then the 2 query objects to the query we're creating
'and the query holding the data we want
Set db = CurrentDb
Set qdf = db.QueryDefs("qryStudRepData")
Set Report_Data = db.QueryDefs("qryReport")
 
'Variables, wooo!
Dim Option1 As String
Dim Option2 As String
Dim Option3 As String
Dim Option1SQL As String
Dim Option2SQL As String
Dim Option3SQL As String
Dim Option1Query As String
Dim Option2Query As String
Dim Option3Query As String
Dim Student As String
Dim strSQL As String
Dim strMessage As String
'Sets the Student variable to equal the value of a combo box on the form - however, as it's now a subform we have to do the full path to the control
Student = Forms![Navigation Form]![NavigationSubform].Form!cboStudent
 
Option1 = DLookup("RegGrp", "qryReport", "Student_Name='Andrew Craig Saxon'")
MsgBox (Option1)
 
End Sub

I've cut out a whole bunch of commented out stuff to save space.

WHen I try and run it, it highlights the DLookup line, with the error "Runtime Error: 2450 Microsoft Access cannot find the reference form 'argh'"

argh is the name of the form that has the button this code runs from. Thing is, I can't see where the form is referenced

Any help much appreciated!
Thanks

 

[pr2-eugin]

This link will help you figure out how to refer Subforms;

http://access.mvps.org/access/forms/frm0031.htm

Or http://www.btabdevelopment.com/ts/ewtrhtrts

Or http://www.btabdevelopment.com/ts/refer2sfrms

 

[geosharp]

THank you for the links pr2-eugin, i'd found someof that info already in scattered places, but it will be very handy to have it all collected together and organised.
The problem i'm having though isn't (well, at least I don't think it is) with referencing the subform controls, i've already worked through that headache.
The line where I set the Student variable works - it's the DLookup statement on the next line that throws an error, and as far as I can tell, this line makes no reference to any forms at all!
It's just pulling a field from a query and assigning it to a variable....
TO try and avoid the problem is variables and referring to them in the DLookup, I commented out the old stuff and wrote a new line that has a fixed critieria (thats the version in the code I pasted) but it still seems to be trying to refer to the form, for reasons I don't understand..... :banghead:

Cheers

 

[pr2-eugin]

The DlookUp seems to look into a pre compiled Query. When you edited the form design, did you also manage to change the Query (if in case it also references the same Form)?

 

[geosharp]

Hey,
Yes, you were right.
I have a pyramid of query's (almost certainly due to my patchy at best understanding of 'em) and the very bottom ones filter based on one of the subform controls, i'd not even thought that i'd have to update those to make it work.....

But it is now working again, so I should now be able to carry on cobbling together something that works

Thanks again, next time i'll try and check everything a bit more thoroughly before i post....

 

[pr2-eugin]

That's alright. At the end of the day you solved your problem, and got to learn where to look into. All is well. Good Luck !