Solved In form, move to another record with known ID

[Pat Hartman]

I never gave you a solution for this problem because arnelgp had already answered it. I didn't even give you the usual warning we give to novices about copying records. I gave you the benefit of the doubt. I did provide a warning that the solution only works for one record.

Just FYI, Tables store data. Forms do not store data. That should give you a clue regarding why the suggestion didn't work for you. When you use copy/paste, do you expect Windows to get some related data from some other place? If not, why would you expect Access to? So, using the Socratic method, what would be the correct solution if you wanted to copy data that wasn't on the form? Hint, the answer is in the first sentence of the paragraph

 

[twgonder]

I never gave you a solution for this problem because arnelgp had already answered it. I didn't even give you the usual warning we give to novices about copying records. I gave you the benefit of the doubt. I did provide a warning that the solution only works for one record.

Just FYI, Tables store data. Forms do not store data. That should give you a clue regarding why the suggestion didn't work for you. When you use copy/paste, do you expect Windows to get some related data from some other place? If not, why would you expect Access to? So, using the Socratic method, what would be the correct solution if you wanted to copy data that wasn't on the form? Hint, the answer is in the first sentence of the paragraph

Sorry, I guess I misunderstood the insinuation in your post #25.
Why would "copy" in a form copy table data? Um, because there is supposed to be a dynaset (connected to the table) under the form (if not run from a query) that has the entire record set available for copying? That was covered in another thread though. If forms don't store data, then the routine should copy the table record, no? Why copy a form to a table, right? Or is that a special feature of Access forms, to only copy a partial record? Maybe M$ could clarify that in the helps? Oh wait, when I went to the Access documentation with F1, this is the big, helpful response.

 

[The_Doc_Man]

I'm in a form on a record. I programmatically copy that record to a new record. After doing that I know the ID of the new record.
How can I "move" to that new record in the same open form with VBA ?

OK, back to your original question: I see three possible situations.

(a) if a copy/paste operation of the whole record was allowed, you now have two records with the same ID, so which one of the two did you want?

(b) if the ID field was a prime key and was declared as such, the whole-record copy would fail due to a key violation; thus the new record doesn't exist so doesn't have an ID at all, much less a known ID.

(c) if you somehow substituted a new ID during the copy process, it would have been nice to state that in the original question.

Further, in this thread's discussion using the macro examples, the "Copy Record" is a whole-record copy. If so, (a) is possible, but (c) isn't possible as a single step because once you perform the copy, the copied record is not accessible until you paste it somewhere. Until then it is in the clipboard, which is (I believe) not technically part of Access memory. If my memory is correct, Access cannot reach outside of itself to make those changes.

I saw later in this thread where option (b) from my list seemed to be relevant because you WERE getting key violations. Which means your whole-record copy actually occurred but there WAS a key field and your "paste" attempt violated the key constraint.

Hope this take is clear enough.

 

[The_Doc_Man]

Why would "copy" in a form copy table data? Um, because there is supposed to be a dynaset (connected to the table) under the form (if not run from a query) that has the entire record set available for copying?

Faulty logic. Copy/Paste operations have a third element - SELECTION. The presence of a dynaset is immaterial to the base question. Could be a table set or anything else. The only thing that matters for Copy/Paste is the selection. No selection? No copy and therefore no paste.

Did you notice arnelgp's warning in this thread that you had to be on the right record for the record selection operation to work correctly? Context is important because a programmatic SELECT operation has no visible mouse cursor to specify the selection. If you are in the wrong place at the wrong time, you either get nothing or get the wrong record.

 

[twgonder]

Faulty logic. Copy/Paste operations have a third element - SELECTION. The presence of a dynaset is immaterial to the base question. Could be a table set or anything else. The only thing that matters for Copy/Paste is the selection. No selection? No copy and therefore no paste.

Did you notice arnelgp's warning in this thread that you had to be on the right record for the record selection operation to work correctly? Context is important because a programmatic SELECT operation has no visible mouse cursor to specify the selection. If you are in the wrong place at the wrong time, you either get nothing or get the wrong record.

