Solved Insert Into Select not working in VBA

[Harry Paraskeva]

Hello to everyone,

Before asking, let me just say that I do have a solution to the question cited below, but I would like to understand why VBA won't work. I have been trying to automate a repetitive task in a quite complex database, but VBA keeps throwing a "too few parameters. expected 2" error that I have no idea what to do with. The code I have used is the following:

Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim strsql As String
strsql = "INSERT INTO Tbl_Grs_Raw_Material_Analysis_Weathered_Faces ( GroundStoneFaceID, Timing_Of_Occurrence, WeatheringID ) " & vbCrLf & _
"SELECT DISTINCT Tbl_Grs_Face_Analysis.ID, ""Pre-Use"" AS [Text], Tbl_Grs_Raw_Material_Analysis_Weathering.ID " & vbCrLf & _
"FROM Tbl_Grs_Face_Analysis, Tbl_Grs_Raw_Material_Analysis_Weathered_Faces, Tbl_Grs_Raw_Material_Analysis_Weathering " & vbCrLf & _
"WHERE (((Tbl_Grs_Raw_Material_Analysis_Weathering.ID)=Forms!Frm_Grs_Ground_Stone_Analysis.Form!Frm_Grs_Raw_Material.Form!Frm_Grs_Raw_Material_Analysis.Form!Frm_Grs_Raw_Material_Analysis_Weathering.Form!ID) And ((Tbl_Grs_Face_Analysis.GroundStoneID)=Forms!Frm_Grs_Ground_Stone_Analysis.Form!Frm_Grs_Raw_Material.Form!Frm_Grs_Raw_Material_Analysis.Form!GroundStoneID));"
Dim face_check As Integer
face_check = DCount("ID", "Tbl_Grs_Face_Analysis", "[GroundStoneID]=" & [Forms]![Frm_Grs_Ground_Stone_Analysis].[Form]![ID])
If face_check = 0 Or IsNull(face_check) Then
Exit Sub
Else
DoCmd.SetWarnings False
dbs.Execute strsql
DoCmd.SetWarnings True
End If
Me.Frm_Grs_Raw_Material_Analysis_Weathered_Faces.Requery

The code that I have used, and works is the following:

Dim face_check As Integer
face_check = DCount("ID", "Tbl_Grs_Face_Analysis", "[GroundStoneID]=" & [Forms]![Frm_Grs_Ground_Stone_Analysis].[Form]![ID])
If face_check = 0 Or IsNull(face_check) Then
Exit Sub
Else
DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry_Grs_Raw_Material_Analysis_Weathered_Faces_PreUse"
DoCmd.SetWarnings True
End If
Me.Frm_Grs_Raw_Material_Analysis_Weathered_Faces.Requery

The query called is as follows:

INSERT INTO Tbl_Grs_Raw_Material_Analysis_Weathered_Faces ( GroundStoneFaceID, Timing_Of_Occurrence, WeatheringID )
SELECT DISTINCT Tbl_Grs_Face_Analysis.ID, "Pre-Use" AS [Text], Tbl_Grs_Raw_Material_Analysis_Weathering.ID
FROM Tbl_Grs_Face_Analysis, Tbl_Grs_Raw_Material_Analysis_Weathered_Faces, Tbl_Grs_Raw_Material_Analysis_Weathering
WHERE (((Tbl_Grs_Raw_Material_Analysis_Weathering.ID)=Forms!Frm_Grs_Ground_Stone_Analysis.Form!Frm_Grs_Raw_Material.Form!Frm_Grs_Raw_Material_Analysis.Form!Frm_Grs_Raw_Material_Analysis_Weathering.Form!ID) And ((Tbl_Grs_Face_Analysis.GroundStoneID)=Forms!Frm_Grs_Ground_Stone_Analysis.Form!Frm_Grs_Raw_Material.Form!Frm_Grs_Raw_Material_Analysis.Form!GroundStoneID));

Hope you find this intriguing...

 

[Galaxiom]

Execute is a method of the database. Its scope does not include the Access objects such as Forms. To use Execute you must concatenate the values into the command.

No point turning off warnings for Execute.

You should use the second argument for Execute so errors are passed back.

dbs.Execute strSQL, dbFailOnError

 

[theDBguy]

Hi. Just in case it helps for future reference, take a look at this custom function.

Code Snippets

 

[Harry Paraskeva]

Execute is a method of the database. Its scope does not include the Access objects such as Forms. To use Execute you must concatenate the values into the command.

