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]

You haven't suggested how the subform record source is related to any text boxes on your main form.

Duane
Hook'D on Access
MS Access MVP

 

[arpeggione]

Duane: Thx for your email and for looking at this.
In the query for the subform requery, the ID txt box on the main form is referred to as:
[Forms]![frmViewCreateAD]![txtUserADID]

SQL qry text:

SELECT DISTINCTROW tblUserAD_Title.UserAD_ID, tblMaterial.Mid, tblUserAssemblyDetail.Qty, tblMaterial.ASSY, tblMaterial.Description, tblMaterial.UOM, tblMaterial.MID_ID, tblUserAssemblyDetail.UserADTitleID
FROM tblUserAD_Title RIGHT JOIN (tblUserAssemblyDetail LEFT JOIN tblMaterial ON tblUserAssemblyDetail.MID_ID = tblMaterial.MID_ID) ON tblUserAD_Title.UserADTitleID = tblUserAssemblyDetail.UserADTitleID
WHERE (((tblUserAssemblyDetail.UserADTitleID)=[Forms]![frmViewCreateAD]![txtUserADID]))
ORDER BY tblMaterial.Mid;

When I substitute the ID 1448 into the query, the query works fine - and when I do a msg box
with that value (in the combo dropdown code), the msg box reports the correct ID.

I am just not getting the actual form to read the query and refresh...

Karen

 

[PHV]

What haapens if you replace this:
Me![AssemblyDetailDescriptionCtnr].Form.Requery
with this ?
Me![AssemblyDetailDescriptionCtnr].Form.RecordSource = Me![AssemblyDetailDescriptionCtnr].Form.RecordSource

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[arpeggione]

To PH: Well....I tried it - and it still doesn't work...thanks for the suggestion...

any other ideas?

this always worked well in 2003 - I'm only having the problem with 2007/2010. Something with the form requery is different in the newer versions I'm guessing...

Thank you,
Karen

 

[dhookom]

did you make sure there is nothing in the link master/child properties?

Duane
Hook'D on Access
MS Access MVP

 

[arpeggione]

master/child properties are empty....
thank you,
karen

 

[dhookom]

Have you added a breakpoint to make sure the code is running that you think should be running?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya arpeggione . . .

Try:

[blue][AssemblyDetailDescriptionCtnr].Form.Requery[/blue]

See Ya . . .

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

 

[arpeggione]

Aceman - yes, I tried that...

Duane - yes...I also tried that....the code trips up at the requery (which is not in code in the subform). The subform is just set up with fields and an Access query - no vba here. I did however, try resetting the Recordsource in code before requerying.

Thank you both for taking the time to offer up suggestions. Much appreciated! Any more ideas? I'll post if I figure it out...

Karen

 

[TheAceMan1]

arpeggione . . .

Have you tried instantiating a form object?

[blue]   Dim frm As Form
   
   Set frm = [AssemblyDetailDescriptionCtnr].Form
   [green]'
   ' your code
   '[/green]
   frm.Requery
   [green]'
   ' your code
   '[/green]
   Set frm = Nothing[/blue]

See Ya . . .

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

 

[TheAceMan1]

arpeggione . . .

Also try writing to the subforms recordsource:

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

Anytime you write to the recordsource of a form it [blue]automatically requeries![/blue]

See Ya . . .

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

 

[arpeggione]

Aceman: I will try your suggestion (though I may have already tried it). Is this the code:

[AssemblyDetailDescriptionCtnr].Form.RecordSource = [AssemblyDetailDescriptionCtnr].Form.RecordSource

For some reason, the code window height in your msg is about 1/2 of a line high....

thank you again....I'll post what happens...
karen

 

[TheAceMan1]

arpeggione . . .

Yes thats it. That is whats typically used to requery a listbox because [blue]Me.ListboxName.Requery[/blue] doesn't work either ...



See Ya . . .

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

 

[dhookom]

Can you tell us what you mean by "the code trips up"?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

arpeggione . . .

In parallel with [blue]dhookom[/blue], in your post origination what do you mean by:

[blue] ... I requery a subform ([purple]in a container[/purple]), ...[/blue]

What is this [purple]container?[/purple]

See Ya . . .

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

 

[arpeggione]

Duane: Good question! You caught me...as actually my statement wasn't very accurate. In fact, I just put message boxes into the procedure (especially before and after the requery). Both messages come up. So the requery statement executes somehow, but doesn't work....does that answer your question?

Karen

 

[arpeggione]

Aceman: Thx for your question. "Container" is the control on the main form which holds the subform.
karen

 

[dhookom]

I would try something like this assuming txtUserADID is numeric:

Dim strSQL as String
strSQL = "SELECT DISTINCTROW tblUserAD_Title.UserAD_ID, tblMaterial.Mid, " & _
    "tblUserAssemblyDetail.Qty, tblMaterial.ASSY, tblMaterial.Description, " & _
    "tblMaterial.UOM, tblMaterial.MID_ID, tblUserAssemblyDetail.UserADTitleID "
strSQL = strSQL & " FROM tblUserAD_Title RIGHT JOIN (tblUserAssemblyDetail LEFT JOIN " & _
    "tblMaterial ON tblUserAssemblyDetail.MID_ID = tblMaterial.MID_ID) ON " & _
    "tblUserAD_Title.UserADTitleID = tblUserAssemblyDetail.UserADTitleID "
strSQL = strSQL & " WHERE tblUserAssemblyDetail.UserADTitleID= " & Me![txtUserADID]
strSQL = strSQL & " ORDER BY tblMaterial.Mid"
Me![AssemblyDetailDescriptionCtnr].Form.RecordSource = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[arpeggione]

Duane: Honestly, I'm not sure how to use a breakpoint. I tried inserting one on the "requery" line of code...but when I click run - it asks for a macro. I've always used msg boxes to frame an area of code I'm working on. Usually I can tell what is and isn't working - as the messages after the problem code (not working) don't show up.

Here, the msgs from both before and after the problem code show up. But the subform is not filled up with the query results as it should be (and there is no code for that - just access form properties).

Could you advise (given the code at the beginning of this thread) or point me to a good website.
thx,
Karen