UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Personnel In/out Db With Ability To In/out Equipment, Access 2013    
 
   
MrWrap2
post Dec 3 2019, 01:12 PM
Post#1



Posts: 121
Joined: 10-January 17



Good afternoon everyone,

With the help of several of you, I've undertaken a small project (found at www.UtterAccess.com/forum/index.php?showtopic=2055986&hl=

I have a DB that currently tracks personnel coming in/out of my facility. Note: I have a controlled entry/egress point manned by an employee in which he is dedicated to perform a few functions:

1) Check in/out personnel in order to maintain accountability of personnel who have been granted access.
a) grant access to those individuals listed with unrestricted access
b) grant access to those individuals with restricted access only after they have been assigned an escort.
2) issue (in/out) serialized keys (note: there are 24 controlled and serialized keys, of which 7 personnel (supervisors) are authorized to receive them. A key can only be issued to one person at a time, thus I must prevent double check in/out)
3) issue (in/out) serialized radios (all individuals are assigned a radio, but they do not always have to have it issued to them when they enter.)
4) Record all personnel who have received a safety brief within the last 12 months and maintain a log of who has/hasn't.

So far, the attached DB fulfils Items 1.a. and 1.b.

In order to tackle 2; from the Check In/Check Out form, after I have scanned the members' Common Access Card (CAC) and if the member is authorized to receive keys, I'd like a message window to pop up prompting if the member intends to receive keys (yes or no buttons). If yes, a new form that lists available keys will display (perhaps preventing (greying out) keys that are already issued), and I can select the keys he intends to take. once all keys have been selected, the data is collected on the tables. I assume I will need to build a new table that lists the key serial numbers, who signed them out, and the date/time they were signed out.

On the reverse, when scanning the CAC for the member to leave the facility, if the member has signed out keys, a pop up will appear to list all keys he has signed out and prompt him to turn them back in.

Anyone up to the challenge to assist? I'll be greatly indebted!

After typing this I realize the zipped DB file exceeds the 2MB upload file (even after compact & repair)....are my efforts fruitless?

Attached File  Access_Control_1.4_with_example_data1.zip ( 279.16K )Number of downloads: 2
(added per request)

-Mike
Go to the top of the page
 
June7
post Dec 3 2019, 02:23 PM
Post#2



Posts: 1,018
Joined: 25-January 16



Did you make a copy and remove most data?

Sounds like relatively simple coding. Have you made attempt?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
theDBguy
post Dec 3 2019, 02:30 PM
Post#3


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi Mike. You could also start out with posting your table structure for a review. You should have at least the following tables:

Personnel
Keys
Radios
AccessLog
KeysIssueLog
RadiosIssueLog

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MrWrap2
post Dec 3 2019, 03:10 PM
Post#4



Posts: 121
Joined: 10-January 17



Thanks again theDBguy

Tables are as follows

tblAccessLevel
-ID
-Access_Level
-Access_Color
-Access_Color_Description
-Access_Instructions
-Access_Instructions_Abv
-Sort_Order

tblAdminPass
-ID
-AdminPW
-AssignedToName

tblDoNotDelete
-ID
-Field1

tblEmployees (this equates to Personnel)
-EmployeeID
-eLast
-eFirst
-eStatus
-Access_Level
-Comment
-Grade
-Grade_Service_Ind
-UIC
-Section
-Delete_Date
-Member_Added_Date
-Has_Photo

tblEventDuration
-ID
-Duration
-EmployeeID

tblEvents (this equates to AccessLog)
-ID
-EmployeeID
-TimeIn
-TimeOut
-TimeInComment
-TimeOutComment
-Purpose
-EscortID_In
-EscortID_Out

tblSectionNames
-ID
-SectionName
-Description

tblUIC
-MSC
-UIC
-UnitName

I have not yet built tables for keys, or radios or their event logs yet but propose something such as the following:

tblKeys
-ID
-SerialNumber
-BuildingNumber
-Status (in/out)

tblRadios
-ID
-SerialNumber
-Status (in/out)
-AssignedTo
This post has been edited by MrWrap2: Dec 3 2019, 03:18 PM
Go to the top of the page
 
MrWrap2
post Dec 3 2019, 03:22 PM
Post#5



Posts: 121
Joined: 10-January 17



Thanks June7,

QUOTE
Did you make a copy and remove most data?

There appears to be no data stored in any of the tables. Is there anywhere else I might be able to find data that might be chewing up a lot of space?
Go to the top of the page
 
MrWrap2
post Dec 3 2019, 05:09 PM
Post#6



Posts: 121
Joined: 10-January 17



I'm terrible with written communication, and pictures express a thousand words... general note about these attachments, anything with a yellow boarder is unbound and created for graphical depiction only, there is no true functionality.

