Assign control source dynamically from a different recordset

Question

I have the following VBA code in my access.

Dim subform As Object
Dim formFilter As String

formFilter = "..."   'a SQL statement

Set subform = Me!my_subform.Form
subform.RecordSource = formFilter

subform.field1.ControlSource = "f1"
subform.field2.ControlSource = "f2"
subform.field3.ControlSource = "f3"

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT TOP 1 f4 FROM my_table " _
                           & "WHERE tableF1= '" & [f1] & "' AND tableF2 = '" & [f2] & "' " _
                           & "ORDER BY tableF5 DESC")
subform.field4.ControlSource = rs(0)

I have first bound my first 3 fields in subform to the fields of my record source. Then I need to bind the 4th field to a different recordset. This recordset has to refer to the first 2 fields of my subform.

However, I got a run-time error 2465. Access is not able to refer to the field [f1] and [f2] of my OpenRecordSet statement.

How should I fix this?

I use this form in a datasheet view. So I need to refer to not a single value of field1 and field2, but the entire columns of records have to be linked.

Thanks a lot.

You can't bind a form to two different recordsets. You should be able to bind a form to a single recordset that accesses two different tables using a table JOIN in the SQL Statement. But be aware that recordsets which use JOINS in their SQL are generally Read-Only. — HK1, Jan 16 '14 at 19:29
@HK1. Thanks. But I can't use JOIN because my field4 has more than 1 matching record. Using JOIN will give me duplicate field1, field2 and field3. That's why in my second recordset I need to select only the top 1 row. — got2nosth, Jan 17 '14 at 00:40
Rather than add the solution in the question you should post it as an answer. — Flexo, Jan 17 '14 at 08:39

score 0 · Accepted Answer · answered Jan 17 '14 at 10:59

(from an earlier edit to the question, since rolled back:)

Apparently the solution in this case was to use the following code in the On Load event handler for the subform instead of the main form

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT TOP 1 f4 FROM my_table " _
                           & "WHERE tableF1= '" & [f1] & "' AND tableF2 = '" & [f2] & "' " _
                           & "ORDER BY tableF5 DESC")
subform.field4.ControlSource = rs(0)

because the [f1] and [f2] controls were on the subform and therefore not visible from the Class Module code for the main form.

Assign control source dynamically from a different recordset

1 Answers1