Check previous record of another field null

[mcurtis812]

Table: Time
Fields: Timein and Timeout are the ones i am concerned about.
Form (Actually subform) : timeentrysubform
on subform
command53 puts current time and date into the timein
command59 puts current time and date in the timeout

on the subform the employee times in and times out of the order throughout
the day with different work functions for job costing. All works beautifully
and is cheat free except the following:

i want it to make sure that before he times in on a new function or job that
the previous was timed out. it is there on the continuous subform but they
are still working to jobs at the same time.

IE timein then timeout only before new record is available.
NOT timein then timein on another function at the sametime.

 

[Allen Browne]

Use the BeforeUpdate event procedure of the *form* (not control) to
DLookup() any previous value where the timeout is null, e.g.
Dim strWhere as String
strWhere = "(TimeIn < " & Format([TimeIn], "\#mm\/dd\/yyyy h\:nn\:ss\#")
& ") AND (TimeOut Is Null)"
If Not IsNull(DLookup("ID", "[Time], strWhere)) Then ...

There are several things you will need to consider here, e.g.:
- Is the user adding a new record, or editing an existing one?

- Could the user get around this by then going back and clearing the TimtOut
in an earlier record?

- Is TimeIn requiried?

- Does TimeIn contain both the date and the time (so it works across
midnight and across days)?

- Could there be a case where someone is validly working on multiple issues?

- Is Time a good name for a table? (It's a reserved word.)

 

[mcurtis812 via AccessMonster.com]

Quick note, i must say that your website has done soooo much for me as far
as coding, ideas and workarounds. You rock! Answers below:

Is the user adding a new record, or editing an existing one?

He will edit the existing to (timeout)

- Could the user get around this by then going back and clearing the TimtOut
in an earlier record?

No, i lock the box totally and use the Command buttons to enter the time into
the fields and once pressed, command buttons, they are then disabled and
setsfocus back to the main form employee number.

- Is TimeIn requiried?

Yes, for morning punch ins, etc. Also, i use a make table query to do the
datediff calculation, which is painfully accurate, and then export it to a
comma del. text file to import into my payroll system for both job audit
trail and payroll. Also, is there a way to write a calculation to a table? I
have heard several opinions but yours would count the most.

- Does TimeIn contain both the date and the time (so it works across
midnight and across days)?

Yes, discovered that the hard way.

- Could there be a case where someone is validly working on multiple issues?

Some smaller orders where we make out own parts for inventory, so we will
probably set an order up for that situation. Your comments on this are
welcome. i just think i can only have it one way or the other. IE situation
that began this post.

Is Time a good name for a table? (It's a reserved word.)

Yes, my predecessor built the initial db and has so many things throughout
that use it. Also, i work with people who hate change so i have kept the same
process to to the export / import procedure and reporting.

Use the BeforeUpdate event procedure of the *form* (not control) to
DLookup() any previous value where the timeout is null, e.g.
Dim strWhere as String
strWhere = "(TimeIn < " & Format([TimeIn], "\#mm\/dd\/yyyy h\:nn\:ss\#")
& ") AND (TimeOut Is Null)"
If Not IsNull(DLookup("ID", "[Time], strWhere)) Then ...

There are several things you will need to consider here, e.g.:
- Is the user adding a new record, or editing an existing one?

- Could the user get around this by then going back and clearing the TimtOut
in an earlier record?

- Is TimeIn requiried?

- Does TimeIn contain both the date and the time (so it works across
midnight and across days)?

- Could there be a case where someone is validly working on multiple issues?

- Is Time a good name for a table? (It's a reserved word.)

Table: Time
Fields: Timein and Timeout are the ones i am concerned about.

[quoted text clipped - 15 lines]

IE timein then timeout only before new record is available.
NOT timein then timein on another function at the sametime.

 

[mcurtis812 via AccessMonster.com]

I did not seem to have any luck with the code provided. Got some ) errors and
tried to resolve but did not have any luck.

Use the BeforeUpdate event procedure of the *form* (not control) to
DLookup() any previous value where the timeout is null, e.g.
Dim strWhere as String
strWhere = "(TimeIn < " & Format([TimeIn], "\#mm\/dd\/yyyy h\:nn\:ss\#")
& ") AND (TimeOut Is Null)"
If Not IsNull(DLookup("ID", "[Time], strWhere)) Then ...

There are several things you will need to consider here, e.g.:
- Is the user adding a new record, or editing an existing one?

- Could the user get around this by then going back and clearing the TimtOut
in an earlier record?

- Is TimeIn requiried?

- Does TimeIn contain both the date and the time (so it works across
midnight and across days)?

- Could there be a case where someone is validly working on multiple issues?

- Is Time a good name for a table? (It's a reserved word.)

Table: Time
Fields: Timein and Timeout are the ones i am concerned about.

[quoted text clipped - 15 lines]

IE timein then timeout only before new record is available.
NOT timein then timein on another function at the sametime.

 

[Allen Browne]

Suggestions:

1. Add the line:
Debug.Print strWhere
before the DLookup() line.
When it fails, press Ctrl+G to open the Immediate Window.
Look at what came out: it has to look just like the WHERE clause of a query.

2. There's a quote missing in this line:
If Not IsNull(DLookup("ID", "[Time]", strWhere)) Then

3. That's just the introduction: you need to decide what to do if the
condition occurs, e.g.:
Cancel = True
MsgBox "Duh!"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I did not seem to have any luck with the code provided. Got some ) errors
and
tried to resolve but did not have any luck.

Use the BeforeUpdate event procedure of the *form* (not control) to
DLookup() any previous value where the timeout is null, e.g.
Dim strWhere as String
strWhere = "(TimeIn < " & Format([TimeIn], "\#mm\/dd\/yyyy
h\:nn\:ss\#")
& ") AND (TimeOut Is Null)"
If Not IsNull(DLookup("ID", "[Time], strWhere)) Then ...

There are several things you will need to consider here, e.g.:
- Is the user adding a new record, or editing an existing one?

- Could the user get around this by then going back and clearing the
TimtOut
in an earlier record?

- Is TimeIn requiried?

- Does TimeIn contain both the date and the time (so it works across
midnight and across days)?

- Could there be a case where someone is validly working on multiple
issues?

- Is Time a good name for a table? (It's a reserved word.)

Table: Time
Fields: Timein and Timeout are the ones i am concerned about.

[quoted text clipped - 15 lines]

IE timein then timeout only before new record is available.
NOT timein then timein on another function at the sametime.

 

[mcurtis812 via AccessMonster.com]

I need to check the previous record only for that employee and date for a
[timeout]. Am i kind of hosed as far as checking the previous record this
information?

Situation:

Employee 1:Times In on an order creating record 1
Employee 2:Times In on an order creating record 2
Employee 3, etc.
so i really cant just check the previous record ID for it may be for another
employee.

If i have thoroughly confused you i am right there with you....

I did however get the initial code working with all the goods but defiantly
need tweaking if it is even possible to get what i want.

Suggestions:

1. Add the line:
Debug.Print strWhere
before the DLookup() line.
When it fails, press Ctrl+G to open the Immediate Window.
Look at what came out: it has to look just like the WHERE clause of a query.

2. There's a quote missing in this line:
If Not IsNull(DLookup("ID", "[Time]", strWhere)) Then

3. That's just the introduction: you need to decide what to do if the
condition occurs, e.g.:
Cancel = True
MsgBox "Duh!"

I did not seem to have any luck with the code provided. Got some ) errors
and

[quoted text clipped - 30 lines]

 

[Allen Browne]

Add more conditions to the criteria string, e.g.:

strWhere = "(TimeIn < " & Format([TimeIn], "\#mm\/dd\/yyyy h\:nn\:ss\#") &
") AND (TimeOut Is Null) AND (EmployeeID = " & Me.EmployeeID & ")"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I need to check the previous record only for that employee and date for a
[timeout]. Am i kind of hosed as far as checking the previous record this
information?

Situation:

Employee 1:Times In on an order creating record 1
Employee 2:Times In on an order creating record 2
Employee 3, etc.
so i really cant just check the previous record ID for it may be for
another
employee.

If i have thoroughly confused you i am right there with you....

I did however get the initial code working with all the goods but
defiantly
need tweaking if it is even possible to get what i want.

Suggestions:

1. Add the line:
Debug.Print strWhere
before the DLookup() line.
When it fails, press Ctrl+G to open the Immediate Window.
Look at what came out: it has to look just like the WHERE clause of a
query.

2. There's a quote missing in this line:
If Not IsNull(DLookup("ID", "[Time]", strWhere)) Then

3. That's just the introduction: you need to decide what to do if the
condition occurs, e.g.:
Cancel = True
MsgBox "Duh!"

I did not seem to have any luck with the code provided. Got some ) errors
and

[quoted text clipped - 30 lines]

IE timein then timeout only before new record is available.
NOT timein then timein on another function at the sametime.