Solved Struggling to use Dlookup to find value from input (1 Viewer)

[MajP]

Assuming you are logging times in and out for a job daily, you would have many times for many jobs, I would think your tables are

TblJob
--JobID 'PK
--JobName
-- Other fields unique to a job

tblJobTimes
-- jobTimeID ' autonumber
-- LogTime ' datetime
--TimeType ' in or out
--JobID_FK 'holds the PK for the Job table

1/1/2022 12:00 pm IN 1
1/1/2022 8:00 pm out 1
1/1/2022 07:00 am In 2
1/2/2022 11:00 am In 1

So I can log many in and out times for many jobs. Job 1, in 12 out at 8 on 1/1/2022. In again for job 1 on 1/2 at 11:00

 

[MajP]

You can post a screen shot of the table design view to show your tables and fields. Or you can type it like I did above. If you set up relations that is even better

 

[DarkBrewer]

Assuming you are logging times in and out for a job daily, you would have many times for many jobs, I would think your tables are

TblJob
--JobID 'PK
--JobName
-- Other fields unique to a job

tblJobTimes
-- jobTimeID ' autonumber
-- LogTime ' datetime
--TimeType ' in or out
--JobID_FK 'holds the PK for the Job table

1/1/2022 12:00 pm IN 1
1/1/2022 8:00 pm out 1
1/1/2022 07:00 am In 2
1/2/2022 11:00 am In 1

So I can log many in and out times for many jobs. Job 1, in 12 out at 8 on 1/1/2022. In again for job 1 on 1/2 at 11:00

It's even more simplistic than that.

I just recreated this table based on your feedback with an AutoNumber PK. JobNames are unique and when the input is provided, it should add the record if it doesn't exist, with the current date/time in the TimeIn value. That piece is working. If the JobName has a record, update the TimeOut value. Again, I hope I'm explaining that clearly.

Seems easy enough in theory!

 

[MajP]

I am kind of guessing at what you are doing so not sure if correct. Is there just one record for each job, or are there many? Are you logging daily times? If there is just one record then the job number is a fine pk, but if logging multiple records for a job then it cannot be the PK.

 

[DarkBrewer]

I am kind of guessing at what you are doing so not sure if correct. Is there just one record for each job, or are there many? Are you logging daily times? If there is just one record then the job number is a fine pk, but if logging multiple records for a job then it cannot be the PK.

There is only a single record for each Job. The times being logged pertain to the job being put in an oven, "TimeIn" and then, when the job is removed from the oven, "TimeOut.

 

[MajP]

If that is the case then jobname can be the PK and the auto number should not make a difference. The easiest is to attach you example db so people can help. However the most important thing is to get your tables correct first.

 

[DarkBrewer]

I finally had a chance to get back to this little project of mine.
Through a bit of reading, and trial and error, I figured out that this was the syntax I needed to use for my Dlookup to make it work.

If IsNull(DLookup("JobName", "Table1", "JobName = '" & Me.Text0.Value & "'")) Then

I do appreciate the feedback and input that was provided!