DoCmd.OpenForm problem

[Guest]

I am using the followiing in the OnOpen Event of a form designated as the
Startup form when the database opens. The record source for the form is a
simple table with a field named user and a field named form. Me.txtUser has
a default value pulling the user name from an environment variable.

Problem is trying to get DoCmd.OpenForm [Forms]![Startup].[txtForm],
acNormal to work. txtForm is the field form in the record source. I want
the form named in this field to open and evidently my syntax is wrong.

Dim USER As DAO.Recordset
Set USER = Me.RecordsetClone

USER.FindFirst "[User] = '" & Me.txtUSER & "'"

If USER.NoMatch Then

MsgBox "No Match", vbOKOnly
DoCmd.OpenForm "Green", acNormal

Else

MsgBox "Match"
DoCmd.OpenForm [Forms]![Startup].[txtForm], acNormal

End If

Can anyone resolve this? Thank you.

 

[Allen Browne]

1. User is a reserved word in JET.
Try replacing it with rsUser.

Note that Form is also a reserved word in Access according to:
http://support.microsoft.com/?id=286335
so it would be a good idea to rename your table and field as well.
(Make sure Name AutoCorrect is off before you do this.)

2. You found a record in the form's RecordsetClone, and tested NoMatch, but
then you used the value of the textbox on the form. The text box does not
have that record: it's just found in the RecordsetClone. Try:
DoCmd.OpenForm rsUser![Form]

 

[Guest]

Thank you Sir .. . in my haste I just put together a simple little thing to
get the code working and didn't even think of the reserved words. Works like
a charm now. I appreciate your help!
--
Jeff C
Live Well .. Be Happy In All You Do

1. User is a reserved word in JET.
Try replacing it with rsUser.

Note that Form is also a reserved word in Access according to:
http://support.microsoft.com/?id=286335
so it would be a good idea to rename your table and field as well.
(Make sure Name AutoCorrect is off before you do this.)

2. You found a record in the form's RecordsetClone, and tested NoMatch, but
then you used the value of the textbox on the form. The text box does not
have that record: it's just found in the RecordsetClone. Try:
DoCmd.OpenForm rsUser![Form]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am using the followiing in the OnOpen Event of a form designated as the
Startup form when the database opens. The record source for the form is a
simple table with a field named user and a field named form. Me.txtUser
has
a default value pulling the user name from an environment variable.

Problem is trying to get DoCmd.OpenForm [Forms]![Startup].[txtForm],
acNormal to work. txtForm is the field form in the record source. I want
the form named in this field to open and evidently my syntax is wrong.

Dim USER As DAO.Recordset
Set USER = Me.RecordsetClone

USER.FindFirst "[User] = '" & Me.txtUSER & "'"

If USER.NoMatch Then

MsgBox "No Match", vbOKOnly
DoCmd.OpenForm "Green", acNormal

Else

MsgBox "Match"
DoCmd.OpenForm [Forms]![Startup].[txtForm], acNormal

End If

Can anyone resolve this? Thank you.