I don't use ADO, only DAO. Here is a DAO sub to try:
Code:
Private Sub Searchbutt_DAO_Click()
Dim rst As DAO.Recordset
Dim sSQL As String
sSQL = "SELECT * FROM products WHERE productid =""" & Me!ProductId & """"
Set rst = CurrentDb.OpenRecordset(sSQL)
If rst.BOF And rst.EOF Then
MsgBox "Not Found", , "Test"
'this is where you would add code to add the product to the table.
Else
' Me!ProductId = rst.Fields("Productid")
MsgBox "found product" & rst.Fields("Productid"), , "test"
End If
'clean up
rst.Close
Set rst = Nothing
End Sub
And here is my attempt at ADO, based on an example from a page from Allen Browne's web site:
Code:
Private Sub Searchbutt_ADO_Click()
Dim myrecordset As New ADODB.Recordset
Dim strSql As String
strSql = "SELECT * from products;"
myrecordset.Open strSql, CurrentProject.Connection
'this is assuming that there will always be records in the recordset.
'need
' an error handler or
' to check recordset record count or
' to check for myrecordset.BOF and myrecordset.EOF
myrecordset.MoveFirst
myrecordset.Find "productid = """ & Me!ProductId & """"
If Not myrecordset.EOF Then
' Me!ProductId = myrecordset.Fields("Productid")
MsgBox "found product" & rst.Fields("Productid"), , "test"
Else
MsgBox "Not Found", , "Test"
'this is where you would add code to add the product to the table.
End If
'clean up
rs.Close
Set rs = Nothing
End Sub