My RecordSource in OpenEvent don't fire (1 Viewer)

[FuzMic]

Hi guys

I have a form with view as 'Pivot Table' and want it to be use for 2 instances with different recordsource.

I normally can do this in all my other forms by changing the recordsource at the Open Event, be not with this form.

I tried the following options without any success:
1 Change back to Single Form View
2 Place the change of Recordsource codes at the Load and Activate Event
3 execute me.requery after the recordsource assignment.

For this form, i must place the RecordSource within the form itself, else changes in the Event gives a blank form.

What's is the diagnosis?

 

[Uncle Gizmo]

I think you are falling foul of the fact that the forms events don't operate in the way you would expect. I blogged about it on my website here:-

Form Load, Activate, Open, Issue, – Form Load Events - Nifty Access

Form Load Events When you first open a form, the following events occur in this order: Load Events, Open, Load, Resize, Activate, Current.... Now you could be forgiven for expecting that a value passed into a form would be available for the form's events to use. However it's not! These videos...

www.niftyaccess.com

If this is the case, then the solution is to create a public function in the form you are opening, I usually term it "function setup" "fSetUp"

If you use a code block using "With" - "End With" like this:-

Dim strFrmName As String
strFrmName = "frmToOpen"
DoCmd.OpenForm strFrmName, acNormal
With Forms(strFrmName)
    .Filter = ""
    .FilterOn = False
    .Caption = " Viewed From the Help Menu"
    .txtBoxName.DefaultValue = """xxx"""
    .fSetUp
    .Caption = "I CAN CHANGE THE CAPTION"
End With

You can set all sorts of different parameters of your Form when you open it. This code also works on an already open Form!

All you really need is:-

Dim strFrmName As String
strFrmName = "frmToOpen"
DoCmd.OpenForm strFrmName, acNormal
With Forms(strFrmName)

    .fSetUp
 
End With

 

[Uncle Gizmo]

For this form, i must place the RecordSource within the form itself, else changes in the Event gives a blank form.

I'm not 100% sure what you mean when you say putting the record source "within the form itself" do you mean directly into the recordsource property, or do you mean in the code module behind the Form?

In any event I should have mentioned you can also directly passed the SQL statement (or a query) to the forms record source using the same with end with block like this:-

Dim strFrmName As String
strFrmName = "frmToOpen"
DoCmd.OpenForm strFrmName, acNormal
With Forms(strFrmName)

    .RecordSource = "Select fld1, fld2, fld3 From myTable"

End With

 

[FuzMic]

Thanks Bro .. learning as always
I mean at the form properties.

 

[FuzMic]

Gizmo
I tried with acnormal in Open form then what you suggest outside the Open Event work

If I use acPivotTable or set DefaultView to Pivot table, then same outcome .. blank

 

[Pat Hartman]

You might want to move away from the Pivot view since it is not available in newer versions of Access. Use a cross

If your form is always opened from the same form, you can pick up the variable values by referencing the previous form. If you are placing them into unbound controls, use the Current Event. If you are placing them into bound controls, use the BeforeInsert event.

Me.fld1 = Forms!previousform!fld1
Me.fld2 = Forms!previousform!fld2

Another method is to place the values into TempVars and then reference the TempVars rather than the previous form in the above example.