Results 1 to 2 of 2
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Retrivng a value using DAO (Access 2000 )

    Since SubjectStudyID is a text field, you must put quotes around the value; you can use Chr(34) for this. You also want to avoid setting varCPH if there was no match.

    ' Find record that matches study ID#
    rst.FindFirst "SubjectStudyID = " & Chr(34) & Me.txtSubjectID & Chr(34)
    If rst.NoMatch Then
    MsgBox "No Subject Matches"
    ' Save matching CPHID #
    varCPH = rst!CPHID
    End If

  2. #2
    New Lounger
    Join Date
    Jul 2003
    Thanked 0 Times in 0 Posts

    Retrieving a value using DAO (Access 2000 )

    I wish to open a table using DAO, Find a matching record to one put in by the user, and retrive a different value from that record. In the below code, Enrollment is the table, SubjectStudyID is the field I wish to match (with user input), and txtSubjectID is the txtbox which the user is entering on the form. When I run this code, with putting say "ABC123" as the subject ID, I get an error message saying "ABC123 is not a valid field name". I can't seem to get past this error. (if I get past this error) Is the line of code 'varCPH = rst!CPHID" a valid way to extract the value from the matching record contained in the field CPHID?
    Thank you for your help!

    Private Sub cmdOpenByStudyID_Click()
    ' Declare variables
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varCPH As Integer
    ' Set error handling
    On Error GoTo ErrHandler

    ' Set' object variables
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Enrollment", dbOpenDynaset)

    ' Find record which matchs study ID#
    rst.FindFirst "SubjectStudyID = " & Me.txtSubjectID
    If rst.NoMatch Then
    MsgBox "No Subject Matches"
    End If
    ' Save matching CPHID #
    varCPH = rst!CPHID

    ' Clean up and get out
    On Error Resume Next
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub
    ' Inform user, then clean up
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts