[access 2000] code runs when opening form but not when opening as subform

[ed_the_unlucky]

i have encountered a situation i don't understand.

i have a subform that runs some VBA on the on open event.

(code takes 2 dates from textbox on form, runs report, e-mails to user.)

when i open the subform independently, everything works fine, but when the main form opens, the code doesn't work properly. do i need a different event to trigger the code when opening the subform from the main form?

 

[rolaaus]

On_Open should work

The on_open event should fire when you open the form, even when it is a sub-form. The problem may be how you are referencing the data controls. I have run across problems (especially with older versions of Access) referencing controls on a sub-form, even when the code is in the sub-form itself.

Having said that, one thing you might try is changing your references to the date controls using the mainform/subform reference.

Forms!MainFormName.SubForm.ControlName

If this doesn't work, then post the offending code and someone might spot something else that isn't obvious without the code.

 

[RuralGuy]

Just so the record is straight, the reference should be:
Forms!MainFormName.SubForm.Form.ControlName

 

[ed_the_unlucky]

rolaaus, thank you for the prompt reply. am attempting your suggestion but the reference is not recognized by the query.

ruralguy: i'm not sure i quite understand your clarification re:syntax. am using forms![switchboard].[subform].[controlname], but you mentioned forms![switchboard].[subform].form.[controlname].

forgive my fatigue, but form should be...? you don't mean for [subform] to be duplicated, right?

 

[RuralGuy]

This link should help explain the reference syntax:
Refer to Form and Subform properties and controls

Then if you would kindly post the code in your Open event, we can look at it and see if we see anything wrong.

 

[rolaaus]

form/sub-form reference in query

If you are designing your query to reference the control, then I would recommend using the Build wizard (right click in the Criteria), this could also help you find the reference then plug it into VBA.

RuralGuy, thanks for the clarification - I haven't used any Sub-forms in quite a while.

 

[ed_the_unlucky]

ruralguy: um...either i'm being obtuse or i didn't explain properly, but that might all be irrelevant.

OK, i believe this is now in the wrong subforum but apparently, i'm passing the date from the form to either the query or the resulting report incorrectly.

i am using forms![subform].[control]

the article linked by ruralguy refers to using references within the mainform/subform 1/subform 2, but i'm at a loss re: when making the references externally, as in this instance, where the reference appears on a query.

 

[boblarson]

If being referred to in a query it would be:

[Forms]![YourMainForm]![YourSubformCONTAINERNAME]![Form]![YourControlName]

Where YourSubformCONTAINERNAME is the name of the subform container control that houses the subform on the main form. It isn't necessarily going to be the same name as the subform (it can be but isn't always), so you use the container name instead of the subform name.

 

[RuralGuy]

I take it you do not want to post the code you have in the OnOpen event of your SubForm?

 

[RuralGuy]

i have encountered a situation i don't understand.

i have a subform that runs some VBA on the on open event.

(code takes 2 dates from textbox on form, runs report, e-mails to user.)

when i open the subform independently, everything works fine, but when the main form opens, the code doesn't work properly. do i need a different event to trigger the code when opening the subform from the main form?

Since you can not reference a control in the OnOpen event of a form, I suspect it is a little different than described.

 

[ed_the_unlucky]

bob: thank you, but for some reason, while i understand that, when the query runs, the parameter is still not understood. i'm realizing now that the subform is housed in a tabcontrol on the main form. have i just made a mess of this?

ruralguy: gosh, i'd honestly forgotten! i'm not sure that it's relevant, but hey, i really need to eliminate a few possibilities...

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

    Dim stDocName As String
    Dim myEmailRecipient As String
    Dim myCCrecipient As String
    Dim myBCCrecipient As String
    Dim mySubject As String
    Dim myMsg As String

    stDocName = "rptAuthExpiration"
    myEmailRecipient = "[redcated]"
    myCCrecipient = "[redcated2]"
    myBCCrecipient = ""
    mySubject = "Auth Expires Report, next 30 days"
    myMsg = "Please find attached the Authorization Expires Report for the next 30 days"
    
    DoCmd.SendObject acReport, stDocName, , myEmailRecipient, myCCrecipient, myBCCrecipient, mySubject, myMsg

Exit_Form_Open:
    Exit Sub

Err_Form_Open:
    MsgBox Err.Description
    Resume Exit_Form_Open
    
End Sub

so there's the code. and as i said, this code is not being invoked when the main form (switchboard) opens.

edit: ruralguy, you're correct, i was conflating 2 different pieces of code. the above runs on the subform opening, but i did have 2 controls for a date range upon which other reports could be run.

 

[rolaaus]

I don't see any reference to any controls.

Also, I'm a bit confused - is your Switchboard form a sub-form? If so, just out of curiousity, what do you have as the main form?

 

[ed_the_unlucky]

guys, thank you so much. i'm a bit tired and hence am having trouble keeping the details straight.

on open event: the code in my previous post runs and automatically runs the report based upon a default period of time, then e-mails it.

the form itself: there are 2 text boxes for the user to specify a date range and an option to run one of 3 reports.

the reason i conflated these things is b/c none of the VBA is running when i open the subform through the switchboard, but it all runs fine when i open the subform directly.

thanks for your patience, gentlemen.

 

[rolaaus]

I might be mistaken, but it sounds to me like you are automatically generating these dates based on the current date.

Either way (if the user choses dates or if they are auto-generated) you should be able to based your report source on a query that picks up those dates, by-passing the need to reference those data controls on the form. Your form code can use the RecorrdSource parameter (may be named something different) and I would just copy the reports default source and create a new query uniquely for this situation (opening from this form).

Unless I am missing something, this is what I would do.

 

[RuralGuy]

Have you put a MsgBox, breakpoint or Debug.Print in the code to make sure it is not running? Does that report depend on any values from any open forms?

 

[ed_the_unlucky]

rolaaus: the dates are defaulting to the current, but the user can opt to change them. and there is an expressed desire to make data entry on the form a necessity, which will not permit me to prompt for the parameters within the query.

ruralguy: an excellent point re: debugging, shall attempt. and yes, the report prints the [from] date and pastes it into the report, so that the user gets confirmation of the parameters.

 

[ed_the_unlucky]

finally figured it out--rolaaus, you suggested using the builder to get the correct syntax for the reference and of course, that turned out to be at the heart of the issue, once i cleaned up a few things.

thank you, guys!

ed

 

[RuralGuy]

Excellent! Glad you got it sorted.