EOF

  • Thread starter Thread starter ROGER MACGREGOR
  • Start date Start date
R

ROGER MACGREGOR

I am having trouble with the EOF function. I am stepping
through 4 records and drawing information from them on a
form using vba.

If there are fewer than 4 records, the program hits end of
file and posts an error message.

The program uses the next command to move to the nexts
records. I have tried the following code - If EOF then
stop - to stop the program, but that syntax will not
compile. I am putting the if statement after the next
statement, and have tried a number of other syntax
possibilities, with no luck. Any suggestions?
 
ROGER MACGREGOR said:
I am having trouble with the EOF function. I am stepping
through 4 records and drawing information from them on a
form using vba.

If there are fewer than 4 records, the program hits end of
file and posts an error message.

The program uses the next command to move to the nexts
records. I have tried the following code - If EOF then
stop - to stop the program, but that syntax will not
compile. I am putting the if statement after the next
statement, and have tried a number of other syntax
possibilities, with no luck. Any suggestions?
Click to expand...

Please post the code. Are you processing a *file* using VBA I/O
statements, or are you stepping through a *recordset* using DAO or ADO
objects? It makes a big difference.
 
Here is the code. At present, it has no eof functions,
and crashes when it runs out of records on the yeparts form

Private Sub Command54_Click()

DoCmd.Close acForm, "YEORDERS", acSaveYes

DoCmd.OpenForm "YEPARTS", acNormal, "", "", , acNormal

DoCmd.OpenForm "YEORDERS", acFormDS, "", acEdit,
acNormal

DoCmd.OpenForm "YEORDERS", acFormDS, "", "[NUM]=
[Forms]![YEPARTS]![NUM]", acEdit, acNormal

DoCmd.FindRecord Forms!YEPARTS!NUM, acEntire, True, ,
True, , True
' find first
num on orders that matches parts

Forms!YEPARTS!BUYU = Forms!YEORDERS!BUYUNIT

Forms!YEPARTS!ENDINV = Forms!YEORDERS!ENDINV


'PROCESS 1ST BUY

Forms!YEPARTS![TOTALRECD 1] = Forms!YEORDERS!TOTALRECD

Forms!YEPARTS![PRICE 1] = Forms!YEORDERS!PRICE

If (Forms!YEPARTS!ENDINV <= Forms!YEPARTS![TOTALRECD
1]) Then
Forms!YEPARTS![QTYTOPRICE 1] = Forms!YEPARTS!
ENDINV
End If

If (Forms!YEPARTS!ENDINV > Forms!YEPARTS![TOTALRECD
1]) Then
Forms!YEPARTS![QTYTOPRICE 1] = Forms!YEPARTS!
[TOTALRECD 1]
End If

Forms!YEPARTS![TOTAL 1] = Forms!YEPARTS![QTYTOPRICE 1]
* Forms!YEPARTS![PRICE 1]

Forms!YEPARTS!TOTALPRICE = Forms!YEPARTS![TOTAL 1]


'PROCESS 2ND BUY


DoCmd.GoToRecord , "", acNext

Forms!YEPARTS![TOTALRECD 2] = Forms!YEORDERS!TOTALRECD

Forms!YEPARTS![EXCESS 2] = Forms!YEPARTS!ENDINV -
Forms!YEPARTS![QTYTOPRICE 1]

Forms!YEPARTS![PRICE 2] = Forms!YEORDERS!PRICE

If (Forms!YEPARTS![EXCESS 2] <= Forms!YEPARTS!
[TOTALRECD 2]) Then
Forms!YEPARTS![QTYTOPRICE 2] = Forms!YEPARTS!
[EXCESS 2]
End If

If (Forms!YEPARTS![EXCESS 2] > Forms!YEPARTS!
[TOTALRECD 2]) Then
Forms!YEPARTS![QTYTOPRICE 2] = Forms!YEPARTS!
[TOTALRECD 2]
End If

Forms!YEPARTS![TOTAL 2] = Forms!YEPARTS![QTYTOPRICE 2]
* Forms!YEPARTS![PRICE 2]

