Open form listed in Text Box

[Guest]

I have a form being used as a menu. I'd like a command button to open which
ever form is listed in a text box. The text box is not visible and is
populated once the user clicks an employee's name in a list box. the DLookup
function is then used to find the form (menu) corresponding to that employee.

Instead of:
DoCmd.OpenForm "frmAaron"
I'd like something like:
DoCmd.OpenForm text60
Unfortuantely, that doesn't work.

Can anyone help me?

 

[Jeff Boyce]

Doesn't work ...

Do you mean "nothing happens"?

Do you mean "I get an error message"? (if so, tell us -- it's hard to
diagnose without it)

Do you mean "my PC turns into a pumpkin and explodes"?

One thing you could try would be to add a MsgBox() command just before the
DoCmd.OpenForm text60 command. In the msgbox, have it display the value
that is in text60. This way, you'd know if the OpenForm command is
receiving anything to open.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I don't know how to write the code to recognize the value in in the text box
"text60" to open the form named there or to display it in a msgbox.

The error message I'm getting with: DoCmd.OpenForm text60

Runtime error '2102'
The form name 'text60' is misspelled or refers to a form that doesn't exist.
I also tried text60.value and got an error also.

 

[Jeff Boyce]

If I take the error message literally (always an iffy prospect with Access
error messages!), Access is telling you that it doesn't have a form named
[text60].

You already have code to open the form. Add the following line of code just
before the OpenForm command...

MsgBox "text60 = " & Me!text60

If you have a control named "text60" on your form, and if the code you
provided is running in that form, the the message box will pop up, showing
you what is in the control named "text60".

How are you putting the name of the form into that control?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I appreciate your time.
Yes, the message box worked. It recognized and displayed the data in text60
on the form.
What I'm trying to accomplish is to have only one button that will open
differnt forms depending on the name found in text60. That way I won't end
up with fifty buttons on the form. Text60 uses DLookup to find the name of
the form corresponding to the employee name selected from a listbox on the
form.

Below is the current entire code for the button to open the form.

Private Sub Command62_Click()

'Check to see if data is entered into the UserName list box

If IsNull(Me.Text24) Or Me.Text24 = "" Then
MsgBox "You must choose a User Name.", vbOKOnly, "Required Data"
Me.Text24.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Valid Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this
'matches value chosen in combo box

'If Me.txtPassword.Value = DLookup("EmpPassword", "tblEmployees", "[EmpID]="
& Me.Text24.Value & " And [EmpName]='" & Me.Text34.Value & "'") Then

'lngMyEmpID = Me.Text24.Value

If Me.txtPassword.Value = DLookup("EmpPassword", "tblEmployees", "[EmpID]="
& Me.Text24.Value & " And [EmpName]='" & Me.Text34.Value & "'") Then

lngMyEmpID = Me.Text24.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo

'THE NEXT LINE OF CODE IS WHERE I'D LIKE TO REFER TO TEXT60 INSTEAD OF
CODING IN A SPECIFIC FORM NAME

DoCmd.OpenForm "frmClyde"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.",
vbCritical, "Restricted Access!"
Application.Quit
End If



End Sub

If I take the error message literally (always an iffy prospect with Access
error messages!), Access is telling you that it doesn't have a form named
[text60].

You already have code to open the form. Add the following line of code just
before the OpenForm command...

MsgBox "text60 = " & Me!text60

If you have a control named "text60" on your form, and if the code you
provided is running in that form, the the message box will pop up, showing
you what is in the control named "text60".

How are you putting the name of the form into that control?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't know how to write the code to recognize the value in in the text
box
"text60" to open the form named there or to display it in a msgbox.

The error message I'm getting with: DoCmd.OpenForm text60

Runtime error '2102'
The form name 'text60' is misspelled or refers to a form that doesn't
exist.
I also tried text60.value and got an error also.

 

[Jeff Boyce]

This isn't the same approach that you've been using, but maybe it would
work for you...

I'm assuming that you have a table somewhere that connects a user/employee
to a form (do I understand you correctly? you have a separate form for EACH
employee?! that must make for a lot of maintenance!).

Instead of DLookup and all the extra work, consider giving your users a
combo box to pick their name from. In the query that you use to fill that
combo box, include the "Start Here Form" field, but set the width of that
column to 0, so it doesn't show in the drop-down.

