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.
Access Level table (tbl_Accesslevel)
Access level deciding what they can and cannot see is decided via VBA.
COde for credentials module:
COde in the login form to determine their credentials on login:
Code for combobox in frm_visualinspectioninput:
and the click event to save the record update:
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:
and for the click event to complete the record update:
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.
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.
- ID
- UserName
- Password
- AccessLvl
- FacilityName
- FacilityName2
- Other fields not related to this
Access Level table (tbl_Accesslevel)
- ID
- 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
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.