Solved In form, move to another record with known ID

[twgonder]

No, I set the break in the beginning of the code and had to hit F8 for 10 minutes.

Ya should have probably said that first.

I don't know what to say. Even if I don't stand on the F8 key, it takes me less than 60 seconds to get through the all the routines. Now, if you're reading each line of code in a loop over-and-over again after the first iteration...

More importantly, I'm curious where the form copy is failing for you.

P.S. I just had a funny thought thinking back on the career lament of some of my friends that are electrical engineers that design chips and boards. If moke123 had a problem with F8 for 10 minutes, then imagine twenty years of doing that, eight hours a day, every day (but with an oscilloscope)! That's why I escaped that world very early on.

 

[twgonder]

@ Pat about post #71, a quick question before I dig into your solution.
Will it copy fields from the record that aren't in the form, but are in the record, to the new copy record? Or will I end up with a bunch of null fields?
I added a label on the right side of the TWG.accdb frm_Entity form explaining this possibility. Even though those fields were updated in the
subroutine I wrote, there are many others that don't get modified before the copy and they aren't on the form.
The fact that they might not make as much sense in an Entity record copy doesn't mean they wouldn't in say a general ledger record that has lots of hidden control attributes that users don't enter in a form.

What can we do if the tag field is already used for something else like language translation?

 

[twgonder]

@ Pat, continuation, I also added in my logic a way to update another table. The reason for this is there might not be a copy command button to press on a form, but backstage logic might want to spawn a new record based on some entry in the current form based on business logic.
For example, a simple requisition form/table might spawn a facilites property manager request for in-house existence search.
If it fails existence in might spawn a new record for a purchase order.
Which may have its own life cycle of clarification or denial.
After PO approval a pending receipt order is generated.
After receiving has validiated the pending receipt, (with possible modification spawned records) a payment voucher record is spawned.
Upon approval a payment record is spawned.
Each step in the process has its own record that spawns a new record in a different table for the succeding life cycle record.
My generalized routine was created to handle this type of transaction lifecycle without hard-coding dozens of forms for common life-cycle field. controls. Now that I happen to use it for simply copying an existing Entity record in the demo TWG.accdb is just a cross-purpose intentional design.
All this is normally in the design of any enterprise system, but I didn't want to get that detailed in the original post #1. For the copy, I just wanted to move to the new record, and it was a given in the post that I knew the ID of the new copy record, which my routine does indeed know and you can see work in the demo.
Will your design handle this data processing reality?

 

[moke123]

What can we do if the tag field is already used for something else like language translation?

common to do that with instr()

 

[CJ_London]

or use the split function if you separate elements with a comma, semicolon, pipe, whatever

 

[twgonder]

... where the Access elves are being smarter than we are and rewriting the RecordSource to discard fields

When you say elves, do you mean the coders at M$ that are maintaining Access?

 

[moke123]

For the copy, I just wanted to move to the new record, and it was a given in the post that I knew the ID of the new copy record

And you were given the solution in the first few responses.

Will your design handle this data processing reality?

Trick question as it seems you keep moving the goal post and your existing code makes little sense to many of us.

Will it copy fields from the record that aren't in the form, but are in the record, to the new copy record?

That is no longer copying data in the form, it is copying the record in the table.

This example is just a slightly modified version of the first which will copy the entire record and allows for data to be modified. It will even add your entity ID.

 

[twgonder]

...
It is pretty rare that the user doesn't see all the fields of the main table but I don't always show the last update fields for user and time.

I've got lots of "personal" information fields, like mother's maiden name, that I put in a different form, and then control who has permission to that form.

 

[twgonder]

This thread has also gotten out of control because you keep moving the goal posts. I made the effort to understand your convoluted code and even simplified it for you by using Access objects with which you were not familiar. So I did it the "Access" way using collections and arrays.

I think I stuck to my original question for purpose of this thread and don't recall moving the "goal posts", with maybe one exception, which was more of a comment about Amber. Now, I did answer people's "why" questions, or commented/questioned on their proposed solutions. But I don't think you can count that as changing the original question I posted. I had no reason to move the goal posts as I mentioned earlier to you, the problem of copying was solved long before I even asked my "move" question here in post #1. It's you and a few others that decided to tackle the copy issue, which wasn't MY question.

