Combobox filter for a subform - recordsource problem

[Sam.Metcalfe]

Hi, I have limited knowledge of the code for Access so go easy on me.

I have a main form [Form1] with a combo box [cboFSE] which i want to use to filter the sub form [F2], the sub form is taking the data from a query [TradeUpcomingPeriodLYAllAccDetails], the combobox is taking data from an embedded query.

I've used the below code for the same use before except the sub form was taking data from a table. Now that i have changed it to fit my new data etc it keeps returning an error with recordsource, I'm completely stuck and no idea what to do to fix it.

Thanks in advance for any help

Sub setfilter()




Dim F As String

F = "select * from TradeUpcomingPeriodLYAllAccDetails"
F = F & " where FSE = '" & cboFSE & "'"

Form_F2.RecordSource = F

End Sub


Private Sub cboFSE_afterUpdate()

'Call subroutine to set filter based on selected FSE
setfilter

End Sub






Private Sub Form_Open(Cancel As Integer)

'Call subroutine to set filter based on selected FSE
setfilter

End Sub

[June7]

What is the error?

Main form is unbound?

Post the SQL of the query object and the combobox RowSource.

FSE is a text field?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Sam.Metcalfe]

Thank you for replying

I'm not sure what the error is, when I go to open the form the pop up says 'Run-time error 424: Object required', when i debug it highlights 'Form_F2.RecordSource = F'.

How would i check if the main form is unbound?

The SQL for the query is:
SELECT TradeTeam_MasterAcc_MthlyBuild.AccTrim, TradeTeam_UpcomingPeriodLY_MthlyBuild.ShipTo, TradeTeam_UpcomingPeriodLY_MthlyBuild.Acc, TradeTeam_MasterAcc_MthlyBuild.AccName, TradeTeam_MasterAcc_MthlyBuild.FSE, TradeTeam_MasterAcc_MthlyBuild.AQSScore, TradeTeam_UpcomingPeriodLY_MthlyBuild.[Week?], TradeTeam_UpcomingPeriodLY_MthlyBuild.Prod, TradeTeam_UpcomingPeriodLY_MthlyBuild.Sales, TradeTeam_UpcomingPeriodLY_MthlyBuild.Qty, TradeTeamAccListNotes_210218.Notes, TradeTeam_MasterAcc_MthlyBuild.CorpAgreeNum, TradeTeam_MasterAcc_MthlyBuild.DiscountPolicy, TradeTeam_MasterAcc_MthlyBuild.PriceList
FROM (TradeTeam_MasterAcc_MthlyBuild LEFT JOIN TradeTeam_UpcomingPeriodLY_MthlyBuild ON TradeTeam_MasterAcc_MthlyBuild.AccTrim = TradeTeam_UpcomingPeriodLY_MthlyBuild.Acc) INNER JOIN TradeTeamAccListNotes_210218 ON TradeTeam_MasterAcc_MthlyBuild.AccTrim = TradeTeamAccListNotes_210218.Acc;

combobox RowSource is:
SELECT TblFSE.FSEName FROM TradeUpcomingPeriodLYAllAccDetails INNER JOIN TblFSE ON TradeUpcomingPeriodLYAllAccDetails.FSE = TblFSE.FSEName GROUP BY TblFSE.FSEName, TradeUpcomingPeriodLYAllAccDetails.FSE HAVING (((TradeUpcomingPeriodLYAllAccDetails.FSE) Is Not Null));

FSE is a text field

Hope some of that helps/makes sense

[Minty]

Is your sub form actually called Form_F2 or F2

[Sam.Metcalfe]

Is your sub form actually called Form_F2 or F2

Its called F2

[Minty]

Then that will be why this

Form_F2.RecordSource = F

Doesn't work.

Your sub form needs to be referred to as Me!Subform1.Form.RecordSource where Subform1 is the name of the CONTAINER that holds the subform.
See http://access.mvps.org/access/forms/frm0031.htm for more examples.

[Sam.Metcalfe]

Solved! Thank you