Adding a filter to a command button that has a security password

[Kevin]

Hi,
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?

Thanks much in advance.

 

[Svetlana]

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

 

[Svetlana]

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

 

[Kevin]

Worked perfectly!! Thanks!!

 

[Kevin]

Hi again,
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

 

[Svetlana]

Replace Me!Combo20 to
Forms!NameOfMainForm!Combo20 if the combo20 is to mainform
Forms!NameOfMainForm!NameOfSubform.Form!Combo20 if the combo20 is to
subform
and see if it works.

 

[Kevin]

Once again your advice worked perfectly!
Thanks very much!

 

[Kevin]

Hi once more Svetlana,

I have one more problem which if you could help me on I'd be very grateful.
And as a word of warning this is a very long post, but here goes:

I have a form on which there are text boxes displaying summarized info on
the Mission Reports or Memorandums of officers at my organization. I further
have 2 sets of 2 command buttons each, one of which opens a PDF version of
the Mission Report or memo respectively and the second which opens the folder
on the hard drive containing all mission report,etc.
These mission reports/ Memoranda are related to international organizations.
I have therefore created two linked combo boxes, the first of which displays
the list of Int. orgs. and the second the mission report or memo relating to
the organization selected in the first combo box. This is the code and the
Row Source SQL I'm using for the combo boxes:

(abrev2 is the name of the column in my table containg the short name of the
Intl Org. / nameIO is the column with the full name. the first combo box has
2 columns):

The first combo box:
Code:

Private Sub Combo99_AfterUpdate()
Me!Combo107 = Null
Me!Combo107.Requery
Me.Filter = "nameIO = '" & Me.Combo99 & "'"
Me.FilterOn = True
End Sub

Row Source SQL: SELECT DISTINCT [IOs4 Query].abrev2, [IOs4 Query.nameIO] AS
Expr1 FROM [IOs4 Query] GROUP BY [IOs4 Query].abrev2, [IOs4 Query.nameIO]
ORDER BY [IOs4 Query].abrev2, [IOs4 Query.nameIO];

and the second:
Code:
Private Sub Combo107_AfterUpdate()
Me.Filter = "MissionRepDesc = '" & Me.Combo107 & "'"
Me.FilterOn = True
End Sub

Row Source SQL: SELECT DISTINCTROW [IOs4 Query].MissionRepDesc FROM [IOs4
Query] WHERE ((([IOs4 Query].nameIO) Like [forms]![IO Mission
Reports]![combo99]));

Ok, my problem now is adding new mission reports / memos and in a way that
the update is reflected in the second combo box. My Edit form here for some
reason isn't filtering according to the Intl Org selected, even though I have
the same code as for the country-based forms:

If strPasswd = "Garfield" Then
DoCmd.OpenForm "Edit Mission Reports", acNormal
Forms![Edit Mission Reports].Filter = "abrev2 = '" & Me!Combo99 & "'"
Forms![Edit Mission Reports].FilterOn = True

Also, when I make entries in the edit form they're saved in the table, but
don't appear in the second combo box. Do you know how I could resolve this?
I'd be very, very grateful if you could help on this one too.

 

[Svetlana]

Hey Kevin

Try this

If strPasswd = "Garfield" Then
DoCmd.OpenForm "Edit Mission Reports", acNormal
Forms![Edit Mission Reports].Filter = "abrev2 = '" &
Me!Combo99.Column(0) & "'"

'Add this line to check the filter then remove it when it works the
way you want
MsgBox Forms![Edit Mission Reports].Filter

Forms![Edit Mission Reports].FilterOn = True
End If

On AfterUpdate event of your Edit form add
Forms!NameOfFirstForm!Combo107.Requery

 

[Kevin]

Hi Svetlana,

Thanks for your reply! I'm still having two issues though: 1st, with the
filter on the edit form: it filters according to the Organization chosen in
the first combo box (Combo99), but it doesn't go to the specific mission
report selected from the second combo box (Combo107). So if I choose say the
'ABEDA' in the first combo box and then a mission report called say 'Bosnia'
(which would be the 3rd report in the list, the first two being 'Pakistan'
and 'Canada' respectively) the edit form opens at 'Pakistan' and then I'd
have to move forward to 'Bosnia' with the navigation buttons. How could I fix
this?
Secondly, when I click the 'add a new record' button from the navigation
buttons and make a new entry, say 'France', it shows up the second menu on
the main form but all the missio report choices disappear and 'France' is now
the only choice. How can I correct this?

