Changing RecordSource of subform

[Help.Chris]

Hi everyone,

Thanks for any help in advance.

I am trying to change the recordsource of a subform at runtime, i know this can be done but cannot get my syntax correct in my vba.

The line needs to be in the main form, as a search utility is held there.

The main form is called [Campaign Search Form]

The subform is called [Campaign Discounts Form]

I have then got two queries ready called "Temp1" and "Temp2".

I want to be able to switch between these.

Could some please help, and provide me the correct syntax for these form names.

Thanks

Chris

 

[Pat Hartman]

Change the recordsource in the Load event of the subform:

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
Dim strFilter As String

strFilter = "ERCID = " & Forms!ERCdetailLOB!txtERCID
strFilter = strFilter & " AND ERCGroupLOB = " & Forms!ERCdetailLOB!txtERCGroupLOB
strFilter = strFilter & " AND ERCDetailLOB = " & Forms!ERCdetailLOB!txtERCDetailLOB
strFilter = strFilter & " AND KindOfReinsuranceCd = " & Forms!ERCdetailLOB!txtKindOfReinsuranceCd

strSQL = "SELECT L.ERCID, L.ERCGroupLOB, L.ERCDetailLOB, L.KindOfReinsuranceCd, "
strSQL = strSQL & "L.Attachment, L.Limit, "
strSQL = strSQL & "L.Layer, L.CurrSubjectPremium, L.CurrGRCShare, L.CurrReinsuranceRate, "
strSQL = strSQL & "L.CurrCedingCommission, L.CurrExpectedAdjustmentPct, L.CurrLossRate, "
strSQL = strSQL & "L.CurrNetReinsPremium, L.CurrNetReinsLosses, L.CurrNetLossRatio, "
strSQL = strSQL & "L.CreatedBy, L.CreatedDt, L.UpdatedBy, "
strSQL = strSQL & "L.UpdatedDt FROM dbo_ERCLayer AS L WHERE "

strSQL = strSQL & strFilter
Me.RecordSource = strSQL
End Sub