Condition issues

[June7]

I think yes, at the end of the Submit click event. But why is SetFocus not showing available for BEMSID? Because you have named the textbox "Scan Your Work Badge". Name it tbxID and reference that name in code.

[fspata]

Not sure I'm following you. Something like tbxID = BEMSID? Sorry but still getting used to all of the coding options out there.

Are you getting error message?

I name data controls different from fields bound to, like: tbxID. Then reference the control:

Me.tbxID.SetFocus

[fspata]

Sorry about previous post. Yours arrived after I submitted lol.

I think yes, at the end of the Submit click event. But why is SetFocus not showing available for BEMSID? Because you have named the textbox "Scan Your Work Badge". Name it tbxID and reference that name in code.

[fspata]

So tbxID would be the Name for field BEMSID? Still not working. Just so I'm not confusing you June7, this coding is part of the Check Out button. Let me know if that changes what you're trying to show me.

[June7]

tbxID would be name for the textbox. The field is still BEMSID.

The suggested edit on your posted db worked for me.

I suggested code in the Submit button.

[fspata]

Maybe I'm going about this the wrong way. At Check Out, I'm just wanting to update their last record, which should be a Check In, with the Check Out date and time ( NOW() ). Should I have a popup of sometype for them to scan their badge, press enter, and it updates the last record??

[fspata]

Maybe I'm going about this the wrong way. At Check Out, I'm just wanting to update their last record, which should be a Check In, with the Check Out date and time ( NOW() ). Should I have a popup of sometype for them to scan their badge, press enter, and it updates the last record??

By the way, this is what I had used after making the textbox the tbxID which still didn't get my cursor to the BEMID field.

Private Sub Timeout_Click()
Outtime = 1
Me.Requery
Me.tbxID.SetFocus
End Sub

[fspata]

AWW HELL YEAH. I've wanted to do scanning to check-in/out for a while, I'll just live my dream through you. So I'm just gonna fly through what you should do, I suggest doing it in a new database altogether (that you're able to share with us so we can more easily help you should you have more problems).

First, make the check-in/out table:
IDCheckInOut (primary key)
employeeID (foreign key)
timeCheckIn
timeCheckOut

Your Employee table should look something like:
IDEmployee (PK)
ScanningCode (this is the code from the ID they scan)
....

Then make a form for people to scan their ID, controls:
txtIDInput (textbox, and should be your only tabstop)
cmdSubmit (button) - set this button to default (clicked when "enter" is pressed) because as far as I know, scanners emulate "enter" after finishing scanning the barcode. Also set tabstop on this to false.



Now the fun begins. This code is untested, but should give you a good idea of how to do exactly what you want:
Click event for cmdSubmit:

'this will get the employee's ID from their scanned code. I'm assuming  no invalid IDs will be scanned here. You should build that in though
Dim empID as Integer
empID = DLast( "IDEmployee", "tblEmployees", "ScanningCode=" & txtIDInput


'this will look for a timesheet entry where the employee is checked in but not checked out - returns NULL if it can't find it (which means the employee is not checked in)
dim checkInOutID as integer
checkInOutID = DLast( "IDCheckInOut", "tblCheckInOut", "employeeID=" & empID &  " AND timeCheckIn IS NOT NULL AND timeCheckOut IS NULL" )



if isnull(checkInOutID) then
  'employee is not checked in, add a check in row!

  ' it's probably a good idea to re-write this to use recordsets and .AddNew rather than what i have below, but this will work.
  ' this code assumes timeCheckOut allows (and defaults to) null
  docmd.setwarnings false
  docmd.runsql "INSERT INTO tblCheckInOut ( employeeID, timeCheckIn ) VALUES ( " & empID & ", " & cdbl( now() ) & " ) "
  docmd.setwarnings true

else
  'employee is checked in, check them out!
  
  ' this should be obvious - update the record (where the employee checked in but not out) to check out now.
  docmd.setwarnings false
  docmd.runsql "UPDATE tblCheckInOut SET timeCheckOut=" & cdbl(now()) & " WHERE IDCheckInOut=" & IDCheckInOut
  docmd.setwarnings true
endif

'this may be unnecessary:
txtIDInput.setFocus

Once you've done that, I'd fancy it up a bit with a subform that shows their recent check-in/out times, maybe stay on the screen for like 20 seconds then go away (so the next employee can't see the previous employee's time), etc.

Keep us posted on how this project goes. I'm very excited for you.

So is this just for my Submit button or is this a whole new form to be created, maybe I misunderstood you earlier?? Also, are you referring to the Submit button being the all-in-one button, Check In/Out? Only reason I'm asking is because they have a line up of people behind them at checkout so I'm trying to streamline the checkout process to get people out faster with the scan Id, access the 'In' record and update it with checkout info.

[fspata]

Bigot, please disregard my question about the Submit being the Check in/out button. Went back through previous posts, the only thing being is, I don't want the Check in/out fields to be updated until they hit submit just incase they change their minds and leave.

[June7]

Nevermind.

[fspata]

Ok, did I miss something June7? Oh, lol!

[June7]

Just me mixed up. Here is something you might find informative.

The form is set for DataEntry Yes - this means it cannot display existing records when it opens. How do you plan to find and display existing records to update the CheckOut field?

Hmm. It appears the Intime and Outtime variables not getting set.

Learn to debug. Review link at bottom of my post.

I changed the DataEntry property to No so I could test code on the existing record. And added textboxes bound to Checkin and Checkout so I could see the results of code.

Outtime button needs [Event Procedure] in the event property - not = True

Option Compare Database
Option Explicit
Public Intime As Integer
Public Outtime As Integer

Private Sub Timeout_Click()
Outtime = 1
End Sub

Private Sub Timein_Click()
Intime = 1
End Sub

Private Sub Reset_Click()
Me.Undo
End Sub

Private Sub Submit_Click()
    If Intime = 1 Then
         Me![Check In] = Now()
    ElseIf Outtime = 1 Then
        Me![Check Out] = Now()
    End If
    Intime = 0
    Outtime = 0
    If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
    Me.Requery
    Me.tbxID.SetFocus
End Sub

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[fspata]

Does it need to display the record in order to update a field within the record? I'm still a newbie at this and that being said, I think with getting experince with this, I could get a better handle on what I'm doing in the future.
I used the code you gave me and it errors out with 'Method or data member not found.' On the Me.tbxID.SetFocus

[June7]

No, it doesn't. Can run an UPDATE sql action. Still need record id to do find record in table.

Edited my previous post quite a bit. Might review again.

I had to rename the textbox to tbxID.

All works.

[ssanfu]

I looked at your dB and became a little frustrated with the (lack of) naming convention..... so I changed a lot of names.

As noted, cannot check out as the dB stands because the form is in data entry mode. You could use VBA to find the already entered record for a badge number, job number, check in date, then set the check out date/time.

I set the global variables in the button click. Seemed easier than using more code in the submit button.
I removed the requires setting for the visitor badge number. If you have a work badge, how would you also have a visitor badge??
I renamed a lot (if not all) controls and most of the field names. I added PK/FK relationships. and properly (IMO) set up the combo boxes.
I added the prefix "frm" to the form name. Not real good idea to have multiple objects with the same names (table named "Initial" and form named "Initial").
I also removed the Layout formatting.


Attached is the modified dB..