input box help (1 Viewer)

So I've been searching for the answer to my problem with no luck. I will make a simple version of what I need to do to make this question easier to ask.

I have 2 tables,

table 1 is [Employees] with Fields [Name] and [Passcode]

table 2 is [Clockedhours] with fields [Employee] , [Date of Work] , [Start Time] , [End Time]

I have one Form with 2 buttons Login and Logout, when pressed brings up an Input box asking for passcode for the user to login.

What I want it to do is search [Employees] for the passcode, if it matches a code then i want it to use that [Employee] to make a record in [Clockedhours] if it is a login, or if it is a log out i want it to find the record based on the employee and edit it to add the [end time].

What I got so far....

Private Sub Command2_Click()
Dim passkeybox As String
passkeytitle = "Enter passkey to clock in....."
passkeymsg = "Passkey Box"
passkeybox = InputBox(passkeytitle, passkeymsg)

If passkeybox = "12345678" Then

Dim dbtimecard As DAO.Database
Dim rstclockedhours As DAO.Recordset

Set dbtimecard = CurrentDb
Set rstclockedhours = dbtimecard.OpenRecordset("clockedhours")

rstclockedhours.AddNew
rstclockedhours("Employee").Value = "Testname"
rstclockedhours("Date of Work").Value = Date
rstclockedhours("Start Time").Value = Time()
rstclockedhours.Update

End If
End Sub

My Problem.....
1. As you can see I can not figure out how to search for a passkey and then come up with the name that goes with it....
2.That snippet does make a generic record,,, but I don't know how to make it error proof to not make a double login , throw up a msg saying you are already logged in if thats the case....
3. And how to edit the log out version to edit a current record instead of making a new one, and throwing up a msg that you are not logged in so you cant log out ect

I figured out my syntax error on dlookup and i do not understand but it works

Private Sub Command2_Click()
Dim passkeybox As String
Dim username As String
Dim result As String

passkeytitle = "Enter passkey to clock in....."
passkeymsg = "Passkey Box"
passkeybox = InputBox(passkeytitle, passkeymsg)

username = DLookup("Employee", "Employees", "passcode = '" & passkeybox & "'")

result = MsgBox("logging in as " & username & " , select ok to continue or hit cancel", vbOKCancel, "Correct User?")

If result = vbOK Then

Dim dbtimecard As DAO.Database
Dim rstclockedhours As DAO.Recordset

Set dbtimecard = CurrentDb
Set rstclockedhours = dbtimecard.OpenRecordset("clockedhours")

rstclockedhours.AddNew
rstclockedhours("Employee").Value = username
rstclockedhours("Date of Work").Value = Date
rstclockedhours("Start Time").Value = Time()
rstclockedhours.Update
Else

End If
End Sub


so now i can use that username , i think im left with how to make or edit records correctly

What happens at this line . . .
. . . if the passcode is not valid? Username will be null, but a record will still be added to the table. You need to check the value of Username to determine if the DLookup() call succeeded.

that line does work, it searches for the username, then in the next line it displays it correctly in a msg box,.... and further down the username is used to make a record.....

so im left now on how to structure the record creating when i make the log in button, to make a new log in without creating a log in when there is an active log in that has not been logged out...........

and then the entire code i will copy to the log out button but edit it to only edit a correct record that matches username & has been logged in & has not been logged out

I just re read your question after writing that above reply and i understand you are saying im missing an error handle for incorrect passcode..... thanks for pointing that out..... a simple ...... if username = null or blank then msgbox passcode invalid and end sub line should work i belive .... will try it out

so i fixed that bad login stuff, tested and it works..... thanks for pointing that out

only the hard part left,,,, i think i got an idea how to make record creation and editing with correctly but still nothing close

Private Sub Command2_Click()
Dim passkeybox As String
Dim username As String
Dim result As String
Dim badlogin As String


passkeytitle = "Enter passkey to clock in....."
passkeymsg = "Passkey Box"
passkeybox = InputBox(passkeytitle, passkeymsg)

username = Nz(DLookup("Employee", "Employees", "passcode = '" & passkeybox & "'"), "Null")

result = MsgBox("logging in as :" & username & " , select ok to continue or hit cancel", vbOKCancel, "Correct User?")

If username = "Null" Then
badlogin = MsgBox("your login shown as user :" & username & " , click ok to try and login again", vbOKOnly, "Correct User?")

Else: GoTo Line5

End If

If badlogin = vbOK Then GoTo LastLine


Line5:

If result = vbOK Then

Dim dbtimecard As DAO.Database
Dim rstclockedhours As DAO.Recordset

Set dbtimecard = CurrentDb
Set rstclockedhours = dbtimecard.OpenRecordset("clockedhours")

rstclockedhours.AddNew
rstclockedhours("Employee").Value = username
rstclockedhours("Date of Work").Value = Date
rstclockedhours("Start Time").Value = Time()
rstclockedhours.Update
Else
LastLine:
End If
End Sub

What I would do is have a UserActivity table, like . . .

tUserActivity
UserActivityID (PK)
UserID (FK)
DateTime
IsLogin

Click to expand...

. . . where if the user is logging in, IsLogin is True and if the user is logging out, IsLogin = False. Then you don't have to find back the Login record, just create the right record for the current event, whatever it is.

Some logouts you won't be able to catch, like if Access crashes, or if the computer crashes. So you'll have to always allow logins, even if there isn't a corresponding logout.

HTH

well,,, i did figure it out, and everything works,,,, the part that creates records i have checking as needed and it seams to be error free . ironically i think i made a simple time card program... with some tweaking it looks good

i made a txt file with all my information...