Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 8th, 2005, 03:31 AM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Function to Find a Record

I feel bad posting because I'm sure this topic has been covered, but I'm pretty new to both Visual Basic and Access, os other than browsing several index pages, I'm not sure where to look for my answer.

I'm parsing lots of text files and placing the data into several tables using a VBA script. One of the tables is for people and another is for events. Each event has several different people and each person associates with several events and each have unique record IDs. So, when I'm making a new event record, I want to take the name I've parsed and see if it exists in the people table. If it does, I want this function to return the person's ID number.

I'm having trouble specifically with the person lookup function, so any help anyone could give to get me started would be appreciated. Thanks!
  #2 (permalink)  
Old March 9th, 2005, 12:52 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   A simple way to do this (assuming you are properly accessing the database) is to do something like the following:

'==========
Set cnn = Connection
Set rs = Recordset
'...
stName = "John Doe"
stSQL = "SELECT * FROM tblYourTable WHERE Name = " & stName
rs.Open stSQL, cnn, 3, 3
   If rs.RecordCount = 1 Then
      With rs
      intNameID = rs.Fields(0) 'this is usually the PK field #
      End With
   End If
rs.Close
cnn.Close
'...
'==========

   You really have to make sure that your Names are unique, which is very difficult, unless you are dealing with a small group of people.

HTH

mmcdonal
  #3 (permalink)  
Old March 9th, 2005, 01:23 PM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your post. I've adapted your code to my database, but it is giving me a nice vague error: "No value given for one or more required parameters" and points me to the rs.Open line. My table is named "Athletes" and has 5 columns, including AthleteID and LastName.


Private Sub MyButton_Click()
     MsgBox GetAthleteID("Doe", "ABC")
End Sub

Function GetAthleteID(Name As String, TeamAbbr As String)
    'Look up name in Athlete table and return ID number
    Dim rsAthlete As ADODB.Recordset
    Dim cnSwim As ADODB.Connection
    Set cnSwim = New ADODB.Connection
    cnSwim.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\data\carpers\My Documents\swimDb\swimDb.mdb"
    Set rsAthlete = New ADODB.Recordset
    '...
    stSQL = "SELECT * FROM Athletes WHERE LastName = " & Name
    rsAthlete.Open stSQL, cnSwim, 3, 3
    If rsAthlete.RecordCount = 1 Then
        With rsAthlete
        GetAthleteID = rsAthlete.Fields(0)
        End With
    Else
        GetAthleteID = -1
    End If
    rsAthlete.Close
    cnSwim.Close
End Function

  #4 (permalink)  
Old March 9th, 2005, 04:29 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Perhaps part of the problem is that this code (as written) won’t quite work (though it almost will)
Code:
    stName = "John Doe"
    stSQL = "SELECT * FROM tblYourTable WHERE Name = " & stName
    Access (indeed, [u]all</u> databses) require literal strings to be delimited.
This needs to either be
Code:
    stName = """John Doe"""
    stSQL = "SELECT * FROM tblYourTable WHERE Name = " & stName
    or
Code:
    stName = "John Doe"
Code:
    stSQL = "SELECT * FROM tblYourTable WHERE Name = """ & stName & """"
    When VB is in literal mode, interpreting a string, and it hits a pair of doublequotes, it adds 1 double quote to the string. So either of those snippets provided will create
Code:
    SELECT * FROM tblYourTable WHERE Name = "John Doe"
In your code provided
Code:
    stSQL = "SELECT * FROM Athletes WHERE LastName = " & Name
you would need
Code:
    stSQL = "SELECT * FROM Athletes WHERE LastName = """ & Name & """"
(unless the string, Name, already has quotes within it.)
  #5 (permalink)  
Old March 9th, 2005, 05:35 PM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That works great! Thanks for helping me out.
  #6 (permalink)  
Old March 10th, 2005, 03:22 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

BrianWren,

   You are right about the stSQL variable. I had pasted that from a script that was calling an integer. I use the following in Access for strings:

'=====
stSQL = "SELECT * FROM tblYourTable WHERE Name = " & "'" & stName & "'"
'=====

   Indeed, this is how the wizards write it when passing a string. I have never used """. I'll give that a try.




mmcdonal


Similar Threads
Thread Thread Starter Forum Replies Last Post
use SQL to find a record linmu VB.NET 2002/2003 Basics 4 May 6th, 2005 05:03 PM
Can't find record, causes Access to crash mmcdonal Access 2 December 3rd, 2004 02:51 PM
Find a record based on alue specified myth12345 VB.NET 2002/2003 Basics 1 November 16th, 2004 02:06 AM
Find Record Use Combo Box martinaccess Access 2 October 17th, 2004 07:42 AM
Find out missing record hari-kumar-vadakkeveedu SQL Server 2000 3 October 15th, 2004 01:09 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.