Solved Filter on form staying resident (1 Viewer)

chizzy42

Registered User.
Local time
Today, 13:29
Joined
Sep 28, 2014
Messages
115
Hi, Hope you are all ok. I've made a form that sends out an email to named members about an issue they have to look at. The email contains a link to a database which opens a specific record, this is ok. The issue I've noticed is if you shut the form down , when you reopen it it still links to the same record so the rest of the database is filtered out to this record only. Even if I open a form to search for records it still only links to the linked record. if the database is shut down and reopened the search forms are clear. I just cant seem to clear the filter from a close button. i have tried in the close button
Code:
Me.Refresh
Me.Requery
Me.Filter = ""
Me.FilterOn = false ' and tried true

the code that generates the link is
Code:
FEpath = "\\server.home.com\home\cqa ecc\RPS\Rpsdb\"
AccessPath = "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE"
ClientDirectory = "\\server.home.com\home\cqa ecc\Rps\RPSdb\RPS.accdb"
FormID = "RPS, RPSno=" & strDevNo

' Create a shortcut object on the desktop
Set MyShortcut = WSHShell.CreateShortcut(FEpath & "\" & "RPS " & FormID & ".lnk")

and this is the code that interprets the in coming command sent from the link
Code:
Private Sub Form_Open(Cancel As Integer)
If Len(Command) > 0 Then
' something was passed from link to open relevant form and required record
MsgBox ("command greater")
Dim logit As String
logit = Command()
MsgBox logit

Dim varParms As Variant

varParms = Split(Command, ",")
MsgBox UBound(varParms)
If UBound(varParms) = 1 Then
DoCmd.OpenForm varParms(0), acNormal, , varParms(1)
End If
Else
'' nothing was passed open entry form to search
MsgBox ("command less")
DoCmd.OpenForm "frmEnter"
End If
End Sub

if anyone has any ideas how to clear the resident filter that would be appreciated

thanks
ian
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:29
Joined
Sep 21, 2011
Messages
14,041
Walk through your code line by line.
I'd hazard a guess that Command still contains something?

Code:
Private Sub Form_Open(Cancel As Integer)
    If Len(Command) > 0 Then
        ' something was passed from link to open relevant form and required record
        MsgBox ("command greater")
        Dim logit As String
        logit = Command()
        MsgBox logit

        Dim varParms As Variant

        varParms = Split(Command, ",")
        MsgBox UBound(varParms)
        If UBound(varParms) = 1 Then
            DoCmd.OpenForm varParms(0), acNormal, , varParms(1)
        End If
    Else
        '' nothing was passed open entry form to search
        MsgBox ("command less")
        DoCmd.OpenForm "frmEnter"
    End If
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:29
Joined
May 7, 2009
Messages
19,169
so you have a Close button on "that" form?
you should close the Form Without Saving it:

DoCmd.Close acForm, Me.Name, acSaveNo
 

chizzy42

Registered User.
Local time
Today, 13:29
Joined
Sep 28, 2014
Messages
115
so you have a Close button on "that" form?
you should close the Form Without Saving it:

DoCmd.Close acForm, Me.Name, acSaveNo
thanks for the reply arnelgp, i tried that and still didn't work the command data still stays resident
 

chizzy42

Registered User.
Local time
Today, 13:29
Joined
Sep 28, 2014
Messages
115
Walk through your code line by line.
I'd hazard a guess that Command still contains something?

Code:
Private Sub Form_Open(Cancel As Integer)
    If Len(Command) > 0 Then
        ' something was passed from link to open relevant form and required record
        MsgBox ("command greater")
        Dim logit As String
        logit = Command()
        MsgBox logit

        Dim varParms As Variant

        varParms = Split(Command, ",")
        MsgBox UBound(varParms)
        If UBound(varParms) = 1 Then
            DoCmd.OpenForm varParms(0), acNormal, , varParms(1)
        End If
    Else
        '' nothing was passed open entry form to search
        MsgBox ("command less")
        DoCmd.OpenForm "frmEnter"
    End If
End Sub
hi gasman the data from the command input does stay the same, i was using the msgbox for debug and whatever data comes from the link stays on the form. I was trying to clear the command after it had opened the form but could get that to work
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:29
Joined
May 7, 2009
Messages
19,169
on your "close button", how about:

Me.Filter = ""
DoCmd.Close acForm, Me.Name, acSaveYes
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:29
Joined
Sep 21, 2011
Messages
14,041
hi gasman the data from the command input does stay the same, i was using the msgbox for debug and whatever data comes from the link stays on the form. I was trying to clear the command after it had opened the form but could get that to work
Well as I see it, until you clear the contents of command, you are stuck?
 

chizzy42

Registered User.
Local time
Today, 13:29
Joined
Sep 28, 2014
Messages
115
on your "close button", how about:

Me.Filter = ""
DoCmd.Close acForm, Me.Name, acSaveYes
That doesn't seem to work either, I tried the code below and the data is there before and after the me.filter="". which im finding confusing as i thought when i sent the command it was to setup the filter.
Code:
Dim filterval
filterval = Me.Filter
MsgBox (filterval)
 Me.Filter = ""
 MsgBox (filterval)
DoCmd.Close acForm, Me.Name, acSaveYes
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:29
Joined
May 7, 2009
Messages
19,169
no, you did not set filterval again:

Dim filterval
filterval = Me.Filter
MsgBox (filterval)
Me.Filter = ""
filterval = Me.Filter
MsgBox (filterval)
DoCmd.Close acForm, Me.Name, acSaveYes
 

chizzy42

Registered User.
Local time
Today, 13:29
Joined
Sep 28, 2014
Messages
115
no, you did not set filterval again:

Dim filterval
filterval = Me.Filter
MsgBox (filterval)
Me.Filter = ""
filterval = Me.Filter
MsgBox (filterval)
DoCmd.Close acForm, Me.Name, acSaveYes
well spotted, when ran now the filter does clear , but when the form is re opened the data from the command goes back in. i thought the data brought in would be a one shot and when the form closed it would clear. I am thinking of putting the code i have in the open parameter into an autoexec which i believe should only trigger when the database is opened for the first time??

Code:
Private Sub Form_Open(Cancel As Integer)
If Len(Command) > 0 Then
' something was passed from link to open relevant form and required record
MsgBox ("command greater")
Dim logit As String
logit = Command()
MsgBox logit

Dim varParms As Variant

varParms = Split(Command, ",")
MsgBox UBound(varParms)
If UBound(varParms) = 1 Then
DoCmd.OpenForm varParms(0), acNormal, , varParms(1)
End If
Else
'' nothing was passed open entry form to search
MsgBox ("command less")
DoCmd.OpenForm "frmEnter"
End If
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:29
Joined
May 7, 2009
Messages
19,169
maybe create a Temvars variable
Code:
Private Sub Form_Open(Cancel As Integer)
Dim varParms As Variant
If IsNull(Tempvars!tvarOpened) Then
    Tempvars.Add "tvarOpened", True

    If Len(Command) > 0 Then
    ' something was passed from link to open relevant form and required record
        MsgBox ("command greater")
        Dim logit As String
        logit = Command()
        MsgBox logit

        varParms = Split(Command, ",")
        MsgBox UBound(varParms)
        If UBound(varParms) = 1 Then
            DoCmd.OpenForm varParms(0), acNormal, , varParms(1)
        End If
    Else
        '' nothing was passed open entry form to search
        MsgBox ("command less")
        DoCmd.OpenForm "frmEnter"
    End If
Else
    DoCmd.OpenForm "frmEnter"
End If
    
End Sub
 

chizzy42

Registered User.
Local time
Today, 13:29
Joined
Sep 28, 2014
Messages
115
maybe create a Temvars variable
Code:
Private Sub Form_Open(Cancel As Integer)
Dim varParms As Variant
If IsNull(Tempvars!tvarOpened) Then
    Tempvars.Add "tvarOpened", True

    If Len(Command) > 0 Then
    ' something was passed from link to open relevant form and required record
        MsgBox ("command greater")
        Dim logit As String
        logit = Command()
        MsgBox logit

        varParms = Split(Command, ",")
        MsgBox UBound(varParms)
        If UBound(varParms) = 1 Then
            DoCmd.OpenForm varParms(0), acNormal, , varParms(1)
        End If
    Else
        '' nothing was passed open entry form to search
        MsgBox ("command less")
        DoCmd.OpenForm "frmEnter"
    End If
Else
    DoCmd.OpenForm "frmEnter"
End If
   
End Sub
nicely done, that works well. Thanks a lot for that id hit a brick wall there
 

Users who are viewing this thread

Top Bottom