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

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