Sequence of forms opening Or There must be a better way ...

[Guest]

Hello, All.

First let me say: I've gotten lots of good information in these discussion
groups. I've been working with Access for all of 3 months and in general it
seems that everybody follows a similar learning curve. So lots of my
questions have been addressed. The responders are patient beyond belief from
what I've read. So thanks in advance.

Question is: I have a main form with 2 subforms (both always present on a
tab control). It is not necessarily the case that there is any data to
populate the subfoms; therefore the SQL is:
tableA as A left join tableB as B on A.ticketID = B.ticketID
(for both subforms). This SQL works fine when executing from SQL View.

However, when I try to open the main form, it seems that (at least one of)
the subforms opens before the main form (I have debug.print "FormName_Open at
" & Now statements in the form open procs to trace the sequence). This causes
some problems, since the subforms depend on the value of TicketID in the main
form to determine which records to select. Since the recordset for the main
form hasn't opened yet, VBA complains that the ADO Recordset object is
closed, so I can't check for a populated master recordset before spending
time trying to retrieve records that I shouldn't be retrieving.

Any suggestions for 1) a cleaner way of coding this that avoids the
dependency? Or 2) how to design this better?

TIA

 

[Rick Brandt]

Hello, All.

First let me say: I've gotten lots of good information in these
discussion groups. I've been working with Access for all of 3 months
and in general it seems that everybody follows a similar learning
curve. So lots of my questions have been addressed. The responders
are patient beyond belief from what I've read. So thanks in advance.

Question is: I have a main form with 2 subforms (both always present
on a tab control). It is not necessarily the case that there is any
data to populate the subfoms; therefore the SQL is:
tableA as A left join tableB as B on A.ticketID = B.ticketID
(for both subforms). This SQL works fine when executing from SQL View.

However, when I try to open the main form, it seems that (at least
one of) the subforms opens before the main form (I have debug.print
"FormName_Open at " & Now statements in the form open procs to trace
the sequence). This causes some problems, since the subforms depend
on the value of TicketID in the main form to determine which records
to select. Since the recordset for the main form hasn't opened yet,
VBA complains that the ADO Recordset object is closed, so I can't
check for a populated master recordset before spending time trying to
retrieve records that I shouldn't be retrieving.

Any suggestions for 1) a cleaner way of coding this that avoids the
dependency? Or 2) how to design this better?

TIA

There should be no reason for the queries used for the subforms to include
tableA in them. The linking should be done using the MasterLink and
ChildLink properties of the subform control.

Subforms _do_ open before the parent form (as counter-intuitive as that
might sound).

 

[Guest]

Thanks, Rick. That was exactly the insight I needed. Having C/Motif in my
background is not helping me here. My inclination is to dive for code. Also,
iin my view of the world, if you are joining information in two tables, you
write a join.

So my approach would have been OK if I had to use event procedures to do
everything- fill a recordset, populate the controls using an OnCurrent
procedure. But Access will do this for me.

I just needed that understanding of the link fields and how they differ from
Join fields.

Once again, thanks much!