Error that is Not an Error with Allen Browne's Copy Subform Values

[Heatshiver]

Issue: I've been using Allen Browne's code, and it has been working well until now. When the code runs it produces no Access errors and seems like it works fine; but on closer inspection one of my subforms is not having its values copied over...

Insight: I have a main form with tabs that have subforms. Each subform is copied over to the new record when you insert a date and click a checkbox. For all the other subforms, this works great. But on the last subform the issue above is now occurring.

Special: The only things special about the subform is that it is actually two subforms under the specified tab (but not one subform within a subform). When a person clicks the next/last page button on the main form the RecordSource of both subforms switch to two other queries. In total, there are 4 tables being used, but only 2 at one time when the main form is in Form View.

Coding: I have a bit of simple extra coding in Browne's coding such as enabling/disabling the the next/last buttons, a refresh, save, etc., all of which occur after Browne's code has implemented. I do have RecordSource switches for the subforms in Browne's code. These occur before, between, and after the coding for the 4 tables of the subforms to be copied:

BEFORE - The reason for this is so that if the RecordSource is on the wrong queries on the Form View it will change to the correct two before copying commences.
BETWEEN - This is to switch to the next two queries.
AFTER - This will change back to the first two queries.

Things I've Tried:

1) Commenting out all extra coding not part of Browne's coding, including RecordSource changes.
2) All of #1 with the inclusion of taking out the subform copy values for tables 2-4 (first table left to check if it will work alone).
3) I tried deleting all other subform copy value codes except for the 4 in question.
4) I tried lessening the code of some of the subform copy values in question.
5) I tried lessening the code of the other subform copy values.
6) I tried placing the code into a VBA module in case there was too much information (as there is a lot) and it just wasn't giving an error saying so.

Things I've Checked:

1) Checked the 4 tables for any values that may have carried over. The new record is always made, but no values are present.
2) Checked Parent/Child relationships, and all are as needed.
3) Checked relationships and all are connected. These 4 tables are connected to an intermediate table (consisting of just a Primary and Foreign key) that connects to the primary table.
4) Checked any coding that relates to the table from the subforms or main form, all seems to be working properly and not impeding on Browne's code.

I have the feeling that the need to change the RecordSources has something to do with it, but even not doing so has not been fruitful in fixing this... Any help, recommendations, advice, etc. would be much appreciated!

 

[michaeljryan78]

Seeing the new record that is created and the values are not updating is interesting. Perhaps a requery of the offending subform will refresh the data and the code can read the values in the boxes.

 

[Heatshiver]

Thanks for the reply. I tried Me.Requery in a few different areas, where the code succeeded, it provided the same results. I tried it inside Browne's code but it gave an error before it could finish.

I even tried the following as a last resort since I'm not sure what else to try:

DoCmd.OpenTable "tblCRI"
DoCmd.Save acTable, "tblCRI"

But all that did was open the table and continue the code without adding the copied values.

I also (again) tried taking out some of the code from the part in question (all but one table, and just a few records left in the first table); just to check if it was somehow stopping from too many fields, but still got the same result.

I will try any other suggestions!

 

[boblarson]

DoCmd.Save only saves object STRUCTURE. It has nothing to do with records. If you are in code and want to save the record currently being worked on you can use

If Me.Dirty Then Me.Dirty = False

to force a save. Checking to see if there have been changes (dirty) first ensures you don't run a save on something that doesn't need saving.

 

[Heatshiver]

Thanks, Bob. Just discovered this difference last night but hadn't tried changing it in this code.

I changed it to Dirty = False and checked, but it was a no go.

 

[Heatshiver]

UPDATE: I think this may have to do with the relationships now. I have a main table with most tables connected to that. However, I have three groups of tables that each connect to their own intermediate table that connect to the main table. One of these groups concerns the tables mentioned previously.

I tested copying values from another group, that used to work fine, but it too is having the same problem. I should mention that this particular group that used to work used to be directly connected to the main table.

 

[Heatshiver]

So this issue is still going on...

I noticed some issues with not allowing data to be entered when changing RecordSource, so I created some extra forms and change any according code for SourceObject. That fixed the disallowing of data.

However, the problem with the copying subform values still remains.

I've stepped through the code to see when the values disappear from screen and it actually happens on the last line of Browne's code:

Me.Bookmark = .LastModified

So, it seems it's not being saved at all, and I'm still unsure as to why. Even without any RecordSource or ObjectSource changes, the same results are provided.

 

[Heatshiver]

SOLVED: Error that is Not an Error with Allen Browne's Copy Subform Values

SOLVED: My problem was that there was not at least ONE value in each table before trying the code. Once I went into each table and added just one value to one field, the problem straightened itself out.

Just a Note: I have another group (A LOT) of tables that do the same thing. It seems that it does not matter if you are changing RecordSource or SourceObject in order to get this working.