Forms!YEPARTS!TOTALPRICE = Forms!YEPARTS![TOTAL 1] +
Forms!YEPARTS![TOTAL 2]

'PROCESS 3RD BUY

DoCmd.GoToRecord , "", acNext ' go to 3rd
record on orders form

If (Forms!YEORDERS!NUM > Forms!YEPARTS!NUM) Then
DoCmd.GoToRecord , "", acNext
End If

Forms!YEPARTS![TOTALRECD 3] = Forms!YEORDERS!TOTALRECD

Forms!YEPARTS![EXCESS 3] = Forms!YEPARTS![EXCESS 2] -
Forms!YEPARTS![QTYTOPRICE 2]

Forms!YEPARTS![PRICE 3] = Forms!YEORDERS!PRICE

If (Forms!YEPARTS![EXCESS 3] <= Forms!YEPARTS!
[TOTALRECD 3]) Then
Forms!YEPARTS![QTYTOPRICE 3] = Forms!YEPARTS!
[EXCESS 3]
End If

If (Forms!YEPARTS![EXCESS 3] > Forms!YEPARTS!
[TOTALRECD 3]) Then
Forms!YEPARTS![QTYTOPRICE 3] = Forms!
YEPARTS![TOTALRECD 3]
End If

Forms!YEPARTS![TOTAL 3] = Forms!YEPARTS![QTYTOPRICE 3]
* Forms!YEPARTS![PRICE 3]

Forms!YEPARTS!TOTALPRICE = Forms!YEPARTS![TOTAL 1] +
Forms!YEPARTS![TOTAL 2] + Forms!YEPARTS![TOTAL 3]


'PROCESS 4TH BUY



DoCmd.GoToRecord , "", acNext ' go to 4th
record on orders form

If (Forms!YEORDERS!NUM > Forms!YEPARTS!NUM) Then
DoCmd.GoToRecord , "", acNext
End If

Forms!YEPARTS![TOTALRECD 4] = Forms!YEORDERS!TOTALRECD

Forms!YEPARTS![EXCESS 4] = Forms!YEPARTS![EXCESS 3] -
Forms!YEPARTS![QTYTOPRICE 3]

Forms!YEPARTS![PRICE 4] = Forms!YEORDERS!PRICE

If (Forms!YEPARTS![EXCESS 4] <= Forms!YEPARTS!
[TOTALRECD 4]) Then
Forms!YEPARTS![QTYTOPRICE 4] = Forms!
YEPARTS![EXCESS 4]
End If

If (Forms!YEPARTS![EXCESS 4] > Forms!YEPARTS!
[TOTALRECD 4]) Then
Forms!YEPARTS![QTYTOPRICE 4] = Forms!
YEPARTS![TOTALRECD 4]
End If

Forms!YEPARTS![TOTAL 4] = Forms!YEPARTS![QTYTOPRICE 4]
* Forms!YEPARTS![PRICE 4]


'SUMMARIZE ALL 4 BUYS

Forms!YEPARTS!TOTALQTY = Forms!YEPARTS![QTYTOPRICE 1]
+ Forms!YEPARTS![QTYTOPRICE 2] + Forms!YEPARTS![QTYTOPRICE
3] + Forms!YEPARTS![QTYTOPRICE 4]

Forms!YEPARTS!TOTALPRICE = Forms!YEPARTS![TOTAL 1] +
Forms!YEPARTS![TOTAL 2] + Forms!YEPARTS![TOTAL 3] + Forms!
YEPARTS![TOTAL 4]

End Sub
 
Here is the code. At present, it has no eof functions,
and crashes when it runs out of records on the yeparts form
Click to expand...

When it runs out of records on the YEPARTS form, or on the YEORDERS
form? I may be overlooking something, but I don't see any logic that
advances the YEPARTS form from record to record, only the YEORDERS form.
And elsewhere you refer to hitting EOF when there are fewer than 4
records, and that would seem to apply to YEORDERS.

I have more comments at the bottom of the quoted text.
Private Sub Command54_Click()

