Optional Paramater

[DAW]

Can someone point me in the right direction regarding optional parameters in a saved query? My query is based on a form with combo boxed that the user may or may not select from.
I say saved because i'm using:

Me.cldTemp.SourceObject = "Query.qry_ByDept"

where cldTemp is a subform on a form. The query must be a saved query because the SourceObject must be an object, not SQL. I will also be providing the option to export to excel.

Any help gratefully recieved.

 

[DAW]

Can't anyone help with this?

I'm trying to put something like this in the query criteria (in the QBE) but obviously it will take null as the parameter instead of ignoring it:

IIf(IsNull([Forms]![frm_Reporting]![cbxDept]),Null,[Forms]![frm_Reporting]![cbxDept])

 

[MSAccessRookie]

Can someone point me in the right direction regarding optional parameters in a saved query? My query is based on a form with combo boxed that the user may or may not select from.
I say saved because i'm using:

Me.cldTemp.SourceObject = "Query.qry_ByDept"

where cldTemp is a subform on a form. The query must be a saved query because the SourceObject must be an object, not SQL. I will also be providing the option to export to excel.

Any help gratefully recieved.

A few years ago when I had a similar problem with n Oracle Based SQL program. We resolved it by creating a temporary SQL File that was based on the value of the variable fields, each time the processs was run. Wouldn't it be posible to do something like that here? Before anyone asks, this is only an idea, and I do not know how to do it in Access. I am interested in finding out whether something like this is possible for my own project.

 

[Banana]

Have a look at raskew's code. This can be made optional, and you could adapt it to your needs.

 

[DAW]

Have a look at raskew's code. This can be made optional, and you could adapt it to your needs.

Thanks for that, Banana. I have had a look at using QueryDef instead but still have the refusal to populate the child form because SourceObject wants an object (error 3011 - ...could not find the object '~sq_cfrm_Reporting~sq_ccldTemp...)

Private Sub cmdView_Click()
    Dim SQL$
    Dim qd As QueryDef
    '----------------
    SQL = "SELECT * FROM tbl_RMAs" & WhereFilter
    Set qd = CurrentDb.CreateQueryDef("qryTemp", SQL)
    If GotRecs(qd.Name) Then
        Me.cldTemp.SourceObject = qd.Name '<<---- FAILS HERE
    Else
         MsgBox "Selected criteria returns no results...", 64, c_AppName
    End If
End Sub

Public Function WhereFilter()
    Dim vWhere
    '------------------------
    vWhere = Null
    If Me.cbxCustomer.Value > 0 Then _
        vWhere = vWhere & "[Customer] = '" & Me.cbxCustomer.Value & "' AND "
    If Me.cbxDept.Value > 0 Then _
        vWhere = vWhere & "[Dept] = '" & Me.cbxDept.Value & "' AND "
    If IsNull(vWhere) Then
        vWhere = ""
    Else
        vWhere = " WHERE " & vWhere
        If Right(vWhere, 5) = " AND " Then vWhere = Left(vWhere, Len(vWhere) - 5)
    End If
    vWhere = vWhere & ";"
    WhereFilter = vWhere
End Function

 

[DAW]

Hang on, this is to do with referencing the subform cldTemp, not with the object. Ok, how do I reference the subform?

 

[Banana]

Go to mvps.org/access, search for "Reference subform and controls" article. It'll give a list of syntaxes you need to know for correctly referencing a control in a subform or subform itself.

HTH.

 

[DAW]

Thanks Banana, very much appreciated.

 

[DAW]

"Flipping heck!" Still can't get this working!!

To reference the subform on the current form it says "Me!Subform1.SourceObject"
I'm using "Me!frmSub.SourceObject = qd.Name" and no go!! (frmSub does exist and is a subform of the current form)

 

[Banana]

Um, SoureObject isn't what you want. I was puzzled and looked at the site and for first time I noticed it was mentioned, though it's for "subform control", not the subform itself.

It's supposed to be under "To refer to form property" but I can understand why you went to that section; the references doesn't quite make the distinction clearly.

Here's Bob's illustration of the difference between the subform itself and the subform control.

In your case, you want to do this:

Me!frmSub.Form.Recordsource = qd.Name

 

[DAW]

Thanks again Banana, I appreciate your time helping me with this.
Bob's document is indeed a very good explanation. However, to get my problem fixed all I had to do was give the subform an initial SourceObject, and it didn't matter what it was (I set it to look at the full table). As soon as I did that everything worked as expected.