Setting 2 RecordSources for a subforms form depending on field in mainform

[crxftw]

Hello. I'm looking for a solution how I can have 2 RecordSources for a form that's SourceObject in my mainforms subform. It needs to choose one of the 2 queries depending on the value that is in hidden field in my mainform.

In my database when I open mainform, then in a subform I need to get results based on first query 'qrySalesComplete' if "txtStatusID > 20" and if it's anything else, zero in this case, then I need the query to be 'qrySalesInvoiced'. Not sure if it's possible to change RecordSource for a form SourceObject for subform. Do I need to have 2 different forms then with different queries and change the SourceObject for subform then depending on the field value?

I've tried to look anywhere but can't find a proper solution for that and not sure if it's the best idea to solve this problem.

Regards.

[pbaldy]

You can set the recordsource:

Me.RecordSource = "QueryName"

Replacing "Me" with a full reference if appropriate. Sounds like you'd want to do it in the after update event of the textbox that will change, and/or the current event.

[crxftw]

I need it to display data when I open up the main form, txtStatusID is already set when opening it up, while creating an invoice its value is set to 0 by default, then I need to display data in subform, which has continuous form linked to it, that belong to the Client I choose from above from dropdown, subform has master and child fields set to their primary key ID which should return data that for the client who was selected. But when an old invoice, which is just submitted, is opened up then StatusID is already set to 100 (if submitted) and 200 (if marked paid, think i mentioned wrong number above but it doesn't matter). And now I need the subform to display me results that are returned from another query, where specific sales are linked to that invoice. Hope it makes sense.

I tried this in main forms Form OnLoad:

With sbfSalesComplete.Form 
    If txtStatusID.Value > 100 Then
        .RecordSource = "qrySalesComplete"
    Else
        .RecordSource = "qrySalesInvoiced"
    End If
End With

but I am not getting anything, tried to add different values to watchlist while debugging it in vba editor and nothing.

Then I also switched things around and made 2 new continuous forms, one with qrySalesCompleted record source and another with qrySalesInvoiced. Then to OnLoad I added:

If txtStatusID.Value > 100 Then
     Me.sbfSalesComplete.SourceObject = "frmCompletedSales"
Else
     Me.sbfSalesComplete.SourceObject = "frmCompletedInvoices"
End If

..and I am getting nothing. Not sure where the problem is, not very familiar with access too. Tried to add requery after every setting too.

[pbaldy]

Personally I'd use a full form reference or Me to avoid ambiguity. Can you post the db?