DoCmd.Close acForm, "YEORDERS", acSaveYes

DoCmd.OpenForm "YEPARTS", acNormal, "", "", , acNormal

DoCmd.OpenForm "YEORDERS", acFormDS, "", acEdit,
acNormal

DoCmd.OpenForm "YEORDERS", acFormDS, "", "[NUM]=
[Forms]![YEPARTS]![NUM]", acEdit, acNormal

DoCmd.FindRecord Forms!YEPARTS!NUM, acEntire, True, ,
True, , True
' find first
num on orders that matches parts

Forms!YEPARTS!BUYU = Forms!YEORDERS!BUYUNIT

Forms!YEPARTS!ENDINV = Forms!YEORDERS!ENDINV


'PROCESS 1ST BUY

Forms!YEPARTS![TOTALRECD 1] = Forms!YEORDERS!TOTALRECD

Forms!YEPARTS![PRICE 1] = Forms!YEORDERS!PRICE

If (Forms!YEPARTS!ENDINV <= Forms!YEPARTS![TOTALRECD
1]) Then
Forms!YEPARTS![QTYTOPRICE 1] = Forms!YEPARTS!
ENDINV
End If

If (Forms!YEPARTS!ENDINV > Forms!YEPARTS![TOTALRECD
1]) Then
Forms!YEPARTS![QTYTOPRICE 1] = Forms!YEPARTS!
[TOTALRECD 1]
End If

Forms!YEPARTS![TOTAL 1] = Forms!YEPARTS![QTYTOPRICE 1]
* Forms!YEPARTS![PRICE 1]

Forms!YEPARTS!TOTALPRICE = Forms!YEPARTS![TOTAL 1]


'PROCESS 2ND BUY


DoCmd.GoToRecord , "", acNext

Forms!YEPARTS![TOTALRECD 2] = Forms!YEORDERS!TOTALRECD

Forms!YEPARTS![EXCESS 2] = Forms!YEPARTS!ENDINV -
Forms!YEPARTS![QTYTOPRICE 1]

Forms!YEPARTS![PRICE 2] = Forms!YEORDERS!PRICE

If (Forms!YEPARTS![EXCESS 2] <= Forms!YEPARTS!
[TOTALRECD 2]) Then
Forms!YEPARTS![QTYTOPRICE 2] = Forms!YEPARTS!
[EXCESS 2]
End If

If (Forms!YEPARTS![EXCESS 2] > Forms!YEPARTS!
[TOTALRECD 2]) Then
Forms!YEPARTS![QTYTOPRICE 2] = Forms!YEPARTS!
[TOTALRECD 2]
End If

Forms!YEPARTS![TOTAL 2] = Forms!YEPARTS![QTYTOPRICE 2]
* Forms!YEPARTS![PRICE 2]

Forms!YEPARTS!TOTALPRICE = Forms!YEPARTS![TOTAL 1] +
Forms!YEPARTS![TOTAL 2]

'PROCESS 3RD BUY

DoCmd.GoToRecord , "", acNext ' go to 3rd
record on orders form

If (Forms!YEORDERS!NUM > Forms!YEPARTS!NUM) Then
DoCmd.GoToRecord , "", acNext
End If

Forms!YEPARTS![TOTALRECD 3] = Forms!YEORDERS!TOTALRECD

Forms!YEPARTS![EXCESS 3] = Forms!YEPARTS![EXCESS 2] -
Forms!YEPARTS![QTYTOPRICE 2]

Forms!YEPARTS![PRICE 3] = Forms!YEORDERS!PRICE

If (Forms!YEPARTS![EXCESS 3] <= Forms!YEPARTS!
[TOTALRECD 3]) Then
Forms!YEPARTS![QTYTOPRICE 3] = Forms!YEPARTS!
[EXCESS 3]
End If

If (Forms!YEPARTS![EXCESS 3] > Forms!YEPARTS!
[TOTALRECD 3]) Then
Forms!YEPARTS![QTYTOPRICE 3] = Forms!
YEPARTS![TOTALRECD 3]
End If

