combobox and subform problem

dbfromnewjersey
dbfromnewjersey used Ask the Experts™
on
I have a combobox on a main form.  
The main form does not have a record source.
The record source of the combobox is a table.

I have a subform on the main form. The subform's record source
is a table (different from the one tied to the combobox).
I have a textbox in the header section of the subform.
I want to set its value to whatever the combobox's value is.
If the combobox value changes, I want the textbox value on the subform
to change.

Below is what I already have under the After Update event of the combobox.
What is going on is when a combobox selection is made, the table tied
to the subform is cleared and then populated with data related to whatever
the combobox selection is.  


Private Sub MyComboBox_AfterUpdate()

With DoCmd
   .SetWarnings False
   .Hourglass True
   .OpenQuery "DeleteTemporaryTable"
   .OpenQuery "PopulateTemporaryTable"
   .SetWarnings True
   .Hourglass False
End With
   
   Me!MySubform.Form.Requery


Adding this code to the AferUpdate event (I've tried placing it in different
sections of the code) is causing a 440 run-time error:

 Me.MySubform.Form.MyTextbox = Me.MyComboBox

What's the problem?   Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Error 440 sounds really odd in this situation.

Are you sure that MySubform in
Me.MySubform.Form.MyTextbox = Me.MyComboBox

is actually the name of the subform control on the main form.  The name of the subform itself is irrelevant to the command.
Commented:
Based on your givens, the code looks solid to me.  And it doesn't matter where you put the statement. Looking for esoteric explanations like myTextbox isn't really a textbox.

do these give the same error:
Me.MyComboBox = Me.MyComboBox
Me.MySubform.Form.MyTextbox = Me.MySubform.Form.MyTextbox
Me.MySubform.Form.MyTextbox.caption = Me.MyComboBox

Author

Commented:
OK, here's another thing that's happening.  I'll go step by step in plain English.

I open the form.  the combobox as well as the subform will already be properly populated with records.

I select a different value in the combobox.  I get a pop-up window: "Run-time error '3167' Record is deleted"

At that point, all records/textboxes, etc in the subform display: #Deleted

I click 'End' in the pop-up window. That closes the pop-up window. Everything in the subform still displays as #Deleted.

I change the combobox value.  The subform is properly populated with records.

I change the combobox value again.  I get the '3167' error message again and everything displays as #Deleted.

I change the combobox value again.  The subform is properly populated.

So there's a pattern.  Every other time I change the combobox value, I get the error message.
This is with me having the following as the very last line of code in the combobox's After Update event:

 Me.MySubform.Form.MyTextBox = Me.MyComboBox

Author

Commented:
Don't ask me why because I'm an idiot but I got the thing to work by coding the following line twice:

Me!MySubform.Form.Requery
------------------------------------------------------------------------------------------
So, the entire After Update Event looks like this:

Private Sub MyComboBox_AfterUpdate()

With DoCmd
   .SetWarnings False
   .Hourglass True
   .OpenQuery "DeleteTemporaryTable"
   .OpenQuery "PopulateTemporaryTable"
   .SetWarnings True
   .Hourglass False
End With
   
   Me!MySubform.Form.Requery
   Me.MySubform.Form.MyTextbox = Me.MyComboBox
   Me!MySubform.Form.Requery

As long as I require twice, I don't get any errors.  Weird.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial