473,320 Members | 1,854 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access/VBA Conditional Statement

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

Nov 13 '05 #1
7 9296
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.
Nov 13 '05 #2
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

Nov 13 '05 #3
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

Nov 13 '05 #4
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

Nov 13 '05 #5
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.
Nov 13 '05 #6
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

Nov 13 '05 #7
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

Nov 13 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: neblackcat | last post by:
Would anyone like to comment on the following idea? I was just going to offer it as a new PEP until it was suggested that I post it here for comment & consideration against PEP 308. I'm far...
3
by: Jouke Langhout | last post by:
Hello all! For quite some time now, I've got the following problem: Access won't close properly when a user closes the application. An ACCESS process stays active and that process can only be...
5
by: Richard | last post by:
Hi, I have a form that take some time to load due to many comboboxes and at least 8 subforms. When I filter or sort the main form I get an error message and then Access shuts down. They ask if...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
0
by: Shaldaman | last post by:
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...
4
by: mux | last post by:
Hi I found out that the following piece of code throws an error. 1 #include "stdio.h" 2 3 int main() 4 { 5 int a,b; 6 a= 10;
9
by: Marty | last post by:
Hi, Does using the the conditional operator (?:) instead of the common "if" statement will give a performance gain in a C# .NET 2003 application (even in C# .NET 2005?). What is the advantage...
5
by: paulo | last post by:
Can anyone please tell me how the C language interprets the following code: #include <stdio.h> int main(void) { int a = 1; int b = 10; int x = 3;
43
by: dev_cool | last post by:
Hello friends, I'm a beginner in C programming. One of my friends asked me to write a program in C.The purpose of the program is print 1 to n without any conditional statement, loop or jump. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.