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!

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

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

Perhaps part of the problem is that this code (as written) won’t quite work (though it almost will)     Access (indeed, [u]all</u> databses) require literal strings to be delimited.
This needs to either be     or     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 In your code provided you would need (unless the string, Name, already has quotes within it.)

That works great! Thanks for helping me out.

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