RE: Set Filter from Startup form to another form

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

"Klatuu" wrote:

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

"RWilly" wrote:

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.


"Klatuu" wrote:

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.

"RWilly" wrote:

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.


"Klatuu" wrote:

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


"RWilly" wrote:

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.


"Klatuu" wrote:

Use the OpenArgs argument of the OpenForm method to pass the filter value to
the form.
strFilter = "The Filter Value you Want"
DoCmd.OpenForm "frmMGSP_Deliv",,,,strFilter

Then use the OpenArgs property of the form to set the filter in the Load
event.

If Not (IsNull) Me.OpenArgs Then
Me.Filter = "[FieldToFilter] = '" & Me.OpenArgs & "'"
Me.FilterOn = True
End If

"RWilly" wrote:

I’m working on a startup form for a database going to 35 remote sites. I’m
trying to create a command button to set or apply a filter to a form
(frmMGSP_Deliv) in the database, and then open the database for entering or
editing data. I would like the users to be able to choose their own program
as the filter. The values are in tblLocalProg–ProgID, ProgName, etc. I would
like the filter to remain on until the users remove it specifically, via
another command button on the startup form, as 99 percent of the time they
will enter/edit data only for their own local program

I can open frmMGSP_Deliv for editing, via the menu, with no problem, but I
can’t figure out how to set the filter. Thanks in advance for any
suggestions.

.

Quantcast