Subform requery

[latex88]

I'm not sure which method to use, but requery does not seem to work. I basically have a main form with many subforms. One of the events in a subform (call this subform1) makes another subform (subform2) visible. The first time when the main form is open and the event is triggered from subform1, subform2 displays the objects (command buttons) fine, but subsequent triggers is not affecting subform2.

Note: subform2 is a form with command buttons that is populated based on results from a query using recordset with VBA, so the form itself is not necessarily bound to a table or a query. Perhaps this is the reason "requery" does not work? Shall I use another method?

 

[RainLover]

Note: subform2 is a form with command buttons that is populated based on results from a query using recordset with VBA, so the form itself is not necessarily bound to a table or a query. Perhaps this is the reason "requery" does not work? Shall I use another method?

I do not understand when you say "the form itself is not necessarily bound to a table or a query"

It either is or it is not. Why are you unsure. Is there more to this than meets the eye.

 

[latex88]

Well, subform2 contains nothing but command buttons with initial "visible" property set to "no". The number of buttons that become visible when the form is open and their captions are displayed is based on a query result that is triggered from subform1. However, I have used VBA codes to generate these events. In other words, the "recordsource" property of subform2 is blank. I hope that makes sense.

 

[RainLover]

So the code that calls the Record Source does not work is that right?

If this is so then put a Message box in the Beginning Middle and End of the code. Run it to see if you get all 3 Messages. This will prove that your code is at least being called.

 

[latex88]

The code is working fine. When I first open the main form and click on a button of subform1, subform2 becomes visible and all the buttons within subform2 are populated correctly. It is when I click on a different button on subform1, subform2 does not change. If subform2 was a just another form (not a subform), I can close it and open it, then it would work fine too, but it's when it's a subform, I don't know what method works. Requery just doesn't work.

 

[RainLover]

Try Me.Repaint and Me.Refresh

I don't think they will work but worth a try first.

I would think that you need to rerun your code that sets the Record Source. The On Focus event may work or the On Current Event.

Can you put it behind the command button you are using.

Without seeing your Database I am taking a few guesses. However what has to happen is the setting of the Record Source again.

See if you can sort it out. If not you will need to post your Database so I can look at it.

BTW if you attach your DB do it in Access 2003. That is the latest version I have.

 

[latex88]

Yeah, as you suspected, Repaint and Refresh did not work, neither did other methods, such as On Load, On Focus, On Open...etc, so I ended up coding as you suggested, which was setting the recordset behind the command button of subform1. That was painful. Now I have other issues...too long to explain, but there's got to be a way to "refresh" the captions of the buttons somehow, otherwise, I have such hard time referencing controls of a subform2 from subform1, all the while subform2 is actually based on variable. The syntax just gets too confusing... sigh...

 

[RainLover]

I would need to see a copy of your Database in order to comment further.

Post in 2003 Please

 

[tranchemontaigne]

Perhaps I'm missing something...are you refreshign the parent form or the child form/subform. If you want the child form to requery and your code only tells the parent form to requery, then only the parent form will requery.

If all else fails, you could reopen the child form/subform and make it become bound to a value on the parent form or a global variable within the form module.

 

[RainLover]

Yeah, as you suspected, Repaint and Refresh did not work, neither did other methods, such as On Load, On Focus, On Open...etc, so I ended up coding as you suggested, which was setting the recordset behind the command button of subform1. That was painful. Now I have other issues...too long to explain, but there's got to be a way to "refresh" the captions of the buttons somehow, otherwise, I have such hard time referencing controls of a subform2 from subform1, all the while subform2 is actually based on variable. The syntax just gets too confusing... sigh...

Try this link. It may help.

http://www.mvps.org/access/forms/frm0031.htm

 

[tranchemontaigne]

The simplest way of referencing a control on a subform is to open a query in design view, then click on the EXPRESSION BUILDER and browse out to the form ==> subform ==> and control. Good syntax will be written for you and you can copy/paste it into yoru code. Once you have done this a few times you can get a handle on how to write the pathing to the control directly. If you use command buttons to dynamically bind the subform control (on the parent form) to different subforms, you will need to pay a little extra attentinon to make sure your pathing to controls on the subform alligns with the subform on display.

In the example here I have a subform control on the parent form named "frmSubform". The "frmSubform" displays whatever form has been setwithin the "SourceObject" property. ("SourceObject" property can be reset at run time). The form named within the "SourceObject" property contains a control named "lblME_REFER_FL"

Setting any property of the named control is done in the normal way:

[Forms]![frmMainMenu]![frmSubform].Form![lblME_REFER_FL].Caption = "This is the new label caption"

Often times I make a parent form that acts like a main menu where command buttons that set the subform. This way a user never ever gets lost when trying to navigate between different pices of the MS Access tool or utility I've prepared for them.

Here's a snippet of what your code could look like"

Private Sub cmdWatch_Click()
'//////////////////////////////////////////////////////////////////
'// Revision History
'// Date Editor Description
'// ---------------------------------------------------
'// Jan 2002 Tranchemontaigne -Created 
'// 28 Dec 2012 Tranchemontaigne -Modified error handling
'//
'//////////////////////////////////////////////////////////////////
On Error GoTo Err_cmdWatch_Click
Dim strSubformName As String
Dim strError As String
 
 
strSubformName = "frmBT_RecordWatchCOD"
 
With [Forms]![frmMainMenu]
'modify controls on the parent form 
![cmdDataSheetView].Visible = True
![cmdDataSheetView].Caption = "Datasheet View"
![txtSubformName].Value = "frmBT_RecordWatchCOD"
![txtMMStart].Visible = False
![txtMMEnd].Visible = False
![txtSearchType].Visible = False
![cmdViewCertificate].Visible = True
 
'dynamically bind the subform control on the parent form to a particular subform
![frmSubform].SourceObject = strSubformName
 
'Given that the dynamically called subform is bound to a value stored in a control on the parent form, you may want to requery the subform just to make sure that you display the correct values
![frmSubform].Requery
End With
 
Exit_cmdWatch_Click:
Exit Sub
 
Err_cmdWatch_Click:
strError = "cmdWatch_Click encountered an error" & Chr(10) & Chr(13) & _
"Error: " & Err.Number & ": " & Err.Description
Debug.Print strError
MsgBox strError, vbCritical, gstrObject & " " & "Error"
Resume Exit_cmdWatch_Click
 
End Sub

 

[RainLover]

The simplest way of referencing a control on a subform is to open a query in design view, then click on the EXPRESSION BUILDER and browse out to the form ==> subform ==> and control. Good syntax will be written for you and you can copy/paste it into yoru code. Once you have done this a few times you can get a handle on how to write the pathing to the control directly.

Good Advise. Not many people know about this so it is good that you have posted it here. It has been so long since I have used it I forgot all about it.

Thanks

 

[latex88]

Ditto on the Expression Builder. I hardly ever use that. To be honest, I couldn't ever figure it out

Thank you RainLover and tranchemontaigne for taking the time to help me, but due to my deadline, I had to abandon the concept of subforms within forms. I've decided to go back and make them all pop up at destined locations. Maybe my reasoning was not a good one, but I wanted all the various forms to be within a confined real estate to ensure all the parts of the forms are visible, but getting them to reference each other and refreshing the data was a pain. Even now, because the forms are not bounded, I have to close and open the forms to refresh the buttons.

 

[RainLover]

latex

The normal practice is to bind forms.

You must have a special reason for running unbound.