My RecordSource in OpenEvent don't fire

FuzMic

DataBase Tinker
Local time
, 07:22
Joined
Sep 13, 2006
Messages
739
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?
 
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:-



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:-

Code:
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:-

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

    .fSetUp
 
End With
 
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:-

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

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

End With
 
Thanks Bro .. learning as always
I mean at the form properties.
 
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
 
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.
 

Users who are viewing this thread

Back
Top Bottom