VBA Code Assistance

[Boost]

Hi there,

I have a database that locks down views in relation to the users assigned login credentials.

Depending on the credentials of the dB login - a form with a continuous subform beneath show up, with only the records related to the particular login credentials showing.

What I am now attempting to achieve is the following:

The main form has a search box. I would like to design a VBA script that sets a variable first, and then allows for wildcard and like functions for other textual entry.

IE. VBA search that first only looks for personnel in Michigan, then search on first name, last name, postcode, etc. (But all additional searching to only be looking in personnel based in the Michigan area).

This is a very wishy-washy way of trying to say what I want to achieve - but in the next comment I will add my current attempts at VBA code in order to assist with my description.

Cheers

Boost

[Boost]

 Private Sub RunSearch_Click()

If user.AccessID = 7 or User.AccessID = 1 or User.AccessID = 11 or User.AccessID = 10 Then
Dim strSearch As String 
Dim strText As String
strtext = Me.txtSearch.Value
strSearch = "Select * from Personnel where ([personnel-Unit] = ""Michigan"") and ([personnel_Surname] like "" * " & strText & "*"") or ([personnel_Inits] like "" * " & strText & "*"") or ([personnel_Title] like "" * " & strText & "*"") or ([personnel_L6] like "" * " & strText & "*"") 
Me.RecordSource = strSearch

That above is my code. To calrify what I would ideally want to happen on click:
- All users with the accessID of 1, 7 ,10, or 11 (already sorted and working) can only perform a search in [personnel-Unit] where Michigan is the value, but that they can then use * searches to search on other variables such as Surname, Initials, L6. etc

[CJ_London]

what exactly is your question?

I can see possible errors in your code, but not sure if these are relevant or whether you just haven't posted the actual code, just a freetyped copy

[Boost]

what exactly is your question?

I can see possible errors in your code, but not sure if these are relevant or whether you just haven't posted the actual code, just a freetyped copy

Yea the code is freehand. But thanks for the heads up!

What I’m trying to do is design a search box, with executable search button in which the first variable is a locked constant. Ie. in the above situation the locked constant is [personnel-unit]= Michigan.

From that point, I then want to add more variables to the search string, using the like function with * wildcards.

In text, a possible search would be “search all people in Michigan (Locked constant) : and then of the people in Michigan search through the following variables (surname, initials, gender, etc) using the value of the text search box.

In this way, I will have different users who will ha e different locked constant variables. IE. the user with the access level of 1 is only shown all records in Michigan, and can then conduct searches. While user with access level 2 may have Oregon searches.

I have already successfully coded the access level part (line 1-2 of my code) but am struggling to fix the variable of [personnel-Unit].

Hope that added some info. If not I may attach screenshots and my dB if possible!

[orange]

Can you separate What you want to do from How it could be done with Access?

Get the logic clearly identified before coding in vba.
See Steve Bishop's youtube videos on Managing User Access

He has other videos re Login Screens etc that may be useful also.

[Boost]

What:
- Executable Function for onClick Event()
- First variable is constant. Or, first variable is acting as a filter. Hence, "Select * from Personnel where ([personnel-Unit] = ""Michigan"")
- After this point in the String, I want to perform searches on multiple variables.

How:
- Multiple variables coded using the LIKE function using * wildcards. hence ([personnel_Surname] like "" * " & strText & "*"")

This has been a free-hand attempt at what I would like to achieve, and I would imagine there are more than a few errors. However, is this even possible? To set a filter with the first block of the string, then allow for the search of certain variables after that point?

Another way of describing what I would like to achieve is this:

User 1: Start Looking in only personnel from Michigan. Now conduct searches on any column from tbl_personnel (age, surname, initials, etc.)

User 2: Start looking in only personnel from Oregon. Now conduct searches on any column from tbl_personnel (age, surname, initials, etc.)

User 3: Start looking in only personnel from California. conduct searches on any column from tbl_personnel (age, surname, initials, etc.)

I have already successfully assigned, and managed user access levels and utilised such an approach in earlier parts of my dB. Hence the line of code: [If user.AccessID = 7 or User.AccessID = 1 or User.AccessID = 11 or User.AccessID = 10 Then]

What I am trying to achieve is the actual search function, where I set the parameter of the search to a certain area, based on a users access level - from which they can then conduct refined searches on multiple other variables.

[June7]

The state can be a dynamic input based on the identified user. Grab that value when user logs in and save it somewhere it can be referenced when needed. I set a textbox on a form that never closes (mainMenu). Or use global variable or TempVar.

Suggest using apostrophe as delimiter instead of doubled quotes.

[Boost]

Thanks June7, I sort of get where you're going - do you think you could briefly annotate what the VBA may look like?

[June7]

Review https://www.accessforums.net/showthread.php?t=23585

[Boost]

Thanks June7, I have successfully implemented the logging of the user access level value, but what I am trying to achieve is to lock a filter to the particular login.

