Confusion between two routines

[Guest]

Hi, I have this routine that works perfect but I Think that isn’t the most
efficient because I am using the sql=… to fill the recordset and after the
recordsetclone and RecordCount, I Tried to use the set rst =
db.openrecordset(“Select …..) It doesn’t produce any error but just bring all
the records, It doesn’t care about the condition Where….. Like…) if someone
can help me I would appreciated.

Thanks


IT WORKS

Private Sub Form_Open(Cancel As Integer)
Dim Sql As String, rst As DAO.Recordset


Sql = "SELECT tblComputers.AssetTag, tblComputers.ManufacturerID, " & _
" tblComputers.DateReceived, tblComputers.PurchasePrice,
tblComputers.Warranty, tblComputers.EmployeeID " & _
"FROM tblComputers WHERE AssetTag LIKE '" & Forms.frmComputers.Text12 & "*'"

Forms!frmQueryComputers.RecordSource = Sql
Set rst = Forms!frmQueryComputers.RecordsetClone

If rst.RecordCount = 0 Then
MsgBox "The Asset tag code or part of the code not in database"
rst.Close
DoCmd.Close
End If
End Sub


IT DOESN’T WORK

Private Sub Form_Open(Cancel As Integer)
Dim Sql As String, rst As DAO.Recordset, db As DAO.Database

'Set db = DBEngine(0)(0)

Set rst = db.OpenRecordset("SELECT tblComputers.AssetTag,
tblComputers.ManufacturerID, " & _
" tblComputers.DateReceived, tblComputers.PurchasePrice,
tblComputers.Warranty, tblComputers.EmployeeID " & _
"FROM tblComputers WHERE AssetTag LIKE '" & Forms.frmComputers.Text12 &
"*'")

If rst.RecordCount = 0 Then
MsgBox "The Asset tag code or part of the code not in database"
rst.Close
DoCmd.Close
End If
End Sub

 

[AccessVandal via AccessMonster.com]

Er...

Maybe it needs...

Set db = CurrentDb ?

Orlando wrote:
IT DOESN’T WORK

Private Sub Form_Open(Cancel As Integer)
Dim Sql As String, rst As DAO.Recordset, db As DAO.Database

'Set db = DBEngine(0)(0)

Set rst = db.OpenRecordset("SELECT tblComputers.AssetTag,
tblComputers.ManufacturerID, " & _
" tblComputers.DateReceived, tblComputers.PurchasePrice,
tblComputers.Warranty, tblComputers.EmployeeID " & _
"FROM tblComputers WHERE AssetTag LIKE '" & Forms.frmComputers.Text12 &
"*'")

If rst.RecordCount = 0 Then
MsgBox "The Asset tag code or part of the code not in database"
rst.Close
DoCmd.Close
End If
End Sub