VBA Syntax for subform record source change

[esn]

Hey everybody,

First off, I apologize in advance for the simplicity of this
question. I know next to nothing about VBA.

I've got a form ("Mammal ID Manual") with a subform ("Mammal ID Manual
Subform") and I want to force the subform to load after the main form
has loaded to avoid error messages. The record source for the subform
includes values from the main form as criteria in a pretty complex
way, so loading the subform first doesn't really make any sense.
Searching around I've found suggestions to use the on load event in
the main form to change the subform's record source. So this is what
I tried:

Private Sub Form_Load()
Forms![Mammal ID Manual]![Mammal ID Manual Subform].RecordSource =
"SM - Mammal ID - 1 Mark Match"
End Sub

"SM - Mammal ID - 1 Mark Match" is the name of the query that should
be the record source for the subform. I cleared the record source
property on the subform, then opened the main form and I get a runtime
error stating that the object doesn't support this property or
method. Where did I screw up?

 

[David-W-Fenton]

m:

I've got a form ("Mammal ID Manual") with a subform ("Mammal ID
Manual Subform") and I want to force the subform to load after the
main form has loaded to avoid error messages. The record source
for the subform includes values from the main form as criteria in
a pretty complex way, so loading the subform first doesn't really
make any sense. Searching around I've found suggestions to use the
on load event in the main form to change the subform's record
source.

Why aren't you using the LinkChild/LinkMaster properties to filter
the child form?

 

[tina]

your problem may be in the reference you're using. the name of the subform
is

Mammal ID Manual Subform

i'm assuming that's the name of the form object as shown in the database
window. okay, but what is the name of the *subform control* within the
mainform? it may be the same as the subform's form object name, or it may be
different. in case you're not understanding me: a subform is a form just
like any other. when you place one form inside another form, the "inside"
form is held in a "container" control, which is a control similar to a
textbox control, or a combobox control, etc. it has a name, and certain
properties that may be manipulated. in your reference in VBA, you need to
use the name of the "container" control. to get that name, open the mainform
in design view, and click ONCE on the subform inside the mainform. in the
Properties box, click on the Other tab, and look at the Name property.
that's the name of the subform control. use that name in your reference in
code, as

Me!NameOfTheSubformControl.Form.RecordSource = "SM - Mammal ID - 1 Mark
Match"

the string value (name of query) should all be on one line, not line-wrapped
as above. and btw, suggest you get rid of the spaces in your object names.
something like SM_MammalID_1MarkMatch would be less problematic.

hth
tina

 

[esn]

your problem may be in the reference you're using.

You got it exactly - I forgot to make sure the control name matched
the object name. Thanks a lot for your help!

 

[esn]

Why aren't you using the LinkChild/LinkMaster properties to filter
the child form?

It's a complicated situation, but there are no fields that are exact
matches. The main form shows a record that couldn't be matched to any
other records (in the same table), but should have matches based on an
"Individual ID" field. This is biological data that includes a
"recapture" field, so anything where recap=yes should have a match
earlier (chronologically) in the data table. We use two marks in the
field to identify an individual, and when a record appears where
recap=yes but no previous record matches the 2 mark fields exactly, I
need to figure out why. There are a variety of reasons this can
happen, but typos during data entry and errors when recording the data
are most common. This is where the subquery comes in - it uses the
mark fields in the main form as criteria to find records that are
close, but not exact, matches. There would probably be a way to
rearrange all this to use the master/child properties instead, but I
could never figure out a simple way to do it, and given how long it
took just to pull the form together I couldn't invest the time.

 

[tina]

you're welcome

and btw, the subform's form object name and the name of the subform
"container" control don't *have* to be the same. i personally prefer to name
my container control differently than the subform's form name; makes it
easier for me to keep clear on just what i'm working with.

 

[David-W-Fenton]

:

It's a complicated situation, but there are no fields that are
exact matches.

You don't need exact matches. Any valid Access expression can be
used for a match. The only exception to that is if you need to match
more than one set of criteria.

The main form shows a record that couldn't be matched to any
other records (in the same table), but should have matches based
on an "Individual ID" field. This is biological data that
includes a "recapture" field, so anything where recap=yes should
have a match earlier (chronologically) in the data table. We use
two marks in the field to identify an individual, and when a
record appears where recap=yes but no previous record matches the
2 mark fields exactly, I need to figure out why. There are a
variety of reasons this can happen, but typos during data entry
and errors when recording the data are most common. This is where
the subquery comes in - it uses the mark fields in the main form
as criteria to find records that are close, but not exact,
matches. There would probably be a way to rearrange all this to
use the master/child properties instead, but I could never figure
out a simple way to do it, and given how long it took just to pull
the form together I couldn't invest the time.

If you're right that there's no alternative, then there are three
basic ways to do it:

1. hide the subform and reveal it only in the OnCurrent event of the
parent form.

2. assign the recordsource of the subform only in the parent form's
OnCurrent event.

3. don't load the subform until the parent form's OnCurrent event.

Whether or not you need to do this only once when the form loads, or
on each OnCurrent I don't know.