Will Not update last record in Table

[mrbboy]

I am building a table, table1, with the following fields:
Name (text)
Sign in date (date/time)
Sign in time (date/time)
Sign out date (date/time)
Sign out time (date/time)

I have 2 forms that enter data into table1, sign in form and the sign out form. The sign in form contains a combo box for the employees names, a text box for the sign in date and another text box for the sign in time. The sign out form contains a combo box for the employee's names, a text box for the sign out date and another text box for the sign out time.

I need the sign out information to always fill in the sign out date and sign out time fields from the employee's last sign in date and time. I am using the following code on the sign out form's On Close event but it always saves over the employee's first sign in records. I don't want it to overwrite the first record.

Private Sub Form_Close()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strcriterium As String
Set db = CurrentDb
Set rs = db.OpenRecordset("table1", dbOpenDynaset)
strcriterium = "name = '" & Me.name & "'"
rs.FindFirst (strcriterium)
If Not rs.NoMatch Then
rs.Edit
rs("sign in date") = Date
rs("sign in time") = Time
rs.Update
Else
MsgBox "employee does not exist"
End If
rs.Close
End Sub

Please help.

 

[Zion7]

But you are editting the [Sign In date] & [sign in time]
fields???

rs.FindFirst (strcriterium)
If Not rs.NoMatch Then
rs.Edit
rs("sign out date") = Date
rs("sign out time") = Time
rs.Update

 

[mrbboy]

OOps. I just typed it wrong here. You are correct, I am editing the [sign out date] and [sign out time] fields.

Private Sub Form_Close()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strcriterium As String
Set db = CurrentDb
Set rs = db.OpenRecordset("table1", dbOpenDynaset)
strcriterium = "name = '" & Me.name & "'"
rs.FindFirst (strcriterium)
If Not rs.NoMatch Then
rs.Edit
rs("sign out date") = Date
rs("sign out time") = Time
rs.Update
Else
MsgBox "employee does not exist"
End If
rs.Close
End Sub

 

[Zion7]

If Not rs.NoMatch Then
rs.AddNew
rs("sign out date") = Date
rs("sign out time") = Time
rs.Update
Else

 

[mrbboy]

I added rs.addnew to the code but it still saved over the first record in the table. Please advise.

 

[Zion7]

well that's impossible?
How can a new record edit, an existing record?

Check your logic mrbboy.
You know how to update(Edit),
and you know how to Create(AddNew).
You can find the cursor you're looking for(FindFirst)

I think the logic is off somewhere?
When you add a new record, don't forget the other Data.
USer Name, time in,

Find the cursor by nOT ONLY checkinh the name, but the
sign in Date. Once you've got the right person,
on the right Date, then Update the Sign out time.

It actually should be an edit for sign out, but an AddNew
for sign In?

 

[mrbboy]

I got it to work. Thanks for all the help.

I do have another question though. I copied this same code for a visitor log form and every hour I am printing a report based on the visitors who have logged in and/or out. How do I stop it from printing the report if no visitors logged in?

The report is "Visitors Report" based on a query (Visitors Report Query) of Table1. Table1 has the following fields:

Name - Text
Sign in Date - Date/Time
Sign in Time - Date/Time
Sign out Date - Date/Time
Sign our Time - Date/Time

Thanks.

Rene

 

[Zion7]

Will a DLookup/Dmax() work?
Maybe, once a person logs on, hold the time in a Global variable, or table or GetSettings()...

Then using DMax(), compare with last
variable update, before you run report

or vica-versa

 

[mrbboy]

How do I include the [sign in date] in the strcriterium?

strcriterium = "name = '" & Me.name & "'"


Thanks

 

[Zion7]

strcriterium = "name = '" & Me.name & "' AND txtDate =#" & _
& Me.txtDate & "#"


DMax("txtDate","tbllogOn",strCriterium)

BTW, when you solve a problem on your own, or by others,
it's considerate to post it, so that all may benefit...

 

[mrbboy]

I apologize for that. I was just so excited that it worked. Your recommendation to add rs.Addnew did the trick. Thanks, I really appreciate it.

 

[mrbboy]

I modified the strcriterium to include the date also but now, I get a "Type Mismatch" error. What does this mean?

If (IsNull(Combo0) And IsNull(Text2)) Or (Not IsNull(Combo0) And Not IsNull(Text2)) Then

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strcriterium As String

Set db = CurrentDb
Set rs = db.OpenRecordset("SIGN IN TABLE", dbOpenDynaset)
strcriterium = "EMPLOYEE = '" & Me.EMPLOYEE & "'" And "[SIGN IN DATE] = #" & Me.[SIGN IN DATE] & "#"
rs.FindLast (strcriterium)
If Not rs.NoMatch Then
rs.AddNew
rs.Edit
rs("SIGN OUT DATE") = Date
rs("SIGN OUT TIME") = Time
rs.Update
Else
MsgBox "Please complete all fields"
End If
rs.Close

 

[Zion7]

strcriterium = "EMPLOYEE = '" & Me.EMPLOYEE & "' And [SIGN IN DATE] = #" & Me.[SIGN IN DATE] & "#"

look closely at my syntax, i removed 2 double quotes...

 

[TheAceMan1]

To All . . .

A[blue]dding a new record with signout without signin[/blue] just doesn't seem logical to me! In the real world the span is always [blue]in to out![/blue] . . .

See Ya! . . . . . .

 

[mrbboy]

Zion7,

I copied and pasted that line and now I get "Syntax error in date in expression". What does this mean?

 

[Zion7]

is [datein] a date, data type?

as Aceman said Rene, and I in my earlier post,
..."It actually should be an edit for sign out, but an AddNew for sign In?"

But you know the app best....

 

[mrbboy]

Zion7,

The [sign in date] field is a date data type. I edited the code for the sign out form to exclude the rs.addnew. I didn't see a need to add code to the sign in form because all it did was add new sign in info in the table and because Data Entry is clicked YES, it always added a new record to the table.

I still do not know why I get the error message "Syntax erorr in date in expression".

 

[Zion7]

try this?

strcriterium = "EMPLOYEE = '" & Replace(Me.EMPLOYEE,"'","''") & "' And [SIGN IN DATE] = #" & Me.[SIGN IN DATE] & "#"

 

[mrbboy]

I tried that and i still got the syntax error in date in expression message. What i tried next was just to set the strcriterium to everything after "and" and that got me the same message. Does that mean that somehow it is not recognizing the date as a date?

 

[Zion7]

strcriterium = "[SIGN IN DATE] = #" & CDate(Me.[SIGN IN DATE]) & "#"

???