By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,797 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

Modify Code

P: n/a
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.ItemsSelected
strSQL = strSQL & Me!EmpList.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
Len(strSQL) - 1) & ")"
DoCmd.OpenForm "frmVacationWeeks", acViewNormal, , strSQL
End If
End If

What I'm trying to do is password protect frmVacationWeeks. 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 "frmVacationWeeks"
DoCmd.Close acForm, "frmPassword"
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 frmVacationWeeks
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I think this has to do with the fact that it is not this line that
finally opens frmVacationWeeks

DoCmd.OpenForm "frmVacationWeeks", acViewNormal, , strSQL

but this one instead:

DoCmd.OpenForm "frmVacationWeeks"

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.ItemsSelected
strSQL = strSQL & Me!EmpList.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
Len(strSQL) - 1) & ")"
if bGotPwd then _
DoCmd.OpenForm "frmVacationWeeks", acViewNormal, , strSQL else _
MsgBox "Sorry, bad password"
End If
End If

Good luck,
Pavel
"jd****@yahoo.com" wrote:

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.ItemsSelected
strSQL = strSQL & Me!EmpList.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
Len(strSQL) - 1) & ")"
DoCmd.OpenForm "frmVacationWeeks", acViewNormal, , strSQL
End If
End If

What I'm trying to do is password protect frmVacationWeeks. 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 "frmVacationWeeks"
DoCmd.Close acForm, "frmPassword"
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 frmVacationWeeks
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

Nov 12 '05 #2

P: n/a
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 frmVacationWeeks 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 <pa*************@hotmail.com> wrote in message news:<3F***************@hotmail.com>...
I think this has to do with the fact that it is not this line that
finally opens frmVacationWeeks

DoCmd.OpenForm "frmVacationWeeks", acViewNormal, , strSQL

but this one instead:

DoCmd.OpenForm "frmVacationWeeks"

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.ItemsSelected
strSQL = strSQL & Me!EmpList.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
Len(strSQL) - 1) & ")"
if bGotPwd then _
DoCmd.OpenForm "frmVacationWeeks", acViewNormal, , strSQL else _
MsgBox "Sorry, bad password"
End If
End If

Good luck,
Pavel
"jd****@yahoo.com" wrote:

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.ItemsSelected
strSQL = strSQL & Me!EmpList.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
Len(strSQL) - 1) & ")"
DoCmd.OpenForm "frmVacationWeeks", acViewNormal, , strSQL
End If
End If

What I'm trying to do is password protect frmVacationWeeks. 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 "frmVacationWeeks"
DoCmd.Close acForm, "frmPassword"
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 frmVacationWeeks
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.