No point turning off warnings for Execute.

You should use the second argument for Execute so errors are passed back.

dbs.Execute strSQL, dbFailOnError

Thank you for the explanation. I understand about Warnings and the dbFailOnError argument, which is useful for rolling back changes in the event of an error.

The first part I did not understand. Which values do I need to concatenate and how do I do that?

 

[Harry Paraskeva]

Hi. Just in case it helps for future reference, take a look at this custom function.

Code Snippets

Useful for reference. Thank you theDBguy! I will need to take a closer look at it to understand what it does fully.

 

[SHANEMAC51]

but I would like to understand why VBA won't work.

in 25 years of working with access, I have not met such a person
- 3 tables in Cartesian product
- and 4th levels of attachment form

of course, up to 7 attachments are allowed, but why such difficulties

...."FROM Tbl_Grs_Face_Analysis,
Tbl_Grs_Raw_Material_Analysis_Weathered_Faces,
Tbl_Grs_Raw_Material_Analysis_Weathering " & vbCrLf & _

"WHERE (((Tbl_Grs_Raw_Material_Analysis_Weathering.ID)=Forms!Frm_Grs_Ground_Stone_Analysis
.Form!Frm_Grs_Raw_Material
.Form!Frm_Grs_Raw_Material_Analysis
.Form!Frm_Grs_Raw_Material_Analysis_Weathering
.Form!ID)

And ((Tbl_Grs_Face_Analysis.GroundStoneID)=Forms!Frm_Grs_Ground_Stone_Analysis
.Form!Frm_Grs_Raw_Material
.Form!Frm_Grs_Raw_Material_Analysis
.Form!GroundStoneID));"

 

[Harry Paraskeva]

in 25 years of working with access, I have not met such a person
- 3 tables in Cartesian product
- and 4th levels of attachment form

of course, up to 7 attachments are allowed, but why such difficulties

The database is complex (158 tables, 208 queries, 209 forms) because it is used for attribute analysis for material culture in archaeology (many one-to-many relationships for having a normalised schema). The user interface is also complex and forms nesting goes up to sixth level on occasion.

In this case, I was requested to create a button on the form that controls contents of the Tbl_Grs_Raw_Material_Analysis_Weathering that would copy the ID of all rows in Tbl_Grs_Face_Analysis into the Tbl_Grs_Raw_Material_Analysis_Weathered_Faces table for the artefact under analysis (that would be in Tbl_Grs_Analysis, which is the eventual parent table for each of the tables). I attach structure to understand the relationships and the necessity for Cartesian joins.

 

[Galaxiom]

Which values do I need to concatenate and how do I do that?

The right hand side of the Where clause refers to a control on a form. You need to read the value and concatenate it much like you did in the DCount.

There are two parts to Access. The Application and the Database. The forms are in the Application. The Application can refer to the database through its Methods but the database can't refer directly to the objects in the Application.

DoCmd is a Method of the Application. Application is the default so is usually omitted but its full reference is:

Application.DoCmd

Execute is a method of the Database so it only understands the database objects.

 

[The_Doc_Man]

Let's try another approach to understanding what is going on. Galaxiom gave you the short answer, so full credit to him, but you asked for an explanation as to why VBA didn't do what you wanted it to do.

If you did a fussy examination of your task list while running your query, you would find that you have at least two tasks running. One is for the front-end application and graphic interface, MSACCESS.EXE; the other is for the back-end database engine, ACE (usually by the name of the .DLL file that contains it). Since they are separate tasks, they have separate virtual and physical memory. Windows security rules say that even for related tasks, if they are separate, they do not share memory except in certain, highly restricted ways.

When you use DoCmd RunSQL methods, the SQL string gets evaluated and some substitution and/or concatenation gets done. Eventually the resultant string is fully built (as fully as it is going to be) and it gets passed to ACE for execution. In this pre-processing context, the variables and form information and anything else you used are still visible while the SQL string processing is under way. That is because the processing is being done in the GUI & development memory space. It can see anything you stored or defined here.

When you use database Execute methods, you are passing an SQL string directly to ACE with the implication that it is completely built and that no more substitution or concatenation will occur. That string gets passed AS-IS to ACE for execution. Once that string is sent to ACE, it is now in a totally different task which DOES NOT SEE the memory of the GUI & development area. Two different memories because two different tasks.

