WHERE clause in the Set command of an db.OpenRecordset method

[Bubba002]

I currently have code working to create a report on an entire database capturing all the changes in the current (live) database from a historical (aged) table of the same database. The problem I’m having is that as the day goes on, the report becomes less useful, as it too is “aged”.

I currently have a new subform on a tab that shows what I want. What I want to do is have my code executed only on the records linked to the primary key of the main form.

I understand that it's faster to open a new Recordset object by using an SQL statement that includes a WHERE clause.

After doing some digging, I still can’t seem to correctly apply the WHERE clause in the Set command of an db.OpenRecordset method.

Below is part of my code showing my definition of what I need in a WHERE clause. It works fine with the WHERE clause commented out. Any ideas of how I can get my recordsets filtered with a WHERE clause?

On Error GoTo Err_CompareTables
Dim db As Database
Dim rstBase As Recordset
Dim rstVarying As Recordset
Dim rstFiltered As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim FieldChanged As Boolean
Dim ErrorMessage As String

Set db = CurrentDb

Set rstBase = db.OpenRecordset(BaseTableQuery)
' WHERE [qryIS-1.Link1] = [subfrmIS-1c].[Form]![qryIS-1.Link1]

Set rstVarying = db.OpenRecordset(VaryingTableQuery)
' WHERE [qryIS-1.Link1] = [subfrmIS-1c].[Form]![qryIS-1.Link1]

Set tdf = db.TableDefs(BaseTable)

db.TableDefs.Delete "tblCmprLoop"
db.Execute ("CREATE TABLE tblCmprLoop (RecordNumber TEXT(255), FieldName TEXT(255), NewText TEXT(255), OldText TEXT(255));")

 

[dhookom]

I would build an SQL statement for the recordset.

'this is partial code and assumes Link1 is text rather than numeric.
Dim strBase as String
Set db = CurrentDb
strBase = "SELECT * FROM … WHERE Link1 = """ & [subfrmIS-1c].[Form]![qryIS-1].[Link1] & """
Debug.Print strBase
Set rstBase = db.OpenRecordset(strBase)

…