Solved In form, move to another record with known ID

[arnelgp]

like i said you only need these 3 commands and it will copy the record and will bring
you to the New record:

    With DoCmd
        .RunCommand acCmdSelectRecord
        .RunCommand acCmdCopy
        .RunCommand acCmdPasteAppend
    End With

Plus, add code to change any field from the new record.
see the code on the button.

 

[moke123]

My bad I meant Field Names.

Dictionary is a scripting dictionary. They are similar to a collection or array but much easier to work with, IMO.

https://learn.microsoft.com/en-us/o...ference/user-interface-help/dictionary-object

 

[twgonder]

like i said you only need these 3 commands and it will copy the record and will bring
you to the New record:

    With DoCmd
        .RunCommand acCmdSelectRecord
        .RunCommand acCmdCopy
        .RunCommand acCmdPasteAppend
    End With

Plus, add code to change any field from the new record.
see the code on the button.

Thanks for that sample db. I am looking it over right now.

Follow up. I asked why it's so complicated in VBA, in most of the sites I've searched for, especially given that I've seen it done with macros (not VBA) in a demo video. But I was never able to convert the macro to VBA to see how it would look. This solution is too simple! I'm going to test it some more.

However, I do call a sub from the form procedure to do some generalized checking before the copy, and as you saw to further modify some of the data of the new record (similar to what you did with the middle name but with extracted fields and codes) Would there be a way to do the DoCmd.RunCommand from a module outside the form class module? Or is it better to sub to external procedure for error check, come back to form procedure for copy, then go back to external sub for data modification?

The reason for this is I'm creating a standardized template form, which has all the logic any other form might need to call one big helper module that all forms can use. The helper code is another module dedicated to handling all the things any form might want to do: new record, delete, copy,, change backcolors, enable command buttons, allow only viewing data, printing, etc.

 

[arnelgp]

just a side note, you need first "to be in the correct" record to copy before pressing the button.
otherwise it will just copy whatever the Current record on the form.

EDIT:

the advantage of this over the Recordset method, is that you don't need Another Code to copy/duplicate
an Attachment or Multivalue field. it will copy also the attachment/multivalue field.

 

[Pat Hartman]

The helper code is another module dedicated to handling all the things any form might want to do: new record, delete, copy,,

There you go thinking you're smarter than Access. Doing things the the Form, which is itself a class module does for you. No wonder you are so unhappy with Access. I'll suggest again, let Access be Access and YOU learn how to take advantage of what the RAD tool is doing for you rather than attempting to remake it in your own image. OR, pick a different platform that won't interfere with your image of what a perfect form should be

 

[CJ_London]

The helper code is another module dedicated to handling all the things any form might want to do: new record, delete, copy,, change backcolors, enable command buttons, allow only viewing data, printing, etc.

Do this in a 'helper form' with buttons/combos/whatever and use as a subform to your 'user forms'. The helper form then just needs to reference the parent form to delete/copy/navigate/whatever. Providing the code that does not work off a control event is public, the parent form can call it - for example to disable or hide certain controls in the helper form, standardise form colours/fonts etc

Commonly used for form navigation/saving/undoing/printing/displaying 'common data' such as user name, time, whatever.

I have one I call a 'green board' which checks the health of the data in the parent form - it displays a a series of boxes, green if everything OK, red if there is incomplete data or data which needs rationalising. User clicks on the appropriate box to display the relevant records with a brief description of the issue. They can then click through to the offending record/s.

 

[twgonder]

There you go thinking you're smarter than Access. Doing things the the Form, which is itself a class module does for you. No wonder you are so unhappy with Access. I'll suggest again, let Access be Access and YOU learn how to take advantage of what the RAD tool is doing for you rather than attempting to remake it in your own image. OR, pick a different platform that won't interfere with your image of what a perfect form should be

Actually, I'm pretty happy with the result of this effort. And you're right, I don't want to use macros to do the task of copying, I want VBA code to do it (because I have error checking wrapped around it). I think my overall form solution will make life easier for those with less than perfect eyesight, and give less tunnel carpel syndrome to the users (both of which have affected me over the years). Not to mention a more foolproof way to avoid errors during record navigation. The helper module ensures I won't be writing the same code over and over again with slight variations and different errors. I'm not saying you have to do things my way, I've just been seeking solutions for the way I want to do it. Cheers.

P.S. I'm busy on a legal matter, but I took a moment to copy this. I had posted a similar image weeks ago as a desired form template, but thanks to all the good help, I'm about 95% there. My friend is waiting for the Spanish translation software part to work, but is happy with the overall design.

I'm looking forward to the challenge of filling in that central blank part of the form with his desired facial recognition of a customer as they walk through the store doors, which then pops this form. He can use the salutation here, or with one click get an "apodo" (friendly nickname) for the customer. Or he can just call them by where they were born, a form of special recognition and pride here in the Latin Countries. He wants to print the form, with the photo, to help himself and employees memorize the names of his best customers. Don't worry, I won't be saving the photo in the Entity record.

With a click on the Relation command button, he can inquire about other family members. Or with a click on the Address button, he can ask about the barrio (although his clients typically own huge haciendas that Pres. Trump could only dream of owning--these guys make Pablo Escobar look like a ghetto hood-rat, and Pablo's once wealth makes Trump look like a pauper). I'm trying to make the application generalized enough that it serves less prosperous business too, that my other friend is trying to grow.

 

[Pat Hartman]

Not sure why you thought I was recommending macros. No one who can write VBA would ever recommend using a macro. Their logic is bizarre at best. The three lines of code recommended by arnelgp solve the problem if you only want to copy one record. If you want to copy something like an order, then you are dealing with one parent record and at least one set of child records and that requires more VBA as well as an append query with two parameters so you can provide the FROM FK the the TO FK to copy the child records.

 

[twgonder]

Not sure why you thought I was recommending macros. No one who can write VBA would ever recommend using a macro. Their logic is bizarre at best. The three lines of code recommended by arnelgp solve the problem if you only want to copy one record. If you want to copy something like an order, then you are dealing with one parent record and at least one set of child records and that requires more VBA as well as an append query with two parameters so you can provide the FROM FK the the TO FK to copy the child records.

Maybe it wasn't you that said the designers of Access at Microsoft created one way to best do things. What could be more RAD than this macro?
Dang, these pages are driving me crazy the past two days with weird stuff, anyways
I said, I suspect "The three lines of code recommended by arnelgp" will fail when there is a no duplicate index other than the ANPK.

 

[twgonder]

The macro fails, but it's not clear why. I suspect the second index that doesn't allow duplicates. Or some form code to control navigation. I'll need to check deeper when I have time.

P.S. I turned off the index and all the navigation controls and the macro still fails without any kind of message that explains why. Okay, enough of that.

P.S.S. Great, removing that command button with the macro just crashed Access and hosed my .accdb file. Gotta love this RAD development. One step forward, two steps back. And it's me that is doing weird stuff, making Access crash with my crazy crap huh? I added a command button, clicked the wizard options, tested it, it failed and deleted the button->crash & contamination. Wow, really wild things I'm doing, huh? Stuff that's right out of the books, in-class and on-line courses I've taken. Didn't touch one line of VBA code to make this happen. I didn't even make it to closing/saving the form before it crashed. Just like when I took a three-week course in Access in 2,000, the instructor (M$ certified instructor, MCSE, etc.) spent half his time running from computer to computer trying to figure out why his simple teaching exercises were crashing student's simple forms and reports left and right.

 

[twgonder]

There you go thinking you're smarter than Access. ...

Oh gosh, here's a guy that truly thinks he's smarter than Access, and one of his customers too. In fact, he has a whole website dedicated to the idea. I'm really hanging out with a bad crowd here in the Access universe. Imagine, him and his 50,000+ Access followers pounding square pegs all day for a product that is perfect in its roundness
Record Locks in Microsoft Access (599cd.com)
Too bad Nautical Nut Guy is ignoring me. He might learn something from the video instead of just posting insipid comments that demonstrate his ignorance.

 

[Pat Hartman]

Only people who don't know how to use VBA use macros. You claim to know how to code. Start by changing the option for events from macros (for people who don't know how to code) to VBA. Then don't let Access create embedded macros on your behalf when you add buttons. If you have macros already, open the form/report in design view and then choose the option on the ribbon to convert to VBA if you want to use VBA. Warning though, the conversion is buggy.

Gotta love this RAD development.

Always blame the tool.

 

[twgonder]

... The three lines of code recommended by arnelgp solve the problem if you only want to copy one record.

Here is the "solution" failing, as I thought it would, but not for the first reason I suggested (although it fails for that reason too). Apparently, the solution doesn't copy record fields that aren't on the form. I think I had read that somewhere before, and the idea just sat there in my intuition. However, the lines of code I posted do work for tables with more indexes than the primary key and for fields that aren't on the form.

