Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Walnut Cove, NC

    Unanswered: Search box on a form

    I have a Search box on my form that shows 3 fields and is bound to the first field which is the primary key and an auto number. I called it Rec_ID. This is the Select statement for the Search box:
    SELECT Licenses_Certifications.Rec_ID, Licenses_Certifications.Employee_Number, Licenses_Certifications.License_Cert_Type FROM Licenses_Certifications;

    When the record to pull up in the form is highlighted, I have an After Update routine that runs:

    Private Sub cboSearch_AfterUpdate()

    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic

    rst.Open "Select * FROM Licenses_Certifications where (Str(Rec_ID))= '" & Me!cboSearch & "'"
    Open "h:\test.txt" For Output As #31
    Print #31, RecordCount, (Str(Rec_ID)), Employee_Number, Expiration_Date, License_Cert_Type, License_Number, Original_Issue_Date, Renewal_Date
    Close #31
    If rst.RecordCount > 0 Then
    cbo_Employee_Number = rst("Employee_Number")
    txt_Expiration_Date = rst("Expiration_Date")
    txt_License_Cert_Type = rst("License_Cert_Type")
    txt_License_Number = rst("License_Number")
    txt_Original_Issue = rst("Original_Issue_Date")
    txt_Renewal_Date = rst("Renewal_Date")
    End If
    End Sub

    When this code runs, my fields on the form are not populated with the data from the selected record because Rec_ID = empty from the select statement. I added the print statement so I could see what was being selected. The Recordcount = "empty", the REC_ID = "empty", the rest of the fields have the data from the first record in the table. I have looked at this code for hours and changed things around so much I have run out of ideas. Please help!



  2. #2
    Join Date
    Sep 2003
    Couple of things:

    ADO recordsets DO NOT have a valid RecordCount UNLESS the CursorLocation is a server-side cursor

    ADO Recordsets DO NOT automatically start you on the 1st record in the result set ... You have to move to the 1st record.
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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