Access/VBA Conditional Statement

[Shaldaman]

I have an Access form called "Login". "Command10" is a button on the
form and "Text5" is a text field on it. When a user enters a value in
the Text5 text field and clicks the button Command10, I want my
function to check if the value in Text5 matches any value of
InstructorID in the Instructor table. If yes, I would like the control
to switch to another form called "Existing Instructor" else I'd like to
generate a message to the user that the "instructor doesn't exist"(the
Else part i'm not concerned about right now). When I enter a value in
Text5 that I know is in the table already(for testing purposes), and
click on the Command10 button, I get an error "Object Required". Is
there something wrong with my conditional statement? What am I doing
wrong? Please help...This is the function that's called when the
button's clicked:
Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Existing Instructor"

If (Forms![Login].Text5 = Instructor![InstructorID]) Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click
End Sub

[fredg]

On 6 Aug 2005 17:50:33 -0700, Shaldaman wrote:

I have an Access form called "Login". "Command10" is a button on the
form and "Text5" is a text field on it. When a user enters a value in
the Text5 text field and clicks the button Command10, I want my
function to check if the value in Text5 matches any value of
InstructorID in the Instructor table. If yes, I would like the control
to switch to another form called "Existing Instructor" else I'd like to
generate a message to the user that the "instructor doesn't exist"(the
Else part i'm not concerned about right now). When I enter a value in
Text5 that I know is in the table already(for testing purposes), and
click on the Command10 button, I get an error "Object Required". Is
there something wrong with my conditional statement? What am I doing
wrong? Please help...This is the function that's called when the
button's clicked:

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Existing Instructor"

If (Forms![Login].Text5 = Instructor![InstructorID]) Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click
End Sub

You can't reference a field in a table that way.
I assume [InstructorID] is a Number datatype.

If DCount("*","Instructor","[InstructorID] = " & Me!Text5) > 0 Then
' that ID is in the table.

I notice you are using stLinkCriteria but nowhere in this code do you
give it a value.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Shaldaman]

Hi Fred
It seemed to work. Thanks a lot. I'm now trying to use the DCount
function to check for 2 variables. You're right - InstructorID is a
numeric datatype. I'm also checking to see if the text datatype
InstructLastName entered on Text7 text field of the form by the user
matches a value in the table. So this is the conditional statement I
used. However I'm getting a Type Mismatch error.

If DCount("*", "Instructor", "[InstructorID] = " & Me!Text5 And
"[InstructLastName] = " & Me!Text7) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "Instructor ID and Last Name do not match. Please try again"
End If

Please advise. The stLinkCriteria seems to be getting generated when i
create a button that will open another form. It's strue - I'm not using
it anywhere else in the code. It's declared earlier in the function but
it's just used as a dummy variable. Any help would be appreciated.
thanks
Shal

[Shaldaman]

I realized my mistake in the conditional statement - it was checking to
see if InstructorID = Me!Text5 and the rest of the characters I typed.
So I changed my conditional statement to read:

If DCount("*", "Instructor", "[InstructorID] = " & Me!Text5) > 0 And
DCount("*", "Instructor", "[InstructLastName] = " & Me!Text7) > 0 Then

But now, I'm getting the following error : You canceled the previous
operation.

Any help would be greatly appreciated because I'm really stuck at this
stage.

thanks
Shal

[Thelma Lubkin]

Shaldaman <sh************@gmail.com> wrote:
: I realized my mistake in the conditional statement - it was checking to
: see if InstructorID = Me!Text5 and the rest of the characters I typed.
: So I changed my conditional statement to read:

: If DCount("*", "Instructor", "[InstructorID] = " & Me!Text5) > 0 And
: DCount("*", "Instructor", "[InstructLastName] = " & Me!Text7) > 0 Then

: But now, I'm getting the following error : You canceled the previous
: operation.

: Any help would be greatly appreciated because I'm really stuck at this
: stage.

This error appears when there's a mismatch of types -- is the
InstructorID field a numeric? If so, define a variable with
the value of Me!Text5, but of the same type as the field, and
use that variable for the first = statement. Also, you can
combine both conditions into one Dcount operation.
--thelma

: thanks
: Shal

[fredg]

On 7 Aug 2005 19:23:43 -0700, Shaldaman wrote:

Hi Fred
It seemed to work. Thanks a lot. I'm now trying to use the DCount
function to check for 2 variables. You're right - InstructorID is a
numeric datatype. I'm also checking to see if the text datatype
InstructLastName entered on Text7 text field of the form by the user
matches a value in the table. So this is the conditional statement I
used. However I'm getting a Type Mismatch error.

If DCount("*", "Instructor", "[InstructorID] = " & Me!Text5 And
"[InstructLastName] = " & Me!Text7) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "Instructor ID and Last Name do not match. Please try again"
End If

Please advise. The stLinkCriteria seems to be getting generated when i
create a button that will open another form. It's strue - I'm not using
it anywhere else in the code. It's declared earlier in the function but
it's just used as a dummy variable. Any help would be appreciated.
thanks
Shal

InstructorID may be a Number datatype, but [LastName] is text, so you
have to use quotes around that value.

Are you trying to see if the InstructorID and [Last Name] are in the
same record, i.e. [InstructorID] = 5 and [Last Name] = 'Smith' ?

If DCount("*", "Instructor", "[InstructorID] = " & Me!Text5 & " And
[InstructLastName] = '" & Me!Text7 & "'") > 0 Then

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Shaldaman]

Hi Fred
Thanks a lot, man. It worked. I'd posted a reply a week ago but it
didn't show up. I have another quick question. Let's say there are 2
Instructors, Joe and Shal with InstructorID 60 and 70 respectively. My
validation works, that is, with the conditional statement I have now,
the user goes from the Login Form to the Existing Instructor Form if
both InstructorID and InstructorLastName match.

Here's the catch. When I enter 60 and Joe on the Login Form, it takes
me to the Existing Instructor Form showing all the information for that
instructor, namely Joe. But when I enter 70 and Shal, on the Login
Form, it takes me to the Existing Instructor Form but it shows all the
information for the first record(InstructorID = 60, InstructorLastName
= Joe). How do I synchronize the forms so that when a certain
InstructorID is entered on the Login form, the corresponding
InstructorID and other information from that record are displayed on
the Existing Instructor Form? Sorry for being such a nuisance but this
is probably my biggest issue and I've been grappling with it for
weeks..

Thanks
Shal

[Shaldaman]

Never mind, gang. I managed to figure it out. I was trying all kinds of
convoluted ways to do it, including using Recordsets as recommended by
the Microsoft Office Assistance website. It turned out to be a simple
solution:

I specified the criteria in my DoCmd.OpenForm command. Thanks everybody
for helping out.

Shal