Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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!
Reply With Quote
  #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
Reply With Quote
  #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

Reply With Quote
  #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.)
Reply With Quote
  #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.
Reply With Quote
  #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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:18 AM.


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