It was nice of you to analyze the "copy" issue, but as I've said, it doesn't solve my particular need, which was, again, solved (maybe not to your standards) before I asked my "move" question. I'm not going to start putting lots of hidden fields on a form to get a solution that requires even more complexity, in my opinion, than the solution offered by a 20+ year Access developer and multi-year MVP, just because you have trouble understanding his code. I understand it quite well and it seems quite straight-forward to me. I understood it after 15 minutes of research on commands I was unfamiliar with, but I had no problem with the logic flow. To each his own.

 

[twgonder]

And you were given the solution in the first few responses.

Which post(s) do you think did that (a solution), based on my clarification of the method I was using shown in posts #5 & #8?

 

[moke123]

Which post(s) do you think did that (a solution), based on my clarification of the method I was using shown in posts #5 & #8?

Post 2 if you took the time to google FindFirst.
Then Doc's post albeit with a minor mistake.

 

[twgonder]

As it happens, I am also a 25+ year Access developer AND a multi-year MVP. And clearly, you are not interested in learning how to use collections and arrays so please use that junk code you got from the expert everywhere Just FYI, even using the split() function to separate your mushed strings into an array would simplify your code but then you've probably not examined the list of Access Functions by Category either which I referenced in some other post.

Glad to hear it, and again, thanks for your observations.
I just don't have time to reinvent other solutions when I have one that works fine.
I do want to learn new things though, that's where I would rather put my time.

 

[CJ_London]

the solution offered by a 20+ year Access developer and multi-year MVP,

Don’t believe you have ever provided a link to the solution provided by this MVP. Sure it won’t be the same as yours as you will have adapted it, but would be interested to see the link

 

[twgonder]

As it happens, I am also a 25+ year Access developer AND a multi-year MVP. And clearly, you are not interested in learning how to use collections and arrays so please use that junk code you got from the expert everywhere Just FYI, even using the split() function to separate your mushed strings into an array would simplify your code but then you've probably not examined the list of Access Functions by Category either which I referenced in some other post.

Well, if you do look carefully, you'll see that I did use split() in one routine (line 80 in the fSarrayPosition function). I've got more routines to write (for delete and insert) that would make working with an array a bit more cumbersome. So, I practiced with the simple one (extract) before moving on. These are the VBA versions of the long-ago assembly code I wrote into firmware, for systems that use variable length string arrays. The kind of arrays that Access uses in its storage under the shell if you use a hex editor to examine the file, but that M$ didn't bother to give us developers using their product.

 

[twgonder]

Don’t believe you have ever provided a link to the solution provided by this MVP. Sure it won’t be the same as yours as you will have adapted it, but would be interested to see the link

As you requested (or as they say in Colombia: A la orden):
Copy Records (599cd.com)

 

[twgonder]

Cognitive dissonance would explain why you prefer Richard's stuff behind the paywall. You paid for it. You are not paying for our advice so It is pretty pointless to keep offering it if you are going to choose incomprehensible variable names and incrementing character by character through a mushed string over a clean loop of a collection controlled by setting a value in the tab property so you have control over which fields to include. Or even bother trying for yourself the suggested similar loop to read through the Fields collection of the RecordSource of a form.


Yep. I've worked with several people over the years who've had 2 years experience 10 times. I guess you didn't understand my point about learning the "Access" way. You are always going to be unhappy with Access if you still think you are writing firmware. I showed you a clean, readable, and short way to save and copy the data from a form using Access methods and properties and yet you think your old firmware code style is better. OK. There is nothing I can say to that.

If you were to follow the link, and see the code, without paying, then you know your argument doesn't hold water. But Richard, being a cool guy, has maybe given me free access to some of his pages, I don't know. His solution worked, yours doesn't. It's that simple. Your form solution doesn't work for an entire record, which is what I need to copy. And what in Richard's code isn't the Access way? I don't see any C or firmware code in there anywhere. Richard typically posts the minimum solution and then states the constraints. My code just deals with those constraints (you wrongly think my "mushed string" has anything to do with the actual Access commands to write the duplicate record). Too bad you don't like it, and want to argue how correct you are with your solution that doesn't work.

