SOLVED

Filter a form with a button (subform)

Steel Contributor

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.  

Tony2021_0-1651332930136.png

 

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. 

 

 

10 Replies

@Tony2021 

 

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.

 

 

Hi George,

My unbound form is actually in form view and this is where the button would be placed...not in the subform that is in datasheet view (this is the subform to filter). What I mean by more efficient is that is quicker to press a button than to click the filter icon and then click again the item you want to filter out of all the choices available. Let me know if that changes anything. thank you.
best response confirmed by Tony2021 (Steel Contributor)
Solution

@Tony2021 

 

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.

 

 

Thank you George. Little above my head but I will see if I can get it to work.
In case it helps, I have two more examples at https://www.isladogs.co.uk/multiple-group-filter/index.html
Hi isladogs, thanks for the response.

When posting the question, I had thought this would be more simple than it is. I looked over your db for awhile and its a little too complicated for my level. For me, the filtering was only a nice to have and I will resort to using the filter icon on my datasheet as I have been.

thank you both.
No problem. George and I were happy to help.

@Tony2021 

 

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

 

@George Hepworth 

OMG I got it to work. Amazing. thank you very much for the help George! Have a good night.

Congratulations on solving the problem. Continued success with the project.
1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

@Tony2021 

 

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.

 

 

View solution in original post