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

Db-why-not

Registered User.
Local time
Today, 16:31
Joined
Sep 17, 2019
Messages
159
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.
1608505198847.png



Code:
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

Super Moderator
Staff member
Local time
Today, 21:31
Joined
Feb 19, 2013
Messages
16,553
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

I’m here to help
Staff member
Local time
Today, 14:31
Joined
Oct 29, 2018
Messages
21,358
Normally, if you want to show "all" records, then you don't apply any filter or where criteria.
 

Db-why-not

Registered User.
Local time
Today, 16:31
Joined
Sep 17, 2019
Messages
159
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

Registered User.
Local time
Today, 16:31
Joined
Sep 17, 2019
Messages
159
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

Super Moderator
Staff member
Local time
Today, 21:31
Joined
Feb 19, 2013
Messages
16,553
you use like when you want a partial hit (i.e. you use one or more *, otherwise you use =
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:31
Joined
Jul 9, 2003
Messages
16,245
I found the function from nifty website

I thought I recognised the variable!

The code is from my Nifty Access website here:-

 

Db-why-not

Registered User.
Local time
Today, 16:31
Joined
Sep 17, 2019
Messages
159
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.

Code:
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])
 

Users who are viewing this thread

Top Bottom