Recordset problem 1

[fogal]

I have a problem in the following code where I reach set rst2........

The problem is that the recordset may contain no records, i.e not even a blank record, because it is based on a complex query. When the module reached this point in code I get error "Too few parameters. Expected 2." If rst2 contained a normal blank tabel or query it would work fine. How could I get around this??????

Dim dbs As Database
Dim rst, rst2, rst3 As Recordset
Dim strsql, strsql2, strsql3, strsql4 As String
Set dbs = CurrentDb
strsql = "SELECT * FROM qryAdCollectionDuplicateAds"
strsql2 = "SELECT * FROM tblAdCollectionFormDuplicateAds"
Set rst = dbs.OpenRecordset(strsql2, dbOpenDynaset, dbSeeChanges)
Do Until rst.EOF
With rst
rst.Delete
End With
rst.MoveNext
Loop
rst.Close
Set rst2 = dbs.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)
Do Until rst2.EOF
With rst2
strsql3 = rst2(0)
strsql4 = rst2(1)
Set rst3 = dbs.OpenRecordset(strsql2, dbOpenDynaset, dbSeeChanges)
With rst3
rst3.AddNew
rst3(0) = strsql3
rst3(1) = strsql4
End With
rst3.Update
rst3.Close
End With
rst2.MoveNext
Loop
rst2.Close
Set dbs = Nothing

 

[PJStephenson]

Hi fogal,
Try testing your recordset, use something like:

if rst2.BOF AND rst2.EOF then
MsgBox "There are no entries in this recordset"
Else
Your Code
End if

HTH
Peter

 

[fogal]

Unfortunately, the error occurs when rst2 is set, so the above is not possible.

 

[nicsin]

From the error message I understand that there is a problem with the query! try using a simple select query to see what is happening.

 

[PJStephenson]

Ok, a couple of things I would look at
1. Change your first recordset loop to a delete query. It should remove some complexity from your script.
2. I assume your second recordset is taking its data from a query. Try replacing the select query in your code with the SQL from behind the query. Does your query take filters from a form control?
3. Check the spelling of your strsql string.
nicsin is probably right about the cause
HTH
Peter

 

[JeremyNYC]

Yow! And use strsql for rst and strsql2 for rst2, not the other way around!

Also, you're having a problem running a sql statement and you wnat help with it, but you haven't posted the sql here. We want to help, but you have to give us more to work with. Clearly there are a couple of parameters in that query, and they are missing. But more than that, we can't tell you without more information.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[fogal]

Guys,

This is a copy of the sql statement
i.e strsql = "SELECT * FROM qryAdCollectionDuplicateAds"

I will try to replace strsql2 with this in the code:
A simple select statement works fine with my code, I have tested this. My code just doesn't work with the below query, but I know this query works okay as a stand alone query, in the queries window.


SELECT tblAdvDuplicates.AdvertID, 1 AS AlreadyDuplicate
FROM (tblAdvDuplicates INNER JOIN tblAdverts ON tblAdvDuplicates.DuplID = tblAdverts.AdvertID) INNER JOIN tblAdverts AS tblAdverts_1 ON tblAdvDuplicates.AdvertID = tblAdverts_1.AdvertID
WHERE (((tblAdverts.PublID)=[Forms]![frmAdvertQueries]![PublicationID]) AND ((tblAdverts_1.PublID)=[Forms]![frmAdvertQueries]![RenewFrom]))
GROUP BY tblAdvDuplicates.AdvertID;

 

[nicsin]

fogal,

what controls are

PublicationID

and

RenewFrom

? Can you confirm that they have a value?

 

[fogal]

Publication Id has a value, but renewfrom only has a value sometimes. Hence, in this example, I would expect the query to return 0 records not even a blank record. But this is where the code crashes when I set rst2.

 

[fogal]

However, even if renewfrom has a value the same error code is returned. i.e when both publication id and renewfrom have values. But now if i run the query directly from the query window, the query returns a result set, so I know the query is working fine.

 

[nicsin]

continuing PJStephenson's second suggestion,

replace

strsql2 = "SELECT * FROM tblAdCollectionFormDuplicateAds"

with

strsql2 = "SELECT " & _
"  tblAdvDuplicates.AdvertID, " & _
"  1 AS AlreadyDuplicate " & _
"FROM " & _
"  (tblAdvDuplicates INNER JOIN tblAdverts ON tblAdvDuplicates.DuplID = tblAdverts.AdvertID) " & _
"                    INNER JOIN tblAdverts AS tblAdverts_1 ON tblAdvDuplicates.AdvertID = tblAdverts_1.AdvertID " & _
"WHERE " & _
"      tblAdverts.PublID = " & [Forms]![frmAdvertQueries]![PublicationID] & _
"  AND tblAdverts_1.PublID = " & [Forms]![frmAdvertQueries]![RenewFrom] & _
"GROUP BY " & _
"  tblAdvDuplicates.AdvertID"

 

[nicsin]

Well, I got confused with strsql and strsql2 but I believe you get my point...