Apr 30 2022 08:41 AM
Hello Experts,
I have a form that I manually filter the combo box by clicking the filter icon on the field (pic below) and then I select the records I want to display. I want to use a button for this now to be more efficient.
Form Setup:
frmFXTracker = Main Form (unbound)
subform = frmFXSubform (this is the form I want to filter)
frmFXSubform.cboVendor = this is the field I need to filter and I am manually filtering now as explained above.
Rowsource of cboVendor (if needed): SELECT tblCompanies.CoID, tblCompanies.CompanyName FROM tblCompanies WHERE (((tblCompanies.CoID)=48 Or (tblCompanies.CoID)=47 Or (tblCompanies.CoID)=64)) ORDER BY tblCompanies.CompanyName;
What I have below is not correct (I get an object required error).
I put a button on frmFXTracker with a click event of:
Private Sub btnFilterBladt_Click()
frmFXTracker.frmFXsubform.Form.cboVendor = 48
Me.frmFXsubform.Form.Filter = True
End Sub
grateful for the some guidance.
thank you. Let me know if additional info is required.
Apr 30 2022 11:38 AM
That is a built-in function of the datasheet view of a form, so replicating it is going to require a different approach. That said, " I want to use a button for this now to be more efficient." raises the question, What inefficiencies have you encountered?
Other relevant details might include, when you create this button, where will it be? Datasheet view doesn't support that, as far as I can tell.
On the other hand, you could create a similar form in Continuous View and put filtering command buttons on it instead.
Apr 30 2022 01:08 PM
Apr 30 2022 03:32 PM
Solution
Thanks for the additional information. The screenshot showed only the filter on the datasheet view, so I misinterpreted the situation. Yes, that does make it possible.
I would actually use a combo box, rather than a command button, though, because the button still has to have a value on which to filter and that has to come from somewhere.
In fact that is a very standard approach to making forms more efficient by limiting records in a form's recordset. That's crucial to working with remote databases, such as SQL Server.
The attached demo is intended for a very different purpose, but the combo box to filter a recordset in a subform is pretty much the same as what you need and can be adapted.
Apr 30 2022 06:00 PM
May 01 2022 12:09 AM
May 01 2022 08:17 AM
May 01 2022 11:42 AM
May 01 2022 12:30 PM
Here's a revision of the sample db I linked earlier. It has a simpler version of the filtering via a combo box. I like to use tempvars for this purpose, although I am aware that that is not a universally popular approach.
Look at the "Read Only" form in this sample
May 01 2022 05:34 PM
OMG I got it to work. Amazing. thank you very much for the help George! Have a good night.
May 01 2022 07:28 PM
Apr 30 2022 03:32 PM
Solution
Thanks for the additional information. The screenshot showed only the filter on the datasheet view, so I misinterpreted the situation. Yes, that does make it possible.
I would actually use a combo box, rather than a command button, though, because the button still has to have a value on which to filter and that has to come from somewhere.
In fact that is a very standard approach to making forms more efficient by limiting records in a form's recordset. That's crucial to working with remote databases, such as SQL Server.
The attached demo is intended for a very different purpose, but the combo box to filter a recordset in a subform is pretty much the same as what you need and can be adapted.