Requery Subform Help

[Jakboi]

Hello,

I have looked at numerous threads here and have some confusion with subform requery. I know thats what I need because when I change the search selections in my combo boxes the subforms do not update, only after hitting design and then preview.

Does the requery code go into the subforms or main form or both?

I have a form with 5 subforms. When search criteria are selected on the main form with 2 combo boxes then the proper subform is visible.

I have tried different things and havent been able to get it yet.

Thanks for any assistance.

 

[ajetrumpet]

Do the search selection combo boxes provide data upon which to search for a record? Just wondering...

You can requery a subform from anywhere on a form, and in any subform on that main form as well. Take a look at Bob's link for various syntax lines you need for this...

http://www.access-programmers.co.uk/forums/showthread.php?t=127981

All of the lines in that link will work, unless of course you have nested your subforms within themselves. That page only lists syntax to use up to 2 subforms deep. Also, if there are just two combo boxes providing search criteria for all 5 subforms, you may need to write nested "IF, THEN" blocks on some form events, unless each subform is based on a separate query, with each of those queries specifying criteria of their own, by referencing the box values.

 

[Zigzag]

Hi Jakbio,

Try this
In the "on Change" event of the combo box use

Me.requery
or
Me.refresh
or both.

If this does not work you may have to directly refresh the subform.
Post back and let us know.

Garry

 

[Jakboi]

Thank you both for the responses. ajetrumpet thanks for the link. I was able to finally get it to work. I was using the wrong control the whole time and have finally got it to work great.

I just added one line to each of these sections in my main form and thats it, seems to work.

Private Sub Command12_Click()

If (IsNull(cboType)) Or (IsNull(txtCriteria)) Then
 MsgBox "Please select search type", vbOKOnly, "please select search type"
 ElseIf [cboType] = "ShortAccountTitle" And [txtCriteria] > "" Then
    Me!frmShortAccountTitle.Form!List0.Requery
    Me.frmShortAccountTitle.Visible = True
    Me.frmContactType.Visible = False
    Me.frmAccountNumber.Visible = False
    Me.frmDateEntered.Visible = False
    Me.frmEnteredBy.Visible = False
    Me.frmInitialContact.Visible = False
 ElseIf [cboType] = "ContactType" And [txtCriteria] > "" Then
    Me!frmContactType.Form!List0.Requery
    Me.frmShortAccountTitle.Visible = False
    Me.frmContactType.Visible = True
    Me.frmAccountNumber.Visible = False
    Me.frmDateEntered.Visible = False
    Me.frmEnteredBy.Visible = False
    Me.frmInitialContact.Visible = False
 ElseIf [cboType] = "AccountNumber" And [txtCriteria] > "" Then
    Me!frmAccountNumber.Form!List0.Requery
    Me.frmShortAccountTitle.Visible = False
    Me.frmContactType.Visible = False
    Me.frmAccountNumber.Visible = True
    Me.frmDateEntered.Visible = False
    Me.frmEnteredBy.Visible = False
    Me.frmInitialContact.Visible = False
 ElseIf [cboType] = "DateEntered" And [txtCriteria] > "" Then
    Me!frmDateEntered.Form!List0.Requery
    Me.frmShortAccountTitle.Visible = False
    Me.frmContactType.Visible = False
    Me.frmAccountNumber.Visible = False
    Me.frmDateEntered.Visible = True
    Me.frmEnteredBy.Visible = False
    Me.frmInitialContact.Visible = False
 ElseIf [cboType] = "EnteredBy" And [txtCriteria] > "" Then
    Me!frmEnteredBy.Form!List0.Requery
    Me.frmShortAccountTitle.Visible = False
    Me.frmContactType.Visible = False
    Me.frmAccountNumber.Visible = False
    Me.frmDateEntered.Visible = False
    Me.frmEnteredBy.Visible = True
    Me.frmInitialContact.Visible = False
 ElseIf [cboType] = "InitialContact" And [txtCriteria] > "" Then
    Me!frmInitialContact.Form!List0.Requery
    Me.frmShortAccountTitle.Visible = False
    Me.frmContactType.Visible = False
    Me.frmAccountNumber.Visible = False
    Me.frmDateEntered.Visible = False
    Me.frmEnteredBy.Visible = False
    Me.frmInitialContact.Visible = True
End If

 

[ajetrumpet]

The code is quite cumbersome!! As long as it works though, right?? Good luck!