Oh silly me for being a doubting Thomas and not trusting in the "intelligence" of the Access RAD designers.
Where the F is Nautical Nut Guy now that we need is genius solution, or to argue that I'm using a square peg by using no duplicate indexes (that Access so generously applies to any field that has ID or Code in its field name)?

 

[Pat Hartman]

Apparently the three lines of code were too complex to understand. So, duh, yeah, they copied the current record. Not sure why you expected anything else. Always blame the tool.

You could tell us what you really want to do rather than making us guess.

 

[The_Doc_Man]

@twgonder -

First, just for the record and for future reference,

Oh gosh, here's a guy that truly thinks he's smarter than Access, and one of his customers too.

If you are referring to Pat Hartman, Pat is a lady.

This next comment is made in my role as a moderator who takes that role seriously and who deeply appreciates the technical value of this forum.

twgonder, I understand you are frustrated by the response you are getting here, but you are presenting an attitude that frequently verges on "highly disrespectful." TONE IT DOWN. But you are not entirely to blame, so other members, please watch out for YOUR disrespectful posts too. If you don't like twgonder's response, just don't react to it. We are all supposed to be professionals here (outside of the Watercooler).

 

[twgonder]

@twgonder -

First, just for the record and for future reference,

If you are referring to Pat Hartman, Pat is a lady.

This next comment is made in my role as a moderator who takes that role seriously and who deeply appreciates the technical value of this forum.

twgonder, I understand you are frustrated by the response you are getting here, but you are presenting an attitude that frequently verges on "highly disrespectful." TONE IT DOWN. But you are not entirely to blame, so other members, please watch out for YOUR disrespectful posts too. If you don't like twgonder's response, just don't react to it. We are all supposed to be professionals here (outside of the Watercooler).

Sorry about the junk images in my response, the web page is putting them there not me (if they post)
Please read what Pat said in my response post #27.
My comment about the smart guy was talking about Richard Rost and his site dedicated to making Access better for the user, and not for Pat.
Normally, I'm only a dick in response to people that insist on being dicks (I have my bad days too I suppose).
As a moderator, thanks for reading carefully what I actually post.

 

[twgonder]

Apparently the three lines of code were too complex to understand. So, duh, yeah, they copied the current record. Not sure why you expected anything else. Always blame the tool.

You could tell us what you really want to do rather than making us guess.

I understood the code. I didn't think the three lines world work based on my understanding, and I was correct.
And no, they didn't copy the record if you look at my posts carefully.
I said very clearly what I wanted in post #1. I wasn't looking for a three line solution that doesn't work for 90% of tables.

 

[twgonder]

Only people who don't know how to use VBA use macros. You claim to know how to code. Start by changing the option for events from macros (for people who don't know how to code) to VBA. Then don't let Access create embedded macros on your behalf when you add buttons. If you have macros already, open the form/report in design view and then choose the option on the ribbon to convert to VBA if you want to use VBA. Warning though, the conversion is buggy.

Always blame the tool.

Gee, I did write the code, AND POSTED IT (#8), before following your suggestion to use the RAD tools provided by M$ in Access instead.
On my version (2021), Access it won't convert this type of macro to VBA that it attached to the form with the wizard.
Okay, I'll start blaming Santa Claus for the Access crashes when doing basic design tasks.
I'm not the only one having these problems, the guy (Richard) has pages and pages and lessons dedicated to trying to avoid them.
But, what the hell does he (Richard) know after almost thirty years with Access and a successful business dedicated to it?
I do search Richard's site, and three forums before posting here.
It's not like I'm a complete idiot trying to make life difficult for myself, but I often find a wall in Access, the same kinds of walls that Richard has hundreds of work-around tips on. You did go watch the link I posted before responding, right?

 

[Pat Hartman]

I don't think I can help you. @arnelgp can explain why his solution copies the current record and then moves to the new record which is what you asked for.

 

[twgonder]

I don't think I can help you. @arnelgp can explain why his solution copies the current record and then moves to the new record which is what you asked for.

No, that's not what I asked for. And I gave examples in post #33 why the proposed solution doesn't work. No explanation needed, thanks.
But, thanks for the helpful links you do provide, they often give me a different perspective on a possible solution found elsewhere.
Heck, I even tried your RAD solution too, see I´m not so stubborn.