Insert Statement in Access Table using VBA

Question

I'm currently creating an attendance form for my work. I'm using MS Access. I'm having trouble with the insert function.

I'm a bit lost on the insert query. The Login button is now done. What I need is a Log out button, so the query would be like this "Insert into tblLogin(ExpectedTimeout) values (value from txtTime) where CurrentDay = Me.txtToday and NID = me.txtNID

Below is my Login code btw:

Private Sub btnTimeIn_Click()
Dim tme As String
Dim test1 As Date 'variable for logintime
Dim test2 As Date 'var for expected timein
Dim test3 As Date 'var for time difference

'tme = Time
'txtTime.Value = tme

'test1 = tme
'test2 = txtExpected

'test3 = test1 - test2
'txtRemarks.Value = test3

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from tblLogin")

rec.AddNew
rec("CurrentDay") = Me.txtToday
rec("UserName") = Me.cboUname.Column(1)
rec("NID") = Me.txtNID
rec("Function") = Me.txtFunction
rec("Shiftdays") = Me.txtShift
rec("LoginTime") = Me.txtTime
rec("MinutesLate") = Me.txtLate
rec.Update

Set rec = Nothing
Set db = Nothing

MsgBox ("Logged In")

End Sub

What is this trouble you're refrring to? Nothing in your question that describes a problem, an error message or results that don't meet expectations. All are key to a good question — dbmitch

Parfait Parfait · Accepted Answer · 2016-10-08T13:12:31

It looks like you actually want to update the existing record created in LogIn, not insert a new record. So, consider using the Recordset .Edit mode wrapped in a Do loop and If statement:

Set rec = db.OpenRecordset("tblLogin")

Do Until rec.EOF 
   If rec("CurrentDay") = Me.txtToday And rec("NID") = Me.txtNID Then
       rec.Edit
       rec("ExpectedTimeOut") = Me.txtTime 
       rec.Update
   End If
   rec.MoveNext
Loop

Insert Statement in Access Table using VBA

1 Answers