open a form from button defining recordset

[gadjet]

Hi,
I have a database with several nearly identical tables and several identical forms, each form linked to a table via a qry to limit the recordset.

I call up each form from a front end form using a seperate button, I would like to use one common form to display the relevant table data from any single one of the different queries.

I would like to open the common form and define the forms recordset from the button code.

I've tried putting the query name in the [Filtername] part of the DoCmd.open form code but it doesn't work.

DoCmd.OpenForm stDocName, , queryName, stLinkCriteria

when the form opens it uses a

Set rs = Me.Recordset.Clone
rs.FindFirst "[dates] >= CDate('" & Date & "')"

and this fails saying that "Object variable or With block variable not set".

I just want one form to manage not multiple ones.

Any ideas anyone?

 

[dcb]

Something like this?
Paste into a module and give it a test

Public Sub sFormData(intOption As Integer)

Dim strRS As String ''strRS can be a Query or Table

DoCmd.OpenForm "frmTest" ''Change to your form

    Select Case intOption
    Case 1
        strRS = "Table1"
    Case 2
        strRS = "Table2"
    Case 3
        strRS = "qryMine"
    End Select
    
    Forms!frmTest.Form.RecordSource = strRS ''Change Form Name
    
End Sub

Public Sub sTest()
sFormData (1)

End Sub

 

[gadjet]

Something like this?
Paste into a module and give it a test

Public Sub sFormData(intOption As Integer)

Dim strRS As String ''strRS can be a Query or Table

DoCmd.OpenForm "frmTest" ''Change to your form

    Select Case intOption
    Case 1
        strRS = "Table1"
    Case 2
        strRS = "Table2"
    Case 3
        strRS = "qryMine"
    End Select
    
    Forms!frmTest.Form.RecordSource = strRS ''Change Form Name
    
End Sub

Public Sub sTest()
sFormData (1)

End Sub

Thanks for taking the time to reply, sometime early this morning I came across a post that suggested using a global variable setup in a module and pass the name of the query to be used as the recordsource for the form and, on opening of the form, set the recordsource using said variable.

It worked a treat. Solved

Thanks again.

Phil