BTW, your use of cogniative dissonance doesn't apply to this situation, the correct term would be confirmational bias (if there was any). I rather we call it being practical with a solution THAT WORKS. It seems to be you that can't accept there is more than one possible solution to a problem. Either that, or your pride won't let you accept that you aren't the master of all things Access. So, did you finally see my split() line? And adjust your erroneous observation? I didn't think so.

 

[twgonder]

I told you EXACTLY what you had to do. Do you expect me to do your job for you? Can't you switch one loop for another? You claim to be an expert programmer.

You and people like you are the reason I almost never build custom solutions here for free. If I have code, I share it. I share logic, and occasionally, if I think I might find the code useful in the future, I build an example. That is what I did here. I built code I thought I might use at some point because every once in a while I do an application that has Purchase Orders or Estimates or something that it is logical to copy with minor changes. HOWEVER, they involve copying a form record so that is the sample I built. I told you that the exact same logic works regardless of whether you were looping through the controls on a form or the fields in the RecordSource. Only the names of the objects change but you said my sample didn't to exactly what you thought it should do. Did you offer to pay me? Do you think I should do your thinking and your coding for you no matter how stupid the requirement? The way to copy a record in a table is to use a query, NOT to use the recordsource of a form!!!! That is what a non-programmer would do because he wouldn't know how to write DAO and apparently, neither do you. And then I told you WHY using the recordsource of the form would be dangerous but you ignored that also because you haven't run into the "bug" that isn't a "bug" but some elf in Redmond cooked up deciding to be "helpful" and making the RecordSource of the form more "efficient" by making it logically correct and selecting ONLY fields that were bound to the form, forgetting of course that forms have code modules written by peons like me and we might want to not bind all the fields to controls on the form. So, instead of giving us the benefit of the doubt and assuming that we built the RecordSource we actually needed, they are taking it upon themselves to build the recordSource they think we should have. So, persisting with your "solution" where you are using a form to do something that should be done with an append query or DAO, you will eventually run into the bug I told you about. Good luck then!

Well, it seems you just offered more reasons why your form solution is defective. Thanks. And you still haven't seemed to catch on to the fact that I had a solution for the copy before I asked my original question, which wasn't about how to copy. What can I say? Why would I pay you for something I didn't need, didn't ask you for, nor did we have an agreement to pay for services? Especially if you say I have to do the job and not you? That's just pure crazy talk Pat.

I don't claim to be an Access expert, but if you can't understand my simple VBA code (not firmware code, that was in assembly, it should be obvious to you as an Access MVP that my code wasn't assembly), then, again, what can I say? You did read my MF/SM BASIC code in the previously referenced post, yes? That was BASIC too, not assembly.

I'm happy with my solution that works. You seem to be very unhappy with that. Very odd.

 

[twgonder]

How about via con Dios

The correct way is: Vaya con Dios.
If it was a question, then you use ¿ and ?.
Proper syntax: "How about, ¿vaya con Dios?"
From educated people, you're more likely to hear, if it's not a question: Que te vaya con Dios.

 

[twgonder]

I'm going to take a moment to discuss something not related to Access, but brought up in this thread: cognitive dissonance.

It's, in simple terms, the discomfort related to two conflicting ideas in the consciousness that creates a desire for resolution.

Journalists, PhD candidates, law students and some scientists are trained to deal with cognitive dissonance. Sometimes people outside those disciplines can, on their own, learn to deal with the need to resolve conflicting ideas without taking a polar position. My mother, a teacher for 30+ years, finally fell to the saying, "To each his own."

Here's a good example: Is it better to use pesticides so that millions have sufficient food supplies, or that we avoid pesticides for better individual health?

This phenomenon was demonstrated quite well with the third link that Pat H. used in her post #66, contrary to the title of the article. The answer to her question was in plain view, in her own reference. It's demonstrated daily in the mass media, where mega corporations find profit in the selling of a one-answer-is-correct-for-all position for conflicting ideals. Politicians as well use this technique to polarize a base for their personal benefit. President Trump, unquestionably a media master of playing to the simple-minded, learned from his reality TV days, couldn't be a better example of this.

Any time you feel the need to justify a single position amongst alternatives, you might want to ask yourself, "can I, without falling victim to the proclivity to adhere to a personal bias, recognize the validity of two opposing positions at once?"