Therefore, if there is some implied operation that ACE cannot manage, you end up with problems such as "wrong number of parameters" (as you mentioned) or any other such similar issues. This next statement is a GUESS, but I would say that at least two of the things you referenced in your big query are either spelled wrong or your location qualifiers are pointing to the wrong location - because Access calls something it can't find a parameter and either asks for it or complains about it.

Note that the statement below doesn't break the rules I just described even though it might seem that it does:

db.Execute "DELETE * FROM table WHERE table.TID = " & Me.Sel_ID & " AND table.MARKED = TRUE ;"

In this special case, the string processing isn't completed as presented, but it happens that the VBA processor automatically completes strings of this type before passing them to whatever is next to process. VBA completes ALL strings of this type, whether as a formal argument or as simply an expression being used in an assignment statement as strSQL= "...whatever..." .

 

[Harry Paraskeva]

The right hand side of the Where clause refers to a control on a form. You need to read the value and concatenate it much like you did in the DCount.

There are two parts to Access. The Application and the Database. The forms are in the Application. The Application can refer to the database through its Methods but the database can't refer directly to the objects in the Application.

DoCmd is a Method of the Application. Application is the default so is usually omitted but its full reference is:

Application.DoCmd

Execute is a method of the Database so it only understands the database objects.

Thank you for the explanation. I now see what the issue is. I have used Insert Into without Select in the past with success in VBA, but this one was particularly tricky. Since calling the Append query from VBA already works, I don't think I will spend more time trying to make it work fully in VBA, as anyway I'm guessing having the query doing the heavy lifting is the best way to do this, especially for complex situations. Thank you for your time.

 

[Harry Paraskeva]

Let's try another approach to understanding what is going on. Galaxiom gave you the short answer, so full credit to him, but you asked for an explanation as to why VBA didn't do what you wanted it to do.

If you did a fussy examination of your task list while running your query, you would find that you have at least two tasks running. One is for the front-end application and graphic interface, MSACCESS.EXE; the other is for the back-end database engine, ACE (usually by the name of the .DLL file that contains it). Since they are separate tasks, they have separate virtual and physical memory. Windows security rules say that even for related tasks, if they are separate, they do not share memory except in certain, highly restricted ways.

When you use DoCmd RunSQL methods, the SQL string gets evaluated and some substitution and/or concatenation gets done. Eventually the resultant string is fully built (as fully as it is going to be) and it gets passed to ACE for execution. In this pre-processing context, the variables and form information and anything else you used are still visible while the SQL string processing is under way. That is because the processing is being done in the GUI & development memory space. It can see anything you stored or defined here.

When you use database Execute methods, you are passing an SQL string directly to ACE with the implication that it is completely built and that no more substitution or concatenation will occur. That string gets passed AS-IS to ACE for execution. Once that string is sent to ACE, it is now in a totally different task which DOES NOT SEE the memory of the GUI & development area. Two different memories because two different tasks.

Therefore, if there is some implied operation that ACE cannot manage, you end up with problems such as "wrong number of parameters" (as you mentioned) or any other such similar issues. This next statement is a GUESS, but I would say that at least two of the things you referenced in your big query are either spelled wrong or your location qualifiers are pointing to the wrong location - because Access calls something it can't find a parameter and either asks for it or complains about it.

Note that the statement below doesn't break the rules I just described even though it might seem that it does:

db.Execute "DELETE * FROM table WHERE table.TID = " & Me.Sel_ID & " AND table.MARKED = TRUE ;"

In this special case, the string processing isn't completed as presented, but it happens that the VBA processor automatically completes strings of this type before passing them to whatever is next to process. VBA completes ALL strings of this type, whether as a formal argument or as simply an expression being used in an assignment statement as strSQL= "...whatever..." .

Thank you very much for the full and extensive explanation as to how Access works. Admittedly, I thought everything was being pre-computed in VBA and then sent to SQL-ACE for execution, but apparently, I need to re-think and read a bit more on what processes are handled by the application and what is done in the database to avoid issues like this in the future. Thank you for taking the time to explain.

For the Guess, the references to parts of the interface are both ok, like I said in the initial post the question relates solely to understanding why VBA wouldn't handle action queries like the one cited. Otherwise, there is no problem, as calling the Append Query (that is the same as the Strsql cited - see initial message) does everything I need.

 

[Pat Hartman]

The prompt you were getting should have been your clue to the solution. You never posted it but by looking at the SQL, we could see the problem.

