Modify Code

[jdph40@yahoo.com]

In Access 97, I have a form named frmEmpList with a list box that
contains the names of all our employees. I have a command button with
the following code in the OnClick event so the form will open showing
only the record of the employee selected from the list:

Dim varSelected As Variant
Dim strSQL As String
If IsNull(Me![EmpList]) Then
MsgBox "You must select an employee's name from the list.",
vbExclamation, "NOTE"
Else
For Each varSelected In Me!EmpList.Item sSelected
strSQL = strSQL & Me!EmpList.Item Data(varSelecte d) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
Len(strSQL) - 1) & ")"
DoCmd.OpenForm "frmVacationWee ks", acViewNormal, , strSQL
End If
End If

What I'm trying to do is password protect frmVacationWeek s. I made a
Password form to prompt for a password with an unbound text box and
the following code in the OnClick event of a command button:

If Me!txtPassword = "password" Then
DoCmd.OpenForm "frmVacationWee ks"
DoCmd.Close acForm, "frmPasswor d"
Else
MsgBox "Incorrect Password", vbOKCancel
End If

Here's what happens - I have frmEmpList open, I select an employee
from the list, click the command button, and frmPassword opens. I
type in the correct password, frmPassword closes, and frmVacationWeek s
opens, but instead of showing the employee I selected from the list,
the first employee in the list shows. So, in effect, frmPassword
"interrupts " the code in the OnClick event of frmEmpList. How can I
modify the code so I can accomplish what I want to do?

By the way, I don't know a lot about VBA - someone helped me with the
code above. Thanks in advance for your help.
JD

[Pavel Romashkin]

Re: Modify Code

I think this has to do with the fact that it is not this line that
finally opens frmVacationWeek s

DoCmd.OpenForm "frmVacationWee ks", acViewNormal, , strSQL

but this one instead:

DoCmd.OpenForm "frmVacationWee ks"

and it does not pass any parameters to the form as it is being opened.
Try putting your password code in the main code module, but make sure
that "password" text box is also the part of the main form:

Dim varSelected As Variant
Dim strSQL As String
Dim bGotPwd as Boolean
bGotPwd = (Me!txtPassword = "password")
If IsNull(Me![EmpList]) Then
MsgBox "You must select an employee's name from the list.",
vbExclamation, "NOTE"
Else
For Each varSelected In Me!EmpList.Item sSelected
strSQL = strSQL & Me!EmpList.Item Data(varSelecte d) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
Len(strSQL) - 1) & ")"
if bGotPwd then _
DoCmd.OpenForm "frmVacationWee ks", acViewNormal, , strSQL else _
MsgBox "Sorry, bad password"
End If
End If

Good luck,
Pavel


"jdph40@yahoo.c om" wrote:[color=blue]
>
> In Access 97, I have a form named frmEmpList with a list box that
> contains the names of all our employees. I have a command button with
> the following code in the OnClick event so the form will open showing
> only the record of the employee selected from the list:
>
> Dim varSelected As Variant
> Dim strSQL As String
> If IsNull(Me![EmpList]) Then
> MsgBox "You must select an employee's name from the list.",
> vbExclamation, "NOTE"
> Else
> For Each varSelected In Me!EmpList.Item sSelected
> strSQL = strSQL & Me!EmpList.Item Data(varSelecte d) & ","
> Next varSelected
> If strSQL <> "" Then
> strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
> Len(strSQL) - 1) & ")"
> DoCmd.OpenForm "frmVacationWee ks", acViewNormal, , strSQL
> End If
> End If
>
> What I'm trying to do is password protect frmVacationWeek s. I made a
> Password form to prompt for a password with an unbound text box and
> the following code in the OnClick event of a command button:
>
> If Me!txtPassword = "password" Then
> DoCmd.OpenForm "frmVacationWee ks"
> DoCmd.Close acForm, "frmPasswor d"
> Else
> MsgBox "Incorrect Password", vbOKCancel
> End If
>
> Here's what happens - I have frmEmpList open, I select an employee
> from the list, click the command button, and frmPassword opens. I
> type in the correct password, frmPassword closes, and frmVacationWeek s
> opens, but instead of showing the employee I selected from the list,
> the first employee in the list shows. So, in effect, frmPassword
> "interrupts " the code in the OnClick event of frmEmpList. How can I
> modify the code so I can accomplish what I want to do?
>
> By the way, I don't know a lot about VBA - someone helped me with the
> code above. Thanks in advance for your help.
> JD[/color]

