Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How do I test for first record in subform? (A2

    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

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I test for first record in subform? (A2

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How do I test for first record in subform? (A2

    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.
    Charlotte

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How do I test for first record in subform? (A2

    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

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I test for first record in subform? (A2

    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

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How do I test for first record in subform? (A2

    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

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I test for first record in subform? (A2

    Hi Mark

    Thanks a lot

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •