Complex Question ot sure of subject lol

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that each user has a company badge number that they sign
equipment out with. When they sign out equipment it goes to an emp file and
finds the user. If the user doesnt exist of course it says so. If it does it
lets them sign out the equipment. I want to take this to another level as
below but need help.

1. I want to only allow them to sign out one piece of equipment at a time.
ANy idea on the code I should use in the Emp bedge# field that it looks up.
My data for units signed out is in a table called tbl_Header.

2. I want to be able to have a table that I will call my Naughty list. This
list will have teh Badge Numbers of people who dont return guns on a regular
basis or the previous day. I want to be able to (manually is ok) add people
to this list (This part I can do easily duh), but the more complex part for
me would be VBCode telling it if the person is in the Emp and not in the
Naughty list table then let them have the unit. If they are in the emp table
but on the Naughty list then display msgbox and not record record in
tbl_Header.
 
RF,
Re #1...
Your form that logs tool disbursement should contain information as to
"the tool was returned". For example, a DateOfReturn (this would be the
best), or a Checkbox (T/F) called Returned, etc... When an employee goes to
take out a tool, and the EmpID is entered, do a Dlookup to see if that EmpID
has any records with a Null DateOfReturn. If so, warn the disburser with a
message box, and deny the transaction. In your recordset, you don't care if
an employee has 30 disbursements... as long as they're all "returned."
Even more productive, would be to... after the warning message,
programmatically find the record that is still open, so that the disburser
can present the employee with the information as to why the disbursement was
denied.

Re #2...
Add a field to your table called DateOfIssue, and along with #1's
DateOfReturn, you can calculate whenever a Return exceeds one day. Further,
since we're tracking returns through date values, we can ask, for example
"What employees have more than 3 overdue returns within the last month?"
That's your "nasty list."
 
On question 2

How would I change the code below to look at a able called
"tbl_RF_Lock_Out_List" with field called "Badge#"? Any help would be greatly
appreciatted!

------------------Start Code:------------------------

Private Sub AssociateNumber_AfterUpdate()
GotoAssociate = False
Set rs = New ADODB.Recordset
Set rsAdd = New ADODB.Recordset
If Not (IsNull(AssociateNumber) Or AssociateNumber = "") Then
rs.Open "SELECT * FROM EMP WHERE EMPNUM = '" & AssociateNumber & "'", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rs.EOF Then
MsgBox "Associate Not Found..."
AssociateNumber = ""
GotoAssociate = True
Else
rsAdd.Open "INSERT INTO GunsLog (SerialNumber,Associate,StartTime)
VALUES('" & SerialNumber & "','" _
& AssociateNumber & "',#" & Now() & "#)", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
End If
End If

End Sub

-------------------End Code--------------------
 
Back
Top