Command to run access, open to specific form and specific record

[Clint Marshall]

I'm struggling to find a way to open an Access Database and go to a specific
form (not the one specified by the Startup screen) and open a specific
record.
I would set up a collection of these commands to open to different records,
depending on the users' needs. I'm hoping there may be a command like:
"c:\accessfile.mdb, frmTenant, ID=345".
Can this be done and, if so, how?
Thank you!

 

[Douglas J. Steele]

It can be done, but it's not quite as simple as that.

There's a /cmd switch that can be specified when you open the database, and
anything passed after the switch can be read inside of Access using the
Command function. The thing is, though, that /cmd is a switch for Access
itself, not for mdb files. That means that you must include the full path to
msaccess.exe in order to use that switch:

"C:\Program Files\Microsoft Office\Office\MSAccess.exe" "C:\Program
Files\Microsoft Office\Office\Samples\Northwind.mdb" /cmd frmTenant,ID=345

or you can use ; instead of /cmd

"C:\Program Files\Microsoft Office\Office\MSAccess.exe" "C:\Program
Files\Microsoft Office\Office\Samples\Northwind.mdb" ;frmTenant,ID=345

(either way, it must be the last switch specified.)

Okay, so now you've got a way to pass something to your application. You
have to be able to do something in your application once you receive that
information. The simplest approach would like be to have a start up form
that looks to see whether or not something was passed using the /cmd switch.
That code would go into the form's Load event, and look something like:

Private Sub Form_Load()

If Len(Command) > 0 Then
' something was passed
Else
' nothing was passed
End If

End Sub

What is it you're going to do? Assuming what's passed is frmTenant,ID=345,
you can use the Split function to parse that into the form name and the
lookup criteria, and use those in conjunction with the FileOpen method:

Dim varParms As Variant

varParms = Split(Command, ",")
If UBound(varParms) = 1 Then
DoCmd.OpenForm varParms(0), acNormal, , varParms(1)
End If

That'll open the form passed in front of the comma, using whatever was
passed after the comma as a Where condition. Note that that means that that
will be the only row displayed on the form. If that's not what you want, you
have the option of passing what's after the comma as the form's OpenArgs
parameter, and having code in the Load event of each possible form you'll be
opening to read what's in the OpenArgs parameter and use that to position
the current row on the form. However, that's more code than I'm willing to
type at the moment. <g>

 

[Clint Marshall]

That's awesome! Thank you!
I need two minor tweaks to make it work for me....

1) When I open the new form, there are several commands that run under the
FormOpen/OnOpen event. These do things like set a filter, set the focus and
set the rowsource for a combo box. There's also a Requery. How do I send a
parameter, or somehow tell the form, that in this case, since it's going to
a specific record, it doesn't have to do these things. I'm fine writing an
If statement in the Private Sub Form_Open, but don't know what the condition
would be or how to get it there .
2) Along these same lines, I need to open the form in the "Call List" tab of
the form. I presume I can add this to the If statement mentioned above, but
don't know what the command is.

Thanks again! You're a huge help and we're VERY appreciative!
-Clint

Here's what it looks like in frmTenant:
Private Sub Form_Open(Cancel As Integer)
ckActiveOnly = True
Me.Filter = "[Active Tenant] = True"
Me.FilterOn = True
GoToTenant.RowSource = "select [Tenant],[Tenant Name] from qryTenantForm
where [Active Tenant]=True;"
Requery
GoToTenant.SetFocus
End Sub

"Douglas J. Steele" wrote

 

[Douglas J. Steele]

You can pass a flag as the OpenArgs parameter. Change

DoCmd.OpenForm varParms(0), acNormal, , varParms(1)

to

DoCmd.OpenForm varParms(0), acNormal, , varParms(1), OpenArgs:=True

(heck, you can pass anything as the OpenArgs: I'm just checking whether or
not something was passed, not its actual value!)

Change your Open event to

Private Sub Form_Open(Cancel As Integer)
If IsNull(Me.OpenArgs) Then
ckActiveOnly = True
Me.Filter = "[Active Tenant] = True"
Me.FilterOn = True
GoToTenant.RowSource = "select [Tenant],[Tenant Name] from qryTenantForm
where [Active Tenant]=True;"
Requery
GoToTenant.SetFocus
Else
Me.NameOfTabControl.Pages("Call List").SetFocus
End If
End Sub

 

[Clint Marshall]

Spectacular!
Thank you very much. It works perfectly!
-Clint