I think a pretty easy Forms question... FindRecord (1 Viewer)

cvansickle

New member
Local time
Today, 07:39
Joined
Feb 13, 2019
Messages
6
Hello all, and again thanks in advance..

So I have a simple time clock database that I created for a department at work.

They user pulls down his name from a combo box and then enters a 4 digit password. Then he has two buttons

Clock IN & Clock OUT

When he/she hits the Clock IN button (using VBA Code) it grabs the date and and time. I also have a textfield call [datename] that captures a unique code
([datename] = [Today] & [Sorter_Name])

The when he/she hits the Clock out button, I would like it to find the first record based on the [namedate] field and add the clock out time.

the code I am using is

DoCmd.FindRecord "2/13/2019John Doe", , True, , True

This code work perfectly.. However, I want it to look into the textfield for the string.

DoCmd.FindRecord [namedate], , True, , True or

DoCmd.FindRecord me.namedate, , True, , True

But none of those work..

Any ideas what I am doing wrong? Can this be done??

Chad
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:39
Joined
Sep 21, 2011
Messages
14,044
Use a Dlookup to get your textfield value using the date & Sorter_Name and RecordType as criteria. ?
 

cvansickle

New member
Local time
Today, 07:39
Joined
Feb 13, 2019
Messages
6
How would I write that code?

??? = (Dlookup (namedate, TableIN, ?????)

Am I close :confused:
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:39
Joined
Sep 21, 2011
Messages
14,044
Yes, but what are you then going to do with DateName ?

Reading your first post I think now you would be better off with an Update query which sets TimeOut to Now()

Code:
strSQL = "Update TableIN SET TimeOut = NOW() WHERE DateName = ' & Me.DateName & "'"
DoCmd.runSQL  strSQL

Somewhat difficult to say not knowing your structure. TableIN ? is there a TableOUT ?

DLookup is https://support.office.com/en-us/article/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937
 

cvansickle

New member
Local time
Today, 07:39
Joined
Feb 13, 2019
Messages
6
Like I said very Basic..

TableIn

Sorter Name
password
Start Time'
End Time
Today
Total Hours
Code
Master IC
datename

When they Clock IN - Button

Code

If [Combo23] = [Text10] Then
1 [Start_Time] = Format(Time, "h:mm AM/PM")
2 [Today] = Date
3 [Name2] = [Sorter_Name]
4 [Text20] = "YOU ARE CLOCKED IN"
5 [Name2] = Forms!TimeClock!Combo23.Column(0)
6 [Sorter_Name] = Forms!TimeClock!Combo23.Column(1)
7 [Master_IC] = Forms!TimeClock!Combo23.Column(2)
8 [datename] = [Today] & [Sorter_Name]
9 [Text30] = "You clocked IN at " & [Start_Time] & " on " & [Today]
10 DoCmd.RunCommand acCmdSaveRecord
11 DoCmd.GoToRecord , , acNewRec

[Text10] = ""
Else
[Text15] = "INCORRECT PASSWORD"

End If



End Sub

When they Clock Out - Button

If [Combo23] = [Text10] Then

1 DoCmd.FindRecord "2/13/2019John Doe", , True, , True
2 [End_Time] = Format(Time, "h:mm AM/PM")
3 [Today] = Date
4 [Text20] = "YOU ARE CLOCKED OUT"
5 [Sorter_Name] = Forms!TimeClock!Combo23.Column(1)
6 [Master_IC] = Forms!TimeClock!Combo23.Column(2)
7 [datename] = [Today] & [Sorter_Name]
8 [Text30] = "You clocked OUT at " & [End_Time] & " on " & [Today]
9 DoCmd.RunCommand acCmdSaveRecord
10 DoCmd.GoToRecord , , acNewRec
[Text10] = ""
Else
[Text15] = "INCORRECT PASSWORD"

End If


End Sub

Ran this way it works just fine..

Line 8 of Clock In is putting the "2/13/2019John Doe" string in the [datename] field

In Line 1 of ClockOut is have just cut and pasted the 2/13/2019John Doe straight out of the table.
I would like to have it find the record based on the [datename] field, which is the same data

No TableOut - I started with one, but didnt think I needed it.
 

cvansickle

New member
Local time
Today, 07:39
Joined
Feb 13, 2019
Messages
6
I am trying to use find record but it is not working

I have tried

DoCmd.FindRecord "datename", , True, , True
DoCmd.FindRecord "[datename]", , True, , True
DoCmd.FindRecord [datename], , True, , True
DoCmd.FindRecord datename, , True, , True

DoCmd.FindRecord "me.datename", , True, , True
DoCmd.FindRecord "[me.datename]", , True, , True
DoCmd.FindRecord [me.datename], , True, , True
DoCmd.FindRecord me.datename, , True, , True

I dont know why it will not find it unless it is an actual string

DoCmd.FindRecord "2/13/2019John Doe", , True, , True

:banghead:
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:39
Joined
Sep 21, 2011
Messages
14,044
THis finds a record in a table of mine. F4 is the control/field that should hold Hubbard:196594

Code:
Private Sub cmdFindText_Click()
Dim strFind As String

strFind = "Hubbard:196594"
Me.F4.SetFocus
DoCmd.FindRecord strFind, acAnywhere, True, acSearchAll, , acAll
End Sub

HTH
 

cvansickle

New member
Local time
Today, 07:39
Joined
Feb 13, 2019
Messages
6
GASMAN!!!!! You have been awesome thanks for taking the time to help me out..

I think I figured out where I was going wrong.

when I hit "Clock Out" button, I was starting with a new record, so my field was null.

I added a line before the findrecord

[Name2] = Date & Forms!TimeClock!Combo23.Column(1)
DoCmd.FindRecord Me.Name2, , True, , True

Then the name2 field had the data I was looking for.. And WAA LAAAA

:)

Thanks again for the time and the help!!
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:39
Joined
Sep 21, 2011
Messages
14,044
FWIW when I have silly errors like that, and by silly I mean everything looks correct but it is still not working, I will walk through the code step by step with F8 in the debug window after setting a breakpoint. That normally highlights my error.
 

Users who are viewing this thread

Top Bottom