I use saved querydefs probably 99% of the time. The only time I use embedded SQL is if it is dynamic. That means that its srurcture changes. For a complex search form with multiple search options, rather than deal with a very complex where clause, I build it on the fly depending on which options the user selected. Otherwise the criteria is static and embedded in the querydef. References for dynamic values (not the same as dynamic structure) are usually to form controls since that is better practice than a prompt. It allows you the ability to validate the criteria before using it.

That means that when I run the SQL in VBA, I have to provide the values for the parameters at runtime. Here's an example of an append query. It selects data from the .csv file and appends it with some identifying information to the permanent table. In queries that need parameters where I am going to run them using .execute, I name the variables [Entersomething] so it is clear that they are arguments.

    Set qd = db.QueryDefs!q837P_AppendBatch
        qd.Parameters!EnterMinSelDT = Me.txtMinSelDT
        qd.Parameters!EnterMaxSelDT = Me.txtMaxSelDT
        qd.Parameters!EnterBatchID = Me.txtBatchID
        qd.Parameters!EnterBatchYY = Me.txtBatchYY
        qd.Parameters!EnterBatchMM = Me.txtBatchMM
        qd.Parameters!EnterBatchSeq = Me.txtBatchSeq
        qd.Parameters!EnterClientID = Me.cboClientID
        qd.Parameters!EnterProcedureID = Me.cboProcedureID
          
    qd.Execute (dbSeeChanges)

When you use SQL strings rather than saved querydefs, you don't get the option to provide the value for arguments before running the query BUT you don't need to. You just need to create your SQL String so it expands the arguments rather than refers to them. So:

strSQL = strSQL & " Where somefield = " & Me.somefield

rather than

strSQL = strSQL & " Where somefield = Forms!yourform!somefield"

The difference is subtle but you need to understand what the difference is and Doc did a good job of telling you why.

Best practice is to always build SQL strings into variables as you have done BUT, to debug them, you just need to put a stop in the code on the .execute statement and print the string. You will frequently see the problem yourself. OR, you can copy the string from the debug window and paste it into a new querydef and try to run it. You will sometimes get a different error which might make the problem clearer.

In your case, you would have been more mystified. The exact same sql string that failed when executed with .execute would have run fine when you ran it as a querydef. The difference is that the queyrdef is preprocessed by Access and so has all the expression service available to it to expand variables but the .execute is run by DAO which does not have those Access services available because DAO needs to run outside of Access and Access doesn't even need to be loaded at all for other applications to use data from .mdb and .accdb's because they use DAO or ADO and NOT Access/VBA.

And that's why when people say they use "Access" as the database that holds the data for their web pages, my teeth grind. They are not using Access at all. They are using DAO or more likely ADO to work with the data and Access is never even installed on the server.

 

[Harry Paraskeva]

The prompt you were getting should have been your clue to the solution. You never posted it but by looking at the SQL, we could see the problem.

I use saved querydefs probably 99% of the time. The only time I use embedded SQL is if it is dynamic. That means that its srurcture changes. For a complex search form with multiple search options, rather than deal with a very complex where clause, I build it on the fly depending on which options the user selected. Otherwise the criteria is static and embedded in the querydef. References for dynamic values (not the same as dynamic structure) are usually to form controls since that is better practice than a prompt. It allows you the ability to validate the criteria before using it.

That means that when I run the SQL in VBA, I have to provide the values for the parameters at runtime. Here's an example of an append query. It selects data from the .csv file and appends it with some identifying information to the permanent table. In queries that need parameters where I am going to run them using .execute, I name the variables [Entersomething] so it is clear that they are arguments.

    Set qd = db.QueryDefs!q837P_AppendBatch
        qd.Parameters!EnterMinSelDT = Me.txtMinSelDT
        qd.Parameters!EnterMaxSelDT = Me.txtMaxSelDT
        qd.Parameters!EnterBatchID = Me.txtBatchID
        qd.Parameters!EnterBatchYY = Me.txtBatchYY
        qd.Parameters!EnterBatchMM = Me.txtBatchMM
        qd.Parameters!EnterBatchSeq = Me.txtBatchSeq
        qd.Parameters!EnterClientID = Me.cboClientID
        qd.Parameters!EnterProcedureID = Me.cboProcedureID
          
    qd.Execute (dbSeeChanges)

When you use SQL strings rather than saved querydefs, you don't get the option to provide the value for arguments before running the query BUT you don't need to. You just need to create your SQL String so it expands the arguments rather than refers to them. So:

strSQL = strSQL & " Where somefield = " & Me.somefield

