I am trying to add "all records" option in my drop down menu to show all records.

[Db-why-not]

I created a function it is named module 1 fCboSearch(vCboSearch As Variant) IT takes the value from a dropdown box cboTechnician on my main form. I want it to show all records if "All Data Technicians" in my query if it is selected from dropdown list if not I want it just to use filter the 1 value selected.

In my query criteria I put fCboSearch(vCboSearch As Variant)

When I run the query it gives me an error message.

Option Compare Database
Option Explicit
Public Function fCboSearch(vCboSearch As Variant)
vCboSearch = Forms!Welcome_menu!cboTechnician
If vCboSearch = "All Data Technicians" Then vCboSearch = "*" Else
vCboSearch = Forms!Welcome_menu!cboTechnician
End
fCboSearch = vCboSearch
'sets value of fCboSearch

End Function

 

[CJ_London]

Really confused about your description

In my query criteria I put fCboSearch(vCboSearch As Variant)

you don't use 'as variant' when you call the function - and where is your query getting vCboSearch from? is that the name of a field?

And how is it used in your criteria? - using = or like?

And why does you function ignore what is being passed to it and assign a value from your welcome menu?

 

[theDBguy]

Normally, if you want to show "all" records, then you don't apply any filter or where criteria.

 

[Db-why-not]

Really confused about your description


you don't use 'as variant' when you call the function - and where is your query getting vCboSearch from? is that the name of a field?

And how is it used in your criteria? - using = or like?

And why does you function ignore what is being passed to it and assign a value from your welcome menu?

So should criteria =vCboSearch()
Or should it be. Like vCboSearch()

I will try both tomorrow.

I really have not written any functions before.

I'm trying to assign a criteria value from a drop down box using an if then statement. I can't use an if then statement in a criteria area so I am trying to get the function to do the if then statement and pass final value as the criteria to my query.

I want my dropdown box to be able to show all records and filter individual records depending on what is selected. If I leave the drop down box null or blank it doesn't pull up any records, When I set criteria to equal my cbotechnician. I had been told to try set criteria
= cbotecnician or is not null that does not work though.

Ords

 

[Db-why-not]

I think I see what I did wrong with the criteria box. I found the function from nifty website. When I saw how it did the criteria i didn't understand why it had the variable and the combobox name. Now I understand why it is written that way.

Like fCboSearch([Forms]![welcome_menu]![cbotecnician])

 

[CJ_London]

you use like when you want a partial hit (i.e. you use one or more *, otherwise you use =

 

[Uncle Gizmo]

I found the function from nifty website

I thought I recognised the variable!

The code is from my Nifty Access website here:-

Combo Filter < All> or Some - Nifty Access

Combo Filtering You can set this up yourself by following the instructions below, or you can study the example file which can be downloaded from the Nifty Digital downloads site for a few dollars.. will normally appear as the first entry in your list because the angle bracket puts it at the...

www.niftyaccess.com

 

[Db-why-not]

I got it to work now. I just didnt understand why it was written that way, but now I do. I am still learning with VBA.

Public Function fCboSearch(vCboSearch As Variant)

If vCboSearch = "All Data Technicians" Then
fCboSearch = "*"
Else
fCboSearch = vCboSearch
End If
End Function

criteria in my query
Like fCboSearch([Forms]![Welcome_Menu]![cboTechnician])