Do the Admins have a specific UserID?

Yes, the Administrator and the Administrative Specialist each have their own UserID.

You'll need an If Then statement.

If UserID=x OR UserId=y Then
  DoCmd.OpenForm "Unit/Center Review Search"
Else
  DoCmd.OpenForm "Unit/Center Review Search",,, "UserID=" & TempVars("UserID")
End If

As a note you might want to take the "/" out of your Form name and rename the button from Command317 to something more descriptive.

In the If Then statement I added TempVars before each "UserID" and enclosed the "UserID" in parentheses and this worked PERFECTLY! Thank you!

Now I am having an issue with trying to use a TempVar as criteria in a query. I have a query where it gathers data from two columns (Person Tested) which is a short text data type and (Staff) which is a yes/no data type. I would like for the query to only pull that data for the user who is currently logged in using the UserID TempVar.

TempVars

A new issue I am having is trying to figure out where in the included code below to insert a TempVar to limit the searchable data to the userID that is currently logged into the front end database..
I have a search box on all my search forms (multiple types of cases REQUIRE multiple forms) and in the On Key Up event I have the following code:

Details

Private Sub txt_search_box_KeyUp(KeyCode As Integer, Shift As Integer)
'******************************************************************
'
'   Search Box
'
'******************************************************************
    '   Declare variables
    Dim filter_data As String
    '   Apply filter if text is entered in search box
    If Len(txt_search_box.Text) > 0 Then
        '   Set the text entered in search box equal to the variable defined above
        filter_data = txt_search_box.Text
        '   Build the filter string
        '   Need to add to filter string for each field you wish to search
        Me.Form.Filter = " [Case Number] LIKE '*" & filter_data & "*'" _
        & " OR [Inmate Involved 10] LIKE '*" & filter_data & "*'"
        '   Apply filter
        Me.Form.FilterOn = False
        '    Prevent text in search box from being removed after refresh
        txt_search_box.SelStart = Len(txt_search_box.Text)
    Else
        '    Clear filter string
        Me.Form.Filter = ""
        '   Remove filter
        Me.Form.FilterOn = False
        '   Set focus
        txt_search_box.SetFocus
    End If
End Sub
Copy

Where in that code would I put the TempVar to limit the search to only the data related to the user who is logged in?

There is also a "Clear" Button with VBA code in it to clear the contents of the search box. When the form is opened it originally goes to ONLY the records specific to the UserID that is currently logged in however, when the user clicks on the clear button all records become available no matter the UserID.

Could you add it to
Me.Form.Filter = " [Case Number] LIKE '*" & filter_data & "*'" _
        & " OR [Inmate Involved 10] LIKE '*" & filter_data & "*'"

How is the data related to the User?

So in the search box there are quite a bit more filters, I just deleted them from my post trying to conform to posting rules. All of the data in the search form is related to the user as being the investigator assigned to a particular case. For example: Case Number, Unit, Unit/Center Incident Number, Date Received, Date Due, Date Closed, Status, Allegation, Employees Involved, Inmates Involved, etc. are each entered per case and then is tied to a case which has been assigned to a specific investigator. I dont want investigator 2 to be able to search investigator 1's assigned cases and vice versa....

So you'd more likely want to store the UserID on the Form, just make it hidden, then make that a Query Parameter and use Value From a Form so it gets automatically chosen each time

Alex,

Where in this part would I add the TempVars criteria?

Me.Form.Filter = " [Case Number] LIKE '*" & filter_data & "*'" _

It could look something like this:

Example:
Me.Form.Filter = "UserID=" & TempVars("UserID") & _
    " And ([Case Number] LIKE '*" & filter_data & "*'" & _
    " OR [Inmate Involved 10] LIKE '*" & filter_data & "*')"

Kevin,

That did not work. First thought it was due to too many line continuations, so I deleted one of the parameters (there were quite a few more than just the two listed) and tried adding in your example and it still allowed me to see records for other users aside from my own UserID.

Is UserID on the Form anywhere?

Yes. It is non-visible.

Try hardcoding it first
Me.Form.Filter = "UserID=1"
Replace 1 with a valid ID.

In your code above you are setting FilterOn to False, it needs to be set to True.

@Alex: I tried your method and it resulted in the same result... when I enter a Case Number in the search box, the datasheet on the split form returns all cases for all users.

@Kevin: I tried turning that setting to true, which it is set that way in an earlier part of that code, and it also resulted in the same results I have been getting.

Can you create a separate Query with a WHERE clause that copies your above, and check that works outside of the Form?

After Update

I must admit I am in a fog this morning. I am likely missing something when creating a separate query... I tried creating one that uses a WHERE clause that is =TempVars("UserID") but it says that TempVars is an undefined function.

To use TempVars in a Query write it like this: TempVars!UserID

Thanks Kevin! The fog is thick in my head today! So the query I created did work to show only the Cases assigned to me (as I am the user logged in at the time). How can this translate to help only show the cases in the search form?

Are you still using the KeyUp() event? Switch to AfterUpdate().

@Kevin: That works a little better. When the search form is opened, it opens only showing the cases assigned to the user. That is UNTIL the user searches for any other case number, then it shows the results for that case which is what I'm trying to avoid allowing to happen.

It might be your AND / OR logic.
Test it in the Query first.

Did you enclose your search criteria in parentheses?

I changed all of the OR logic to AND then tested in the form to no change in result. I also tried to insert the whole search box code into the query as WHERE criteria to no luck.

Here I am with yet another TempVars question.

I have reverted back to the search form and in an "A-HA" moment thought "I don't need to limit the searchable data to ONLY what is assigned to each user but rather need to limit the way in which a user can interact with data. So, here is my new question:

On my Search form, is there a way in the VBA code to enable the text or combo boxes of the form related to the table to be edited by the user which they are assigned to? I have tried going through the various events for the search box in the form using a tempvar to verify the UserID box of the case matches the UserID of the current user doing the search which does not result in the desired effect.

Basically, what I would like to happen is this: Let's say I have 2 users (UserID 1, and UserID 2). UserID 1 has a case assigned to them (25-001R) which has all the pertinent information included. UserID 2 logs into the database and wants to search ALL records in the database to see if John Doe was involved in any incidents. So, to the search box UserID 2 goes and types in "John Doe" and WAHLAH! he sees the record of 25-001R which is assigned to UserID 1. Now, UserID 2 shouldnt be able to edit any part of that data but UserID 1 should be able to... that is what Im trying to get the form to do now...

Richard shows how to accomplish this in the Access Security Seminar.

Thanks Kevin, unfortunately I am doing this database on my own and as such am limited in funds for purchasing seminars at this time.

Simple Security
User Level Security