I currently have a form called "Verify Customer Details" which can be accessed from two different forms. Depending upon which form "Verfiy Customer Details" is accessed from, the recordsource needs to be set to the relevant one of two queries.
I tried setting the recordsource in the Open and Load events of "Verify Customer Details", but if the form is being opened from the other source (ie the recordsource has changed since the from was last accessed) then "Verify Customer Details" is prompting for the parameters (which are non-existent because the recosrdsource has changed since the form was last accessed) before I can have a chance to change the recordsource.
open happens before load and open can be canceled so i would try removing the code from the load event (if it's there) and see if that helps. also make sure the form isn't already open or the open event won't fire. if it is open use the activate event. or try setting the record source on your button's click event. otherwise can we see your code? how does your opening form know what is calling it?
Sorry, should've clarified that I tried the Open and Load events seperately - still the same problem when just the Open event is used. I tried the following code:
Code:
Private Sub Form_Open()
'Set recordsource depending upon source form
Select Case intSource
Case 1
Me.RecordSource = "Inputs 10 - Verify Customer Details (Inputs 5)"
Case 2
Me.RecordSource = "Inputs 10 - Verify Customer Details (Inputs 11)"
End Select
End Sub
or try setting the record source on your button's click event
As Wazz said "how does your opening form know what is calling it?"
You're basing which recordsource to use on the value of intSource, how is the form to be opened getting the value for intSource?
The simplest solution, if I read the question correctly, would be to have "twin" forms, with only the record sources being different, and calling one or the other.
- like missinglinq said, the easiest way is to use two separate forms. but you almost have it with your code.
- the question remains, what is intSource?? let us know if you actually set intSource somewhere and maybe just didn't include part of your code.
- also, i don't know why your second code didn't work - also wondering what you've omitted if anything. there could be something wrong that we can't see in your code or db in which case these suggestions might not help.
- strongly recommend (take a guess...) renaming your forms and queries. start with 'frm' or 'qry' and don't use spaces:
e.g. frmExcellentForm, frmBetterForm, frmSplash, frmHome
e.g. qryInputs10VerifyCustDetails_Inputs5, qryInputs10VerifyCustDetails_Inputs 11
what you can do is, on your cmdbutton to open the form, add an OpenArgs argument, like so:
Code:
DoCmd.OpenForm "frmName", , , , , , Me.Name
you can see Me.Name at the very end. that info (the name of your current form) gets sent to the form you open.
then modify the code you have to:
Code:
Private Sub Form_Open()
'Set recordsource depending upon source form
Select Case Me.OpenArgs
Case "frmName1"
Me.RecordSource = "Inputs 10 - Verify Customer Details (Inputs 5)"
Case "frmName2"
Me.RecordSource = "Inputs 10 - Verify Customer Details (Inputs 11)"
End Select
End Sub
Thanks for all the suggestions guys. Still confused as to why it is not working the way I wanted, but have decided to go the twin form route, and it is all working well.
John's point is correct. But another part of this is harder to understand fully. You see, it IS permitted to change your .RecordSource any time the form is not dirty. Doing so would force an automatic Requery. See this link, and in particular see the highlighted area in the Remarks section. I suspect the OP's claim that changing the .RecordSource in the .Open or .Load events suffered from some other procedural or technique error.
Some of the advice in the original thread is not very good.
1. Maintaining identical forms with different recordsources is a configuration management nightmare. That advice is silly but may suffice for a novice user incapable of writing the most basic code.
2. Changing the rowsource of a form can be done in the on open or on load but on open is recommended. Not really sure of the OPs problems.
@NauticalGent
I disagree with some of your characterization.
Is read only. To change a Form's RecordSet, you would either have to open the form in design mode with VBA:
1. First opening the form in design view at run time is just a bad idea for lots of reason. This should never be done. If someone has to do it then there are serious problems with the design. Likely it will make the db unstable and it cannot be done in a compiled DB.
Second there are lots of ways to change the recordsource (which will change the recordset once loaded).
As mentioned you can use openargs, but normally only really needed if opening the form ACDIALOG.
2. If opening the form ACDIALOG then code execution stops. So the calling form cannot modify the recordsource. Openargs solves that issue by passing the query name. Then in the open event
Code:
Private Sub Form_Open(Cancel As Integer)
If Not (Me.OpenArgs & "") = "" Then Me.RecordSource = Me.OpenArgs
End Sub
One thing this should be done in the open event although it will work in the load event too.
If you are trying to decide whether to use the Open or Load event for your macro or event procedure, one significant difference is that the Open event can be canceled, but the Load event can't. For example, if you are dynamically building a record source for a form in an event procedure for the form's Open event, you can cancel opening the form if there are no records to display
3. Another way if you are going to open the form ACDIALOG is to set the query name in a global variable or temp var and use the open event to read the variable. Using a global variable you could even pass in a recordset and set the recordset. I do not recommend this method but exists if needed.
4. However if you are not opening it ACDIALOG (often this is overused anyways only needed when you need code execution to stop) then simply set it after the fact. In my demo I have a combo with names of queries.
Private Sub cmdOpenSet_Click()
DoCmd.OpenForm "frmProducts"
'Since not ACDIALOG I can modify the rs
If Not IsNull(Me.cmboQuery) Then
Forms!frmProducts.RecordSource = Me.cmboQuery
End If
End Sub
is read only. That is not an accurate description and may confuse others. There is no issue here dealing with "read only"
The recordsource property is clearly read/write. It can be changed at design time or run time. The real issue is that you cannot first of all reference a closed form using the FORMS collection. The FORMS collection only holds references to open forms. That is why the OP got the error that the form is not found. Even so you still cannot modify properties of forms that are not open.
IMO method (4) is the easiest and most flexible since it does not require writing additional code in the called form. However in method (4) the recordsource is not set until all of the events associated with a form opening complete (open, load, current, etc...). If you have code in any of those events that rely on specific data it may be to late. That would be another reason to use open args.
Thanks for the info and alternatives. My main intent was to provide the OP and anyone else who came here with a reason why using Forms!"FormName".RecordSource = "..." gives the error "Does not exist" if the form is not loaded.
