requerying subform (container) in 2007 vs. 2003

[arpeggione]

Hi all: From a ComboBox "afterupdate" event in a form, I requery a subform (in a container), based upon the ID of the record the user chooses (me.txtUserADID). This worked fine in Access 2003. The query - at the query level also works fine in 2007 (if I type in the ID) - either in the query or when asked. But...I can't get the subform to display the requeried data in 2007 or 2010. Any ideas? Code below:

Thank you!
Karen

Public Sub ComboAdUpdate()
On Error GoTo myErr
Me.txtSiteAD.SetFocus
Me.txtUserADID = Me.cboSiteAd.Column(2)
Me.txtSiteAD = Me.cboSiteAd.Column(0)
Me.txtADDescrip = Me.cboSiteAd.Column(1)
MsgBox "txtuseradid = " & Me.txtUserADID

Me![AssemblyDetailDescriptionCtnr].Form.Requery
Me.cboEditMid.RowSourceType = "Table/Query"
Me.cboEditMid.RowSource = "SELECT [MID_ID], [MID], [Assy], [Description], [UOM] FROM tblMaterial ORDER BY tblMaterial.MID;"
Me.txtEditQty.SetFocus

myExit:
Me.txtEditQty.SetFocus
Exit Sub

myErr:
If Err.Number = 3021 Or Err.Number = 3077 Then
Resume Next
Else
MsgBox Err.Number & ", " & Err.Description
GoTo myExit
End If
End Sub

 

[dhookom]

Karen,
I am at a loss as to why your code doesn't work. You might want to look at faq705-7148.


Duane
Hook'D on Access
MS Access MVP

 

[arpeggione]

ok...Duane: I pasted your code (a vba version of my query) into my procedure - and....I got the message both before and after. I also deleted the container (in case it was corrupted) and replaced it with a new one - same name. And...still no luck.
I don't think it is the code. It is something with the container (Note that I can get the subform to fill on its own outside the container with this query....

thx for your help - I will try your link.

Karen

 

[TheAceMan1]

arpeggione . . .

And what happened with:

[blue][AssemblyDetailDescriptionCtnr].Form.RecordSource = [AssemblyDetailDescriptionCtnr].Form.RecordSource[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[arpeggione]

Aceman...Alas...it did not work...

 

[dhookom]

Do you have Option Explicit set in the General Declarations section of your code? Does your code compile?



Duane
Hook'D on Access
MS Access MVP

 

[arpeggione]

Yes...on the "Option Explicit" question...

and...the "Debug Compile" option works. No errors come up (I actually fixed a couple a few days ago that I found using the "Debug Compile". Is that what you mean?

Thank you again for all of your efforts...

 

[dhookom]

When your code is in break mode, you can open the debug window (press ctrl+G) and type in various expressions to check their value like:

? Me.[AssemblyDetailDescriptionCtnr].Form.RecordSource

? Me.txtUserADID

Duane
Hook'D on Access
MS Access MVP

 

[arpeggione]

ahh...thank you. I tried this....I get the same result with my message boxes....

 

[TheAceMan1]

arpeggione . . .

At this point (if it were me) I'd delete then [blue]instantiate the subform again[/blue]. For different reasons from this thread, I've done this in the past, subsiding some problems to nothing. Hope it works for you ...

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!