Search form

[svuille]

Hi Everyone!

My question is: how can I automatically switch to filter design mode on a form using vba? I'm designing a form which will be used initially for searching, and I don't want my users to have to click on the "filter by form" button. Is it possible to automate this? Can the apply filter be automated as well?

Thank you for your help

Simon

 

[dungstar]

Turn on Filter By Form / Enabled Controls during Filter By Form?

You can use this code to have your user get into filter by form mode:

DoCmd.RunCommand acCmdFilterByForm

I'm wondering if you can add a button to Apply the Filter on the form that is not disabled during Filter By Form mode, instead of having the user use the Filter toolbar that pops up.

It would be ideal to add an Apply Filter button usable in Filter By Form mode and hide the Filter toolbar in Filter By Form mode all together. Anyone have any ideas?

 

[still_rookie]

ok .. ill tell you a way that will enable a command button to search whatever you type in a text box, directly from the table...


This is the shortcut way of doing it, if you want the whole process do tell me, and unlike most users here i will be happy to instruct you.. so here it goes

1.Make a text box (with any name)
2.Make a command button (with any name)
3. put this code:

Private Sub Command16_Click()
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Employee Code] = " & Str(Nz(Me![Text17], 0))
    If Not rs.EOF Then
    Me.Bookmark = rs.Bookmark
    Else
      resp = MsgBox("No employee with this code", , "ERROR!!")
    End If
End Sub

in the above code, whereever it says 'Command16', please replace it with the name of your command button, and where it says 'Text17' replace it with the name of your textbox and where is says 'Employee Code', replace it with the field in the table that you want to search everytime you search (usually primary key, atleast for me it was )

And whoppie ! your done ..

do tell me if it works, and if it was more helpful that the reply before this one. .. enjoy !

 

[dungstar]

svuille - to automatically apply filter, you can try:
DoCmd.RunCommand acCmdApplyFilterSort

However, it appears that in Filter by Form mode, controls are disabled, with the exception of the "input"-able controls that is used to query the recordset, so you may not get a chance to use it as intended. (If you find out how, please let me know.)

As an alternative, you can also create a custom toolbar with just the apply filter button (and other buttons to your liking) and hide the default filter toolbar.

still_rookie - Thanks for your contribution to the forum, however, I believe the objective here was to filter a form by any field, rather than having to create a separate search field for each control that the user wishes to search on.

....and unlike most users here i will be happy to instruct you..

And whoppie ! your done ..

do tell me if it works, and if it was more helpful that the reply before this one. .. enjoy !

I sympathize for you if you've felt that any of your questions where intentionally neglected. Contrary to what you believe, there are a lot of users on here who are more than willing to help out, where they can. This is why the forum even exists.

Also, you should be even more proud of yourself than you already are for believing that you're more helpful and being so humble about it... The idea here is to support the community of Access developers. If you want to contribute - great, but I'd advise leaving the side remarks at home.

 

[still_rookie]

lol, i dunno why, but ppl are so ****** rude to me here... i never get a proper reply for the question i ask... you can check out my threads... itz so s*** ..

sorry about the foul language.. but thats how i feel at the moment about the forum

 

[svuille]

thanks

Thanks everyone. The trick does work to get into filterbyform mode, and no, there seem to be no way around the limitation which disables all controls but the linked input text fields. I was thinking of using a "fake" input text fields which would reference to an empty table/field, and make it look sort of like a button, unfortunately, it seems the events are not triggered when in filter by form mode. Too bad, grrr Xsoft.

 

[dungstar]

As an alternative that may require a bit of work, you could use 2 forms that look like one. Have button on both forms that toggles (or rather, close one and open the other) between the two. One form would be bound to a recordset, the other unbound and used as a "Query By Form" (you can try to find some QBF examples by searching here) form which would somewhat emulate a filter. When in QBF mode with certain fields filled out, you can click the pseudo-toggle button, to open up the first form (bound form) again with the record source set to whatever the QBF form returns.