Form Event Order Confusion (Problems) When Using Record Source Property

[Don]

Currently my Access DB has a form whose 'Record Source' property is set to a
query. The query is pretty simply in that selects all fields and all
records from a table with personnel data then orders them by last name then
first name. The SQL view of the query is:

SELECT tblPrimary.*
FROM tblPrimary
ORDER BY tblPrimary.[Last Name], tblPrimary.[First Name];

On the form are numerous subforms which show data from other tables. The
data in these tables is linked to the individual's record in tblPrimary
using a field called LinkID which is nothing more than randomly generated,
unique numerical identifier for each individual. For a variety of reasons,
I want to move the 'Record Source' selection into the event code of the
form. So I inserted the following into the Form_Open event:

Dim strSQL As String
strSQL = "SELECT tblPrimary.* " _
& "FROM tblPrimary " _
& "ORDER BY ORDER BY tblPrimary.[Last Name], tblPrimary.[First Name]"
Me.RecordSource = strSQL

However, now when I open the form I get numerous message boxes asking for a
value for LinkID.

Best I can figure, the subforms are being processed prior to the Form_Open
event so LinkID is "undefined" and therefore all the message boxes
requesting the value for LinkID. From this I am concluding that when the
'Record Source' is entered as a property of the form it is somehow processed
before subforms. However, if the 'Record Source' for a form is set
programmatically, subforms are processed first.

So, do I need to change the fundamental structure of my form? Or is there
some way to manipulate the order the form is processed? With regard to the
latter, how does one get around the fact that according to the
documentation:

When you first open a form, the following events occur in this order:
Open ? Load ? Resize ? Activate ? Current

Any solutions, observations, comments, etc will be greatly appreciated!

Thanks!

Don

 

[Rick Brandt]

Currently my Access DB has a form whose 'Record Source' property is
set to a query. The query is pretty simply in that selects all
fields and all records from a table with personnel data then orders
them by last name then first name. The SQL view of the query is:

SELECT tblPrimary.*
FROM tblPrimary
ORDER BY tblPrimary.[Last Name], tblPrimary.[First Name];

On the form are numerous subforms which show data from other tables. The data
in these tables is linked to the individual's record in
tblPrimary using a field called LinkID which is nothing more than
randomly generated, unique numerical identifier for each individual. For a
variety of reasons, I want to move the 'Record Source'
selection into the event code of the form. So I inserted the
following into the Form_Open event:
Dim strSQL As String
strSQL = "SELECT tblPrimary.* " _
& "FROM tblPrimary " _
& "ORDER BY ORDER BY tblPrimary.[Last Name], tblPrimary.[First
Name]" Me.RecordSource = strSQL

However, now when I open the form I get numerous message boxes asking
for a value for LinkID.

Best I can figure, the subforms are being processed prior to the
Form_Open event so LinkID is "undefined" and therefore all the
message boxes requesting the value for LinkID. From this I am
concluding that when the 'Record Source' is entered as a property of
the form it is somehow processed before subforms. However, if the
'Record Source' for a form is set programmatically, subforms are
processed first.
So, do I need to change the fundamental structure of my form? Or is
there some way to manipulate the order the form is processed? With
regard to the latter, how does one get around the fact that according
to the documentation:

When you first open a form, the following events occur in this order:
Open ? Load ? Resize ? Activate ? Current

Any solutions, observations, comments, etc will be greatly
appreciated!

The subforms alway "open" first. Not sure why. Is there a reason you want to
assign the RecordSource in the Open event instead of just setting it in design
view?

 

[Albert D.Kallal]

Generally, the on-open event is for testing, and also has a cancel event for
the form.

Also, note that you can NOT modify the contents of controls in the on-open
event (you can read values however).

Given the above, the on-open event is quite early of a event.

However, I have OFTEN set the data source of a form like you. However, I
think it is quite obviously that if the main form does not have a data
source set, then the sub-form is going to complain. The simply solution
would be to set the sub-form data source AFTER you set the main forms data
source.

me.MySubFormContorl.Form.ReocrdSource = "your subforms sql goes here"

The above should thus clear up the errors messages etc. You *might* have to
also set the master/child links in code..but try the above first.

 

[Don]