Any ideas?

[orange]

Did you watch the Steve Bishop video I mentioned?
The way he manages who has access to forms may be able to be extended to form and filter.
Good luck.

[June7]

Yes and you need the user info for code to manage that. Conditional code can disable controls or build SQL statements appropriate to the user permissions. So instead of the static "Michigan" concatenate reference to a textbox.

The procedure in the link shows passing a value to Menu form via OpenArgs. I now see I don't show code behind Menu that handles the passed value. It simply grabs value from OpenArgs and populates a textbox. Then other procedures reference that textbox.

[davegri]

Try something like below. I don't know where User.AccessID comes from so I just called it AccessID and set it to 10 for testing.
Any time the recordsource is altered, a form requery is required.

Option Compare Database
Option Explicit


Private Sub runSearch_Click()
    Dim strText As String
    Dim strSearch As String
    Dim accessID As Integer
    strText = Trim(Me.txtSearch)
    accessID = 10
    Select Case accessID
        Case Is = 1, 7, 10, 11
            strSearch = "Select * from Personnel where ([personnel-Unit] = ""Michigan"") and " _
            & "(([personnel_Surname] like ""*" & strText & "*"") " _
            & "or ([personnel_Inits] like ""*" & strText & "*"") or " _
            & "([personnel_Title] like ""*" & strText & "*"") or " _
            & "([personnel_L6] like ""*" & strText & "*""))"
        Case Is = 3, 4, 5
            strSearch = "Something else"
        Case Else
    End Select
    Debug.Print strSearch
    Me.RecordSource = strSearch
    Me.Requery
End Sub

[Boost]

Thanks heaps daveGri, I saw your message this morning and it has been super helpful as I can see I was slowly heading in the same direction!

Before seeing your message this is what I had constructed... be gentle, it's very rough!

But I see where your code makes more sense.. one thing I struggle to see how it works though is line 08 of text. How does your VBA script compile with an incomplete / expected end of expression error?

The user.AccessID is taken from tbl_SecurityAccess.

Would I have to construct three instances of the code for say, three login user.AccessID's (to utilise the requery)?

Here is what I had started. Will run your code however as I see it as a cleaner set up by far!

Private Sub Command23_Click()Dim strText As String
Dim strSearch As String
strText = Trim(Me.txtSearch)
If User.AccessID = 7 Or User.AccessID = 1 Or User.AccessID = 11 Or User.AccessID = 10 Then
strSearch = "Select * from Personnel where ([personnel-Unit] = ""Michigan'') and " _
& " (([personnel_Surname] like ""*" & strText & "*"") " _
& " (([personnel_Inits] like ""*" & strText & "*"") or " _
& " (([personnel_Rank/Title] like ""*" & strText & "*"") or " _
& " (([personnel_L6] like ""*" & strText & "*"") or " _
& " (([personnel_L7] like ""*" & strText & "*"") or " _
& " (([personnel_L4] like ""*" & strText & "*"") or " _
& " (([personnel_SVC#] like ""*" & strText & "*"")) "
Else
End If

If User.AccessID = 8 Or User.AccessID = 1 Or User.AccessID = 12 Or User.AccessID = 10 Then
strSearch = "Select * from Personnel where ([personnel-Unit] = ""Oregon'') and " _
& " (([personnel_Surname] like ""*" & strText & "*"") " _
& " (([personnel_Inits] like ""*" & strText & "*"") or " _
& " (([personnel_Rank/Title] like ""*" & strText & "*"") or " _
& " (([personnel_L6] like ""*" & strText & "*"") or " _
& " (([personnel_L7] like ""*" & strText & "*"") or " _
& " (([personnel_L4] like ""*" & strText & "*"") or " _
& " (([personnel_SVC#] like ""*" & strText & "*"")) "
Else
End If

If User.AccessID = 9 Or User.AccessID = 1 Or User.AccessID = 13 Or User.AccessID = 10 Then
strSearch = "Select * from Personnel where ([personnel-Unit] = ""California'') and " _
& " (([personnel_Surname] like ""*" & strText & "*"") " _
& " (([personnel_Inits] like ""*" & strText & "*"") or " _
& " (([personnel_Rank/Title] like ""*" & strText & "*"") or " _
& " (([personnel_L6] like ""*" & strText & "*"") or " _
& " (([personnel_L7] like ""*" & strText & "*"") or " _
& " (([personnel_L4] like ""*" & strText & "*"") or " _
& " (([personnel_SVC#] like ""*" & strText & "*"")) "
End If

Debug.Print strSearch
Me.RecordSource = strSearch
Me.Requery
End Sub

[Boost]

Hi Orange, I sure did - however, Steve only covers user access to a particular form with the outcome being yes/no, rather than filtering a particular form to show only certain values. But thanks heaps for offering some assistance, as I still got some useful information!