Slide 1 depicts the current initial user interface: The Access Control Point (ACP) manager, scans the barcode on the back of the common access card (CAC) for the person requesting entry (depicted on the left side of the image). Once that field is updated, the information is populated as depicted on the right side of the image. This records the date/time the individual was granted access onto tblEvents (EmployeeID, TimeIn, ..., Purpose, Escort_In, ...). I would like to add a few steps between to have the ability to check in/out keys.

Note 1: If no profile has been created, we are presented with another form to create the Members' Profile (I've omitted this from the attachments for the time being).


Slide 2: The left image is the Update Members form. This is where the ACP can assign access levels (unrestricted, limited, and restricted) to individuals. I'd like to add a combobox to this form for Authorization to Receive Keys. If the user is authorized to receive keys, I'd like to have a pop-up window (not sure if this should be a message box, or open a form) which prompts ACP "Does the member intend on receiving keys?" (frmKeyPrompt); if the user is not authorized to receive keys, this message will not display. If the answer is no, the window will close and only the data for personnel entry will be recorded; if the answer is yes....

Note 2: Only a specific list of individuals will have permission to sign out keys. These individuals are known. Visitors and the like will by default not have the authorization to receive keys.

Slide 3: If ACP answers yes to "Does the member intend on receiving keys?" (and this is 100% an idea with no idea how to do it...), frmKeyPrompt will close and frmKeyOutMenu will open as a new pop-up. Available keys will be displayed in green, unavailable keys will be displayed in gray. As ACP selects the desired keys, they will turn red indicating they have been selected. ACP will then press "Check Selected Keys OUT" button, and another pop-up will request the CAC for the individual issuing the key (ACP).

Note 3: I believe each key should have its own record on tblKeyEvents vice having the EmployeeID with a concatenated list of all keys he signed out during that transaction.

Slide 4: After the CAC has been scanned (our scanner automatically enters the information then TAB), all selected buttons will turn gray and the data will be recorded on tblKeyEvents (EmployeeID, KeySerialNumber, TimeOut, ..., IssuedBy). If it is possible to nest a dashboard showing what keys the individual actively has signed out, that would be great, but not required.


Big hopes that we can achieve these. Once we tackle these challenges, we can consider how the employee will turn the keys back in using a reverse of what is depicted in slide 3.

Open to any thoughts or suggestions.

-Mike
Attached File(s)
Attached File  Slide1.JPG ( 122.79K )Number of downloads: 5
Attached File  Slide2.JPG ( 150.81K )Number of downloads: 4
Attached File  Slide3.JPG ( 188.11K )Number of downloads: 6
Attached File  Slide4.JPG ( 152.8K )Number of downloads: 3
 
Go to the top of the page
 
MrWrap2
post Dec 4 2019, 06:35 AM
Post#7



Posts: 121
Joined: 10-January 17



compressed and compact & repair worked. DB attached.
Attached File(s)
Attached File  Access_Control_1.4_with_example_data1.zip ( 279.16K )Number of downloads: 6
 
Go to the top of the page
 
jleach
post Dec 4 2019, 06:48 AM
Post#8


UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


Hi - I added this attachment to the original post per request.

Cheers,

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
mike60smart
post Dec 4 2019, 11:46 AM
Post#9


UtterAccess VIP
Posts: 13,488
Joined: 6-June 05
From: Dunbar,Scotland


Hi

Had a quick look at your relationship window and am a little puzzled as to why there is no Referential Integrity set between any tables??


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
MrWrap2
post Dec 4 2019, 11:57 AM
Post#10



Posts: 121
Joined: 10-January 17



Mike60smart,

Tough question. I have no contact with the original developer....
Go to the top of the page
 
MrWrap2
post Dec 4 2019, 02:56 PM
Post#11



Posts: 121
Joined: 10-January 17



Keeping everything together,

Step 1 was to create a method for establishing the authorization to receive keys, i.e. creating a user permission. We've been able to do that: Ability to Authorize Access to Keys

Now that I have the capability to review those, I need to add a step into a (seemingly massive) VBA After Update on the main form used for checking individuals in/out (Check In/Check Out).

Within the below VBA, how/where do I 'step-in' and create prompts such as if the user is authorized to receive keys "Does the user intend on receiving keys?"

CODE
Private Sub txtCACBack_AfterUpdate()

Dim qrydef As QueryDef
Dim rst As DAO.Recordset
Dim strFile As String

DoCmd.SetWarnings False

'Check to see if admin badge was scanned

Set db = CurrentDb
Set qrydef = CurrentDb.QueryDefs("qryAdminPass")
qrydef.Parameters(0) = Me.txtCACBack.Value