[jdph40@yahoo.com]

Re: Modify Code

Pavel, thanks for your reply. I did as you suggested, but in testing
this to see what the users would see if they click the command button
to open frmVacationWeek s without selecting an employee from the list,
instead of the message "You must select an employee's name from the
list", I get an error message "Invalid use of null". This will
confuse some users - I need for the message in the code to come up.
Any ideas?

Thanks,
JD

Pavel Romashkin <pavel_romashki n@hotmail.com> wrote in message news:<3F6F83CD. 1E86BE67@hotmai l.com>...[color=blue]
> I think this has to do with the fact that it is not this line that
> finally opens frmVacationWeek s
>
> DoCmd.OpenForm "frmVacationWee ks", acViewNormal, , strSQL
>
> but this one instead:
>
> DoCmd.OpenForm "frmVacationWee ks"
>
> and it does not pass any parameters to the form as it is being opened.
> Try putting your password code in the main code module, but make sure
> that "password" text box is also the part of the main form:
>
> Dim varSelected As Variant
> Dim strSQL As String
> Dim bGotPwd as Boolean
> bGotPwd = (Me!txtPassword = "password")
> If IsNull(Me![EmpList]) Then
> MsgBox "You must select an employee's name from the list.",
> vbExclamation, "NOTE"
> Else
> For Each varSelected In Me!EmpList.Item sSelected
> strSQL = strSQL & Me!EmpList.Item Data(varSelecte d) & ","
> Next varSelected
> If strSQL <> "" Then
> strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
> Len(strSQL) - 1) & ")"
> if bGotPwd then _
> DoCmd.OpenForm "frmVacationWee ks", acViewNormal, , strSQL else _
> MsgBox "Sorry, bad password"
> End If
> End If
>
> Good luck,
> Pavel
>
>
> "jdph40@yahoo.c om" wrote:[color=green]
> >
> > In Access 97, I have a form named frmEmpList with a list box that
> > contains the names of all our employees. I have a command button with
> > the following code in the OnClick event so the form will open showing
> > only the record of the employee selected from the list:
> >
> > Dim varSelected As Variant
> > Dim strSQL As String
> > If IsNull(Me![EmpList]) Then
> > MsgBox "You must select an employee's name from the list.",
> > vbExclamation, "NOTE"
> > Else
> > For Each varSelected In Me!EmpList.Item sSelected
> > strSQL = strSQL & Me!EmpList.Item Data(varSelecte d) & ","
> > Next varSelected
> > If strSQL <> "" Then
> > strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
> > Len(strSQL) - 1) & ")"
> > DoCmd.OpenForm "frmVacationWee ks", acViewNormal, , strSQL
> > End If
> > End If
> >
> > What I'm trying to do is password protect frmVacationWeek s. I made a
> > Password form to prompt for a password with an unbound text box and
> > the following code in the OnClick event of a command button:
> >
> > If Me!txtPassword = "password" Then
> > DoCmd.OpenForm "frmVacationWee ks"
> > DoCmd.Close acForm, "frmPasswor d"
> > Else
> > MsgBox "Incorrect Password", vbOKCancel
> > End If
> >
> > Here's what happens - I have frmEmpList open, I select an employee
> > from the list, click the command button, and frmPassword opens. I
> > type in the correct password, frmPassword closes, and frmVacationWeek s
> > opens, but instead of showing the employee I selected from the list,
> > the first employee in the list shows. So, in effect, frmPassword
> > "interrupts " the code in the OnClick event of frmEmpList. How can I
> > modify the code so I can accomplish what I want to do?
> >
> > By the way, I don't know a lot about VBA - someone helped me with the
> > code above. Thanks in advance for your help.
> > JD[/color][/color]