VBA Code Assistance

[davegri]

Well, you can't just pull a field from a table out of nowhere in the sub. User is a table containing AccessID. You can pass it to the sub as a parameter:
Private sub Command23_Click(strText as string, nUser as integer)
You pass the AccessID to the sub as nUser, then

Select Case nUser
    case is = 1,7,10,11
           'use michigan
    case is = 1,8,10,12
            'use Oregon
    case is = 1,9,10,13
            'use California
    case else
end select

.
But there's logic errors in this code and yours. If the nUser (AccessID) is 1 or 10, my code will always select Michigan, yours will always select California.
Both can't be right but both can be WRONG!
Whatcha think?

[Boost]

The following thread has been successfully solved by user davegri who crafted the below code which allows for a preassigned UserID to determine what fields are searchable in a form's search box.

Thanks davegri !

Option Compare Database
Option Explicit

Private Sub cmdShowOregon_Click()
    Dim strSearch As String
    txtSearch = Null
    strSearch = "SELECT * from Personnel where [personnel-Unit] = 'Oregon'"
    Me.RecordSource = strSearch
    Me.Requery
    Me.txtCount = fcnCount
End Sub


Private Sub cmdShowTexas_Click()
    Dim strSearch As String
    txtSearch = Null
    strSearch = "SELECT * from Personnel where [personnel-Unit] = 'Texas'"
    Me.RecordSource = strSearch
    Me.Requery
    Me.txtCount = fcnCount
End Sub


Private Sub cmdShowWashington_Click()
    Dim strSearch As String
    txtSearch = Null
    strSearch = "SELECT * from Personnel where [personnel-Unit] = 'Washington'"
    Me.RecordSource = strSearch
    Me.Requery
    Me.txtCount = fcnCount
End Sub


Private Sub cmdShowAll_Click()
    Dim strSearch As String
    txtSearch = Null
    strSearch = "SELECT * from Personnel"
    Me.RecordSource = strSearch
    Me.Requery
    Me.txtCount = fcnCount
End Sub


Private Sub Command23_Click()
Dim strText As String
Dim strSearch As String
strText = Trim(Me.txtSearch)


If User.AccessID = 1 Or User.AccessID = 10 Then
    strSearch = "Select * from Personnel where " _
    & " [personnel-Unit] like ""*" & strText & "*"" or " _
    & " ([personnel_Surname] like ""*" & strText & "*"" or " _
    & " [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 & "*"") "
    Me.RecordSource = strSearch
    Me.Requery
    Me.txtCount = fcnCount
    Exit Sub
End If


If User.AccessID = 7 Or User.AccessID = 11 Then
    strSearch = "Select * from Personnel where ([personnel-Unit] = 'Washington') and " _
    & " ([personnel_Surname] like ""*" & strText & "*"" or " _
    & " [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 & "*"") "
    Me.RecordSource = strSearch
    Me.Requery
    Me.txtCount = fcnCount
    Exit Sub
End If


If User.AccessID = 8 Or User.AccessID = 12 Then
    strSearch = "Select * from Personnel where ([personnel-Unit] = 'Texas') and " _
    & " ([personnel_Surname] like ""*" & strText & "*"" or " _
    & " [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 & "*"") "
    Me.RecordSource = strSearch
    Me.Requery
    Me.txtCount = fcnCount
    Exit Sub
End If


If User.AccessID = 9 Or User.AccessID = 13 Then
    strSearch = "Select * from Personnel where ([personnel-Unit] = 'Oregon') and " _
    & " ([personnel_Surname] like ""*" & strText & "*"" or " _
    & " [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 & "*"") "
    Me.RecordSource = strSearch
    Me.Requery
    Me.txtCount = fcnCount
End If
End Sub


Private Sub Form_Current()
    Call fcnCount
End Sub


'Private Sub Select_Click()
'
'    If InStr("|" & SelectedList, "|" & ID & "|") Then
'       SelectedList = Replace(SelectedList, ID & "|", "")
'    Else
'       SelectedList = SelectedList & ID & "|"
'    End If
'
'End Sub


Private Sub Form_Open(Cancel As Integer)
    If User.AccessID = 1 Or User.AccessID = 10 Then
        Me.cmdShowAll.Enabled = True
        Me.cmdShowOregon.Enabled = True
        Me.cmdShowTexas.Enabled = True
        Me.cmdShowWashington.Enabled = True
        Exit Sub
    End If


    If User.AccessID = 7 Or User.AccessID = 11 Then
        Me.cmdShowWashington.Enabled = True
        Me.cmdShowOregon.Enabled = False
        Me.cmdShowTexas.Enabled = False
        Me.cmdShowAll.Enabled = False
        Exit Sub
    End If


    If User.AccessID = 8 Or User.AccessID = 12 Then
        Me.cmdShowTexas.Enabled = True
        Me.cmdShowOregon.Enabled = False
        Me.cmdShowWashington.Enabled = False
        Me.cmdShowAll.Enabled = False
        Exit Sub
    End If


    If User.AccessID = 9 Or User.AccessID = 13 Then
        Me.cmdShowOregon.Enabled = True
        Me.cmdShowTexas.Enabled = False
        Me.cmdShowWashington.Enabled = False
        Me.cmdShowAll.Enabled = False
    End If
End Sub


Private Function fcnCount() As Long
    If Me.RecordsetClone.RecordCount > 0 Then
        Me.Recordset.MoveLast
        Me.Recordset.MoveFirst
        fcnCount = Me.Recordset.RecordCount
    Else
        fcnCount = 0
    End If
End Function