rather than

strSQL = strSQL & " Where somefield = Forms!yourform!somefield"

The difference is subtle but you need to understand what the difference is and Doc did a good job of telling you why.

Best practice is to always build SQL strings into variables as you have done BUT, to debug them, you just need to put a stop in the code on the .execute statement and print the string. You will frequently see the problem yourself. OR, you can copy the string from the debug window and paste it into a new querydef and try to run it. You will sometimes get a different error which might make the problem clearer.

In your case, you would have been more mystified. The exact same sql string that failed when executed with .execute would have run fine when you ran it as a querydef. The difference is that the queyrdef is preprocessed by Access and so has all the expression service available to it to expand variables but the .execute is run by DAO which does not have those Access services available because DAO needs to run outside of Access and Access doesn't even need to be loaded at all for other applications to use data from .mdb and .accdb's because they use DAO or ADO and NOT Access/VBA.

Thank you Pat for the in-depth response and explanation.

A couple of explanatory notes: a. The prompt I was getting is the one mentioned in the original post that it was getting "too few parameters. expected 2". I did gather that it wasn't parsing something correctly, I was just wondering why it didn't do it and now based on multiple answer here I am beginning to understand. It is also necessary to clarify that I am not a programmer.
b. I needed this to be dynamic, as it concerns data being processed live. The interface is using tabs and forms with multiple subforms and multiple subform nesting levels. The button executing the code is on a third-level nested subform and the requirement is to get data at any given moment that are stored in a second-level nested subform open in another tab and copy it to a fourth-level nested subform on the tab currently being processed, and also add a bit of extra text. In other words, the feature is copying data from one form to another form, and although their tables are related, the forms are not related in the interface and that is why I can't use a Me.somefield in the Where clause.

 

[The_Doc_Man]

The button executing the code is on a third-level nested subform and the requirement is to get data at any given moment that are stored in a second-level nested subform open in another tab and copy it to a fourth-level nested subform on the tab currently being processed, and also add a bit of extra text. In other words, the feature is copying data from one form to another form, and although their tables are related, the forms are not related in the interface

Well.... yes and no. When you have sub-forms, whether or not they are related via parent/child relationships, they are structurally related by being part of the nest of forms. If you have a form and it has a sub-form control that is loaded with a form, you can (as you have already shown) use Forms!Mainform.subformcontrolname.Form!Control to get a control on the 1st-level sub-form. That works anywhere. But let's say you wanted to do something from the sub-form using the parent form's data. From there, it is Me.Parent.ParentFormControlName to get the control on the sub-form's parent. To get to a "sibling" form, it would be Me.Parent.Siblingsubformcontrol.Form!Control.
If you were doing this from the 3rd-level child form and wanted a 1st-level control, the Me.Parent.Parent!Control works. Might save some typing. Of course, you DO have to be fully aware of your structure. But you suggested that the layout is relatively static. If you are doing it from code in a form that is loaded at a particular level, all levels above it MUST be loaded, so the Me.Parent shortcuts make sense. If those were four independently loaded forms, maybe not so much.

 

[Pat Hartman]

It is also necessary to clarify that I am not a programmer.

Not a problem. We are here to help you. I'd venture to say that fewer than 80% of the experts who answer most of the questions could explain why the SQL string would fail when being run using .execute but would work fine using the RunSQL method or being run directly by opening a querydef and running it.


Your query is NOT dynamic.

Select fld1, fld2, fld3, from yourtable where fld1 = Forms!yourform!somefield
is different from
Select fld1, fld2, fld3, from yourtable where fld2 = Forms!yourform!somefield

Because the criteria uses a different column. You could build the query as

Select fld1, fld2, fld3, from yourtable where fld1 = Forms!yourform!somefield Or fld2 = Forms!yourform!somefield

The fact that the query has an argument does NOT make it dynamic. Dynamic SQL has some structural difference. Substituting the value 1 for 2 in the formfield reference does NOT make a query dynamic.

Where somefield = Forms!yourform!somefield
Where somefield >= Forms!yourform!somefield

there is no way to substitute ">=" for "=" in a queyrdef. That is a structural change.

But

It makes no difference to the structure of the query if the value of Forms!yourform!somefield is 5 or 249273.

If you need the query to sometimes use "=" and other times use ">=", then the query is dynamic and must be build as an SQL string.

Just FYI, Forms do not store data. queries do not store data. Tables store data so if you think of these objects correctly, you will have a better understanding of what they are for.