I forgot to mention before that on the edit form I have a macro on the
OnClick event which opens it and not code on the AfterUpdate event. The Macro
is as follows:
Action: Open Form
Form Name: Edit Mission Reports
View: Form
Filter Name: IOs4 Query
Where Condition: [IOs4 Query]![abrev2]=[Forms]![IO Mission Reports]![abrev2]
Data Mode: Edit
Window Mode: Normal

On the Data tab of the edit form the Control Source is: IOs4 Query,

and the Filter is: ((((IOs4.abrev2)="ABEDA" Or (IOs4.abrev2)="[ACP Group]" )))

I inserted the code you suggested for the AfterUpdate event while leaving
this Macro in place (and which is probably why I'm still having problems). As
you can doubtless tell I'm not very proficient with VBA coding and so if you
could help me through this last patch it would be great and once again much
appreciated.

Thanks in advance

 

[Svetlana]

...you have a button that opens the "Edit Mission Reports" form
after you enter a password..

If im saying it right then

On click event of your button put the code

Dim strPasswd As String
strPasswd = InputBox("Enter Password", "Password?")

If strPasswd<>"" Then
If strPasswd = "Garfield" Then

Dim strCriteria As String
strCriteria="abrev2=""" & Me!Combo99.Column(0) & """" & _
" AND MissionRepDesc = """ & Me.Combo107.column(0) & """"

DoCmd.OpenForm "Edit Mission Reports", acNormal, , strCriteria

End If
End If

The code must do what the macro does so you dont need the macro.
Try it to a copy of your form before delete it forever.
I am not proficient with VBA coding too so you better be carefull..

On AfterUpdate event of that form where you add the new entry that you
wanna see
in the dropdown list of combo107 put the code

Private Sub Form_AfterUpdate()

Forms!NameOfFormWhereTheComboIs!Combo107.Requery

End Sub

Do you have problems after add a new record on which form?
You many need to remove your filters for see all the entries again.

 

[Kevin]

Hi Svetlana,

Thanks again for your help. Been sick the last few days so I'm just looking
at your sugestion now.

However, I have yet another problem (actually two, but I"ll stick to one
here) I'd like to ask you to help me with, I know this is getting to be a lot
now but I have to make a major presentation to my bosses in a few days and I
really need help here!

Ok, The database I'm working on I've actually "inherited" from someone else
and on the switchboard they put in check boxes that filtered the selection of
countries on the main form when you click the "Execute" command button.

The code behind the command button is as follows:
Private Sub ComExec_Click()
On Error GoTo Err_ComExec_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Form_MainForm2.cbsig1.Value = True Then
CurrentDb.QueryDefs("Experimental_Query2").SQL = "SELECT [table 1].*
FROM [table 1] WHERE ((([table 1].annex2)=Yes Or ([table
1].annex2)=[Forms]![MainForm2]![annex2CB]) AND (([table 1].region) Like
[Forms]![MainForm2]![ComboRegion]) AND (([table 1].[IO participation]) Like
[Forms]![MainForm2]![ComboIO]) AND (([table 1].signature) Is Not Null) AND
(([table 1].ratification) Is Not Null));"
ElseIf Form_MainForm2.cbsig2.Value = True Then
CurrentDb.QueryDefs("Experimental_Query2").SQL = "SELECT [table 1].*
FROM [table 1] WHERE ((([table 1].annex2)=Yes Or ([table
1].annex2)=[Forms]![MainForm2]![annex2CB]) AND (([table 1].region) Like
[Forms]![MainForm2]![ComboRegion]) AND (([table 1].[IO participation]) Like
[Forms]![MainForm2]![ComboIO]) AND (([table 1].signature) Is Not Null) AND
(([table 1].ratification) Is Null));"
ElseIf Form_MainForm2.cbsig3.Value = True Then
CurrentDb.QueryDefs("Experimental_Query2").SQL = "SELECT [table 1].*
FROM [table 1] WHERE ((([table 1].annex2)=Yes Or ([table
1].annex2)=[Forms]![MainForm2]![annex2CB]) AND (([table 1].region) Like
[Forms]![MainForm2]![ComboRegion]) AND (([table 1].[IO participation]) Like
[Forms]![MainForm2]![ComboIO]) AND (([table 1].signature) Is Null) AND
(([table 1].ratification) Is Null));"
Else
CurrentDb.QueryDefs("Experimental_Query2").SQL = "SELECT [table 1].*
FROM [table 1] WHERE ((([table 1].annex2)=Yes Or ([table
1].annex2)=[Forms]![MainForm2]![annex2CB]) AND (([table 1].region) Like
[Forms]![MainForm2]![ComboRegion]) AND (([table 1].[IO participation]) Like
[Forms]![MainForm2]![ComboIO]));"
End If

stDocName = "experimental_form1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ComExec_Click:
Exit Sub

Err_ComExec_Click:
MsgBox Err.Description
Resume Exit_ComExec_Click
End Sub

Originally this worked, but then I merged this database with two other
Access ones by importing all the forms, tables and so on inot one database.
I also expanded this part of the database by adding new new columns to the
"table 1" form, which is the main underlying form for this portion of the
database. Since then I've been unable to get the check boxes to work. There
are four of them, one for "annex 2 countries"; one for "Treaty ratified"; one
for "Treaty Signed only"; and one for "Not Signed Yet". Now when none of the
boxes are selected all 197 countries in the database are displayed and there
is a text box which displays the number. When one or more of the boxes are
checked and the execute command button is clicked the countries are filtered
according to the selection made.
I've been knocking my head against the wall trying to figure out how exactly
this code works but to no avail. Would you have any idea what I've done wrong
to make it stop working? or at least what does what in this code?

Thanks again for reading another long post from me, but I deeply appreciate
any help you can provide.

 

[Svetlana]

The code changes the sql statement for Experimental_Query2 according
the choices of the user then opens experimental_form1 (that has
probably as record source the Experimental_Query2?)
What you mean it doesnt work? Do you get any error message? Have you
changed any of the original names and data types?
When you saying you merged the database with two other Access ones you
mean that you created two same ones? Do Experimental_Query2, [table 1]
exist in the current database?
You can check if and how your code is executed line by line putting
toggle breakpoints between the lines of the code.

 

[Kevin]

Hi Svetlana,
Thanks for the explanation. The code works as is, but my problem is that I
need to add one more country (Montenegro) to the list of countries in the
"Table 1" table. I've done this by recopying into the table a list of
countries with Montenegro included, but the addition is not reflected in
Experimental_Query2 which has the following SQL:

SELECT [table 1].*
FROM [table 1]
WHERE ((([table 1].annex2)=Yes Or ([table
1].annex2)=[Forms]![MainForm2]![annex2CB]) AND (([table 1].region) Like
[Forms]![MainForm2]![ComboRegion]) AND (([table 1].[IO participation]) Like
[Forms]![MainForm2]![ComboIO]));

Also, in the "Combo20" combo box on the "experimental_Form1" form,
Montenegro doesn't work, ie the text boxes do not change to display the info
for that country when it is selected. As well, on this form there is a text
box which displays the number of countries according to the check boxes
ticked on the "Main Form2" before, but which shows for example 133 countries
( which have ratified) when the number should be 137. The expresion behiond
it is this: "=Count(

)".

I'll check the code by putting in the toggle breakpoints as you sugested,
but if you understand what's happeing here that'd be great.

I merged the databases by copying all the forms, tables, queries, etc of
each database into the database view page. So Experimental_Query2,  and
[table 1] exist in the current database together.

Thank you so much again for your time and help! Really appreciate it!

 

[Svetlana]

Kevin, open your query in design view and check the property MaxRecords
and see if there is a limited number of returning records. If yes then
delete it and leave the property box clear. Same for your combobox. Go
into row source property which opens the sql statement in design view
and check MaxRecords property.

I can't think anything else right now but i suggest you to make a new
post with your problem so someone else to help you further on this.