Currently my Access DB has a form whose 'Record Source' property is
set to a query. The query is pretty simply in that selects all
fields and all records from a table with personnel data then orders
them by last name then first name. The SQL view of the query is:

SELECT tblPrimary.*
FROM tblPrimary
ORDER BY tblPrimary.[Last Name], tblPrimary.[First Name];

On the form are numerous subforms which show data from other tables. The
data in these tables is linked to the individual's record in
tblPrimary using a field called LinkID which is nothing more than
randomly generated, unique numerical identifier for each individual. For
a variety of reasons, I want to move the 'Record Source'
selection into the event code of the form. So I inserted the
following into the Form_Open event:
Dim strSQL As String
strSQL = "SELECT tblPrimary.* " _
& "FROM tblPrimary " _
& "ORDER BY ORDER BY tblPrimary.[Last Name], tblPrimary.[First
Name]" Me.RecordSource = strSQL

However, now when I open the form I get numerous message boxes asking
for a value for LinkID.

Best I can figure, the subforms are being processed prior to the
Form_Open event so LinkID is "undefined" and therefore all the
message boxes requesting the value for LinkID. From this I am
concluding that when the 'Record Source' is entered as a property of
the form it is somehow processed before subforms. However, if the
'Record Source' for a form is set programmatically, subforms are
processed first.
So, do I need to change the fundamental structure of my form? Or is
there some way to manipulate the order the form is processed? With
regard to the latter, how does one get around the fact that according
to the documentation:

When you first open a form, the following events occur in this order:
Open ? Load ? Resize ? Activate ? Current

Any solutions, observations, comments, etc will be greatly
appreciated!

The subforms alway "open" first. Not sure why. Is there a reason you
want to assign the RecordSource in the Open event instead of just setting
it in design view?

Rick,

Thanks for confirming my suspicions! As for "why assign in the Open event",
I have found it is a bit easier on my brain cell to have everything defined
in the code. That way I just have to look at the form and the code and not
have to look through the properties sheet. Maybe it is time to get a bigger
brain cell!

For this situation, I guess I will work with the design view settings.

Thanks!

Don

 

[Guest]

requesting the value for LinkID. From this I am concluding that when

'Record Source' is entered as a property of the form it is somehow
processed before subforms.

When a 'Record Source' is entered as a property of the form, it
is processed at Design Time - information about the (table) is
retrieved, object properties are created to match the recordset
fields, type matching is done etc, and information is saved with
the form when you save the form.

If you set the recordsource at runtime, some of this takes place
at runtime - you never get object properties to match the recordset
field properties - but that is more than balanced by the fact that you
can avoid querying your database until after you have decided which
records you want, which is particularly important with subforms
and with union queries.

(david)

Currently my Access DB has a form whose 'Record Source' property is set to a
query. The query is pretty simply in that selects all fields and all
records from a table with personnel data then orders them by last name then
first name. The SQL view of the query is:

SELECT tblPrimary.*
FROM tblPrimary
ORDER BY tblPrimary.[Last Name], tblPrimary.[First Name];

On the form are numerous subforms which show data from other tables. The
data in these tables is linked to the individual's record in tblPrimary
using a field called LinkID which is nothing more than randomly generated,
unique numerical identifier for each individual. For a variety of reasons,
I want to move the 'Record Source' selection into the event code of the
form. So I inserted the following into the Form_Open event:

Dim strSQL As String
strSQL = "SELECT tblPrimary.* " _
& "FROM tblPrimary " _
& "ORDER BY ORDER BY tblPrimary.[Last Name], tblPrimary.[First Name]"
Me.RecordSource = strSQL

However, now when I open the form I get numerous message boxes asking for a
value for LinkID.

Best I can figure, the subforms are being processed prior to the Form_Open
event so LinkID is "undefined" and therefore all the message boxes
requesting the value for LinkID. From this I am concluding that when the
'Record Source' is entered as a property of the form it is somehow processed
before subforms. However, if the 'Record Source' for a form is set
programmatically, subforms are processed first.

So, do I need to change the fundamental structure of my form? Or is there
some way to manipulate the order the form is processed? With regard to the
latter, how does one get around the fact that according to the
documentation:

When you first open a form, the following events occur in this order:
Open ? Load ? Resize ? Activate ? Current

Any solutions, observations, comments, etc will be greatly appreciated!

Thanks!

Don