473,287 Members | 1,492 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,287 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 9294
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. ...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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)...

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.