Access 2000 EOF problem - simple problem, simpler programmer!

[GolferJohn]

This must be a simple problem, but is driving me mad!! (I am a newbie, as you can guess).

I have a form 'Stocks' with an unbound textbox 'ShareBal' and two bound textboxes 'IOwn' and 'OpenOrder', filled by the following code which calculates values from my transactions in the 1 to many child table 'purchases'.

ShareBal = DSum("Bshares", "purchases", "Purchases!StockName = Forms!Rollbacks!StockName&quot - DSum("Sshares", "purchases", "Purchases!StockName = Forms!Rollbacks!StockName&quot

If ShareBal > 0 Then
Forms!Rollbacks.IOwn = "Y"
Else
Forms!Rollbacks.IOwn = "N"
End If
If DSum("BidOffer", "purchases", "Purchases!StockName = Forms!Rollbacks!StockName&quot > 0 Then
Forms!rollbacks.OpenOrder = "Y"
Else
Forms!rollbacks.OpenOrder = "N"
End If

the code correctly populates the textboxes, but when I get to the end of file it also populates the blank record, which I cannot move off, since it has nothing in the key field. I have tried nesting the above code in the following:

If Not StockName = "" Then

End If

and this allows me to get off the last record, because it puts nothing in the boxes at all.

I have also tried
If StockName <> &quot;&quot; Then....
If Not IsNull(StockName) Then ....

both of which compile, but do not populate the textboxes.

If Not (EOF) Then ....
will not compile.

What code will correctly populate my textboxes, but not the last 'dummy' record??

Thanks for any suggestions.

John

 

[TonyJollans]

Hi GolferJohn,

You want to use the NewRecord property of the form. Depending on how you want to reference your form this could be Me.NewRecord or Forms!Rollbacks.NewRecord. It is boolean and True means it is a new record and False means it isn't.

Enjoy,
Tony

 

[GolferJohn]

Hi. Thanks for the suggestion.

I'm Sorry, I guess I misled you.
My code above correctly calculates the share balance & populates the unbound 'ShareBal' textbox. The problem is preventing the If Then Else statements from populating the two bound textboxes, thereby initiating a new record (with a null key field)

If rollbacks.NewRecord Then
'If then statements'
End If

does not compile. I get an error message 'method not found'

If Me.NewRecord Then
'If then statements'
End If

works, but again at EOF the two bound textboxes get populated with 'N' and again I cannot navigate off the record without placing at least one character in the key field, and If I then delete the record, I am again left in a blank record I cannot navigate off.

I need to test for a null in the key field, indicating I have reached the dummy record at EOF, and prevent the if then statements from executing.

If Not StockName = &quot;&quot; Then
'If then statements'
End If

apparantly always prevents the if-then statements from executing.
If Not (StockName = &quot;&quot Then . . . .
does the same.

Thanks in advance for any suggestions.

John.

 

[GolferJohn]

Sorry - momentarily brain dead there, now got it working with

If Not (Me.NewRecord) Then
'If then statements'
End If

Thanks,
John