Trying to pass a criteria value to a query from a function and textbox (1 Viewer)

Db-why-not

Registered User.
Local time
Today, 18:41
Joined
Sep 17, 2019
Messages
159
I have query that uses a function and a dropdown box, cboTechnician to get different criteria. When "All Technicians" is selected from my dropdown box I want the criteria/function to show all values for a field except the value in my login textbox, cboUser that is open. I had previously set it up to actually show all records, but I need to it to show all records except the value in my login textbox. IT works to show all records, but it doesn't work when I show only records not = cbouser (it doesn't show anything when "all Data Tecnicians" is selected.

This value is in my criteria field: Like fCboSearch([Forms]![Welcome_Menu]![cboTechnician])

Code:
'This function not working properly, shows nothing when "all data technicians", shows correctly when anything else is selected
Public Function fCboSearch(vCboSearch As Variant)
If vCboSearch = "All Data Technicians" Then
'Prevents data techs from seeing records they worked on
fCboSearch = "Not [Forms]![Login_frm]![cboUser]"
Else
fCboSearch = vCboSearch
End If

End Function

'This function works it shows all records, but I needed to modify it to show only values not = to my cboUSer textbox
Public Function fCboSearch(vCboSearch As Variant)
If vCboSearch = "All Data Technicians" Then
fCboSearch = "*"
Else
fCboSearch = vCboSearch
End If
End Function
 

Isaac

Lifelong Learner
Local time
Today, 16:41
Joined
Mar 14, 2017
Messages
8,738
fCboSearch = "Not [Forms]![Login_frm]![cboUser]"

something more like:
fCboSearch = "Not in ............"

I never refer to form controls inside saved queries. You're going to the work of writing the VBA code anyway.....Might as well do it right.
fCboSearch = "Not In ('" & Form_Login_frm.cboUser.value & "')"
 

Db-why-not

Registered User.
Local time
Today, 18:41
Joined
Sep 17, 2019
Messages
159
fCboSearch = "Not [Forms]![Login_frm]![cboUser]"

something more like:
fCboSearch = "Not in ............"

I never refer to form controls inside saved queries. You're going to the work of writing the VBA code anyway.....Might as well do it right.
fCboSearch = "Not In ('" & Form_Login_frm.cboUser.value & "')"
So I should be modifying the function to:
Leave the criteria in the query Like fCboSearch([Forms]![Welcome_Menu]![cboTechnician])
Code:
Public Function fCboSearch(vCboSearch As Variant)
If vCboSearch = "All Data Technicians" Then

'Prevents data techs from seeing records they worked on
fCboSearch = "Not In ('" & Form_Login_frm.cboUser.value & "')"

Else
fCboSearch = vCboSearch
End If
 

Db-why-not

Registered User.
Local time
Today, 18:41
Joined
Sep 17, 2019
Messages
159
So I should be modifying the function to:
Leave the criteria in the query Like fCboSearch([Forms]![Welcome_Menu]![cboTechnician])
Code:
Public Function fCboSearch(vCboSearch As Variant)
If vCboSearch = "All Data Technicians" Then

'Prevents data techs from seeing records they worked on
fCboSearch = "Not In ('" & Form_Login_frm.cboUser.value & "')"

Else
fCboSearch = vCboSearch
End If
that isn't working what do I need to change
fCboSearch = "Not In ('" & Form_Login_frm.cboUser.value & "')"

should it be "Not In ('" & Form.Login_frm.cboUser.value & "')"
 

Db-why-not

Registered User.
Local time
Today, 18:41
Joined
Sep 17, 2019
Messages
159
fCboSearch ="Not In ('" & Form.Login_frm.cboUser.value & "')" that isn't working either.
 

Isaac

Lifelong Learner
Local time
Today, 16:41
Joined
Mar 14, 2017
Messages
8,738
Remember I have no idea how you're using this function.. You posted some code without explaining how the heck you're using it.
 

Db-why-not

Registered User.
Local time
Today, 18:41
Joined
Sep 17, 2019
Messages
159
Like fCboSearch([Forms]![Welcome_Menu]![cboTechnician]) is in my criteria for my query

Then I have the function
Public Function fCboSearch(vCboSearch As Variant)
If vCboSearch = "All Data Technicians" Then

'Prevents data techs from seeing records they worked on
fCboSearch = "Not In ('" & Form_Login_frm.cboUser.value & "')"

Else
fCboSearch = vCboSearch
End If

Each user logins to the database with a userid cboUser, (user name) this form stays open in the background
I have drop down box cboTechnician where the user selects a value(This also has names). I already made the dropdown list so that the user that is logged in name is not listed on the dropdown list, so they can't select themselves. cboTEchncian dropdown box has a "All Data Technician" value that can be selected. I want all names to show up for the query except the users name that logged into the database. I don't want a user to select/view their own records. This is an auditing database.
 

Users who are viewing this thread

Top Bottom