Hoe to Dynamically assign Record Source 2

[mini1969]

Hai,

I would like to know how can i dynamically assign the recordsource of a report or form.

1. from another forms VBA where I wnat to set the report/Forms Recordsource just befor opening?

2. or the only way is to do it whithin the forms / reports code

kindly help me!

Mini

 

[VBUser77]

I assume you can assign the record source from within any form. It will look something like this:

dim MYSQL as string



Docmd.OpenForm "FormName", ac design view
Forms.[FormName].Visible = False
Forms![FormName].RecordSource = MYSQL
Docmd.Close "FormName"

Docmd.OpenForm "FormName"

Hope it helps!

 

[hwkranger]

You can do it dynamically. I do it dynamically, see the following:

Private Function fOpenBalance()
Dim SQL As String
SQL = "exec usp_rtnBalanceSheet '" & Me![BRANCH] & "'"

'change the sql of the query that the form is based upon.


If fIsWindowOpen(wBalBranch) Then
Set Forms(wBalBranch).RecordSet = ADOFormRstSource(SQL)
Forms(wBalBranch).Refresh
DoCmd.OpenForm wBalBranch, acNormal
Else
DoCmd.OpenForm wBalBranch, acNormal
End If

End Function

where:

Public Function ADOFormRstSource(Str_FormSQL As String, Optional int_RecordLock As Integer = adLockReadOnly) As ADODB.RecordSet
On Error GoTo ADOFormRstSource_Err
Dim rst As New ADODB.RecordSet
rst.Open Str_FormSQL, DBCnn, adOpenKeyset, int_RecordLock
Set ADOFormRstSource = rst
Exit Function
ADOFormRstSource_Err:
MyErrorHandler ("ADOFORMRSTSOURCE")
End Function

Randall Vollen
National City Bank Corp.

 

[mini1969]

thank you VBuser 77 and hkwranger.
tek-tips the great!