Then, after the user picks him/herself in the combo box, and after s/he
successfully passes the password portion, you can automatically open the
form by referring to the hidden (width=0) column in the combo box.

Use the .Column(n) property, but remember that .Column() is zero-based --
you start counting columns (in your combo box) as "0, 1, 2, ..."

Instead of forcing the user to click a button, you could add code after the
password test that looks something like:

DoCmd.OpenForm Me!YourEmployeeComboBoxName.Column(2)

(you'd use your own field names and your own combo box's query to determine
the correct column number - remember, zero-based!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I appreciate your time.
Yes, the message box worked. It recognized and displayed the data in
text60
on the form.
What I'm trying to accomplish is to have only one button that will open
differnt forms depending on the name found in text60. That way I won't
end
up with fifty buttons on the form. Text60 uses DLookup to find the name
of
the form corresponding to the employee name selected from a listbox on the
form.

Below is the current entire code for the button to open the form.

Private Sub Command62_Click()

'Check to see if data is entered into the UserName list box

If IsNull(Me.Text24) Or Me.Text24 = "" Then
MsgBox "You must choose a User Name.", vbOKOnly, "Required Data"
Me.Text24.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Valid Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this
'matches value chosen in combo box

'If Me.txtPassword.Value = DLookup("EmpPassword", "tblEmployees",
"[EmpID]="
& Me.Text24.Value & " And [EmpName]='" & Me.Text34.Value & "'") Then

'lngMyEmpID = Me.Text24.Value

If Me.txtPassword.Value = DLookup("EmpPassword", "tblEmployees",
"[EmpID]="
& Me.Text24.Value & " And [EmpName]='" & Me.Text34.Value & "'") Then

lngMyEmpID = Me.Text24.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo

'THE NEXT LINE OF CODE IS WHERE I'D LIKE TO REFER TO TEXT60 INSTEAD OF
CODING IN A SPECIFIC FORM NAME

DoCmd.OpenForm "frmClyde"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.",
vbCritical, "Restricted Access!"
Application.Quit
End If



End Sub

If I take the error message literally (always an iffy prospect with
Access
error messages!), Access is telling you that it doesn't have a form named
[text60].

You already have code to open the form. Add the following line of code
just
before the OpenForm command...

MsgBox "text60 = " & Me!text60

If you have a control named "text60" on your form, and if the code you
provided is running in that form, the the message box will pop up,
showing
you what is in the control named "text60".

How are you putting the name of the form into that control?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't know how to write the code to recognize the value in in the text
box
"text60" to open the form named there or to display it in a msgbox.

The error message I'm getting with: DoCmd.OpenForm text60

Runtime error '2102'
The form name 'text60' is misspelled or refers to a form that doesn't
exist.
I also tried text60.value and got an error also.

:

Doesn't work ...

Do you mean "nothing happens"?

Do you mean "I get an error message"? (if so, tell us -- it's hard to
diagnose without it)

Do you mean "my PC turns into a pumpkin and explodes"?

One thing you could try would be to add a MsgBox() command just before
the
DoCmd.OpenForm text60 command. In the msgbox, have it display the
value
that is in text60. This way, you'd know if the OpenForm command is
receiving anything to open.

Regards

Jeff Boyce
Microsoft Office/Access MVP



I have a form being used as a menu. I'd like a command button to
open
which
ever form is listed in a text box. The text box is not visible and
is
populated once the user clicks an employee's name in a list box.
the
DLookup
function is then used to find the form (menu) corresponding to that
employee.

Instead of:
DoCmd.OpenForm "frmAaron"
I'd like something like:
DoCmd.OpenForm text60
Unfortuantely, that doesn't work.

Can anyone help me?

 

[Guest]

Thank you for all your time. You're correct about the table /
user/Employee....
I did get this to work by adding the code below in place of the plain DoCmd.
It recognizes the value in text60 and opens the corresponding form.

I will rethink the entie setup when I can. I'm learning better methods all
the time thanks to people like you - and will taking a VB class starting next
week.

Dim stDocname As String
Dim StLinkCriteria As String

