Run-time error 3078 (1 Viewer)

KDenham

New member
Local time
Today, 11:18
Joined
Jul 26, 2016
Messages
3
I am building an application in which I need to execute several queries on the same database table and use the RecordCount of the returned RecordSet to set values in a field of a different table.
Below is an excerpt from the code I am using:

Dim dB As Database, rst As DAO.Recordset, rstProductList as DAO.Recordset
...
Set dB = CurrentdB
Set rst = dB.OpenRecordset("tblSuppliers")
...
Do Until rstProductList.EOF = True
strItem = rstProductList!title

'Delete all records from rst prior to running a series of Make table queries to populate it with a filtered set of records from tblSuppliers
DoCmd.RunSQL "DELETE FROM rst"

'First, set each product item availability value to FALSE then check against tblSuppliers to see if any supplier can provide it
rstProductList.Edit

DoCmd.RunSQL "SELECT tblSuppliers.* INTO rst FROM tblSuppliers WHERE tblSuppliers." & strItem & " = TRUE"
rst.MoveLast
If rst.RecordCount > 0 Then
rstProductList!Available = True
Else
rstProductList!Available = False
End If
rstProductList.Update
rstProductList.MoveNext

'Clear all records from rst before running the next Make Table query
DoCmd.RunSQL "DELETE FROM rst"
Loop

I receive the run-time error on the first instance of
DoCmd.RunSQL "DELETE FROM rst"
with the message indicating the database engine cannot find the input query or table 'rst'. I've used the debugger to track the properties/values of the recordset and when I initially create it with the Set rst = dB.OpenRecordset("tblSuppliers") statement, the watch shows that rst is populated with all the fields/values of the tblSuppliers from which it was created. Why does the program not recognize the recordset when I try to delete all of its existing records before running a subsequent Make Table query command to fill it with a filtered list?
 

KenHigg

Registered User
Local time
Today, 11:18
Joined
Jun 9, 2004
Messages
13,327
I like to create the query in the query builder then copy the sql to a text string. Maybe you should try this - ?
 

KDenham

New member
Local time
Today, 11:18
Joined
Jul 26, 2016
Messages
3
Thanks, but the SQL query syntax is correct and I use it on other recordsets earlier in the program without any issues.
 

KenHigg

Registered User
Local time
Today, 11:18
Joined
Jun 9, 2004
Messages
13,327
Um... I don't recall using sql to manipulate a recordset object. Try plugging the table name in the sql instead of 'rst'.

Edit: Or you can use rst.delete to remove the current record after you utilize it...
 
Last edited:

KDenham

New member
Local time
Today, 11:18
Joined
Jul 26, 2016
Messages
3
I've figured out a solution using the FindFirst method on my Recordset which seems much more efficient, too:

Dim dB As Database, rst As DAO.Recordset, rstProductList as DAO.Recordset
...
Set dB = CurrentdB
Set rst = dB.OpenRecordset("tblSuppliers", dbOpenDynaset)
...
Do Until rstProductist.EOF = True
strItem = rstProductList!title

'Use FindFirst method on rst to see if any Supplier provides it
rstProductList.Edit

rst.FindFirst "[" & strItem & "] = TRUE"
If rst.NoMatch Then
rstProductList!Available = False
Else
rstProductList!Available = True
End If
rstProductList.Update
rstProductList.MoveNext
Loop
 

Users who are viewing this thread

Top Bottom