RecordsetClone code creating an error

[daveh]

Hi there,

I have a form with a subform on it. The parent form is literally parents, and then the subform is their children that belong to that parent based on the ParentID. Pretty simple stuff.

I've created my own navigation buttons on the subform using some code I found online, and also a line of text that automatically fills in "Child x of y". I want to do the same for the parent form, so I've put the code, like in the child form, in the Form_Current event. When I add new children in the children subform, there are absolutely no problems, it works fine. However, when I try and add a parent, it comes up with an error within the subform Form_current event, possibly because when you add a new parent there is no child associated with it, and therefore there is no recordset to play with.

Private Sub Form_Current()
    
    Dim rst As DAO.Recordset
    Dim lngCount As Long

    Set rst = Me.RecordsetClone
    
    With rst
        .MoveFirst
        .MoveLast
        lngCount = .RecordCount
    End With
    
    If Me.CurrentRecord > lngCount Then
        lngCount = lngCount + 1
    End If
    
    Me.ChildCounter = "Child " & Me.CurrentRecord & " of " & lngCount
    
    If lngCount = 1 Then
        Me.First.Enabled = False
        Me.Previous.Enabled = False
        Me.Next.Enabled = False
        Me.Last.Enabled = False
    Else
        Me.First.Enabled = True
        Me.Previous.Enabled = True
        Me.Next.Enabled = True
        Me.Last.Enabled = True
    End If
    
    If Me.CurrentRecord = 1 Then
        Me.First.Enabled = False
        Me.Previous.Enabled = False
    Else
        Me.First.Enabled = True
        Me.Previous.Enabled = True
    End If
    
    If Me.CurrentRecord = lngCount Then
        Me.Next.Enabled = False
        Me.Last.Enabled = False
    Else
        Me.Next.Enabled = True
        Me.Last.Enabled = True
    End If
    
End Sub

This code exists in both forms on the Form_Current event. When I click on the 'add parent' button, it picks up the following lines with the subform when its running the code...

    With rst
        .MoveFirst
        .MoveLast
        lngCount = .RecordCount
    End With

It throws up "Run-time error '3201': No Current Record."

I'm sure this must be a relatively easy thing to do, but because I'm not clever enough to have written the code myself, I'm not sure how to approach the problem!

Thanks for your help in advance.

David.

 

[PHV]

You have to test the BOF and EOF properties of the Recordset object:
Set rst = Me.RecordsetClone
If rst.BOF Or rst.EOF Then
Me.ChildCounter = "Child 0 of 0"
Me.First.Enabled = False
Me.Previous.Enabled = False
Me.Next.Enabled = False
Me.Last.Enabled = False
Exit Sub
End If
With rst
.MoveFirst
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[daveh]

Hi

Thanks for your prompt response. I've put that into both forms' code and it doesn't seem to have fixed the problem. The problem appears (and I'm not sure) to be occurring within the child subform when the main parent form has had the "new customer" button clicked. It seems to be fine to add a new child to an existing parent, but when I try to add a new customer, it comes up with the error message specifically around the...

        .MoveFirst
        .MoveLast

... lines of code.

Thanks,
Dave.

 

[daveh]

Success!

I've been sitting here playing with code, and I worked out how to count the number of records, and if zero, to just set both to 1 and to ignore the rest of the code. So thanks to your help, I just added an additional condition in the if statement, and its now working fine.

    Set rst = Me.RecordsetClone
    
    Dim numberrecs As Integer
    numberrecs = rst.RecordCount

    If rst.BOF Or rst.EOF Or numberrecs = 0 Then
        Me.First.Enabled = False
        Me.Previous.Enabled = False
        Me.Next.Enabled = False
        Me.Last.Enabled = False
        Exit Sub
    End If

Thanks for all your help, I really appreciate it.

Dave.

 

[daveh]

One additional question...

I have got a combo box on the parent form that selects the parent, and then obviously selects the first of the parents' children in the subform because of the link between the two.

I have also created a combo box on the parent form that lists the children, and the based on the ParentID within the children table, automatically selects the correct parent based on which child they select. However, because all its doing is looking at the ParentID, if there is more than one child, and the one selected from the combo is not the 1st, it always shows the first (because the ChildID is not being passed through any way).

The problem is that I want it to select the correct parent (which its already doing) and then to select the correct child in the Child subform. But I can't seem to make it control the recordset of the subform, or do anything with it.

Any tips?

Dave.

 

[PHV]

Have a look at the Column property of the ComboBox object and at the RecordsetClone, FindFirst, NoMatch, Bookmark methods/properties of the Form/Recordset objects.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886