Code to auto fill txt/cbo boxes

[Marshall Barton]

If I remove the format from the txt box I get "949051" at 1:30pm
I have been trying a Dlookup function in this instead but will run
into a problem with the shift change, I think.....

I know I showed different times with the thread with Evi...some
confusion
there. I have fixed them to show only 7:00am to 4:30pm Day shift
(shift #1)
and 5:45pm to 3:15am Afternoon shift (shift #2)

You will get that kind of weird result if you set txtTime to
Now instead of Time.

 

[Opal]

If I remove the format from the txt box I get "949051" at 1:30pm
I have been trying a Dlookup function in this instead but will run
into a problem with the shift change, I think.....

I know I showed different times with the thread with Evi...some
confusion
there.  I have fixed them to show only 7:00am to 4:30pm Day shift
(shift #1)
and 5:45pm to 3:15am Afternoon shift (shift #2)

You will get that kind of weird result if you set txtTime to
Now instead of Time.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

So I changed the txtTime box from Now to Time and I still get
949051

 

[Marshall Barton]

So I changed the txtTime box from Now to Time and I still get
949051

Please post the code that you used.

I'm getting the impression that you have more than one
version and I am having trouble tracking what you are using
when you get these strange results.

 

[Opal]

Please post the code that you used.

I'm getting the impression that you have more than one
version and I am having trouble tracking what you are using
when you get these strange results.

Hi Marsh!

I think I have been too many things at once with this database
but now I think I have a handle on it all.....I have the following
in the On Current event for the form:

If Me.txtTime >= #7:00:00 AM# And Me.txtTime <= #4:30:00 PM# Then
Me.txtShiftHour = DateDiff("n", #7:30:00 AM#, Me.txtTime) \ 60
+ 1
Else
Me.txtShiftHour = DateDiff("n", #5:45:00 PM#, Me.txtTime) \ 60
+ 1
End If

And its working now.....I was trying too many other options -
DLookups, etc

Thank you so much for all your help!

 

[Opal]

Hi Marsh!

I think I have been too many things at once with this database
but now I think I have a handle on it all.....I have the following
in the On Current event for the form:

 If Me.txtTime >= #7:00:00 AM# And Me.txtTime <= #4:30:00 PM# Then
        Me.txtShiftHour = DateDiff("n", #7:30:00 AM#, Me.txtTime) \ 60
+ 1
        Else
        Me.txtShiftHour = DateDiff("n", #5:45:00 PM#, Me.txtTime) \ 60
+ 1
        End If

And its working now.....I was trying too many other options -
DLookups, etc

Thank you so much for all your help!- Hide quoted text -

- Show quoted text -

Hmmm... further thought on this one....Monday to Thursday shift starts
@ 5:45pm but
on Friday it starts @ 4:45pm....would this just be a matter of an
additional line of
code?

 

[Marshall Barton]

I think I have been too many things at once with this database
but now I think I have a handle on it all.....I have the following
in the On Current event for the form:

If Me.txtTime >= #7:00:00 AM# And Me.txtTime <= #4:30:00 PM# Then
Me.txtShiftHour = DateDiff("n", #7:30:00 AM#, Me.txtTime) \ 60
+ 1
Else
Me.txtShiftHour = DateDiff("n", #5:45:00 PM#, Me.txtTime) \ 60
+ 1
End If

And its working now.....

Not so fast ;-)

The Current event can't be the right place for that. It
needs to be in the Timer event procedure along with all the
other "clock" calculations.

BTW, you should double check the form's TinerInterval
setting. Since you are not concerned with seconds, the
interval should be 60000 so it doesn't waste any time
calculating the same values many times per minute.

 

[Marshall Barton]

Hmmm... further thought on this one....Monday to Thursday shift starts
@ 5:45pm but
on Friday it starts @ 4:45pm....would this just be a matter of an
additional line of
code?

Well, it could be a few more lines of code that check the
time and the day of the week, but IMO, it really is time to
put the shift schedure in a table. I think your other
thread on this aspect has reached a breakthrough, but you
will need to add a DOW (day of week) field to the table so
you can have different schedules for different days.
Graham's function will have to be modified to deal with this
issue.

 

[Opal]

Well, it could be a few more lines of code that check the
time and the day of the week, but IMO, it really is time to
put the shift schedure in a table.  I think your other
thread on this aspect has reached a breakthrough, but you
will need to add a DOW (day of week) field to the table so
you can have different schedules for different days.
Graham's function will have to be modified to deal with this
issue.

My thoughts exactly. I have put the schedule in a table and
as soon as I saw Graham's code I started to think that this
could just be modified to work for the schedule too!

I need to throw another variable into Graham's code to
account for the day of the week as you said I'm just not sure
how and where....