Forms!YEPARTS![TOTAL 3] = Forms!YEPARTS![QTYTOPRICE 3]
* Forms!YEPARTS![PRICE 3]

Forms!YEPARTS!TOTALPRICE = Forms!YEPARTS![TOTAL 1] +
Forms!YEPARTS![TOTAL 2] + Forms!YEPARTS![TOTAL 3]


'PROCESS 4TH BUY



DoCmd.GoToRecord , "", acNext ' go to 4th
record on orders form

If (Forms!YEORDERS!NUM > Forms!YEPARTS!NUM) Then
DoCmd.GoToRecord , "", acNext
End If

Forms!YEPARTS![TOTALRECD 4] = Forms!YEORDERS!TOTALRECD

Forms!YEPARTS![EXCESS 4] = Forms!YEPARTS![EXCESS 3] -
Forms!YEPARTS![QTYTOPRICE 3]

Forms!YEPARTS![PRICE 4] = Forms!YEORDERS!PRICE

If (Forms!YEPARTS![EXCESS 4] <= Forms!YEPARTS!
[TOTALRECD 4]) Then
Forms!YEPARTS![QTYTOPRICE 4] = Forms!
YEPARTS![EXCESS 4]
End If

If (Forms!YEPARTS![EXCESS 4] > Forms!YEPARTS!
[TOTALRECD 4]) Then
Forms!YEPARTS![QTYTOPRICE 4] = Forms!
YEPARTS![TOTALRECD 4]
End If

Forms!YEPARTS![TOTAL 4] = Forms!YEPARTS![QTYTOPRICE 4]
* Forms!YEPARTS![PRICE 4]


'SUMMARIZE ALL 4 BUYS

Forms!YEPARTS!TOTALQTY = Forms!YEPARTS![QTYTOPRICE 1]
+ Forms!YEPARTS![QTYTOPRICE 2] + Forms!YEPARTS![QTYTOPRICE
3] + Forms!YEPARTS![QTYTOPRICE 4]

Forms!YEPARTS!TOTALPRICE = Forms!YEPARTS![TOTAL 1] +
Forms!YEPARTS![TOTAL 2] + Forms!YEPARTS![TOTAL 3] + Forms!
YEPARTS![TOTAL 4]

End Sub
-----Original Message-----


Please post the code. Are you processing a *file* using VBA I/O
statements, or are you stepping through a *recordset* using DAO or
ADO objects? It makes a big difference.
Click to expand...
Click to expand...

First let me say that you are going about this the wrong way. I don't
know your application well enough to give specific recommendations, but
there is no need to open a form to step through the records in a table,
the way you are doing with your YEORDERS form. In many cases you don't
need to step through records at all, instead using queries or the domain
aggregate functions DSum, DCount, etc., to look up and summarize your
data. When you do need to step through records, it's simpler, cleaner,
and quicker to open a recordset on the table (or query) and use the
recordset to step through the records.

Also, how reliable is your assumption that there will always be exactly
4 orders per part, or at least no more than 4 orders per part? It looks
as though you have been engaged in what my colleague John Vinson calls
"spreadsheet thinking" -- storing information horizontally (e.g., fields
named [TOTALRECD 1], [TOTALRECD 2], [TOTALRECD 2], [TOTALRECD 4]) that
really should be stored vertically -- one field named [TOTALRECD]
occurring in multiple records. I gather that's the way it already is
stored in the table that underlies the form YEORDERS, so I have doubts
that you should even be doing this looping process. But as I said, I
don't know your application.

Setting all those reservations aside, if you want to continue with this
approach you can probably detect the "EOF" condition on form YEORDERS by
checking to see if you are on the "new record". So enclose the
processing of each record on that form with a test like

If Not Forms!YEORDERS.NewRecord Then

' ... process this record ...

' ... go to next record ...
DoCmd.GoToRecord , "", acNext

End If

That won't work if the form or its recordsource don't allow new records
to be added. If that's the case, and if you continue with this
approach, get back to me.
 
Back
Top