Limiting records displayed in combobox to current user (1 Viewer)

P

psyc0tic1

Access Moron
Local time
Today, 14:51
Joined
Jul 10, 2017
Messages
360
Is there anyone willing to help me with one more detail on my database?

I have access levels which are:
Visual Inspector
Lab Tester
Multi Tester

At some facilities there are only Visual Inspectors. Because they do not have a lab... they send their parts needing lab testing to another facility.

At that other facility, they can also have visual inspectors but also have lab testers and also have multi testers. A multi tester can do both visual inspections and lab testing and the other two are self explanatory.

I put all new records in the database and it sets the status in tbl_auditdata to Awaiting Visual Inspection.

Lets just take 2 facilities to start...

Whitehall, WI receives parts (their own P.O. numbers)... they have to visually inspect parts but do not have a lab so they have to send parts to Arcadia, WI to have them lab tested. All people at Whitehally are just Visual Inspectors.

Arcadia, WI also receives parts (their own P.O. numbers)... they have to visually inspect their parts as well but they can also perform their own lab testing. They also have to perform the lab testing for Whitehall. This facility has Visual Inspectors, Lab Testers and a few Multi Testers that can do both roles.

So now... to the question starting with the visual inspections... there is a form frm_visualinspectioninput where obviously the visual inspection data is added tot he records that I put in the database. On their form there is a combobox that pulls all the records that are in the database with a status of "Waiting on Visual Inspection" and I would like for the records that appear in the combobox to only relate to the current user so they have no chance of updating the wrong record.

This gets tricky when it comes to the users that are in facilities that lab test parts from other facilities as well as for their own... and trickier when not all multi testers lab test parts from facilities other than their own.

User table (tbl)users) has these fields.
  1. ID
  2. UserName
  3. Password
  4. AccessLvl
  5. FacilityName
  6. FacilityName2
  7. Other fields not related to this

Access Level table (tbl_Accesslevel)
  1. ID
  2. AccessLvl

Access level deciding what they can and cannot see is decided via VBA.
COde for credentials module:
Code: 
Option Compare Database
Option Explicit

Public UserName As String
Public UserId As Integer
Public AccessLvlID As Integer
Public Function GetCurrentUser() As Integer
    GetCurrentUser = UserId
End Function

COde in the login form to determine their credentials on login:
Code: 
Private Sub Command1_Click()
    If IsNull(Me.txtLoginID) Then
        MsgBox "Please Enter Login", vbInformation, "Need ID"
        Me.txtLoginID.SetFocus
    ElseIf IsNull(Me.txtPassword) Then
        MsgBox "Please Enter Password", vbInformation, "Need Password"
        Me.txtPassword.SetFocus
    Else
        Credentials.UserName = Me.txtLoginID.Value
        If DLookup("Password", "tbl_users", "UserName = '" & Credentials.UserName & "'") = Me.txtPassword Then
            Credentials.UserId = DLookup("ID", "tbl_users", "UserName = '" & Credentials.UserName & "'")
            Credentials.AccessLvlID = DLookup("AccessLvl", "tbl_users", "UserName = '" & Credentials.UserName & "'")
        MsgBox "Please wait for the main form to load, Network traffic can cause this to be slow", vbOKOnly, "Please Be Patient"

        DoCmd.Close acForm, Me.Name
        DoCmd.OpenForm "frm_home", , , , , acDialog 'CR - added acDialog 18/09/2018
    
    Else
            MsgBox "Incorrect Login or Password"
        End If
    
    End If
   
End Sub

Code for combobox in frm_visualinspectioninput:
Code: 
Private Sub cboGoToRecord_AfterUpdate()
     On Error Resume Next
     Dim rst As Object
     Set rst = Me.RecordsetClone
     rst.FindFirst "AuditID = " & Me.cboGoToRecord.Value
     Me.Bookmark = rst.Bookmark
End Sub

Private Sub Form_Current()
    Me.cboGoToRecord.Value = Me.AuditID.Value
End Sub
and the click event to save the record update:
Code: 
Private Sub Vis_Input_Submit_Click()
    Dim strMsg As String
    
    blnGood = True
    
    If (validate) Then
        Me.Recordset.Edit
        Me.Recordset.Fields("status").Value = "Waiting On Lab"
        Me.Recordset.Fields("VisualInspectorUserId").Value = Credentials.UserId
        Me.Recordset.Update
        If Me.CurrentRecord < Me.Recordset.RecordCount Then
            Me.Recordset.MoveNext
        Else
            Me.Recordset.MoveFirst
        End If
    Else
        strMsg = "All Fields are required."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If
        Application.Echo False
        DoCmd.Close
        DoCmd.OpenForm "frm_home"
        Form_frm_home.Visual_Inspection_Input_Form.SetFocus
        Application.Echo True
    blnGood = False
End Sub

Private Function validate() As Boolean
    validate = True
    If (IsNull(Me.VisInspectDate.Value)) Then
        validate = False
    End If
    If (IsNull(Me.QCLine.Value)) Then
        validate = False
    End If
    If (IsNull(Me.BlackGreenDot.Value)) Then
        validate = False
    End If
    If (IsNull(Me.PartProdDate.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalVisInspected.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalVisBad.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalVisGood.Value)) Then
        validate = False
    End If
    If (IsNull(Me.MfgPONum)) Then
        validate = False
    End If
