How do I test for first record in subform? (A2k (9.0.3821) SR-1)

[Support4John]

How do I test for first record in subform?
A2k (9.0.3821) SR-1

I have the following code in the subform datasheet and if works OK, however I only want the code to fire when entering the first record in the subform.

How do I test for first record in a subform?

Private Sub lngFeeTypeCodeID_GotFocus()

If Me!lngFeeTypeCodeID = 0 Then
lngFeeTypeCodeID = Forms![frm_TOC_BP_ACD]![lngTypeID]
Me!lngFeeTypeCodeID.Dropdown
End If

End Sub

Any help would be appreciated.

Thanks, John

[MarkD]

There's probably more than one way to do this. One way is to test subform's RecordsetClone AbsolutePosition property. If using DAO this value is zero-based, meaning the first record in recordset has AbsolutePosition value of 0 (zero). Sample code for subform's On Current event demonstrates how you can test for this property:
<pre>Private Sub Form_Current()

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

rst.Bookmark = Me.Bookmark
If rst.AbsolutePosition = 0 Then 'first record
MsgBox "First record in recordset. " & _
"Absolute Position = " & rst.AbsolutePosition, _
vbInformation, "FIRST RECORD!"
Else
MsgBox "Not first record in recordset. " & _
"Absolute Position = " & rst.AbsolutePosition, _
vbInformation, "NOT FIRST RECORD!"
End If
Set rst = Nothing

End Sub</pre>


You can adapt this concept for your sub to test for first record in subform. For this to work correctly the subform must have specified sort order. Also note you have to first set RecordsetClone's bookmark equal to form's bookmark to get correct Absolute Position.

There may be some simpler way to do this.

HTH

[Support4John]

Hi Mark

I put in your code verbatim, sorted the table by autonumber field and getting:

run time error 3021 - "No current record" on

rst.Bookmark = Me.Bookmark

What am I missing?

Thanks, John

You can adapt this concept for your sub to test for first record in subform. For this to work correctly the subform must have specified sort order. Also note you have to first set RecordsetClone's bookmark equal to form's bookmark to get correct Absolute Position.

[charlotte]

John,

Where are you running the code? You did put it in the subform's module, right? You'll get an error if there are no records in the subform for that parent record.

[MarkD]

Sorry, did not take into account may be no records in subform!! (Tested with subform that always had at least one record.) Before trying to get Absolute Position, test to make sure there's at least one record in subform's recordset by checking RecordCount property. Here is modified code:
<pre>Private Sub Form_Current()

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

If rst.RecordCount > 0 Then
rst.Bookmark = Me.Bookmark
If rst.AbsolutePosition = 0 Then 'first record
MsgBox "First record in recordset. " & _
"Absolute Position = " & rst.AbsolutePosition, _
vbInformation, "FIRST RECORD!"
Else
MsgBox "Not first record in recordset. " & _
"Absolute Position = " & rst.AbsolutePosition, _
vbInformation, "NOT FIRST RECORD!"
End If
Else
MsgBox "Subform has no records.", vbExclamation, "NO RECORDS"
End If

Set rst = Nothing

End Sub</pre>

As noted, this code should be copied to SUBFORM's On Current event procedure to test how it works, not main form's!!

HTH

[Support4John]

Hi Mark & Charlotte

The following code works for me.

I probably did not explain myself very well

When I go to a Tab Control sub form, on the first record only I want to copy lngTypeID from the main form into subform lngFeeTypeCodeID then open sub form combo for lngFeeTypeCodeID and fire the lngFeeTypeCodeID AfterUpdate code.

It seems to be working, is there something that is going to come back and bite me?

Thanks for your help

This is in my sub form:

Private Sub lngFeeTypeCodeID_GotFocus()

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
If rst.RecordCount = 0 Then
lngFeeTypeCodeID = Forms![frm_TOC_BP_ACD]![lngTypeID]
Me!lngFeeTypeCodeID.Dropdown
Call lngFeeTypeCodeID_AfterUpdate
End If
Set rst = Nothing

End Sub

[MarkD]

If you mean this event procedure should take place only when adding first NEW record to subform (as opposed to when entering control where record already exists and it is first record on subform) then should work OK, only I would simplify code slightly to:
<pre>Private Sub lngFeeTypeCodeID_GotFocus()

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
If rst.RecordCount = 0 Then
With Me.lngFeeTypeCodeID
.Value = Me.Parent![lngTypeID]
.Dropdown
End With
lngFeeTypeCodeID_AfterUpdate
End If
Set rst = Nothing

End Sub</pre>

It's simpler to refer to main form using subform's Parent property than using the Forms! syntax. If you wanted event to be triggered anytime you added new record whether or not it was first record then you could test for subform's NewRecord property instead of RecordCount.

HTH

[Support4John]

Hi Mark

Thanks a lot

John