Need help w/ error

[Tom]

I need some help with a RunTime Error.

Here's the scenario:
I use multiple layers of subforms. Each subform uses combo boxes which
values drive the value of the next underlying subform.

On a given level (subform), I cannot use duplicate values. If I would
enter a duplicate value accidentally, then the Form_Error function below is
called and I need to press escape.

The AfterUpdate function below ensures that (if no duplicates are entered),
the when selecting/entering a new record from e.g. ComboBox1, the values of
ComboBox2 are adjusted (simple example would be the relationship between
"States" and "Cities").

Here's my problem:

1. Without the AfterUpdate function
- I cannot enter duplicates (which is good), but I won't update the next
tier of combo box values (which is bad)

2. With the AfterUpdate function
- I do update the next tier of combo boxes (which is good), but I now get a
Runtime error (which is bad)

Using both functions, the RunTime error (2455) indicates the following:
"Run-Time Error 2455. You entered an expression that has an invalid
reference to the property form/report".


My question now is the following:
How can modify both or either function to accomodate that I cannot enter
dups (Data_Err 3022) but I also can call the refresh query
"Me.NameofSubform.Form.CboBoxControl.Requery" without getting the error
2455?


************************
Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then
MsgBox "This duplicate value cannot be added!" & vbCrLf & "Push ESC
key to cancel"
Response = acDataErrContinue
End If

End Sub
************************
Private Sub Division_AfterUpdate()

Me.SubdatasheetExpanded = True
DoEvents
Me.NameofSubform.Form.CboBoxControl.Requery

End Sub
************************

 

[tina]

"Run-Time Error 2455. You entered an expression that has an invalid

reference to the property form/report".

the error is telling you that your reference
Me.NameofSubform.Form.CboBoxControl.Requery.
is invalid.
you need to fix the reference. my first guess is that you're using the wrong
"NameofSubform". you need to reference the name of the subform *control*,
not the name of the subform object as it shows in the database window. to
get the subform control name: from the database window, open the form (even
if it's another subform) in design view. in design view, click on the
subform *once* to select it. in the Properties box, click the Other tab and
look at the Name property.
btw, normally you can refer to a control on a subform without using .Form in
the reference. usually you only need .Form when you're refering to a
property of the subform itself, such as .Filter. so the following should
work:

Me!NameofSubformCONTROL!CboBoxControl.Requery

hth

 

[Tom]

Tina:

Thanks for sharing the info w/ me....

Hmh, not sure if I entirely follow your suggestions.

Here's the structure of form:
- 1st tier (mainform) is called "frmBoards"
- frmBoards contains a subform called "frmDivisions"
- frmDivisions contains a subform called "frmSections"
- On frmDivisions, I have a combobox. On both the Data tab (Control Source)
and Other tab (Name), the value is "Division".
- On frmSections, I also have a combobox. Same as above, I find
"SectionCode" on both the Data and Other tabs.

So, I have modified the "Me!NameofSubformCONTROL!CboBoxControl.Requery" to
"Me!Division!SectionCode.Requery". This now throws "Error 451 - Property
let procedure not defined and property get did not return an object.".

Any additional pointers?

Tom

 

[tina]

in the first tier, you have the following "relationship":

mainform: frmBoards
subform: frmDivisions

to requery controls on frmDivisions, from code in frmBoards, use the
following:

Me!frmDivisionsSubformControlName!CboBoxControl.Requery

in the second tier, you have the following relationship:

"mainform": frmDivisions
subform: frmSections

to requery controls on frmSections, from code in frmDivisions, use the
following:

Me!frmSectionsSubformControlName!CboBoxControl.Requery

when you're working with objects *that are within the second tier only*,
forget about the first tier - it has no part in those references. it's like
having three generations: mother, daughter, granddaughter. when you're
describing the relationship between mother and daughter, granddaughter has
no place in that description. when between daughter and granddaughter,
mother has no place. it's only when you're describing the relationship
between mother and granddaughter, that you have to include a reference to
daughter - because she is "between" the two. and remember, when daughter has
a child, she is still daughter in the mother/daughter relationship *but* she
is now also a mother in a separate mother/daughter relationship of her own.

hth

 

[Tom]

Tina:

Thanks, but it still doesn't work for me. I guess I'm uncertain as to what
the difference is between "frmDivisionsSubformControlName" &
"CboBoxControl". Aren't both of them the same? Divsion and Division vs.
SectionCode and SectionCode?

BTW, I just noticed that it's you again... you helped me about 2 months ago
with a data inport database. So, thanks for reaching out to me again...
unfortunately, I'm still no further in this process.

Any more ideas?

 

[tina]

hey there, Tom - nice to talk to you again! your import db still working
okay for you , i hope.

i really don't know how to explain the reference syntax any more clearly.
which leads me to wonder if i misunderstood what you're trying to do. let me
state what i understand the task to be:

you have a form called frmBoards. it has a subform on it, called
frmDivisions. while you're "in" frmBoards, you want to requery a combo box
in the subform. also, the subform frmDivisions has its' own subform, called
frmSections. while you're "in" subform frmDivisions, you want to requery a
combo box in its' subform.

is that the task we're working on, or have i got the wrong picture?

 

[Tom]

Tina:

Yes, I think you got it right... frmBoards is the main form and does NOT
contain any combo boxes.

The 1st combo box resides on frmDivsions (equivalent to "Country").
frmDivisions has a subform called frmSections. On that subform, I have
also a combo box (equivalent to "State"). frmSections then also has
another subform called frmBillets. frmBillets (equivalent to "City") has
then the last tier of combo boxes.

I checked the "Data" and "Other" tab of the properties for each combo box.
Their values are the following for both (Control Source & Name): Division,
SectionCode, BilletCode. That is when I single-click on the combo box and
then right-click and selecte Properties.

For the actual form, I know there are 2 ways I can look at some properties.
1. Single-click on the form; right-click and select properties at the top
left corner (mouse icon changes to a "hand"), or
2. Click on the "square" in the top left corner. This puts a "black
square" in that box. The select Properties.

With the 2 steps to view and validate form properties plus the single option
for the actual combo box. I am not entirely certain where to look for what
to make sure all properties are referenced correctly.

BTW, yes I still use the import database a great deal... and it's working
awesome!!!

 

[tina]

good - that was a success story for us!
i decided a picture is worth a thousand words, so i emailed you an example
db showing the nested subforms you described, with the required syntax.

hth

 

[Tom]

Tina:

I didn't get the email... well, it might be because I recently changed my
work email address to reduce spam. The new email is
firstnameDOTlastname@companyDOTcom

In the email address, replace the following:
firstname = thomas
lastname = bock
company = sigmongroup
and naturally DOT with .


Could you please resent it again?! Thanks so much, I hope I can make sense
of this. Otherwise, not sure where to go from here.

 

[tina]

on its' way.

Tina:

I didn't get the email... well, it might be because I recently changed my
work email address to reduce spam. The new email is
firstnameDOTlastname@companyDOTcom

In the email address, replace the following:
firstname = thomas
lastname = bock
company = sigmongroup
and naturally DOT with .


Could you please resent it again?! Thanks so much, I hope I can make sense
of this. Otherwise, not sure where to go from here.

 

[Tom]

Tina:

Thanks... I have sent you an email w/ some more questions. Hope that was
okay?

Tom

 

[tina]

of course.

Tina:

Thanks... I have sent you an email w/ some more questions. Hope that was
okay?

Tom