Not sure why you are using Option Compare Binary. All the rest can be read in post #66
Thanks for your thoughtful comments. Yep, I'm working in Spanish, French, Portuguese, Italian and English, so far with the app.
What part of moke123's response do you agree with? I think I addressed all his concerns in post #63.
I'll read your articles on compare to see how it applies. I just know I was getting "c" equal to "C" in my code, and from my historical experience, that too can cause problems. I'm not sure my subs would affect how Access perfoms SQL commands. I'll check it out.
Some of what you say is subjective, take the Dims for example. You can have 50 lines of dims or 10 rows with multiples in a complex program, I'm not one for scrolling through pages of Dims. But to each their own.
My generic procedure works for any form I hope to build, as it does in the demo I posted, so I'm not sure what your objection is.
BTW, that's not "my" routine for copying (since there aren't obvious line numbers in Access, it's hard for me to know exactly which part of any procedure you have a concern about-only with that can I say who the author was). As I've stated several times in posts, I started with the copy code from an Access 20+ year instructor and developer. I had to read the code line by line and lookup most of the commands, as they are foreign to me and my experience designing at the OS level, developing RAD tools and large applications in languages ranging from assembler to higher level languages like BASIC. VBA is its own unique animal, as were the systems I worked in before.
I'm not sure what to say about your observations on arrays. There's nothing that says arrays have to be "same data types". Even Access itself isn't designed that way or why allow arrays as variant? XML was designed around the concept opposite of structured data and nobody seems to complain that is "bad". It would be silly to say that Access itself should only allow one type of field in a table. I don't see any advantage to using TempVars over a normal array in this situation. In fact, it doesn't allow for variant and that opens up the null can of worms to any sub/function I use that can properly have a null.
I've designed hundreds of forms over the years. I even included a snip of code similar to a form class module in the sequential number thread I recently posted. It was from the RAD tool I had built some 30 years ago and is still in commercial use today. I also added some RAD record parameter code in a post to DOC that clearly shows the use of forms in the application (although they are called screens, not forms). So, I'm not sure where you got the impression my experience is only in "batch", whatever that is since it has many meanings in computing.
I'm perfectly capable of hardcoding non-generic code, as my first commercial assembly job was writing code into firmware. I like making high level code more flexible than that. If that wasn't the case in Access, then there wouldn't be the intense focus on everything being a function or sub, or the ability to make calls. That's the whole point of higher-level languages; to be more generic and cross functional. However, you are welcome to write all your form class modules to be very specific to just that one form, and do it over-and-over again for the same basic logic, and then maintain all those versions when a bug is found or enhancement is needed. I've seen it before, and that's your choice, not mine thank you.
BTW, when I was first playing with how I wanted the template to work, I did hardcode specific mods to the record, and then I thought, how can I make this work for any form? And then wrote the code you now see. I've been through the same cycle about a dozen times while working on the template. That's the whole point of a template, to be generic that can then be customized to a particular task.
I'm confused about the problem with the new record being dirty comment. In the test I just did with a modified TWG.accdb, it doesn't go dirty until a textbox is changed. I've tweaked it a bit more for very minor appearance changes, so I doubt it would make a difference to what you claim. But if you have a dirty record, and can show it after the copy with a snip, I'll go download my twg.accdb and check. P.S. Okay, I went and downloaded my posted TWG.accdb, and it's not dirty on the newly created record and it can be modified as needed as a copied record. I would like to know on what version of Access you see a different result. The whole idea is to copy the record, go to the new one, and make user changes there, in the new record. Certain changes have to be made for the copy record before it's actually copied to avoid conflicts with the table properties, like no duplicates in an indexed field.
I'm not sure what you mean that my code doesn't run in the before update procedure. You can clearly see on the form that the update stamp changes to a new date when saved, so it had to run to do that. Can you tell me exactly where it doesn't work for you? In my testing it works fine.
Again, I was pretty clear in the original post what I wanted. It's not what everyone wanted to talk about though. Here's the actual question, "How can I "move" to that new record in the same open form with VBA ?" Notice that the question doesn't include how to copy. The copy was in the given part of the post. Now since I've learned there are at least five ways to copy the code, maybe I wasn't specific enough. It's just at the time, I didn't know there were five or more ways. I surely didn't expect to run into commands/methods that include "bookmark".
I don't expect that every form will need to be doing a copy record. But I've had enough that do in the past, that it's worth making the "hook" in the template form, at least for me. It's a command button in the footer that has a simple parameter to enable or not for any or all users of a particular form.
If you look at the actual copy code commands (Sub sFrmCopyRecord), you'll see that there's about six lines of code that do the work of the actual copying, if you exclude a DIM or two. And I can get rid of one of those if I always copy to the same table. So, is it really worth rewriting and all the testing to possibly save two lines of code? Besides, it would probably take four or more lines of code to work around the order of the three for the modifications I need to make to the newly copied record (since we would have to call and return to/from the helper form module.)
