Binding controls to recordset with join

[Lyn]

Hi,
I am opening a form in Continuous mode to list the records from a recordset
created in the calling form. The recordset object is declared as Public and
is set into the new form's Recordset property during the Open event.
According to the VBA Help file, setting the Recordset property may adjust
the RecordSource property accordingly.

If I set the RecordSource to blank in Design mode, it remains blank when the
form is opened even after the Recordset property is set. Worse, it results
in the form shrinking to display only one record at a time. You can scroll
the form to see the complete set of records, but only one at a time. This
was the subject of a recent posting -- no one was able to explain why this
was happening. However, I found that by populating RecordSource with the
name of the table used in the recordset, the full vertical length of the
form was restored and I was able to see all the records.

Since then, I have changed the query behind the recordset so that it now
contains a join of two tables -- the original table and a second table.
With RecordSource still set to the original table, I can display all the
columns in the recordset belonging to that table, but not those belonging to
the joined table.

The RecordSource property can only be set to a table, query or SQL
statement. I need to be able to set it to the recordset. I thought that
setting Recordset property might achieve that effectively (but apparently
not).

I have tried setting the controls displaying the joined table columns via
VBA in the Current event, but this sets the control in every row to the
value of the column in the first record only.

I hope that this makes sense so far. My question is: how can I display in
Continuous mode fields in a Public recordset resulting from a joined query
in another form? I will be happy if the solution is in the query's SQL, in
the form's properties or in VBA. Any help greatly appreciated.

--
Cheers,
Lyn.

[Larry Linson]

"Lyn" <lh******@ihug.com.au> wrote

The RecordSource property can only be
set to a table, query or SQL statement.
I need to be able to set it to the recordset.

Reconsider this statement. My guess is that you NEED to be able to bind the
form to particular records, and that the recordset you describe is ONE way
to obtain those records, but perhaps not the ONLY way. Then, consider how
you might rethink your design so that you select the same records as those
in the recordset in some other manner.

Larry Linson
Microsoft Access MVP

[Lyn]

"Larry Linson" <bo*****@localhost.not> wrote in message
news:1Mz3e.2341$Tm5.472@trnddc07...

"Lyn" <lh******@ihug.com.au> wrote

The RecordSource property can only be
set to a table, query or SQL statement.
I need to be able to set it to the recordset.

Reconsider this statement. My guess is that you NEED to be able to bind
the
form to particular records, and that the recordset you describe is ONE way
to obtain those records, but perhaps not the ONLY way. Then, consider how
you might rethink your design so that you select the same records as those
in the recordset in some other manner.

Larry Linson
Microsoft Access MVP

Hi Larry,
Thanks for your response. I take it that you are saying that binding an
imported recordset to a form cannot be done (despite what Help says about
the Recordset property, unless I have misread it).

I can't use Table in RecordSource because my fields come from two different
tables (this is where I am at now).

I can't use a stored query because the query is dynamically built in the
calling form based on user input.

I could use the SQL generated in the calling form and pass it to the form in
question (rather than the resulting recordset) via OpenArgs. This does
work, I have used it before. Is this the solution you were hinting at? The
only reservation I have is that it requires me to generate the same
recordset twice -- once in the calling form where I do some error checking
before calling the form in question, and then again in the new form so that
I can access the fields from both tables. If this is the only (or at least
the best) way to do it, then I will have to settle for that and take the
slight performance hit of having to access the database twice for the same
data. I just thought that there must be a more efficient way of doing this
(and maybe there is if I still haven't understood what you were hinting
at!).

Thanks again for the help.

--
Cheers,
Lyn.