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)

…

 

[hermanlaksko]

Try this
Sub Test()
Dim SQL As String, Re As DAO.Recordset
SQL = CurrentDb.QueryDefs("YourQueryName").SQL
'The "SQL" will have the "string" from your query and you can add the where string or other things as you please.
'Also be aware that the last caracter of the sql string will be a ";" this must be removed before adding the where-string
'Like so:
SQL = Left(SQL, Len(SQL) - 3)
SQL=SQL & " WHERE....."
Set Re = CurrentDb.OpenRecordset(SQL)
End Sub

 

[Bubba002]

Thanks dhookom and hermanlaksko for your suggestions! I'll have time later today to work on them. I'll let you know what happens.

​

 

[Bubba002]

I've updated my code with dhookom's suggestion to just build an SQL statement for the recordset. I caught some referencing errors and fixed them. But, now I get the error 'External name not defined' when the execution gets to line with first reference of [subfrmIS-1a].

To clarify my querys and forms:
[qryIS-1] is my current live database of [tblLoop] (parent) and [tblInstr] (child). [Link1] is the primary key.
[tblqryIS-1c] is a table made on a particular date of [qryIS-1]. This is my historical or 'aged' data.
[qryIS-1a] is a link of [qryIS-1] and [tblqryIS-1c] based on [Link1]. This shows live data. The text is red if live data differs from aged data.

My main comparison form is [frmIS-1c] which has 4 tabs.
[subfrmIS-1a] is my live data on tab 1.
[subfrmIS-3-Chng] is on tab 2. The data on this subform is linked to a table populated by the code I'm trying to fix.

My plan is to run a macro that calls the new code when the tab 2 is clicked. I expect the time to execute the populating code to be very fast as it will only be on one record of [subfrmIS-1a] at a time. Then, it will show the "Current" and "Aged" data for that record for comparison when tab 2 is clicked.

I'm not sure why [subfrmIS-1a] is not recognized. It is listed in the Microsoft Access Class Objects on the left panel. Looking at my code below, can you tell why [subfrmIS-1a] is not recognized? Is there something I need to do make it recognized?

Option Compare Database
Option Explicit
'--------------------------------
'Updated by GSC 11/19/24 --------
'   Set recordset to current instrument
'   Create a table named 'tblCmprLoop' to populate with new and old data
'--------------------------------
Function CompareLoop1()
       Call CompareLoop("BaseComparisonTable", "Link1", "qryBase", "qryVarying")
End Function

Sub CompareLoop(BaseTable As String, PrimaryKeyField As String, _
                BaseTableQuery As String, VaryingTableQuery As String)

'---  parameters  ---------------
'   BaseTable: the table that is considered the 'base', that is, the one considered aged
'   PrimaryKeyField: the primary key for both tables
'   qryBase: an ordered query based on the 'base' table
'   qryVarying: an ordered query based on the table being compared to the 'base'
'--------------------------------

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

Set db = CurrentDb

strBase = "SELECT * FROM db WHERE Link1 = """ & [subfrmIS-1a].[Form]![qryIS-1].[Link1] & """"
Debug.Print strBase
Set rstBase = db.OpenRecordset(strBase)

strVarying = "SELECT * FROM db WHERE Link1 = """ & [subfrmIS-1a].[Form]![tblqryIS-1c].[Link1] & """"
Debug.Print strVarying
Set rstBase = db.OpenRecordset(strVarying)

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));")

rstBase.MoveFirst
rstVarying.MoveFirst

My code below this point works for the entire database.

Any suggestions are appreciated.

 

[dhookom]

Where to begin… Your sql has FROM db which is a database object and should be a table or query. If subfrmIS-1a is a subform you need to reference it as a control on the main form.

There may be other issues but these are the most obvious.

 

[Bubba002]

Thanks dhookom,
I have fixed the sql to FROM [qryIS-1a]. [qryIS-1a] is the data that my subform [subfrmIS-1a] is based on.

Using the Expression Builder I added a control on my main form for the subform with its Control Source as
=[subfrmIS-1a].[Form]![qryIS-1]![Link1] but I only get #Name? as the result. I also tried
=[Me]![subfrmIS-1a].[Form]![qryIS-1]![Link1] with the same #Name? result.

Everything else in main form and [subfrmIS-1a] is working. However, [subfrmIS-1a] is still not recognized in code. What am I missing?

 

[dhookom]

What form or subform hosts the code? Every expression on the page I linked to begins with either "FORM" or "Me." When referencing a subform, keep in mind the sorcerer object and control name might not be the same.

 

[Bubba002]

The code is in a module. Tab 2 has subfrmIS-3-Chng. The On Enter event for subfrmIS-3-Chng calls a macro which runs a function that calls the code.

What is working so far is when I click the tab, the code begins to execute. But it stops with the error 'External name not defined'.
What is not working is the control on the main form showing the value of =[Me]![subfrmIS-1a].[Form]![qryIS-1]![Link1] as I move down the records in my subform subfrmIS-1a.

Referring to the expression page, If you are on Main form, To refer to a control On Sub 1: Me!Subform1.Form!ControlName is the expression.
Me!Subform1.Form!ControlName
[Me]![subfrmIS-1a].[Form]![qryIS-1]![Link1]
I'm using the Expression Builder, so I think the syntax should be correct. Is this not the correct way to pass a value from a subform to main form?

I'm not clear on your comment 'keep in mind the sorcerer object and control name might not be the same.' I must be overlooking something.

 

[dhookom]

If the code is in a module, I expect that to mean it is not in a form otherwise you would have responded to my question "What form or subform hosts the code?".

Since the code is in a module, I would expect any reference to a control on a form would begin with "Forms!". You can test your expression in the immediate window (press Ctrl+G) and enter something like:
? Forms![frmIS-1c].[subfrmIS-1a].[Form]![qryIS-1].[Link1]
What is qrySI-1? You stated "[qryIS-1] is my current live database" however, it seems like it might be a record source of a form. It is certainly not a "database".

Autocorrect and fat-fingering messed up that part. The properties of a subform control have a name (top indicated) as well as a Source Object (below). These are typically the same but can be different. You would always use the top property which is the name.