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.

 

[Bubba002]

You are correct, qryIS-1 is a record source and not a database. It is my 'live' data.

The main form name is frmIS-1c. Its Record Source is tblLoop.
The subform on tab 1 is subfrmIS-1a, and it's Name and Source Object are the same "subfrmIS-1a".
The Record Source for subfrmIS-1a is qryIS-1a. And qryIS-1a is qryIS-1 (live) data query and tblqryIS-1c (aged) data table via Link1.

Another solution I'm thinking of now hinges on having a control on the main form showing the value of field qryIS-1.Link1 which is in subformIS-1a.
If that works, then I can change my code to reference this main form control (I'll name it txtLink1). Since I am moving from tab 1 to tab 2 on the main form, then perhaps the code will recognize this main form control, thus filtering my code recordset to just one record.

To update my code, I think the correct syntax to reference txtLink1on main form should be Forms!Mainform!ControlName
Forms!frmIS-1c!txtLink1

Does that sound like a workable solution to you?

Testing my expression in the immediate window gives:
? Forms![frmIS-1c].[subfrmIS-1a].[Form]![qryIS-1].[Link1] Can't find the field 'qruIS-1' in expression.
? Forms![frmIS-1c].[subfrmIS-1a].[Form]![qryIS-1.Link1] Property isn't available in Design view.

All of the references I've tried on my main form give '#Name?'. I can't seem to find an example to pass a subform value to the main form.
Any help is appreciated.

 

[Bubba002]

Hey dhookom, Hallelujah !!! Thanks for your help.

The code is executing now without the main form reference. This was my fix:
strBase = "SELECT * FROM [qryIS-1] WHERE Link1 = """ & Forms![frmIS-1c]![subfrmIS-1a].[Form]![qryIS-1.Link1] & """"
strVarying = "SELECT * FROM [tblqryIS-1c] WHERE Link1 = """ & Forms![frmIS-1c]![subfrmIS-1a].[Form]![qryIS-1.Link1] & """"

Testing it on several records, the SELECT line identifies only the record that was on subformIS-1a!

However, now I get this error: The database engine could not lock table ‘tblCmprLoop’ because it is already in use by another person or process.

I know why. Previously, I would close all my forms to run the report on the entire database. It would populate a table. This is the table that is used for tab 2 subfrmIS-3-Chng.

Now, since my form needs to be open, I have a different issue to resolve. Currently the code jumps to error on the line
db.TableDefs.Delete "tblCmprLoop". I can't delete a table that is in use.
Here is the code as it is working now.

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 [qryIS-1] WHERE Link1 = """ & Forms![frmIS-1c]![subfrmIS-1a].[Form]![qryIS-1.Link1] & """"
Debug.Print strBase
Set rstBase = db.OpenRecordset(strBase)

strVarying = "SELECT * FROM [tblqryIS-1c] WHERE Link1 = """ & Forms![frmIS-1c]![subfrmIS-1a].[Form]![qryIS-1.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

Do Until rstBase.EOF
    If rstVarying.EOF = True Then
        ErrorMessage = "---- record " & rstBase(PrimaryKeyField) & _
                    " aDded ----"
        db.Execute ("INSERT INTO tblCmprLoop (RecordNumber) " _
                & "VALUES ( '" & ErrorMessage & "');")
                
        For Each fld In tdf.Fields
            db.Execute ("INSERT INTO tblCmprLoop(RecordNumber, FieldName, NewText)" _
                    & " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & rstBase(fld.Name) & "');")
            FieldChanged = True
        Next fld

        rstBase.MoveNext
    ElseIf rstBase(PrimaryKeyField) > rstVarying(PrimaryKeyField) Then
        ErrorMessage = "---- record " & rstVarying(PrimaryKeyField) & _
                    " deleted ----"
        db.Execute ("INSERT INTO tblCmprLoop(RecordNumber) " _
                & "VALUES ( '" & ErrorMessage & "');")
        
        For Each fld In tdf.Fields
            db.Execute ("INSERT INTO tblCmprLoop(RecordNumber, FieldName, OldText)" _
                    & " VALUES ( '" & rstVarying(PrimaryKeyField) & "','" & fld.Name & "','" & rstVarying(fld.Name) & "');")
            FieldChanged = True
        Next fld
        
        rstVarying.MoveNext
    ElseIf rstBase(PrimaryKeyField) < rstVarying(PrimaryKeyField) Then
        ErrorMessage = "---- record " & rstBase(PrimaryKeyField) & _
                    " adDed ----"
        db.Execute ("INSERT INTO tblCmprLoop(RecordNumber) " _
                & "VALUES ( '" & ErrorMessage & "');")
        
        For Each fld In tdf.Fields
            db.Execute ("INSERT INTO tblCmprLoop(RecordNumber, FieldName, NewText)" _
                    & " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & rstBase(fld.Name) & "');")
            FieldChanged = True
        Next fld

        rstBase.MoveNext
    Else
        FieldChanged = False
        For Each fld In tdf.Fields
            If Nz(rstBase(fld.Name)) <> Nz(rstVarying(fld.Name)) Then
                If Not FieldChanged Then
                    ErrorMessage = "---- record " & rstBase(PrimaryKeyField) & _
                        " Modified ----"
                    db.Execute ("INSERT INTO tblCmprLoop(RecordNumber) " _
                        & "VALUES ( '" & ErrorMessage & "');")
                End If
                db.Execute ("INSERT INTO tblCmprLoop(RecordNumber, FieldName, NewText, OldText)" _
                        & " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & rstBase(fld.Name) & "','" & rstVarying(fld.Name) & "');")
                FieldChanged = True
            End If
        Next fld
'        If Not FieldChanged Then
'            ErrorMessage = "Record " & rstBase(PrimaryKeyField) & _
'                    " identical"
'        End If
        rstBase.MoveNext
        rstVarying.MoveNext
        FieldChanged = False
    End If
Loop
Do Until rstVarying.EOF
    ErrorMessage = "---- record " & rstVarying(PrimaryKeyField) & _
                " Deleted ----"
        db.Execute ("INSERT INTO tblCmprLoop(RecordNumber) " _
                & "VALUES ( '" & ErrorMessage & "');")
    
        For Each fld In tdf.Fields
            db.Execute ("INSERT INTO tblCmprLoop(RecordNumber, FieldName, OldText)" _
                    & " VALUES ( '" & rstVarying(PrimaryKeyField) & "','" & fld.Name & "','" & rstVarying(fld.Name) & "');")
            FieldChanged = True
        Next fld

    rstVarying.MoveNext
Loop

Exit_CompareTables:
    Set rstBase = Nothing
    Set rstVarying = Nothing
    db.Close
    Exit Sub

Err_CompareTables:
    If Err.Number = 3010 Then   '*** if the error is the table is missing
        Resume Next             '*** then skip the delete line and resume on the next line
    ElseIf Err.Number = 3265 Then
        Resume Next
    Else
        MsgBox Err.Description      '*** write out the error and exit the sub
        Resume Exit_CompareTables
    End If
End Sub

 

[dhookom]

Is your problem resolved?

 

[Bubba002]

Partially, yes. I am now able to filter my record source to just one record. This was my first challenge.

My code was originally made to run and populate tables when no forms were open. I will now need to adjust my code to essentially do the same thing while a form is open. (I need to get around the 'The database engine could not lock table ‘tblCmprLoop’ because it is already in use by another person or process.' error.)

Instead of deleting a table and then creating a table as before, perhaps I can erase the table, and then INSERT INTO the table while the form is opened. I'll begin looking into ways of doing that.

Thanks again. I appreciate all your help.

 

[dhookom]

I guess I don't know your exact requirement but maybe I don't want to .

 

[Bubba002]

Hey dhookom, Yes, I hear you. Just FYI, I'll describe below.

My database design requirement is pretty easy to describe, but much harder to implement.

As an engineering design progresses on an industrial project, many people have access to my design database. Using the forms I created they can see the "many" data points related to instrumentation in a readable and logical manner. (One giant spreadsheet just doesn't cut it, no matter how big your screen.) Just as engineering drawings go from Rev.1 to Rev.2, with the changes identified with a cloud, I needed to do something similar with a database. Let's say a engineering package is Issued for Client Review. I save the database on that date in a table. That becomes my 'aged' data.
Any changes to the database from that issue is shown in red on the forms. Although that is very helpful, the next question an engineer will ask is "What was it before?". So I developed code to cycle through all the fields of all the records and capturing the changes in a table. This table is used to create printed reports identifying the changes. Great for issuing to clients, but not helpful for every day work.

So my effort here was to allow anyone to see up-to-date (live) changes on any record showing the old data and new data.

Your effort was a big help. I have modified my code to allow it to run while the form is opened. I also added a re-query because the code generated table was changed, but the screen wasn't. So it's all working as planned now.

By the way, you and I were working some time back this January on 'How do you pass a subform fieldname-ID as a string variable in Conditional Formatting expression.' I just kind of put that aside, as the current hard-coded CF expressions are working on my forms. But I just might get back to it, later.

Thanks again for all your help.

 

[dhookom]

There are data macros in Access that essentially create triggers that allow tracking data updates. I haven’t used these but I expect you could find some great resources on the web.