stDocname = [Text60]
DoCmd.OpenForm stDocname, , , StLinkCriteria
DoCmd.Close acForm, "frmLogon", acSaveNo

This isn't the same approach that you've been using, but maybe it would
work for you...

I'm assuming that you have a table somewhere that connects a user/employee
to a form (do I understand you correctly? you have a separate form for EACH
employee?! that must make for a lot of maintenance!).

Instead of DLookup and all the extra work, consider giving your users a
combo box to pick their name from. In the query that you use to fill that
combo box, include the "Start Here Form" field, but set the width of that
column to 0, so it doesn't show in the drop-down.

Then, after the user picks him/herself in the combo box, and after s/he
successfully passes the password portion, you can automatically open the
form by referring to the hidden (width=0) column in the combo box.

Use the .Column(n) property, but remember that .Column() is zero-based --
you start counting columns (in your combo box) as "0, 1, 2, ..."

Instead of forcing the user to click a button, you could add code after the
password test that looks something like:

DoCmd.OpenForm Me!YourEmployeeComboBoxName.Column(2)

(you'd use your own field names and your own combo box's query to determine
the correct column number - remember, zero-based!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I appreciate your time.
Yes, the message box worked. It recognized and displayed the data in
text60
on the form.
What I'm trying to accomplish is to have only one button that will open
differnt forms depending on the name found in text60. That way I won't
end
up with fifty buttons on the form. Text60 uses DLookup to find the name
of
the form corresponding to the employee name selected from a listbox on the
form.

Below is the current entire code for the button to open the form.

Private Sub Command62_Click()

'Check to see if data is entered into the UserName list box

If IsNull(Me.Text24) Or Me.Text24 = "" Then
MsgBox "You must choose a User Name.", vbOKOnly, "Required Data"
Me.Text24.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Valid Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this
'matches value chosen in combo box

'If Me.txtPassword.Value = DLookup("EmpPassword", "tblEmployees",
"[EmpID]="
& Me.Text24.Value & " And [EmpName]='" & Me.Text34.Value & "'") Then

'lngMyEmpID = Me.Text24.Value

If Me.txtPassword.Value = DLookup("EmpPassword", "tblEmployees",
"[EmpID]="
& Me.Text24.Value & " And [EmpName]='" & Me.Text34.Value & "'") Then

lngMyEmpID = Me.Text24.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo

'THE NEXT LINE OF CODE IS WHERE I'D LIKE TO REFER TO TEXT60 INSTEAD OF
CODING IN A SPECIFIC FORM NAME

DoCmd.OpenForm "frmClyde"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.",
vbCritical, "Restricted Access!"
Application.Quit
End If



End Sub

If I take the error message literally (always an iffy prospect with
Access
error messages!), Access is telling you that it doesn't have a form named
[text60].

You already have code to open the form. Add the following line of code
just
before the OpenForm command...

MsgBox "text60 = " & Me!text60

If you have a control named "text60" on your form, and if the code you
provided is running in that form, the the message box will pop up,
showing
you what is in the control named "text60".

How are you putting the name of the form into that control?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I don't know how to write the code to recognize the value in in the text
box
"text60" to open the form named there or to display it in a msgbox.

The error message I'm getting with: DoCmd.OpenForm text60

Runtime error '2102'
The form name 'text60' is misspelled or refers to a form that doesn't
exist.
I also tried text60.value and got an error also.

:

Doesn't work ...

Do you mean "nothing happens"?

Do you mean "I get an error message"? (if so, tell us -- it's hard to
diagnose without it)

Do you mean "my PC turns into a pumpkin and explodes"?

One thing you could try would be to add a MsgBox() command just before
the
DoCmd.OpenForm text60 command. In the msgbox, have it display the
value
that is in text60. This way, you'd know if the OpenForm command is
receiving anything to open.

Regards

Jeff Boyce
Microsoft Office/Access MVP



I have a form being used as a menu. I'd like a command button to
open
which
ever form is listed in a text box. The text box is not visible and
is
populated once the user clicks an employee's name in a list box.
the
DLookup
function is then used to find the form (menu) corresponding to that
employee.

Instead of:
DoCmd.OpenForm "frmAaron"
I'd like something like:
DoCmd.OpenForm text60
Unfortuantely, that doesn't work.

Can anyone help me?