Can't update RecordSource of subform

[nickweavers]

I have a combo box that I am using to select a CustomerID from a table called Customers. Once a value has been selected I would like to update the RecordSource property of a sub form called order_entry_frm so that it will filter Order records for that customer. I tried the following...

Private Sub cboCustomer_Change()
  strSQL = "SELECT OrderID, OrderDate, RequiredByDate, DatePaid " & _
  " FROM Orders_tbl " & _
  " WHERE CustomerID=" & Me!cboCustomer.Value & ";"
  Me!order_entry_frm.RecordSource = strSQL
  Me!order_entry_frm.Requery
End Sub
 

...but got a compiler debug message pointing at line " Me!order_entry_frm.RecordSource = strSQL" which said "Run-time error 438. Object does not support this property or method".

Is there another/better way of doing what I want?

TIA,
Nick.

[Rabbit]

I believe the correct syntax is Me.order_entry_frm.Form.RecordSource

[nickweavers]

Great, that fixed it.

Many thanks Rabbit.

[Rabbit]

Not a problem.

[NeoPa]

A better way to get the results you want would be to use the .Filter property instead, followed by a .requery.

[nickweavers]

Hi Neopa,

How does the Filter proprty work? Is it a way of just appending "criteria" to an existing query?

[NeoPa]

Absolutely.
A report or form object can be called from VBA code, and the fourth parameter of each is called 'WhereCondition'.

DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)
DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

Whatever the RecordSource is for the object, it will be further filtered by the 'WhereCondition' parameter.