Main Form Combobox to Filter Subform Results (1 Viewer)

Local time
Today, 20:12
Joined
Feb 27, 2022
Messages
49
Hi all,

I've got a combo box on a form and after the user selects something, I want the corresponding details on the subform, but I'm getting a reference error in the After Event Code.

I've been trying to follow this example: https://www.access-programmers.co.u...combobox-to-filter-records-in-subform.172503/ and for my database, have used:

Private Sub cboCategory_AfterUpdate()
[Forms]![frmInvestigationSample]![frmSubformTest].Form.Filter = "[Category] = '" & Me.cboCategory & "'"
[Forms]![frmInvestigationSample]![frmSubformTest].Form.FilterOn = True
End Sub

When I try selecting something from the Combo box, I get:
"Run time error 2465- Microsoft Access can't find the field 'frmSubformTest' referred to in your expression'.

I'm not sure what's wrong here.

I've put together a watered down version of my database with sample data and attached it here- if that helps.

Could you please enlighten me what I'm doing wrong?

Thank you.
 

Attachments

  • TestDB.zip
    57.5 KB · Views: 241

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:42
Joined
May 7, 2009
Messages
19,169
I use Query1 as recordsource of your subform.
when the combo change, i just Requery the subform.
 

Attachments

  • TestDB.zip
    65.6 KB · Views: 250
Local time
Today, 20:12
Joined
Feb 27, 2022
Messages
49
I use Query1 as recordsource of your subform.
when the combo change, i just Requery the subform.
Awesome- Thank you arnelgp. I didn't think of trying to use a query for it instead.

Just curious (for learning purposes), in your query, the criteria of "IIf(Len([Forms]![frmInvestigationSample]![cboCategory] & "")=0,[ID],[Forms]![frmInvestigationSample]![cboCategory])", what was your logic behind the first part please? (IIf(Len([Forms]![frmInvestigationSample]![cboCategory] & "")=0,[ID])
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:42
Joined
May 7, 2009
Messages
19,169
if the Combobox on the form is blank, therefore the Len(gth) = 0, use the ID on the table,
otherwise,
use the value of the Combo to filter the table.
 
Local time
Today, 20:12
Joined
Feb 27, 2022
Messages
49
I use Query1 as recordsource of your subform.
when the combo change, i just Requery the subform.
Hi arnelgp,

For some reason when I applied this to my actual database, I keep getting:

"The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"

I've tried for 4 days to try and figure out why it's not working but have not been successful.

I've put together another sample database with some test data and if you try and open frmInvestigation (the main form that hosts the subform "frmReferralFee", you get that message. It seems to be the query "qryfrmReferralFee" that the subform is running on as per your solution that I think is creating the message?

Would you be able to advise me what I've done wrong please?
 

Attachments

  • RepTest.zip
    113.2 KB · Views: 224

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:42
Joined
May 7, 2009
Messages
19,169
the subform Loads first rather than the Main form, so Mainform does not exists yet.
that is why you got the error.
i change the criteria to Tempvars.
 

Attachments

  • RepTest.zip
    113.3 KB · Views: 272
Local time
Today, 20:12
Joined
Feb 27, 2022
Messages
49
the subform Loads first rather than the Main form, so Mainform does not exists yet.
that is why you got the error.
i change the criteria to Tempvars.
Thank you arnelgp. I understand the reason, but never heard of Tempvars! Really appreciate it. Will look up Tempvars to try and learn more about it.
 

Users who are viewing this thread

Top Bottom