Of course I noticed that warning (correct record) and knew about it. What fool would think if they are on record 2 and want to copy record 1 that the system would magically divine that? The selection takes place when the user moves to the record they want to copy. Or does everyone here have some magic version of Windows, Excel and Word that I don't know about?

 

[twgonder]

OK, back to your original question: I see three possible situations.

(a) if a copy/paste operation of the whole record was allowed, you now have two records with the same ID, so which one of the two did you want?

(b) if the ID field was a prime key and was declared as such, the whole-record copy would fail due to a key violation; thus the new record doesn't exist so doesn't have an ID at all, much less a known ID.

(c) if you somehow substituted a new ID during the copy process, it would have been nice to state that in the original question.

Further, in this thread's discussion using the macro examples, the "Copy Record" is a whole-record copy. If so, (a) is possible, but (c) isn't possible as a single step because once you perform the copy, the copied record is not accessible until you paste it somewhere. Until then it is in the clipboard, which is (I believe) not technically part of Access memory. If my memory is correct, Access cannot reach outside of itself to make those changes.

I saw later in this thread where option (b) from my list seemed to be relevant because you WERE getting key violations. Which means your whole-record copy actually occurred but there WAS a key field and your "paste" attempt violated the key constraint.

Hope this take is clear enough.

Um Doc, I don't think you've looked at my code or tried the example .accdbs that people have provided.
Most of what you say isn't what happens in reality or the examples provided.
My code works, the examples of how I should do it, provided by others don't work (three lines of code or a macro RAD solution).
I was pretty clear, you even quoted it (although that just dissapeared from my screen), on which record I wanted to move to after my code executed properly for the copy (please see post #1).

The problem was solved early on (post #18). People started suggesting alternatives to my "wrong" way of doing it (based on their erroneous perception that I was using some old-sailing-ship approach when in fact the code started from the vault of a 20+ year Access developer). I tried what they suggested more for the experience of it all. You're analyzing their incorrect solutions, which I already pointed out don't work and why.

P.S. In retrospect, during my shower, I realized that even I as the OP might have a problem understanding all the posts here. Allow me to suggest a < 9 minute video to help you understand the basics of what we're talking about:
Duplicate Record in Microsoft Access (599cd.com)

 

[The_Doc_Man]

Regarding the video: As if I had trouble in knowing how to create a partially duplicated record... No big thing at all using VBA. But with a properly normalized structure, there is usually nothing to copy except a PK/FK situation.

I realized that even I as the OP might have a problem understanding all the posts here.

I would hope so, as otherwise the implication would be that you have been asking questions to which you already knew the answer and just wanted to waste our time.

As to my original response, I may have been focusing on the Macro solution and for that, my answer was quite correct. Using a CopyRecord macro, you have to EITHER pre-edit or post-edit the record before selecting and copying it and pasting it, because otherwise it is a whole-record operation and you get key violations. With a pre- or post-edit of the record, it becomes my case (c) - regardless of using a raw copy or a macro or some VBA to edit selected fields behind the scenes.

What fool would think if they are on record 2 and want to copy record 1 that the system would magically divine that?

If you've been here long enough, you wouldn't have to ask that question. You would have seen for yourself.

 

[KitaYama]

@twgonder
Can you mock up a simple database, a people table and a form where you use arnelgp's method and it doesn't work and post it here?
Because his code works here, no matter how many times I test.

 

[twgonder]

@ KitaYama Here you go. This is what I showed in post #33
Several developers in various articles, and some here, have said not to use autonumbers as a reference.
I get it as I watched this short video some months ago:
Microsoft Access AutoNumbers Are NOT For You (599cd.com)
So, I modified arnelgp's test .accdb with one field to incorporate this simple idea.
Cheers!

 

[KitaYama]

[Person Code] is set to NoDuplicate in your table design.
If you want to copy and save the new record, you have to change [Edit Person] prior to running with .RunCommand acCmdSaveRecord.
Because your Person Code can not be a duplicated text.

Or change the field property to accept duplicates.


No offense but we are on post 50 and it is a design problem. Yet you blame Access.

PS: And as a professional programmer you should know better not to use space in your field name.

 

[twgonder]

[Person Code] is set to NoDuplicate in your table design.
If you want to copy and save the new record, you have to change [Edit Person] prior to running with .RunCommand acCmdSaveRecord.
Because your Person Code can not be a duplicated text.

Or change the field property to accept duplicates.


No offense but we are on post 50 and it is a design problem. Yet you blame Access.

PS: And as a professional programmer you should know better not to use space in your field name.

As to the field space name, (I know, I know) that may have been an inadvertent error (I copied stuff quickly--it was an example and not a production db, so I didn't give a lot of attention to proofing stuff--the point was the error in copying). Check the next post for a cleaned up version that tackles your other issues.

 

[KitaYama]

Which field name do you see that has a space in it?

[Person Code]

 

[twgonder]

Okay, for those that stuck it out this far, this should help you see what I was looking for...
AND THE SOLUTION!
(Yes, I know, I know, it's not the only or ultimate solution, it's just mine for now.)

I tried to keep it simple. I used the original .accdb from post #17 and modified it to my standards.
Now, you may not like all the code, I'm still working on that and cleaning it up. The core of my experience is not in Access.
I cut this code out of my bigger helper form module (mod_Form), so there's a lot you aren't seeing.
There's also a lot of code and variables missing, for things like the user, that I fudged, to keep it simple.
I'm working on a template, and the helper form procedures are meant to be written once and used by all forms based on the template.
You may not like that idea, but if you do you can see how to implement it.

Code was also pasted out of other modules, so they wouldn't normally be in the form helper module. Again, to keep it simple.
There's some kernel stuff normally there, like the pause that I didn't add so no one can claim I crashed their system.
If you try to copy more than one record a second you will get an error, the error will result in a stop.
I use stop in development to catch errors and fix them, if you don't know how to use VBA don't create more than one copy a second!
As to this error, using now() is just a stop-gap measure until I get my sequential Id/Code routines written (another thread).

I tested the form based on the needs of the original post, and it worked in my Access 2021. Your version may behave differently, and you may crash the form if you try to do stuff outside the scope of this thread, I'm not going to test it for all possibilities, because it's not meant to be a fully functioning form for production.

My thanks to all those that were constructive in the development of this solution. Enjoy!

 

[KitaYama]

I'm still working on that and cleaning it up
there's a lot you aren't seeing.
There's also a lot of code and variables missing,
Code was also pasted out of other modules, so they wouldn't normally be in the form helper module
There's some kernel stuff normally there, like the pause that I didn't add so no one can claim I crashed their system.
If you try to copy more than one record a second you will get an error, the error will result in a stop.
you may crash the form if you try to do stuff outside the scope of this thread,
I'm not going to test it for all possibilities, because it's not meant to be a fully functioning form for production.

I really can't understand what's the point of posting a database with above situation.


And personally this section is my favorite:

if you don't know how to use VBA don't create more than one copy a second!

 

[twgonder]

I really can't understand what's the point of posting a database with above situation.
And personally[sic] this section is my favorite:

Well, it's not a database for anyone to use in production (as is customary in a Forum like this one).
It's a demo database that shows how one can copy a record,
and then most importantly based on my original post, how to move to that record.
Did it do that for you?
Now if you pick up any pointers (that I copied and modified from others in some cases) then great, no harm done.
As to the VBA warning, it's just that, a warning that came with an explanation (which you conveniently omitted).
If you can get that all done in three or four lines of code that are generalized and parameterized (not hard-coded), I'm all eyes.

 

[Pat Hartman]

No actual expert would ever suggest creating a macro. Seems like you're listening to the wrong "experts". arnelgp gave you the simple RAD solution using Access methods. He gave it to you in VBA because he is an expert and actually knows how to code. However, the others offered alternate solutions that don't make use of Access methods. Here we have the "let Access be Access" advice as opposed to the i have a better way suggestions. I don't write macros except for two that i keep in ALL my applications so I can't say whether when you use a macro, the copy works differently than when you use the VBA method but it might. There are several places of inconsistency in Access. They've been around for years so MS isn't fixing them. You eventually discover them and adapt. For example, the programmer who wrote the code for the CrossTab query enforced the rule that arguments should be defined but if you use arguments in any other situation, defining them is optional. If you create a Select query that references a crosstab, the requirement is enforced because the CrossTab is part of the total query.

If you had a query open that selected 5 fields out of 50, and you selected a row and hit copy, would you expect Access to copy all 50 columns or the 5 selected? How about if it selected only 10 rows? Would you expect the copy to return the 10,000 from the table? The bound controls of the form apply the field selection logic.

Best practice when using relational databases which I'm sure you already know is to NOT use Select * but instead to select the specific columns you want. Working with Access is no different. Why transfer data you aren't going to look at? Also, our queries should use WHERE clauses for optimum efficiency. Again, why select 10,000 rows when the user wants to see ONE. Access allows you to bind forms to queries without criteria or to naked tables. It then allows you to use the built in filters to whittle down what you are viewing. Since you have experience with RDBMS, you would never think of doing that even if you discover the "feature". When the BE is Jet/ACE this isn't a serious problem but if you want at some point to convert the BE to some RDBMS, you've made the conversion that much harder. When the BE is RDBMS, you absolutely do not want the server sending thousands or 10's of thousands of rows when you actually want just one. For this reason alone, developers are totally flabbergasted when they do a straight convert of an application that uses form filter methods to find that their database is slower with the SQL Server BE than it was with the Jet/ACE BE.

 

[moke123]

and then most importantly based on my original post, how to move to that record.
Did it do that for you?

No it didnt. I think it has something to do with the value of temp1 as .findfirst jumps all over the place. I'm assuming you want findfirst to go to the newly added record.
Then again I find your coding style impossible to understand.

 

[twgonder]

No actual expert would ever suggest creating a macro.

I agree, I think macros are obscure and not very flexible. But then, you have to ask, why did the "experts" that created Access create so many different macro actions? Why does M$ keep calling VBA code "macros" in the documentation? Very confusing for a RAD or any other db tool.

If you were referring to Richard's videos, He has several levels of instruction, and in most of his videos he is very clear that this is a macro, but he'll show the better way in VBA for his members. "You have to learn to walk before you can run", he often repeats in the videos.

My experience in dbs on MF/SM was quite different from the way Access works. Everything started at the record level and all the programming language commands in BASIC were geared towards that. You could, as an option, feed the programs a "list" of selected records based on the equivalent of a SQL with where, but then you had control on a record-by-record, field-by-field basis. So, things like the copy and move in this example thread were a trivial task. In a post on this thread (Solved - Sequential numbers | Page 4 | Access World Forums (access-programmers.co.uk) I shared a little snip of code that reads and writes sequential numbers very effeciently, no big SQL or DAO routines needed, just a simple read and write. It's too bad M$ didn't think to add something simple like that in Access, other than the DLookup function (I don't think there is an equivalent write function).

I expect that at the local level (i.e. a store or government office) the ACE BE should be able to handle less than twenty concurrent users without a problem. Two years ago I took my old db application and scaled the automated test to 100 users pounding tens of thousands of records each, and it didn't blink on a slower laptop, so Access ACE shouldn't be too bothered. This all thanks to how fast hardware is these days and with all that memory available.

Oh, oh, oh I'm curious if anyone has tried an Access BE on Amber (product on Amazon) just for fun? That should be a new thread I suppose.

 

[twgonder]

No it didnt. I think it has something to do with the value of temp1 as .findfirst jumps all over the place. I'm assuming you want findfirst to go to the newly added record.
Then again I find your coding style impossible to understand.

Odd, I've tested it about 50 times, and it hasn't failed me yet. It's supposed to jump to the new record that was copied. Did it go to some other record than the new one for you? A good way to tell is look at the minutes and seconds in the EntityCd text box (you may want to make that one character wider for the full seconds, or change the font size.). The name fields should remain the same. If it's not the current time, the form code went to the wrong record.

There's bits of code that are mine, and lots of modified from others, so I'm not sure which you're having trouble understanding. Doing an F8 through the code should provide some insight. Thanks for helping with the testing on other versions/computers.

 

[moke123]

It only jumped to the new record once. All other attempts it went to record 4 of 7, 7 of 15, etc. F8 is no help as it seems to be endless going thru loops like crazy.

I'm not sure which you're having trouble understanding

Pretty much all of it. I'm pretty active on several forums and most code I can follow. Yours not so much.