Setting RecordSource

[David44Coder]

Can I send a in Forms record source via Property Let ?
I think the Form must be opened first but by then its too late. That is, the events where you'd apply Recordsouce are done.
Or am I wrong ?

 

[MajP]

You cannot set the recordsource prior to opening the form. You can pass it in openargs and check it in the form's load event.

Form.OpenArgs property (Access)

Office VBA reference topic

docs.microsoft.com

 

[David44Coder]

Thanks.. I'll not attempt to set it before opening anymore.
I was using OpenArgs but quite often it would report invalid use of Null. Particularly when writing code and testing different things. So I was looking looking for an alternative method. Nz() fixed the null problem but if I can use Let it might be better.

 

[David44Coder]

DoCmd.OpenForm "frmFiller", acFormDS, , , , acDialog
Forms!frmFiller.Target = "ddd"

Form code is

Option Compare Database
Option Explicit
Private iSource As String

Private Sub Form_Current()
MsgBox iSource
End Sub

Public Property Let Target(a As String)
    iSource = a
End Property

By stepping through I find The Let is not executed until after Form current. I can't call it before the Form opens so how else would a value be passed to the Form, that is can see immediately (apart from openArgs) ?

 

[MajP]

By stepping through I find The Let is not executed until after Form current.

In fact, you can not do what you show at all. If you call openform using ACDIALOG code execution in the calling form stops until the called form is closed. In VBA there is no way to set form properties prior to opening the form. Well theoretically you can open first in design view then close it, but that is a horrible idea. In something like VB.NET you can set form properties before opening the form.
Why do you need to call ACDIALOG (why do you want code execution to stop?)? Why can't you set this property after the load event.

OpenArgs but quite often it would report invalid use of Null.

That is not a shortfall of OpenArgs that is a problem with your code.
Every time I use openargs the onload event looks like this

If not (me.openArgs & "") = "" then
  do something here.
end if

 

[Galaxiom]

Open the form with it not Visible, set the RecordSource then make it Visible.
IIRC the visibility can be controlled in the parameters of OpenForm

 

[MajP]

Open the form with it not Visible, set the RecordSource then make it Visible.
IIRC the visibility can be controlled in the parameters of OpenForm

Whats the point? That does not do anything different.

 

[The_Doc_Man]

You can set the form's .RecordSource after it has been opened. However, doing so will cause a .Requery and thus may also trigger a Form_Current event to recognize the changed source. This change would be visible to users as a flicker if nothing else. The more complex the form, the more pronounced the flicker.

Form.RecordSource property (Access)

Office VBA reference topic

docs.microsoft.com

In theory you can also change the form's .RecordSet, with the same comments regarding .Requery and flicker. Also, if you change .RecordSource, you implicitly change the .RecordSet (and vice versa) because they are interdependent. Changing one changes the other.

If you open the form in design mode, you can set the .RecordSource without causing events on that form, and as noted by others, if you declare the form "Hidden" then the .RecordSource change won't be visible. But opening the form in Design mode and having to then make it hidden MIGHT cause a visible flicker too. AND of course the form being modified can't do this to itself because a form in Design mode doesn't run code.

There is this other consideration... changing the .RecordSource presumes your form will have a compatible record. So if the change to the source is merely a WHERE clause as a filter, you could just define the form's .Filter property and then set .FilterOn = TRUE. Ditto for the ORDER BY, since there is a property for that, too.

 

[David44Coder]

THE ACDIALOG wasn't needed (at this stage anyway) as I was just looking at ways to set the recordset where it needed to be calculated.
I've abandoned the idea of using Property Let and gone back to writing a query before opening the Form. Which is hard wired in Properties.
This works fine but seemed unnecessary (if I knew more).
The bigger picture is depending on which field is clicked, the Form opens showing data than can be copied to the target.

 

[Galaxiom]

Whats the point? That does not do anything different.

Since setting the RecordSource of a form programmatically is a trivial task per se, I assumed the OP was trying to set it before it opened so that the user wouldn't see it before it changed.

Of course no well designed database should ever need to open any objects in design mode.

 

[MajP]

HE ACDIALOG wasn't needed (at this stage anyway) as I was just looking at ways to set the recordset where it needed to be calculated.
I've abandoned the idea of using Property Let and gone back to writing a query before opening the Form. Which is hard wired in Properties.
This works fine but seemed unnecessary (if I knew more).
The bigger picture is depending on which field is clicked, the Form opens showing data than can be copied to the target.

Changing the query def prior to opening is a viable solution. But my question still is why can't you change the recordsource after opening. Is there something in the initial events that has to happen? If not then just open your form with no recordsource and then assign after opening. The calling code can do this if not opened ACDIALOG.

 

[Pat Hartman]

Sometimes, rather than us trying to make YOUR solution work, it is better for you to give us a better understanding of the business process you are trying to solve because you may have chosen an inferior solution and we will be days before we figure that out. The fact that you are talking about property let and property get indicates you have some programming knowledge but you may know nothing about how to use Access and are trying to use it the way you would use some other tool when there may be a simple "Access" solution that you don't know exists.

When you open one form from another, typically, you would use the Where argument of the OpenForm method to open a form to a specific record or set of records. You may not need to change the form's RecordSource at all. If you are opening a form where the record may not exist and you need to pass it information such as the ID to use as a FK, you would use the OpenArgs argument and then populate the FK in the BeforeInsert event of the popup form.

The command that immediately precedes the OpenForm, should save the current record in case it is dirty. This is SOP whether you are opeing a second form or a report. Save the record you are on before opening a new form/report.