Code for form question

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I have a form with a lenghty If Then Else code that works perfectly however
I have to open the form and cycle through all of the records to update each
record. I'm checking conditions (several) to see if an order is on time,
late, not scheduled to run ect. There are several hundred orders per day. Is
there a way to update all of the records without opening the form or is
there a better way of doing this? The code is in the OnCurrent event.
Code;
If [DueShipTime] > [Dispatch Date Time] Then
[OnTime] = "On Time"
ElseIf [Sched Qty] = 0 Then
[OnTime] = "Not Scheduled"
ElseIf [Ship Qty] = 0 Then
[OnTime] = "Not Shipped"
ElseIf IsNull([Dispatch Date Time]) Then
[OnTime] = "No Dispatch Time"

Else: [OnTime] = "Late"
End If
 
well, i might use your If statement in a public function to return a value
to an Update query. then just run the Update query whenever you need to
update the OnTime field in your table's records. that way you don't have to
manually flip through the records in a form.

hth
 
Thanks Tina I'll work toward doing that and see if I can get it work.


tina said:
well, i might use your If statement in a public function to return a value
to an Update query. then just run the Update query whenever you need to
update the OnTime field in your table's records. that way you don't have
to
manually flip through the records in a form.

hth


Richard said:
I have a form with a lenghty If Then Else code that works perfectly however
I have to open the form and cycle through all of the records to update each
record. I'm checking conditions (several) to see if an order is on time,
late, not scheduled to run ect. There are several hundred orders per day. Is
there a way to update all of the records without opening the form or is
there a better way of doing this? The code is in the OnCurrent event.
Code;
If [DueShipTime] > [Dispatch Date Time] Then
[OnTime] = "On Time"
ElseIf [Sched Qty] = 0 Then
[OnTime] = "Not Scheduled"
ElseIf [Ship Qty] = 0 Then
[OnTime] = "Not Shipped"
ElseIf IsNull([Dispatch Date Time]) Then
[OnTime] = "No Dispatch Time"

Else: [OnTime] = "Late"
End If
Click to expand...
Click to expand...
 
Another way is to run an Update query. The If..then..else will need to be
changed to IIF() function. (See below)

Create a new form and add a button. In the OnClick event of the button paste
this code:

'*********
Dim strSQL As String

strSQL = "UPDATE Table3 SET Table3.OnTime = IIf([DueShipTime]>[Dispatch Date
Time],'On Time',IIf([Sched Qty]=0,'Not Scheduled',IIf([Ship Qty]=0,'Not
Shipped',IIf(IsNull([Dispatch Date Time]),'No Dispatch Time','Late'))));"

CurrentDb.Execute strSQL

'********

Change "Table3" to the name of your table (2 places).

The strSQL line should be one line.

When you press the button, the SQL runs updating the 'On Time' field.

You could also add a Where clause to limit the records changed by using
controls on the form.


I do think there might be a problem with your If...Then code (and the SQL).
What should the field "On Time" have in it if [DueShipTime]>[Dispatch Date
Time] but [Ship Qty]=0. Right now is will show 'On Time'. Shouldn't it be
'Not Shipped' ??


HTH
--

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Richard said:
Thanks Tina I'll work toward doing that and see if I can get it work.


tina said:
well, i might use your If statement in a public function to return a value
to an Update query. then just run the Update query whenever you need to
update the OnTime field in your table's records. that way you don't have
to
manually flip through the records in a form.

hth


Richard said:
I have a form with a lenghty If Then Else code that works perfectly however
I have to open the form and cycle through all of the records to update each
record. I'm checking conditions (several) to see if an order is on time,
late, not scheduled to run ect. There are several hundred orders per day. Is
there a way to update all of the records without opening the form or is
there a better way of doing this? The code is in the OnCurrent event.
Code;
If [DueShipTime] > [Dispatch Date Time] Then
[OnTime] = "On Time"
ElseIf [Sched Qty] = 0 Then
[OnTime] = "Not Scheduled"
ElseIf [Ship Qty] = 0 Then
[OnTime] = "Not Shipped"
ElseIf IsNull([Dispatch Date Time]) Then
[OnTime] = "No Dispatch Time"

Else: [OnTime] = "Late"
End If
Click to expand...
Click to expand...
Click to expand...
 
Back
Top