Forms are for viewing and editing data. Queries, in an Access application are NEVER exposed to the user. They are used to provide a recordSource for forms/reports or to use in VBA to process or update multiple records. Tables store data. PERIOD. That is their sole job.

And finally, we don't know what you are doing but very few applications require duplicating data so think long and hard about why you are doing what you are doing because there could be a better solution.

 

[Harry Paraskeva]

Well.... yes and no. When you have sub-forms, whether or not they are related via parent/child relationships, they are structurally related by being part of the nest of forms. If you have a form and it has a sub-form control that is loaded with a form, you can (as you have already shown) use Forms!Mainform.subformcontrolname.Form!Control to get a control on the 1st-level sub-form. That works anywhere. But let's say you wanted to do something from the sub-form using the parent form's data. From there, it is Me.Parent.ParentFormControlName to get the control on the sub-form's parent. To get to a "sibling" form, it would be Me.Parent.Siblingsubformcontrol.Form!Control.
If you were doing this from the 3rd-level child form and wanted a 1st-level control, the Me.Parent.Parent!Control works. Might save some typing. Of course, you DO have to be fully aware of your structure. But you suggested that the layout is relatively static. If you are doing it from code in a form that is loaded at a particular level, all levels above it MUST be loaded, so the Me.Parent shortcuts make sense. If those were four independently loaded forms, maybe not so much.

This sounds like a workable idea. Thank you, I'l ltry it!

 

[Harry Paraskeva]

Not a problem. We are here to help you. I'd venture to say that fewer than 80% of the experts who answer most of the questions could explain why the SQL string would fail when being run using .execute but would work fine using the RunSQL method or being run directly by opening a querydef and running it.


Your query is NOT dynamic.

Select fld1, fld2, fld3, from yourtable where fld1 = Forms!yourform!somefield
is different from
Select fld1, fld2, fld3, from yourtable where fld2 = Forms!yourform!somefield

Because the criteria uses a different column. You could build the query as

Select fld1, fld2, fld3, from yourtable where fld1 = Forms!yourform!somefield Or fld2 = Forms!yourform!somefield

The fact that the query has an argument does NOT make it dynamic. Dynamic SQL has some structural difference. Substituting the value 1 for 2 in the formfield reference does NOT make a query dynamic.

Where somefield = Forms!yourform!somefield
Where somefield >= Forms!yourform!somefield

there is no way to substitute ">=" for "=" in a queyrdef. That is a structural change.

But

It makes no difference to the structure of the query if the value of Forms!yourform!somefield is 5 or 249273.

If you need the query to sometimes use "=" and other times use ">=", then the query is dynamic and must be build as an SQL string.

Just FYI, Forms do not store data. queries do not store data. Tables store data so if you think of these objects correctly, you will have a better understanding of what they are for.

Forms are for viewing and editing data. Queries, in an Access application are NEVER exposed to the user. They are used to provide a recordSource for forms/reports or to use in VBA to process or update multiple records. Tables store data. PERIOD. That is their sole job.

And finally, we don't know what you are doing but very few applications require duplicating data so think long and hard about why you are doing what you are doing because there could be a better solution.

All the above are very informative and unfortunately a great deal of this knowledge is not in manuals or even knowledgebases for Access, so thank you for sharing these.

To address a few worries, the user never sees queries, as the interface hides everything and locks users out of all the Access interface (no ribbon, no menus, no navigation, compiled interface, backend encrypted, right-click menus disabled).

As for the purpose, technically, it is not duplication of data, as the mechanism is for accelerating via automation one task in the analysis that copies relevant foreign key IDs from previously populated subforms to another subform, where a different part of the analysis is done. Also, the database schema was built with a view to respect 2NF with many tables in 3NF.

 

[Pat Hartman]

copies relevant foreign key IDs from previously populated subforms to another subform

forms do not store data. Tables store data. That kind of thinking will get you in trouble and force you into writing code when you should be running action queries. Queries are always more efficient than VBA code loops. You haven't given us anywhere near enough information to offer you a viable alternative but I can tell you as an experienced developer, your solution is likely not best practice. As you said, you are not a professional. The good thing about Access is that people with no development experience can figure out how to make it do things. The bad thing about Access is that people who have no development experience can choose poor development options and take them pretty far before their flaws prevent further progress.

But, we've solved your initial problem and even explained in great depth how queries are executed so you understand why you ran into the problem So, unless you have another question, just mark the thread solved and we'll move on.