Form to Open and Automatically provide users records

[Sharkiness]

Good Afternoon,

I would like some help on creating some code on my forms Load function so that when a user opens it they will automatically have only their allocated records available for viewing.

A bit of Background;

I have a table that lists a number of companies and the contact details for these companies. Each company has someone that deals with them. The field name in my table to indicate this is [Caseworker].

I want the form to load for any user and based on the user name they have logged onto the computer with it will display their companies on the form.

Each user logs onto their system with a PID. Therefore I have a table linked to my companies table which shows the PID of each user.

I was able to do this in the past but now I have forgotten and it is really frustrating me. I have provided the field names of both tables below and the start of the code which may or may not be useful.

tblCompanies

Reference
Suffix
Company
Address Line 1
Address Line 2
City
Postcode
Caseworker

The Reference and suffix combined make the primary key as related companies will have the same reference.

tblCaseworker

PID
Caseworker

'API Declaration
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

The next bit is where I have started the code and is for the Form Load event which I think is where I am supposed to start but not sure how to finish it to produce the result I want.

Private Sub Form_Load()
    'Variables
    Dim userPID As String, userName As String
    Dim objRSet As Recordset
    
    'Get user PID
    userPID = String(100, Chr$(0))
    GetUserName userPID, 100
    userPID = Left$(userPID, InStr(userPID, Chr$(0)) - 1)
    
    'Get user name
    Set objRSet = CurrentDb.OpenRecordset("tblCompanies", dbOpenTable)
    Do While Not objRSet.EOF
        If objRSet.Fields("PID") = userPID Then
            userName = objRSet.Fields("Caseworker")
            Exit Do
        End If
        objRSet.MoveNext
    Loop

Hopefully I haven't confused by going into too much detail.

Thanks for any assistance

[ChipR]

On line 12, you want to look in tblCaseworker, not tblCompanies.

[Sharkiness]

Thanks but this still doenst enable to form to open with just the records for the user logged on. Would you have any solution to this?

[ChipR]

Now that you have the name of the Caseworker, you just want to filter the records shown by the form. Assuming the form is bound to tblCompanies:

Me.Filter = "[Caseworker] = '" & userName & "'"
Me.FilterOn = True