Simple search using ADO to mdb

[pe]

I have a vb6 frontend that uses ADO to connect to access2000 mdb. I have a tblJobNo in my mdb. I want to search for a specific jobno in the mdb and don't know where to start. Is there a sample vbp that i could look at or some code. Thanks a lot for the help.

 

[Databaseguy]

Use the seek method on a recordset object.

Public Sub SeekX()
Dim rst As ADODB.Recordset
Dim strID As String
Dim strPrompt As String
strPrompt = "Enter an EmployeeID (e.g., 1 to 9)"

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open "employees", _
                "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=c:\temp\northwind.mdb;" & _
                "user id=admin;password=;", _
                adOpenKeyset, adLockReadOnly, adCmdTableDirect

' Does this provider support Seek and Index?
If rst.Supports(adIndex) And rst.Supports(adSeek) Then
rst.Index = "EmployeeId"
' Display all the employees.
        rst.MoveFirst
        Do While rst.EOF = False
            Debug.Print rst!EmployeeID; ": "; rst!firstname; " "; _
                            rst!LastName
            rst.MoveNext
        Loop
    
' Prompt the user for an EmployeeID between 1 and 9.
        rst.MoveFirst
        Do
            strID = LCase(Trim(InputBox(strPrompt, "Seek Example")))
            ' Quit if strID is a zero-length string (CANCEL, null, etc.)
            If Len(strID) = 0 Then Exit Do
            If Len(strID) = 1 And strID >= &quot;1&quot; And strID <= &quot;9&quot; Then
                rst.Seek Array(strID), adSeekAfterEQ
                If rst.EOF Then
                    Debug.Print &quot;Employee not found.&quot;
                Else
                    Debug.Print strID; &quot;: Employee='&quot;; rst!firstname; &quot; &quot;; _
                    rst!LastName; &quot;'&quot;
                End If
            End If
        Loop
End If

rst.Close
End Sub

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[pe]

wow! thanks so much

 

[Databaseguy]

On second thought, wouldn't be easier just to use a SQL statement ?

strSQL = Select * FROM "Your Table" Where....." Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[pe]

I have a field in my tblProject table called JobNo. I'm trying this code from a command button on my form. I have an ado control on my form called adoProject which is connected to tblProject.

Private Sub cmdSearch_Click()
Dim JobNoInput As String
Dim sqlText As String

JobNoInput = InputBox("Enter Job Number: &quot
sqlText = "SELECT * FROM tblProject where JobNo=" & JobNoInput & "*"

adoProject.RecordSource = sqlText
adoProject.Caption = adoProject.RecordSource

End Sub

...but i'm missing something. When I hit the cmdSearch I want it to show that record in my tblProject on my form. How do i make the ado control go to that record and display it on my form? thanks