View Single Post
 
Old March 9th, 2005, 01:23 PM
me2357 me2357 is offline
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