We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
finding item
Posted on 2000-05-15
When I enter in an item to search the PCID field with I am running into problems. If I enter in the first item in the record I get "good find" but if I enter in anything else I get "bad find". I get this even when I enter in good search item. Below is my code.
Option Explicit
'declare database variables
Public db As Database
Public rs As Recordset
Private Sub Cancel_Click()
'quits login and closes program
Unload LogInForm
End Sub
Private Sub Form_Load()
'open database
Set db = DBEngine.Workspaces(0).Ope
Set rs = db.OpenRecordset("select PCID from parts", dbOpenDynaset)
rs.MoveLast
'centers login in center of screen
Me.Top = (Screen.Height - Me.Height) / 2
Me.Left = (Screen.Width - Me.Width) / 2
'starts user in login box
LogInForm.Show
OperatorID.SetFocus
End Sub
Private Sub LogIn_Click()
If rs.RecordCount > 0 Then rs.MoveLast
rs.MoveFirst
If rs.Fields("PCID").Value = LogInForm.OperatorID.Text Then
MsgBox "good find"
Else
MsgBox "bad find"
End If
End Sub
TIA
Craig
Option Explicit
'declare database variables
Public db As Database
Public rs As Recordset
Private Sub Cancel_Click()
'quits login and closes program
Unload LogInForm
End Sub
Private Sub Form_Load()
'open database
Set db = DBEngine.Workspaces(0).Ope
Set rs = db.OpenRecordset("select PCID from parts", dbOpenDynaset)
rs.MoveLast
'centers login in center of screen
Me.Top = (Screen.Height - Me.Height) / 2
Me.Left = (Screen.Width - Me.Width) / 2
'starts user in login box
LogInForm.Show
OperatorID.SetFocus
End Sub
Private Sub LogIn_Click()
If rs.RecordCount > 0 Then rs.MoveLast
rs.MoveFirst
If rs.Fields("PCID").Value = LogInForm.OperatorID.Text Then
MsgBox "good find"
Else
MsgBox "bad find"
End If
End Sub
TIA
Craig
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
8 Comments
Private Sub LogIn_Click()
rs.FindFirst "PCID = " & LogInForm.OperatorID.Text
If not rs.NoMatch Then
MsgBox "good find"
Else
MsgBox "bad find"
End If
End Sub
I get "Data type mismatch in criteria expression"
Here is the code as you suggested:
Option Explicit
'declare database variables
Public db As Database
Public rs As Recordset
Private Sub Cancel_Click()
'quits login and closes program
Unload LogInForm
End Sub
Private Sub Form_Load()
'open database
Set db = DBEngine.Workspaces(0).Ope
Set rs = db.OpenRecordset("select PCID from parts")
rs.MoveLast
'centers login in center of screen
Me.Top = (Screen.Height - Me.Height) / 2
Me.Left = (Screen.Width - Me.Width) / 2
'starts user in login box
LogInForm.Show
OperatorID.SetFocus
End Sub
Private Sub LogIn_Click()
rs.FindFirst "PCID = " & LogInForm.OperatorID.Text
If Not rs.NoMatch Then
MsgBox "good find"
Else
MsgBox "bad find"
End If
End Sub
Here is the code as you suggested:
Option Explicit
'declare database variables
Public db As Database
Public rs As Recordset
Private Sub Cancel_Click()
'quits login and closes program
Unload LogInForm
End Sub
Private Sub Form_Load()
'open database
Set db = DBEngine.Workspaces(0).Ope
Set rs = db.OpenRecordset("select PCID from parts")
rs.MoveLast
'centers login in center of screen
Me.Top = (Screen.Height - Me.Height) / 2
Me.Left = (Screen.Width - Me.Width) / 2
'starts user in login box
LogInForm.Show
OperatorID.SetFocus
End Sub
Private Sub LogIn_Click()
rs.FindFirst "PCID = " & LogInForm.OperatorID.Text
If Not rs.NoMatch Then
MsgBox "good find"
Else
MsgBox "bad find"
End If
End Sub
Deighton's answer is correct, but if the PCID field is a string, you will need:
rs.FindFirst "PCID = '" & LogInForm.OperatorID.Text & "'"
String values in query criteria have to have single or double quotes around them.
rs.FindFirst "PCID = '" & LogInForm.OperatorID.Text & "'"
String values in query criteria have to have single or double quotes around them.
But if I enter in PCID which is the column name it come back ok, but if I enter in a value in that column no good.
TIA
Craig
TIA
Craig
Private Sub LogIn_Click()
rs.FindFirst "ucase(trim(PCID)) = '" & ucase(trim(LogInForm.Opera
If Not rs.NoMatch Then
MsgBox "good find"
Else
MsgBox "bad find"
End If
End Sub
rs.FindFirst "ucase(trim(PCID)) = '" & ucase(trim(LogInForm.Opera
If Not rs.NoMatch Then
MsgBox "good find"
Else
MsgBox "bad find"
End If
End Sub
That was it deighton! Great. could you please explain the line: rs.FindFirst "ucase(trim(PCID)) = '" & ucase(trim(LogInForm.Opera
that way I can understand better what the code is doing.
Craig
that way I can understand better what the code is doing.
Craig
When comparing strings "ABCD" can be different from "ABCD ". Trim removes any leading or trailing or leading spaces to give you a nice clean string.
ucase converts lower case to upper case. That way "ABCD" can be matched with "abcd" (no case sensitivity). You'd remove the ucase if you needed case sensitivity.
You don't necessarily need to use the trim & ucase functions.
..FindFirst simply finds the first record matching the search parameter.
..noMatch tells you when no record is found
As was mentioned earlier by PaulHews you need to get string parameters enclosed within ' ', but not with numerics.
Another idea to make it easier to see what is happening.
Private Sub LogIn_Click()
dim sscan as string
msgbox sscan
sscan = "ucase(trim(PCID)) = '" & ucase(trim(LogInForm.Opera
rs.FindFirst sscan
If Not rs.NoMatch Then
MsgBox "good find"
Else
MsgBox "bad find"
End If
End Sub
ucase converts lower case to upper case. That way "ABCD" can be matched with "abcd" (no case sensitivity). You'd remove the ucase if you needed case sensitivity.
You don't necessarily need to use the trim & ucase functions.
..FindFirst simply finds the first record matching the search parameter.
..noMatch tells you when no record is found
As was mentioned earlier by PaulHews you need to get string parameters enclosed within ' ', but not with numerics.
Another idea to make it easier to see what is happening.
Private Sub LogIn_Click()
dim sscan as string
msgbox sscan
sscan = "ucase(trim(PCID)) = '" & ucase(trim(LogInForm.Opera
rs.FindFirst sscan
If Not rs.NoMatch Then
MsgBox "good find"
Else
MsgBox "bad find"
End If
End Sub
Featured Post
In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.
What are they?
An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month9 days, 10 hours left to enroll
722 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.