Solved

finding item

Posted on 2000-05-15
8
173 Views
Last Modified: 2010-05-02
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).OpenDatabase(App.Path & "\tenneco.mdb")
    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
0
Comment
Question by:csindorf
  • 4
  • 3
8 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 2810829

                    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
0
 

Author Comment

by:csindorf
ID: 2811083
Adjusted points from 50 to 60
0
 

Author Comment

by:csindorf
ID: 2811084
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).OpenDatabase(App.Path & "\tenneco.mdb")
    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
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 38

Expert Comment

by:PaulHews
ID: 2811126
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.
0
 

Author Comment

by:csindorf
ID: 2811127
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
0
 
LVL 18

Expert Comment

by:deighton
ID: 2811172
Private Sub LogIn_Click()

                        rs.FindFirst "ucase(trim(PCID)) = '" & ucase(trim(LogInForm.OperatorID.Text))  & "'"
                                               
                        If Not rs.NoMatch Then
                            MsgBox "good find"
                        Else
                            MsgBox "bad find"
                        End If
                             
                    End Sub
0
 

Author Comment

by:csindorf
ID: 2811241
That was it deighton!  Great.  could you please explain the line: rs.FindFirst "ucase(trim(PCID)) = '" & ucase(trim(LogInForm.OperatorID.Text))  & "'"

that way I can understand better what the code is doing.

Craig    
                       
0
 
LVL 18

Accepted Solution

by:
deighton earned 60 total points
ID: 2813561
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.OperatorID.Text))  &    "'"

  rs.FindFirst sscan
                                                                   
                                      If Not rs.NoMatch Then
                                              MsgBox "good find"
                                        Else
                                             MsgBox "bad find"
                                         End If
                                                 
                                        End Sub
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question