Query subform using a value from table

[darnnnel]

Hi this is what i have so far, but it doesnt seem to be working right. I am getting an error here: qryReferral.Status = 'Open'

Private Sub cmdFltrOpen_Click()
 
    Dim sSQL As String
 
    sSQL = "SELECT * FROM qryReferral WHERE qryReferral.Completed = False AND qryReferral.Status = 'Open' ORDER BY qryReferral.ReferralDate;"
    Me![frmReferralSub].Form.RecordSource = sSQL
 
End Sub

Thanks

[ChipR]

What error?

[ajalwaysus]

First, please put your code in the [code] brackets:

Private Sub cmdFltrOpen_Click()
Dim sSQL As String
 
sSQL = "SELECT * FROM qryReferral WHERE qryReferral.Completed = False AND qryReferral.Status = 'Open' ORDER BY qryReferral.ReferralDate;"
Me![frmReferralSub].Form.RecordSource = sSQL
 
End Sub
 

Second, please post the error text you are getting.

-AJ

[darnnnel]

Hi, sorry about that. I am not getting an error message, but i i test using the command button, the subform goes to new record and does not show the results.

[ajalwaysus]

As far as I can tell, I see nothing wrong with your query. This may be a problem with the logic, since the syntax seems fine to me. You will need to review that, if it is still an issue you will need to give us enough information to be on the same page as you so we can debug with you.

ChipR, your opinion.

-AJ

[darnnnel]

Ok thanks. i have a form called frmReferral, which has a subform frmReferralSub displaying records based on a query qryReferral. i have a command buttons to query the different records by status. one buttton for OPEN, one for CLOSED, etc. When the OPEN button is clicked i would like the subform to be "filtered" so that only records that which have a status of OPEN in the qryReferral.Status field.

[ajalwaysus]

If you copy this data to a query and run it, does it work?

-AJ

[darnnnel]

Hi,yes it does.

LL

[ChipR]

I don't really see anything wrong on the surface.
What's the query look like for the subform when it does show records, before you click a command button?
What happens if you try actually filtering instead?

frmReferralSub.Form.Filter = "[Status] = 'Open'" 
frmReferralSub.Form.FilterOn = True 

If that doesn't work, what are the field types you're working with?

[darnnnel]

the filter worked, are there any drawbacks to using the filter instead of "Me![frmReferralSub].Form.RecordSource" ?

[ChipR]

From a performance perspective, I think you're at least as well off filtering the one field rather than resetting the RecordSource. I haven't tested it though.

[darnnnel]

thank you so much, you have been very helpfull, i really appreciate it.

LL

[ajalwaysus]

I'd like someone to confirm this, but I thought I heard that using the filter function doesn't use indexes when filtering the data, but using a query does. I could be wrong, I will look into it if someone doesn't have a quick answer.

-AJ

[NeoPa]

That would be useful AJ. I've never heard that before. Worrying if true.

On the point of filtering, I always advise creating a general purpose query then filtering the form as a separate thing. Usually in the Open call, but can also be done perfectly well on the fly when the form is already open.