Cheese on a raft please...
Try this, it uses the reference to ado (see above):
Oh and it has 2 text boxes called txtPrice and txtItemNumber. Also there is a command button called cmdFetch.
VB Code:
Private Sub cmdFetch_Click()
Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim strPath As String
Dim strSQL As String
If Trim(txtItemNumber.Text) = "" Then
Beep
MsgBox "Please enter an item!"
Exit Sub
End
If Not IsNumeric(txtItemNumber.Text) Then
Beep
MsgBox "Item number must be numeric!"
Exit Sub
End If
strPath = "Path location of you database ie: C:\WinNT\Woof\"
Set adoConnection = New ADODB.Connection
adoConnection.CursorLocation = adUseServer
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data source =" & strPath & "\Inventory.mdb"
strSQL = "SELECT Price "
strSQL = strSQL & "FROM TableName "
strSQL = strSQL & "WHERE Item_Number = " & txtItemNumber.Text & " "
Set adoRecordset = adoConnection.Execute(strSQL)
With adoRecordset
If Not .EOF And Not .BOF Then
txtPrice.Text = Format(.Fields("Price"), "Currency")
Else
MsgBox "Item not found!"
End If
End With
adoRecordset.Close
Set adoRecordset = Nothing
adoConnection.Close
Set adoConnection = Nothing
End Sub
Does this work?