Here is all the code from the Load Event and the Function
*********************
Private Sub Form_Load()
Dim strPrg As String
On Error GoTo Form_Load_Error
strPrg = CurrentDb.Properties("MyProgram")
Form_Load_Exit:
On Error Resume Next
Exit Sub
Form_Load_Error:
If Err.Number = 3270 Then
strPrg = StartProgram(False)
Else
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure Form_Load of VBA Document Form_frmMasterSubTest"
End If
GoTo Form_Load_Exit
End Sub
' Here is the function it calls if the program does not exits:
Private Function StartProgram(blnPropExists) As String
Dim Prop As Property
Dim strProgName As String
strProgName = InputBox("Please Enter Program Code")
With CurrentDb
If blnPropExists Then
.Properties("MyProgram") = strProgName
Else
Set Prop = .CreateProperty("MyProgram", dbText, strProgName)
.Properties.Append Prop
End If
End With
StartProgram = strProgName
End Function
***************************
Thanks again
:
Post back with the all the code as you have it. I tested it and it does
work. There must be some error in copying and pasting the code.
:
I'm back to using the original approach you suggested, as each user will have
their own copy of the mdb (front and back end, 30 offices across the state
with no network but the Internet).
On using the code from your last message, I get
run-time error "13"
type mismatch
Debug points to
Set Prop = .CreateProperty("MyProgram", dbText, strProgName)
I've tried specifying an object for CreateProperty, but I don't really know
what I'm doing in this case. I really appreciate your patience, and any help
or suggestions you have.
:
Using the Database Property method is presistent. The original approach I
suggested would really only be useful if you correctly implement your
applcation so that each user has their own copy of the mdb on their computer.
Using a table will work in a multi user environment regardless of how the
deploy the application.
The primary difference is, that once set, the Property can be read without
any other actions.
If you want to have the program presistent througout the application, make
this the Load event of your start up form. Or, if your start up code is not
a form, change the name of the sub, and execute it somewhere in your start up
code. It will ensure the property exists and create it if it does not.
Private Sub Form_Load()
Dim strPrg As String
On Error GoTo Form_Load_Error
strPrg = CurrentDb.Properties("MyProgram")
Form_Load_Exit:
On Error Resume Next
Exit Sub
Form_Load_Error:
If Err.Number = 3270 Then
strPrg = StartProgram(False)
Else
MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & _
") in procedure Form_Load of VBA Document Form_frmMasterSubTest"
End If
GoTo Form_Load_Exit
End Sub
Here is the function it calls if the program does not exits:
Private Function StartProgram(blnPropExists) As String
Dim Prop As Property
Dim strProgName As String
strProgName = InputBox("Please Enter Program Code")
With CurrentDb
If blnPropExists Then
.Properties("MyProgram") = strProgName
Else
Set Prop = .CreateProperty("MyProgram", dbText, strProgName)
.Properties.Append Prop
End If
End With
StartProgram = strProgName
End Function
Now all that is necessary is to set the filter for each form where you want
the filtering to apply. Put this code in the Load Event of the forms:
Me.Filter = "[Program] = '" & Currentdb.Properties("MyProgram") & "'"
Me.FilterOn = True
:
Thanks for the reply. I was hoping you might have an idea on how to make the
filter set on the data entry/editing forms permanent, or sticky, so the
filters remain until they're removed explicitly. It's important enough that
I'm going back to the code you forwarded and seeing if I can use/adapt it.
Regards.
:
That will do just fine, in fact in a multi user environment, you may want to
add the userid to the table. Then in the startup, lookup the userid and if
they already have a program defined, use it.
:
Thanks for your response. Sorry it took me so long to get back. I tried to
implement the code you were kind enough to send. Once I added
currentdb.Properties.Refresh
to the two lines of code in the Immediate Window it ran without errors, but
I couldn’t get the code in the command button to run without errors. I should
have made a note of the exact error.
Right now I’ve created a form that is close to doing what I want it to. I’ve
got an unbound combo box that shows all the local districts (aka local
programs). One I select a local district, I then run the code below from a
command button on the same form.
Private Sub cmdFilterMGSP_Deliv_Click()
Dim strSQL As String
If Me("cboProgFilter") <> "" Then
' Build SQL String.
strSQL = "((tblLocalProg.[ProgID]" _
& " = " & Me("cboProgFilter") & "))" ‘ProgID is a long integer
End If
If strSQL <> "" Then
' Set the Filter property.
Forms![frmMGSP_Deliv].Filter = strSQL
Forms![frmMGSP_Deliv].FilterOn = True
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "frmMGSP_Deliv"
End Sub
I do have to open frmMGSP_Deliv, which seems clumsy, but the filter on
frmMGSP_Deliv is set. However, it doesn’t stay set when it’s opened the next
time. I’ve commented out the code you sent in your last message, so that
shouldn’t be a problem.
Any suggestions are appreciated. Thanks.
:
Getting pretty advanced here, aren't we
Not that hard, really. All you need to do is create a database property
that you can read each time you need to open the form and load it's value in
the filter. You may also want a button that allows the user to change the
value of the property if they need to change programs.
The first thing you will need to do is create the property. The quick and
easy way is to do it in the immediate window of the the VBA editor. Here are
the two lines of code to type in:
set prop = currentdb.CreateProperty("MyProgram",dbtext,"New")
currentdb.Properties.Append prop
You can change the name of the property by replacing MyProgram with whatever
you want to call it. I use the value New because we will want that later.
Now, just change your code like this:
strDocName = "frmMGSP_Deliv"
strFilter = CurrentDb.Properties("MyProgram")
If strFilter = "New" Then
currentdb.Properties("MyProgram") = inputbox("Enter Program Name")
strFilter = CurrentDb.Properties("MyProgram")
End If
DoCmd.OpenForm strDocName, , , , acFormEdit, , strFilter
If you want the user to be able to change the program, provide a command
button and in the click event:
Dim strNewProgram
strNewProgram = inputbox("Current Program Is " &
Currentdb.Properties("myprogram") & vbnewline & "Change to")
If Len(strNewProgram) > 0 Then
Currentdb.Properties("MyProgram") = strNewProgram
MsgBox "Program Changed to " & strNewProgram
Else
MsgBox "Change Canceled"
End If
:
Thanks for your help. I can now open the form using a constant, i.e.,
strDocName = "frmMGSP_Deliv"
strFilter = "1"
DoCmd.OpenForm strDocName, , , , acFormEdit, , strFilter
This occurs through a command button on my startup form. Now I’d like to
able to have the users click on this command button, or another one, and have
them choose their own program as the filter to the form. Ideally they would
only have to do this one time, when they first start using the database-that
is, the form would open with the same filter already set every day, so they
don't have to choose their own program every time they use the database.
What do you think of using a command button from the starup form to call a
pop-up form with an unbound combo box that would get their selection, and
then use it to set the form filter? I would appreciate any ideas or comments
you have, regarding where to put code and how to handle the variables.
Thanks again.
:
Use the OpenArgs argument of the OpenForm method to pass the filter value to
the form.
strFilter = "The Filter Value you Want"
