Need a better way

  • Thread starter Thread starter Josh
  • Start date Start date
J

Josh

I'm using continuous forms to match as close as possible an excel file. At the
end of each day, I export the day's info to this excel file, which is then
emailed to a business which requires excel files.
(just trying to explain why I did it this way in the first place)

It has worked fine for a couple of years, but I'm trying to clean up the code,
and looking for a way of doing the following better.

Currently, I have HEVar (HourEndingVariable), where if we are going to turn a
motor on, we schedule it for 6:00am for example. Leaving the schedule field
sets HEVar to 6 (for this example). Then, I have this in Exit Event:

If Me.HEvar = 6 Then
Me.HE1 = Me.txtTodayKW
Me.HE2 = Me.txtTodayKW
Me.HE3 = Me.txtTodayKW
Me.HE4 = Me.txtTodayKW
Me.HE5 = Me.txtTodayKW
Me.HE6 = Me.txtTomorrowKW
Me.HE7 = Me.txtTomorrowKW
Me.HE8 = Me.txtTomorrowKW
(skip the rest of this, but goes on to Hour24)

ElseIf Me.HEvar = 7 Then
Me.HE1 = Me.txtTodayKW
Me.HE2 = Me.txtTodayKW
Me.HE3 = Me.txtTodayKW
Me.HE4 = Me.txtTodayKW
Me.HE5 = Me.txtTodayKW
Me.HE6 = Me.txtTodayKW
Me.HE7 = Me.txtTomorrowKW
Me.HE8 = Me.txtTomorrowKW
etc etc......

As you can see, basically have to have an IF for each hour.

Can anyone suggest some method of having it in a Module, and do something like:
(not even attempting code, just trying to give an example)

all HE's where the hour part of the field (HE6 - 6 is the hour part of the
field name) is < HEVar, set value to Me.txtTodayKW
All HE's where the hour part of the field is >= HEVar, set value to
Me.txtTomorrowKW

Thanks, Josh
 
Josh said:
I'm using continuous forms to match as close as possible an excel file. At the
end of each day, I export the day's info to this excel file, which is then
emailed to a business which requires excel files.
(just trying to explain why I did it this way in the first place)

It has worked fine for a couple of years, but I'm trying to clean up the code,
and looking for a way of doing the following better.

Currently, I have HEVar (HourEndingVariable), where if we are going to turn a
motor on, we schedule it for 6:00am for example. Leaving the schedule field
sets HEVar to 6 (for this example). Then, I have this in Exit Event:

If Me.HEvar = 6 Then
Me.HE1 = Me.txtTodayKW
Me.HE2 = Me.txtTodayKW
Me.HE3 = Me.txtTodayKW
Me.HE4 = Me.txtTodayKW
Me.HE5 = Me.txtTodayKW
Me.HE6 = Me.txtTomorrowKW
Me.HE7 = Me.txtTomorrowKW
Me.HE8 = Me.txtTomorrowKW
(skip the rest of this, but goes on to Hour24)

ElseIf Me.HEvar = 7 Then
Me.HE1 = Me.txtTodayKW
Me.HE2 = Me.txtTodayKW
Me.HE3 = Me.txtTodayKW
Me.HE4 = Me.txtTodayKW
Me.HE5 = Me.txtTodayKW
Me.HE6 = Me.txtTodayKW
Me.HE7 = Me.txtTomorrowKW
Me.HE8 = Me.txtTomorrowKW
etc etc......

As you can see, basically have to have an IF for each hour.

Can anyone suggest some method of having it in a Module, and do something like:
(not even attempting code, just trying to give an example)

all HE's where the hour part of the field (HE6 - 6 is the hour part of the
field name) is < HEVar, set value to Me.txtTodayKW
All HE's where the hour part of the field is >= HEVar, set value to
Me.txtTomorrowKW
Click to expand...


I think this will do that for you.

For he = 1 To 24
If he < Me.HEvar Then
Me("HE" & he) = Me.txtTodayKW
Else
Me("HE" & he) = Me.txtTomorrowKW
End If
Next he
 
On Sun, 23 Apr 2006 15:30:38 -0500, Marshall Barton <[email protected]>
wrote:

Thanks, that works except for one thing, I can't leave the field!
This is continuous Forms, if that makes a difference.

It does do what I need, it fills in the various HE's as needed. Then, the
cursour goes to the beginning of the textboxfield rather than exiting and
doesn't allow me to leave the textboxField at all. For testing, I removed all
other BeforeUpdate, Enter, and all other code I had for the Exit Event, so that
the below code is ALL there is for this textbox. Still won't let me leave. (if
it matters, I used Exit rather than AfterUpdate because I insert a zero on
Enter. Because I insert it, AferUpdate doesn't fire).

One oddity; I copied and pasted the below, and noticed that "he" changed to
"HE". That normally happens when there is an object already with that name, but
I can't find any. However, I renamed "he" to something else, and had same
behaviour.

Thanks, Josh
 
Ok, I think I see the problem, I had code in the AfterUpdate Event, but moved it
to the Exit Event. That is probably the problem, I have to move it back to the
AfterUpdate Event. This was the last textbox on the record, so exiting takes
it to the next record.

Josh
 
RIght. I thought I posted that, but I don't see it so I
must have blown it somehow.

You will go to the next record from either event. If you do
not want it to fo that, it shouldn't be the last control in
the tab order or you need to set the focus to a different
control.

Note that setting a control's value in a VBA procedure will
not trigger the event sequence (i.e. AfterUpdate), but this
is not a significant drawback because you can just add a
line of code to call the AfterUpdate procedure directly.
 
On Sun, 23 Apr 2006 21:53:11 -0500, Marshall Barton <[email protected]>
wrote:

Thanks, moved back to AfterUpdate and handled everything there, so the code you
provided works fine.......

Thanks.
 
Back
Top