Passing a Option Group value from a (Sub-)Form into a Select Query

[steve_odea]

Hi There, I know that this is a common thread and I have managed to determine the syntax etc, but I am still having difficulty in passing the value of an Option Group into a select query and wonder if somebody might be able to shine some light on what is happening.

I have a Form which contains an option group with two values (1 & 2), which has a default value of 1 and is called [Frame54].

I have fed the value of the Option Group into a the criteria line of a select query using the following syntax:

=[Forms]![frmMergeWorkMenu]![Frame54]

The query runs fine, but only if the corresponding form is open, which kind makes sense, but the problem I have is that the open version of my form exists as a sub-form in a navigation menu, so when I run the query with the navigation menu open, I am prompted for a parameter value for [Forms]![frmMergeWorkMenu]![Frame54].

My question is, do I need to reference [Frame54] through the navigation menu (I suspect I do)? If so what is the syntax I should be using?

I have tried the following, but to no avail:

=[Forms]![frmMain].[Form]![NavigationSubform]![frmMergeWorkMenu]![Frame54]
and


=[Forms]![frmMain]![NavigationSubform].[Form]![frmMergeWorkMenu]![Frame54]

but neither work and I am struggling to find any reference material regarding subform reference online, so any suggestions would be very welcome.

S

[steve_odea]

I have figured it out, the correct syntax is:

=[Forms]![frmMain]![NavigationSubform].[Form]![Frame54]

The problem I have now is that my VBA that runs off this Recordset no longer works and it falls over when the VBA code tries to Open the Recordset:

    Dim db As Database
    Dim rs As Recordset    


    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryMergeEzineCompleteMerges", dbOpenDynamic, dbSeeChanges)

The VBA Code now no longer proceed past this last line, with the error message being Run-time error '3001': Invalid Argument

Does anyone have any idea why the inclusion of the subform reference above might've cause my VBA to halt?

[steve_odea]

So through my investigations I have discovered that my VBA code won't work as it doesn't recognise the parameter that I introduced into my query i.e. it doesn't recognise [Forms]![frmMain]![NavigationSubform].[Form]![Frame54]

So does anyone know what VBA method/code I should use in order to introduce this parameter into my code?

[aytee111]

Bring the SQL into VBA;
Dim strSQL As String
strSQL="Select.... WHERE fieldname=" & Me!Frame54 & ";"

If this is running from the main form then change it to Me!NavigationSubform!Frame54

Then in the Set rs change the query name to strSQL (no quotes).

[steve_odea]

Thank for this, do I need to reference a specific library to put sql in my code?

[aytee111]

You aren't putting SQL into the code. The SQL is inside a string so is treated like a bunch of characters.

[steve_odea]

So this doesn't work I'm afraid. I am getting an error stating that Me is an invalid keyword.

Here is the code as it stands, can you tell me if I've understood your advise correctly?

Dim db As Database
    Dim rs As Recordset
    Dim strSQL as String
    
    strSQL = "SELECT qryMergeCYCompletesWithDetail.Email, qryMergeCYCompletesWithDetail.EzineSent, IIf(IsNull([EzineSent]),2,1) AS [Option] 
From qryMergeCYCompletesWithDetail 
WHERE ((IIf(IsNull([EzineSent]),2,1))= " & Me![NavigationSubform]![Frame64] & ";"
               
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryMergeEzineCompleteMerges")

[aytee111]

If Me is invalid that means that this code is running as a stand-alone routine, not as part of a form. My mistake. Change it to Forms!formname!....

In post # 1 it was called "Frame54", in post # 7 it is called "Frame64"...?

[steve_odea]

OK So I have the syntax correct and that is working fine, but the rest of my code is based upon the recordset that I am opening i.e.

Set rs = db.OpenRecordset("qryMergeEzineCompleteMerges", dbOpenDynaset, dbSeeChanges)

which does not include the filter defined in the string...

How do I link the sqlStr that I have just defined to the remainder of the code?

[steve_odea]

For example the following VBA refers to the recordset rs and not strSQL

        StudentID = Format(rs![NUIG Student Number], "00000000")
        RecipientMail = rs![Personal Email]
        RecipientName = rs![First Name]
        NUIGMail = rs![NUIG Email]
        SaveAsName = FPath & "RSEzine-" & StudentID & ".docx"

[aytee111]

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

[steve_odea]

Thanks Very much, you're a legend!

I'm new to VBA so still really trying to get my head around the syntax

Best wishes

S