DAO Openrecordset Error

[Jeff Sievenpiper]

Good day,

I am trying to use a DAO Recordset with a runtime SQL expression. However,
when I try and code the Openrecordset model, instead of 'source' (where the
SQL string goes) I get 'Name as String'. When I run the code I get an error
stating the object "<my SQL expression>" could not be found.

I have referenced the Microsoft DAO 3.6 and it is only one of three
references for this database, the other two being VBA for application and the
Access 11.0 references.

I would greatly appreciate any assistance on getting this SQL expression to
work for this recordset.

Code snippet is as follows:

Option Compare Database
Public rst As DAO.Recordset, dbs As DAO.Database, strSQL As String

Private Sub NewItem_Click()
On Error GoTo Err_NewItem_Click


'DoCmd.GoToRecord , , acNewRec
Set dbs = CurrentDb
'On Error Resume Next
strSQL = "SELECT * from PIMBody WHERE PIMNumber = """ &
Forms![PIM]![PIMNumber].Value & """"
Set rst = dbs.OpenRecordset(strSQL, dbOpenTable)

If rst.RecordCount = 0 Then
On Error GoTo Err_NewItem_Click
Set rst = dbs.OpenRecordset("PIMBody", dbOpenTable)
rst.AddNew
rst![PIMNumber].Value = Forms![PIM].[PIMNumber].Value
rst![ItemNumber].Value = 1
rst![Status].Value = "A"
rst.Update
Forms![PIM]![PIMBodyQuery subform].Requery
Else

rst.MoveFirst
rst.MoveLast

Forms![PIM]![PIMBodyQuery subform].[ItemNumber].Value =
(rst![ItemNumber].Value + 1)
Forms![PIM]![PIMBodyQuery subform].[ItemNumber].SetFocus
End If

Exit_NewItem_Click:
Exit Sub

Err_NewItem_Click:
MsgBox Err.Description
Resume Exit_NewItem_Click

End Sub

 

[Ken Snell [MVP]]

You're trying to use the dbOpenTable option for the recordset when you're
basing the recordset on a query. Won't work. Try this:

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

dbOpenTable is only for tables that are in the same database (not linked);
and only for tables, not for queries.

 

[Jeff Sievenpiper]

Thanks Ken. I really appreciate your help. Worked great!

You're trying to use the dbOpenTable option for the recordset when you're
basing the recordset on a query. Won't work. Try this:

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

dbOpenTable is only for tables that are in the same database (not linked);
and only for tables, not for queries.

--

Ken Snell
<MS ACCESS MVP>

Good day,

I am trying to use a DAO Recordset with a runtime SQL expression.
However,
when I try and code the Openrecordset model, instead of 'source' (where
the
SQL string goes) I get 'Name as String'. When I run the code I get an
error
stating the object "<my SQL expression>" could not be found.

I have referenced the Microsoft DAO 3.6 and it is only one of three
references for this database, the other two being VBA for application and
the
Access 11.0 references.

I would greatly appreciate any assistance on getting this SQL expression
to
work for this recordset.

Code snippet is as follows:

Option Compare Database
Public rst As DAO.Recordset, dbs As DAO.Database, strSQL As String

Private Sub NewItem_Click()
On Error GoTo Err_NewItem_Click


'DoCmd.GoToRecord , , acNewRec
Set dbs = CurrentDb
'On Error Resume Next
strSQL = "SELECT * from PIMBody WHERE PIMNumber = """ &
Forms![PIM]![PIMNumber].Value & """"
Set rst = dbs.OpenRecordset(strSQL, dbOpenTable)

If rst.RecordCount = 0 Then
On Error GoTo Err_NewItem_Click
Set rst = dbs.OpenRecordset("PIMBody", dbOpenTable)
rst.AddNew
rst![PIMNumber].Value = Forms![PIM].[PIMNumber].Value
rst![ItemNumber].Value = 1
rst![Status].Value = "A"
rst.Update
Forms![PIM]![PIMBodyQuery subform].Requery
Else

rst.MoveFirst
rst.MoveLast

Forms![PIM]![PIMBodyQuery subform].[ItemNumber].Value =
(rst![ItemNumber].Value + 1)
Forms![PIM]![PIMBodyQuery subform].[ItemNumber].SetFocus
End If

Exit_NewItem_Click:
Exit Sub

Err_NewItem_Click:
MsgBox Err.Description
Resume Exit_NewItem_Click

End Sub

 

[Jess Martyn]

Hello,

Recently, http://extremedesigners.5u.com - A website about web designing -
has been launched. I would like you to take a look at it and pass your
suggestions - complements - about it.

- Regards,
Extreme Designing Team.