Command button filter stops working when put in a subform

[Kevin]

Hi again Svetlana (and anyone else who can help!),
I've put my form with the edit button as a subform in my database. When I
click the edit button and enter my password a run-time error message (2465)
appears saying that Access can't find the field 'Combo20' referred to in my
expression and then when I click on debug opens the code window with the line

Forms![Edit Quick info].Filter = "country = '" & Me!Combo20 & "'"

highlighted in yellow, so effectively the filter that's been put in is
lost. Do you know what the problem could be here?
Thanks in advance

Kevin

:

Try instead of ME to put
Forms![Edit Quick info].Filter = "country = '" & Me!Combo20 & "'"
Forms![Edit Quick info].FilterOn = True

:
I have a form with locked fields. In order to select individual records for
194 countries I use a combo box. To update this form I use another form with
unlocked fields which is opened with a command button with a Macro behind it.
I also use the follwing expression to filter this Edit form:

[ctbto table 1]![country]=[Forms]![experimental_form1]![country]

This all works fine, but I wanted to add a password to the command button
which opens the Edit form to restrict users' ability to edit the form. I
found the following code which creates a password function:

Private Sub Command354_Click()
'Attached to On Click event of cmdOpenEmpForm

Dim strPasswd

strPasswd = InputBox("Enter Password", "Restricted Form")

'Check to see if there is any entry made to input box, or if
'cancel button is pressed. If no entry made then exit sub.

If strPasswd = "" Or strPasswd = Empty Then
MsgBox "No Input Provided", vbInformation, "Required Data"
Exit Sub
End If

'If correct password is entered open Edit form
'If incorrect password entered give message and exit sub

If strPasswd = "graham" Then
DoCmd.OpenForm "Edit Quick info", acNormal
Me.Filter = "country = '" & Me.Combo20 & "'"
Me.FilterOn = True

Else
MsgBox "Sorry, you do not have access to this form", _
vbOKOnly, "Important Information"
Exit Sub
End If

End Sub

My question is: what code can I add to filter the Edit form? I've found that
I have to remove the Macro hwich also removes the filter. I've tried using
"Me.Filter..." as shown above, but without success. What can I do?

 

[TonyT]

Hi Kevin,

Use the fully justified control name when reffering between main form and
sub form;
use
Forms!MainformName.ControlName 'for the main form
and
Forms!MainFormName.SubFormName.SubFormControlName 'for the subforms control
so assuming the filter is on the main form and combo20 the subform use;
Forms![Edit Quick info].Filter = "country = '" & Forms![Edit Quick
Info].INSERTSubFormNameHere.Combo20 & "'"

or vice-versa if the filter is on the subform and combo20 the main form.

Hope this helps,

TonyT..

Hi again Svetlana (and anyone else who can help!),
I've put my form with the edit button as a subform in my database. When I
click the edit button and enter my password a run-time error message (2465)
appears saying that Access can't find the field 'Combo20' referred to in my
expression and then when I click on debug opens the code window with the line

Forms![Edit Quick info].Filter = "country = '" & Me!Combo20 & "'"

highlighted in yellow, so effectively the filter that's been put in is
lost. Do you know what the problem could be here?
Thanks in advance

Kevin

:

Try instead of ME to put
Forms![Edit Quick info].Filter = "country = '" & Me!Combo20 & "'"
Forms![Edit Quick info].FilterOn = True

:
I have a form with locked fields. In order to select individual records for
194 countries I use a combo box. To update this form I use another form with
unlocked fields which is opened with a command button with a Macro behind it.
I also use the follwing expression to filter this Edit form:

[ctbto table 1]![country]=[Forms]![experimental_form1]![country]

This all works fine, but I wanted to add a password to the command button
which opens the Edit form to restrict users' ability to edit the form. I
found the following code which creates a password function:

Private Sub Command354_Click()
'Attached to On Click event of cmdOpenEmpForm

Dim strPasswd

strPasswd = InputBox("Enter Password", "Restricted Form")

'Check to see if there is any entry made to input box, or if
'cancel button is pressed. If no entry made then exit sub.

If strPasswd = "" Or strPasswd = Empty Then
MsgBox "No Input Provided", vbInformation, "Required Data"
Exit Sub
End If

'If correct password is entered open Edit form
'If incorrect password entered give message and exit sub

If strPasswd = "graham" Then
DoCmd.OpenForm "Edit Quick info", acNormal
Me.Filter = "country = '" & Me.Combo20 & "'"
Me.FilterOn = True

Else
MsgBox "Sorry, you do not have access to this form", _
vbOKOnly, "Important Information"
Exit Sub
End If

End Sub

My question is: what code can I add to filter the Edit form? I've found that
I have to remove the Macro hwich also removes the filter. I've tried using
"Me.Filter..." as shown above, but without success. What can I do?

 

[Kevin]

Hi TonyT,

I've been given a solution pretty similar to yours already, but thanks very
much for your help! Really appreciate it!

Hi Kevin,

Use the fully justified control name when reffering between main form and
sub form;
use
Forms!MainformName.ControlName 'for the main form
and
Forms!MainFormName.SubFormName.SubFormControlName 'for the subforms control
so assuming the filter is on the main form and combo20 the subform use;
Forms![Edit Quick info].Filter = "country = '" & Forms![Edit Quick
Info].INSERTSubFormNameHere.Combo20 & "'"

or vice-versa if the filter is on the subform and combo20 the main form.

Hope this helps,

TonyT..

Hi again Svetlana (and anyone else who can help!),
I've put my form with the edit button as a subform in my database. When I
click the edit button and enter my password a run-time error message (2465)
appears saying that Access can't find the field 'Combo20' referred to in my
expression and then when I click on debug opens the code window with the line

Forms![Edit Quick info].Filter = "country = '" & Me!Combo20 & "'"

highlighted in yellow, so effectively the filter that's been put in is
lost. Do you know what the problem could be here?
Thanks in advance

Kevin

:

Try instead of ME to put
Forms![Edit Quick info].Filter = "country = '" & Me!Combo20 & "'"
Forms![Edit Quick info].FilterOn = True

:
I have a form with locked fields. In order to select individual records for
194 countries I use a combo box. To update this form I use another form with
unlocked fields which is opened with a command button with a Macro behind it.
I also use the follwing expression to filter this Edit form:

[ctbto table 1]![country]=[Forms]![experimental_form1]![country]

This all works fine, but I wanted to add a password to the command button
which opens the Edit form to restrict users' ability to edit the form. I
found the following code which creates a password function:

Private Sub Command354_Click()
'Attached to On Click event of cmdOpenEmpForm

Dim strPasswd

strPasswd = InputBox("Enter Password", "Restricted Form")

'Check to see if there is any entry made to input box, or if
'cancel button is pressed. If no entry made then exit sub.

If strPasswd = "" Or strPasswd = Empty Then
MsgBox "No Input Provided", vbInformation, "Required Data"
Exit Sub
End If

'If correct password is entered open Edit form
'If incorrect password entered give message and exit sub

If strPasswd = "graham" Then
DoCmd.OpenForm "Edit Quick info", acNormal
Me.Filter = "country = '" & Me.Combo20 & "'"
Me.FilterOn = True

Else
MsgBox "Sorry, you do not have access to this form", _
vbOKOnly, "Important Information"
Exit Sub
End If

End Sub

My question is: what code can I add to filter the Edit form? I've found that
I have to remove the Macro hwich also removes the filter. I've tried using
"Me.Filter..." as shown above, but without success. What can I do?