RecordSetClone and navigating subform

[sparkyrose]

Hi all,

I have a form and subform that shows when a particular vendor has certified compliance which must be done annually. In order to navigate among the various annual records in the subform I use a combo with the RecordSetClone property as follows:

Private Sub cboCertYear_AfterUpdate()

Dim rs As DAO.Recordset

    If Not IsNull(Me.cboCertYear) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        rs.FindFirst "[CertYear]=" & Me.cboCertYear
        If rs.NoMatch Then
            MsgBox "Network does not have a record for that year"

        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
    
End Sub

The issue I have is with the options that appear in the combo dropdown. I would like the options to be limited to whatever year has data in the certification table, i.e. if a vendor has only certified for 2012 there would be one option in the dropdown; if for 2012 and 2011. both would appear.

Currently it seems to show any year which any vendor has certified, leading to the MsgBox appearing if I select a year where there is no record for that particular vendor.

The combo Row Source references a query, that pulls based on the [Forms]![frmMainForm].[Network] field. Is this an issue with the Row Source, maybe? I tried

SELECT [qryMainSubForm].[CertYear] FROM qryMainSubForm WHERE [qryMainSubForm].[Network]=[Forms!].[frmMainForm].[Network]

but to no avail. I get a blank dropdown.

Any help greatly appreciated.

 

[jdraw]

I would put a

Debug.Print me.recordset
before your rs.Findfirst
to see what value is there.

 

[sparkyrose]

Thanks. I added that line in, and I got Runtime Error 13: Type mismatch.

Clearly I'm missing something here.

 

[jdraw]

Can you post exactly what you ran?

 

[sparkyrose]

Sure.

Private Sub cboCertYear_AfterUpdate()

Dim rs As DAO.Recordset

    If Not IsNull(Me.cboCertYear) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        Debug.Print Me.Recordset
        rs.FindFirst "[CertYear]=" & Me.cboCertYear
        If rs.NoMatch Then
            MsgBox "Network does not have a record for that year"

        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
    
End Sub

 

[jdraw]

Sorry try Debug.Print me.Recordsource

 

[sparkyrose]

No prob. Running that I get this in the Immediate Window:

Debug.Print Me.Recordsource
qryMainSubForm

That query is the source for the Combo's dropdown. I guess I still need to limit the query to what is returned for the specific Main Form Record.

Thanks!

 

[jdraw]

Yes I think so . I'm working on another issue at the moment and could not get the recordsource for the form.

In your case I didn't see how you were restricting the values of Vendor.

Good luck.