How to test query through form for similar records?

[harleyskater]

I have a timeclock form, that allows people to clock in and out. The recordsource for that form is a table. I want to have the form check the table to see if someone has already clocked in, so they don't clockin twice without clocking out. There will have to be some sort of code/query that checks for a null value in the clockout field and the persons name selected in the combo box.

The form is simple, a dropdown combo box with employee names source is a select statement, a clockin date/time field set now() and a type field for is default "hourly" and then a cmdButton with this code

Option Compare Database
Private Sub Command15_Click()

    DoCmd.Close

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    lngAnswr = MsgBox("Is your Entry Correct " & emp_first & "?", vbOKCancel + vbQuestion)
    If lngAnswr = vbOK Then
        Cancel = False
        MsgBox ("ClockIn Confirmed " & emp_first & "")
    Else    ' User chose No.
        Cancel = True
        MsgBox ("ClockIn Canceled " & emp_first & "")
    End If

End Sub


Private Sub job_num_Change()
    Me.item_id
End Sub

I do not know how to check for the number of records returned in a query. I assume if you could run a query that has clockout = null and dbo_employees.emp_first like emp_first and you could check to see if it returns a record then you could prompt with a clockin error that would be awesome. Does anyone know how to test for how many records are returned in vba?

 

[ajetrumpet]

I want to have the form check the table to see if someone has already clocked in, so they don't clockin twice without clocking out. There will have to be some sort of code/query that checks for a null value in the clockout field and the persons name selected in the combo box.Does anyone know how to test for how many records are returned in vba?

I don't know what you're talking about regarding the counts of records, but if you want to see what I did with a timeclock, check out the attached.

When you record the time, it is written to the first field in the table that is NULL (weather it be time-in, out, lunch-in, out, whatever). If you push the button twice, it pops up a message that tells you that you have already recorded your time during the current session. Maybe it'll help a bit as an example...

 

[pbaldy]

You could use a DCount against your query. I'd probably open a recordset with similar criteria, so I already had a window to the data open. If the recordset opens with no records, you can add one. If it has a record, you can clock the person out or whatever you want to do.

 

[harleyskater]

First off thank you for your response Our timeclocks are completely different : )

I will say it more simple what I have, I know I am confusing
There are 2 tables: Employees ; TimeClock
There is 1 form for clockin and a seperate for clockout:
ClockIn consists of - 1 combobox that lists employee names ; 1 textbox with the date and time for now and 1 last textbox with the type of clockin "ie hourly, vacation, funeral, sickpay"
one command button with the code listed above in the start of the thread.

So if I run a vbquery on the table "timeclock",which is just 4 fields(emp_id;clockIn, clockOut, and type), from my vbcommandbutton after the user has chosen there name. that has emp_id from the form = to the emp_id from the combo box and clockOut = null I would hope the query would be empty and if it does return a record in that query I would have to prompt the user that they didn't clockout.

I don't know what you're talking about regarding the counts of records, but if you want to see what I did with a timeclock, check out the attached.

When you record the time, it is written to the first field in the table that is NULL (weather it be time-in, out, lunch-in, out, whatever). If you push the button twice, it pops up a message that tells you that you have already recorded your time during the current session. Maybe it'll help a bit as an example...

 

[harleyskater]

I will research this, I am not familar with it. I will get back with you, Thank you

You could use a DCount against your query. I'd probably open a recordset with similar criteria, so I already had a window to the data open. If the recordset opens with no records, you can add one. If it has a record, you can clock the person out or whatever you want to do.