Trap no record in recordset 1

[RealKiwi]

I'm trying to fire a procedure from VBA without much success...

I have OrderItems (mainform), each OrderItem can have several Drops to customer (subform). When I navigate the OrderItems I want to add one default Drop per OrderItem.

My problem is I can't trap an empty recordset in the subform using any of these methods:

If me.recordset.recordcount = 0 then...
If me.Recordset.EOF then ...
If me.Recordset.BOF then ...

The subform is filtered when navigation buttons on the mainform are clicked. I guessed an OrderItem without a Drop record would result in an empty recordset, but that doesnt seem to be the case.

Any ideas?? I'm confused...

RK

 

[RoyVidar]

Hi!

You could try count on the recordsetclone:

[tt]me.recordsetclone.recordcount[/tt]

Though I think it would me more reliable to trap this from the main forms on current:

[tt]me!subformcontrolname.Form.RecordsetClone.RecordCount[/tt]

Roy-Vidar

Roy-Vidar

 

[RealKiwi]

You're a scholar and a gentleman. That did the job perfectly! 'Recordsetclone' must know how to count.. hehe

thanks
RK

 

[RealKiwi]

Oops I spoke too soon.

Seems neither recordset.recordcount, nor recordsetclone.recordcount can count a filtered group.

If I filter on the OrderItem before any record is in ItemDrop, I can't explain why, but both types of Recordcount omit the empty ItemDrop record and see the recordcount for the 'next' OrderItem.

eg. mainform record item#125 --> click next record button --> mainform current rec is #126 --> ItemDrop recordcount is 1 (relates to #127 not #126)

OrderItem ItemDrop
125 1000
125 2000
126 --
127 3000
128 --

This is the same sequence of events I was experiencing with Me!subformItemDrops.Form.Recordset.Recordcount

RK

 

[RoyVidar]

Thank you for the very kind words, and the star!

To the challenge at hand, I can't say I fully understand. I use similar in the main forms on current quite a lot, and have found it rather reliable.

That said, I've sometimes seen what seems to be timing issues when filtering, perhaps the count is performed before the filtered recordset is fully populated. Here's a thread where this seems to be the issue thread702-818368, with some workarounds. Might some of those apply?

You are using the main forms on current?

To use the absoluteposition, as in the referenced thread, perhaps something like this:

[tt]dim rs as dao.recordset
set rs=Me!subformItemDrops.Form.recordsetclone
if not rs.eof then
rs.movelast
end if
if rs.absoluteposition<0 then
' no records
end if
set rs=nothing[/tt]

Could perhaps use [tt]rs.recordcount[/tt] in stead of absoluteposition too, but in any case, test for eof, and if not, issue a movelast first.

Roy-Vidar

 

[RealKiwi]

Seems I inadvertently put the code in the subform On Current event - douh! moved it to the mainform and voila.

My sub to add the new record (when recordsetclone.recordcount = 0) is located in the subform:

Sub AddFirstDrop
dim dtInitialDropDate

dtInitialDropDate = fnSetDefaultDropDate

Me.Form.AllowAdditions = True

' add the new record
DoCmd.GoToRecord , , acNewRec
Me.dp_ITMID = vOrderItemID
Me.dp_ADATE = dtInitialDropDate
Me.dp_MNTH = MonthName(Month(dtInitialDropDate), True)
Me.dp_YEAR = Year(dtInitialDropDate)
Me.dp_AMT = vOrderItemQuantity

Me.Form.AllowAdditions = False
Me.Repaint

End sub

Would it be possible to call this subform event from the mainform? Or does this seem impractical...

RK

 

[RoyVidar]

Quite possible, practical, dunno;-)

1 - ensure the sub is declared as a public
[tt]Public Sub AddFirstDrop()[/tt]
2 - reference

A public sub in a subform should be available thru:

[tt]Me!subformItemDrops.Form.AddFirstDrop[/tt]

Roy-Vidar

 

[RealKiwi]

Thanks Roy, Ive implemented your suggestions and came to this roadblock:

The mainform is debugging at statement:

Me!subformItemDrops.Form.AddFirstDrop

with the error 2105: You can't go to the specified record.

This all seems rather odd as I've already trapped the empty recordset with Me!subformItemDrops.Form.recordsetclone.recordcount! It's just telling me what I already know. hehe

RK

 

[RealKiwi]

I've just retraced the steps of On Current and it is still doing some rather archaic counts.

Here's what happened:

ER: Order 1 -- M Items 1 -- M Drops

The Order record (#130) is set as follows:
ItemID# DropID#
10 1
11

1 When i open the OrderItem mainform
Item# 10 has 1 count /TRUE

2. I click MoveNextRec button on OrderItem mainform
Item# 11 has 1 count /WRONG!

3. I click MovePrevRec button on OrderItem mainform
Item# 10 has 0 count /WRONG!

4. I click MoveNextRec button on OrderItem mainform
Item# 11 has 1 count /WRONG!

It appears the recordcount statement is only counting on Item#10, however I did navigate back to Item#10 after going to Item#11 and the count was 0! Go figure.

I'm wondering does a recordsetclone reset after setting a filter??

RK

 

[RealKiwi]

I've restructured the code, given the prior outcome. My filters were part of the MovePrev, MoveNext buttons code. I'm now applying the Filters in the On Current event, immediately before the subform recordcount, with success.

I'll now go back to AddFirstDrop procedure and see if I can get past the error msg.

AAT