Set rst = qrydef.OpenRecordset
If (rst.BOF And rst.EOF) Then
Else
Call ShowAdmin
GoTo CleanUpAndExit
End If

'Ensure correct CAC barcode was scanned

If Len(Me.txtCACBack) <> 18 Then
  MsgBox "Invalid Barcode Scanned."
   Me.txtCACBack.Value = ""
   GoTo CleanUpAndExit
      Else
     End If


'Check for Account, create one if it does not exist
Set qrydef = CurrentDb.QueryDefs("qryCheckForAccount")
qrydef.Parameters(0) = Me.txtCACBack.Value

Set rst = qrydef.OpenRecordset
    
If (rst.BOF And rst.EOF) Then

        If Left(Me.txtCACBack.Value, 3) = "XXX" Then
        MsgBox "The scanned certificate is not valid. Destroy the certificate and re-add the member."
        GoTo CleanUpAndExit
        Else
        MsgBox "No member associated with the scanned CAC.  Add the member and re-scann the CAC."
        Me.txtCACBack.Value = ""
        DoCmd.OpenForm "frmAddEmployee"
        GoTo CleanUpAndExit
        End If

Else
End If

'Check if employee is on deck

Set qrydef = CurrentDb.QueryDefs("qryCheckNotcheckIn")
qrydef.Parameters(0) = Me.txtCACBack.Value

Set rst = qrydef.OpenRecordset
    
If (rst.BOF And rst.EOF) Then
'MsgBox "Not on Deck"
GoTo CheckIn
GoTo CleanUpAndExit
Else
GoTo CheckOut
End If

CheckIn:


Set db = CurrentDb
Set qrydef = CurrentDb.QueryDefs("qryCheckForAccount")
qrydef.Parameters(0) = Me.txtCACBack.Value

Set rst = qrydef.OpenRecordset

'check is account status is active
If rst.Fields(3).Value = "Active" Then
Else
MsgBox "The members account is set to 'Inactive'.  Notify an Administrator for assistance."
GoTo CleanUpAndExit
End If


If rst.Fields(11).Value = 0 Then
Me.txtEscort1.Value = "NA"
Else
Me.txtEscort1.Value = ""
DoCmd.OpenForm "frmEscort", acNormal, , , , acDialog
End If

'checks to see if the user canceled the escort select
If Nz(Me.txtEscort1, "") = "NA" Or Nz(Me.txtEscort1, "") <> "" Then
DoCmd.OpenQuery "qryCheckInAppend"
Else
MsgBox "An Escort was not selected. Check in canceled."
GoTo CleanUpAndExit

End If

Me.txtEscort1.Value = ""
Me.txtEscort.BackStyle = 1
Me.txtEscort.BackColor = Nz(rst.Fields(9), 0)
Me.Refresh
Me.txtChechedIn.Value = rst.Fields(1) & ", " & rst.Fields(2) & " Checked in at " & Now()
Me.txtAccessDescription.Value = rst.Fields(10)

strFile = "C:\Users\Public\Documents\Ground_Ammunition_Files\Member_Pictures\" & Me.txtCACBack.Value & ".jpg"
If FileExists(strFile) Then
Me.imgMemberPicture.Picture = "C:\Users\Public\Documents\Ground_Ammunition_Files\Member_Pictures\" & Me.txtCACBack.Value & ".jpg"
Else
Me.imgMemberPicture.Picture = "C:\Users\Public\Documents\Ground_Ammunition_Files\Member_Pictures\NoPhoto" & ".jpg"
End If


GoTo CleanUpAndExit


''Check Out
CheckOut:
If Me.Option24.Value = 0 Then
DoCmd.OpenForm "frmAdmin", acNormal, , , , acDialog
Else
Me.txtPurpose.Value = "Emergency Checkout"
End If
'
If Nz(rst.Fields(6).Value, "") = "NA" Then
Else
DoCmd.OpenForm "frmEscort", acNormal, , , , acDialog
'Me.txtEscort1.Value = "Emergency Checkout"
End If

If (Nz(Me.txtEscort1, "") = "" And Nz(rst.Fields(6).Value, "") <> "NA") Then
MsgBox "An Escort was not seleced. Check out canceled."
GoTo CleanUpAndExit
Else

End If
Go to the top of the page
 
June7
post Dec 4 2019, 09:05 PM
Post#12



Posts: 1,018
Joined: 25-January 16



Possibly after checking if account is active.

'check is account status is active
If rst.Fields(3).Value = "Active" Then
'do something here with keys
Else

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
MrWrap2
post Dec 5 2019, 11:10 AM
Post#13



Posts: 121
Joined: 10-January 17



I may have jumped the gun a bit,

I've created a new fractured discussion pertaining to Afterupdate Open a Form
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 09:20 AM