End Function

The code for the frm_labtestinput is virtually the same but the tricky part will be here because visual inspectors never have multiple facilities... only lab testers or multi testers.

Code for frm_labtestinput combobox:
Code: 
Private Sub cboGoToRecord_AfterUpdate()
    On Error Resume Next
    Dim rst As Object
    Dim HasUSB As Long
    
    Set rst = Me.RecordsetClone
    rst.FindFirst "AuditID = " & Me.cboGoToRecord.Value
    Me.Bookmark = rst.Bookmark
     
    HasUSB = DLookup("HasUSB", "tbl_parts", "ID = " & Me.Part_Number)
    If HasUSB = 2 Then
      ShowStretch
    ElseIf HasUSB = 1 Then
      ShowStretch2
    ElseIf HasUSB = 11 Then
      ShowStretch3
    Else
      HideShrink
    End If
End Sub

Private Sub Form_Current()
    Me.cboGoToRecord.Value = Me.AuditID.Value
End Sub

and for the click event to complete the record update:
Code: 
Private Sub UpdateRecord_Click()
    Dim strMsg As String
    
    blnGood = True
    
    If (validate) Then
        Me.Recordset.Edit
        Me.Recordset.Fields("status").Value = "Complete"
        Me.Recordset.Fields("LabInspectorUserID").Value = Credentials.UserId
        Me.Recordset.Fields("LabUpdate").Value = Date
        Me.Recordset.Update
        If Me.CurrentRecord < Me.Recordset.RecordCount Then
            Me.Recordset.MoveNext
        Else
            Me.Recordset.MoveFirst
        End If
    Else
        strMsg = "All Fields are required."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If
        Application.Echo False
        DoCmd.Close
        DoCmd.OpenForm "frm_home"
        Form_frm_home.Lab_Test_Input_Form.SetFocus
        Application.Echo True
    blnGood = False
End Sub

Private Function validate() As Boolean
    validate = True
    If (IsNull(Me.LabTestDate.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalFunctBad.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalFunctTested.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalFunctGood.Value)) Then
        validate = False
    End If
    If (Credentials.UserId = 0 Or IsNull(Credentials.UserId)) Then
        validate = False
    End If
End Function

So on frm_visualinspectioninpit, we (just for general users info) take user1 is a visual inspector with an access level of 2 and a facility of Whitehall only needs to see records in the combobox that are "Waiting on Visual Inspection" in the tbl_auditdata for FacilityName Whitehall in the tbl_users

On frm_labtestinput, we take user2 who is a lab tester with an access level of 3 who needs to see records in the combobox that are "Waiting on Lab Testing" in the tbl_auditdata for facilityname Arcadia and facilityname2 whitehall.

Does any of this make sense? Did I go about it in the wrong way with the facilities in the users table?

this one is way too complicated for me to even think about attempting on my own.
 
P

psyc0tic1

Access Moron
Local time
Today, 14:51
Joined
Jul 10, 2017
Messages
360
I totally forgot to post the querries for the comboboxes.

in frm_visualinspectioninput: (qry_visinspectinputcombo)
Code: 
SELECT tbl_auditdata.AuditID, tbl_auditdata.PONumber, tbl_auditdata.Status, [tbl_parts].[PartNumber] & " - " & [tbl_parts].[PartDesc] AS Expr1
FROM tbl_auditdata INNER JOIN tbl_parts ON tbl_auditdata.PartNumber = tbl_parts.ID
WHERE (((tbl_auditdata.Status)="Waiting on Visual Inspection"))
ORDER BY tbl_auditdata.PONumber;

in frm_labtestinput: (qry_labtestcombo)
Code: 
SELECT tbl_auditdata.AuditID, tbl_auditdata.PONumber, tbl_auditdata.Status, [tbl_parts].[PartNumber] & " - " & [tbl_parts].[PartDesc] AS Expr1
FROM tbl_auditdata INNER JOIN tbl_parts ON tbl_auditdata.PartNumber = tbl_parts.ID
WHERE (((tbl_auditdata.Status)="Waiting on Lab"))
ORDER BY tbl_auditdata.PONumber;
 
P

psyc0tic1

Access Moron
Local time
Today, 14:51
Joined
Jul 10, 2017
Messages
360
Forgot about the queries for the forms themselves too but figured it out. It was actually pretty simple.

Thanks for all the thread views all. I don't blame anyone for not attempting this from all that bla bla bla I posted :D
 
Last edited:
You must log in or register to reply here.

Similar threads

T
Solved Run-time error '2101'
Replies
2
Views
1,082
tihmir
T
P
Run-Time Error 3197 (please read anyway)
Replies
13
Views
2,442
The_Doc_Man
The_Doc_Man
A
Loop through current records to find null value in the Subform
Replies
8
Views
939
Pat Hartman
P
J
Solved After Login to Display Logged in Username
Replies
4
Views
1,519
justin0312
J
H
double click on picture frame in the form to enlarge the picture
Replies
1
Views
950
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom