Update invisible subform

[deadman]

OK. I have been working on this quite a while and can't seem to get it. I checked out the archives here and tried almost everything that had been suggested in the past with no luck. Maybe it makes a difference because my subforms are not visible.

I have designed a form that contains 2 subforms. I have made the subforms not visible unless they contain records by using the following code in the main forms OnCurrent Event:

Private Sub Form_Current()

With Me![tblCoupons].Form
.Visible = (.RecordsetClone.RecordCount > 0)

End With

With Me![sfrmCouponsStorecategory].Form
.Visible = (.RecordsetClone.RecordCount > 0)

End With

End Sub

I ripped the code from a tutorial as I haven't been playing with Access long enough to have it down. It works great if I close the main form and then come back to the record, the appropriate subform (if any) is visible with the correct data in it or it is not visible if there are no records.

How can I get this to happen without closing and coming back in. Please reply in simple terms/examples.

(I have already tried:
me.requery
me.refresh
combobox.requery
and almost everything else listed in the archives, here)

Thanks in advance.

 

[Oldsoftboss]

Try Forms![Mainform]![Subform].refresh

You may then have to run the code to check for records.

HTH

Dave

 

[deadman]

Thanks Dave. I had tried the code that you suggested without success, but you mentioning that I might have to run my existing code a second time never occurred to me. I already had the form refreshing On Got Focus on the next field after the relevant information field but never ran my code again. I changed it to:

Private Sub brand_GotFocus()
On Error GoTo Err_brand_GotFocus

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

With Me![tblCoupons].Form
.Visible = (.RecordsetClone.RecordCount > 0)
End With

With Me![sfrmCouponsStorecategory].Form
.Visible = ).RecordsetClone.RecordCount > 0)
End With
Exit_brand_GotFocus:
Exit Sub

Err_brand_GotFocus:
MsgBox Err.Description
Resume Exit_brand_GotFocus
End Sub

the way the code is written is probably dirty as all get out, but for a newbie....it works... and it and I have been happy ever since. Thanks a million.

 

[David R]

If it's your subform that's not refreshing, you'll probably have to refresh it directly. Me.xxx is referring to the main form, if that's where your Current event code is.

You can find the syntax in Access help: refer subform in form

HTH,
David R


[This message has been edited by David R (edited 02-14-2002).]