Solved In form, move to another record with known ID

[cheekybuddha]

Why does M$ keep calling VBA code "macros" in the documentation?

It doesn't when you look at Access documentation.

In Excel, VBA is called macros.

 

[CJ_London]

Why does M$ keep calling VBA code "macros" in the documentation?

That will be office documentation

But then, you have to ask, why did the "experts" that created Access create so many different macro actions?

for those who have simple requirements and don't want to learn VBA

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.

never heard of it, but definitely for a new thread. Not clear whether this is a service like onedrive/google drive or a net based rdbms. If the former, then no it won't work and if the latter an access FE should be able to connect to it providing it is ODBC compliant. Wouldn't have thought it was of interest to you since your objective is to avoid anything that relies on the internet.

 

[twgonder]

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.


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

Okay, I understand, a lot of times I don't understand other's code either. That's the way it is.
Are you saying you're getting caught in a real endless loop? In that case the form will stop working, I haven't seen that.
Yes, string array manipulations are all about loops, you can easily set a break-point for after their execution to avoid the loops.
Count, find, extract, replace, insert, delete are common enough string commands, just not in Access. So, I had to write the subs and functions. Maybe that's what's confusing to you? I'm pretty sure I added enough comments to explain what they're doing. They're not so important to the original post and purpose of copy. They're used to manipulate the record before write.
As to the record 4 of 7, etc. I presume you understand how random ANPKs work. That's Access, not my code.
Did you find a wrong name or wrong time after a copy?

Everything in my table exactly as it should be:

 

[twgonder]

never heard of it, but definitely for a new thread. Not clear whether this is a service like onedrive/google drive or a net based rdbms. If the former, then no it won't work and if the latter an access FE should be able to connect to it providing it is ODBC compliant. Wouldn't have thought it was of interest to you since your objective is to avoid anything that relies on the internet.

Amazon.com: AMBER X Smart Personal/Home Cloud Storage Device for Data and Media Files, Built-in 512GB High-Speed SSD with USB Storage Expansion, iOS/Android/Windows/Mac Compatible, Black : Electronics

They just aren't real clear as to whether it runs Windows Apps or not.

 

[twgonder]

It doesn't when you look at Access documentation.

In Excel, VBA is called macros.

I've seen it many times over the years. I'll keep an eye out and post back next time I see such a macro reference for VBA in M$ documentation.
(I'll just have to remember which thread this is.)

 

[Pat Hartman]

Not sure why you are using Option Compare Binary. That has no effect on how Jet/ACE work so you will be getting conflicting results with sorts and compares. Do you really want lower case a to sort after ALL the upper case letters rather than with the A's? I'm not sure where you are so I don't know what the local language might be. If it has accented characters, they will sort after the lower case characters. Take a look at the links for options and opinions.

Microsoft Access VBA Module Options Settings

Setting Microsoft Access/VBA Module Options Properly

www.fmsinc.com

VBA Tutorial => Option Compare {Binary | Text | Database}

Learn VBA - Option Compare {Binary | Text | Database}

riptutorial.com

Note: The use of this setting is discouraged unless the module is used for writing custom Access UDFs (User defined functions) that should treat text comparisons in the same manner as SQL queries in that database.

Pros and Cons of Option Compare Binary/Text in VB.NET

What are the pros and cons of standardizing on using Option Compare Text vs Option Compare Binary for VB.NET development? --- EDIT --- Just some background since it seems like it would help - my

stackoverflow.com

I also don't understand why you would ever think that a generic copy command would ever perform the customized action you wanted. When you ask for help, you get far better replies if you tell us what you actually want to do and "copying a record and moving to it" isn't even close to what you wanted. The three lines arne gave you did that even though you couldn't get it to work, although that was probably because you didn't tell us the whole picture.

I guess you posted your solution to elicit comments so here goes. I agree with moke123's response but here are some more specific and I hope, helpful comments.

1. Combining multiple commands (or dims) does not speed up execution or save space. It does however, increase the length of a line of code which decreases readability and therefore increases the chance of the reader missing one of the actions. Poor practice.
2. Single dimensional arrays are normally multiple instances of the same variable. 31 instances of attendance by day. 12 instances of auto expenses by month. 22 instances of headcount by department. Your array is 8 different variables, 2 of which are actual arrays and should be stored as such. (7) and (8) should each be a single dimensional array or both could be one 2-dimensional array whichever floats your boat.
3. Your reason for creating the array is to "simplify" the coupling but you've made the code much more difficult to understand because the index number has no meaning making it useless as a variable name and even worse than that, as you pass the data around, you switch to using arguments and so the variables change names. Really poor practice. A far more easily understood method would be to use TempVar's (Introduced in A2007) for (1) - (6) OR create your own DataType that includes the 6 named variables and then use arrays instead of strings for (7) and (8). That would cut out all that code where you loop through a variable character by character to determine its length and what it holds.
4. If you ever worked with CICS or IMS/DC, you would recognize the Access form for the human interface that it is. If you only ever wrote batch programs, Forms would be completely alien. Forms are a little like bound web pages but with local processing. The only remote processing is the FE's interaction with the BE when the BE is RDBMS. Web pages are more like very pretty CICS transactions that can be more than 80 characters wide and 24 rows long and are processed at the server using the concept of exchanges You call up a page. You send the page to the server. The server sends a response. Little to no processing is done locally due to requirement of being able to run a page on virtually any OS or brand of browser.

If I have time tomorrow, I'll create a non-generic version of your copy because the common part is too trivial to bother making generic.

There is one aspect of your plan that is very wrong and my non-generic solution won't resolve the issue although it could. It lies in the difference between the macro solution and the custom one you've developed. I don't know what MS is doing behind the scenes in the macro because the three line solution doesn't mimic it but you get to the "new" record with the columns filled in but the record is still dirty. That means that you have a chance to modify the data before you save a useless duplicate record. Also, because the form is dirty, your validation code in the form's beforeUpdate event will run. I'm not sure it does with the three line solution and it absolutely doesn't run with your custom solution.

I've created hundreds of small and large applications and copying a single record never figured largely in any of them. Certainly not enough to prompt me to try to standardize it. One recent client wanted the ability to copy bids and purchase orders and also to make a bid into a sales order. The key to the three actions was the copy process prompted for some data fields in the Order or Bid so that the copy had some different identification than the original. The parent record was copied using the .AddNew method to make it easy to change the appropriate fields and to capture the generated PK. The child table was copied with an append query that took two variables - the "from" PK and the "target" FK so it would select the children of the correct parent record using the "from" PK and then append them using the ID of the newly added parent as the FK so they got added to the new parent record. The longest part of the code was the field - field mapping of the form to the recordset for the .AddNew. The rest was probably less than 20 lines of code. The AppendQuery was a querydef with two arguments because I never write embedded SQL unless it is dynamic.

 

[moke123]

Are you saying you're getting caught in a real endless loop?

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

Yes, string array manipulations are all about loops, you can easily set a break-point for after their execution to avoid the loops.

Ya should have probably said that first.

 

[twgonder]

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.)

 

[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.

 

[Pat Hartman]

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.

I'm talking about how we read. We are much more efficient with tall, thin text than short, wide text. Also, you can alphabetize the tall, thin and you wouldn't bother with the short, wide. And if you're defining 50 variables, you are probably doing too much in the procedure.

I'm not sure what to say about your observations on arrays. There's nothing that says arrays have to be "same data types".

Correct. My view of arrays comes from COBOL tables where all the rows were the same data type just like an actual table and could actually include multiple fields per "row". To do that with VBA, you need a multidimensional array. But making arrays of disparate fields simply makes no sense even though you can do it.
You can refer to NewID or you can refer to gSp(1). You can refer to TableName or you can refer to gSp(4); or you can refer to PK or you can refer to sSp(6). What did putting the different pieces of data into an array do for you? Absolutely nothing except to make the code difficult to follow. Referring to ExpAmt(i) does make sense. That would be an array of multiple instances of ExpAmt

And then the things that should be arrays in your model are mushed pieces of text that you need to loop through character by character to decode whereas, if you had just put the values into an array, you could avoid all that counting characters code to separate the data as you use it.

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.

That IS the problem. The copied record which is meaningless since it is a duplicate, has already been saved. The macro code copies the code into a new record that you are now positioned on but leaves the record dirty because it has not yet been saved and it is still under the control of the form. If you think about the code that the macro generates it is more like - move to a new record, copy field by field, give focus back on the new record. Whereas the suggested three lines is copy, paste append (which adds a new record and saves it), move to new record. As I said, I find so little need for this type of code that I've never experimented with it. I'll see if I can copy, move to new record, then paste (just plain paste rather than paste append). That should leave the new record dirty. Of course it doesn't fit with what you want to do.

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.

That field is changed by your copy code, NOT by the BeforeUpdate code. You are using DAO to save the record, NOT the form so the form events don't run.

"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.

But it included "how to find the ID" which is how we got to the copy part. The only reason you don't know the ID is because you just pasted a new record.

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

The form failed for me the first time also. I wasn't stepping through the code so I don't know what happened either.

 

[Pat Hartman]

I added a new button to the form to show you how to take advantage of Access and work with its collections and to properly use arrays.
Here's the copy code for those who don't want to open the db. To indicate which fields to copy, add a numeric value between 1 and 255 to the tag property of the control.

The upside to this method of copying is that it copies the common fields but leaves the fields that need to be filled in for the copy empty AND leaves the form positioned on the new record and the new record is dirty (has not yet been saved) so you can add the necessary data and then save the record which causes the Form's BeforeUpdate event so your validation code will run.
I think you'll all find it significantly easier to follow

Option Compare Database
Option Explicit

Public FormFields(1 To 255) As Variant

Public Function LoadFormFields(frm As Form)
Dim ctl As Control

    For Each ctl In frm.Controls        ''' Remember - do NOT tag the PK.  It can't be copied.  Also, don't tag any field with a unique index
        If IsNumeric(ctl.Tag) Then
            FormFields(ctl.Tag) = ctl.Value
        End If
    Next
End Function

Public Function FillFormFields(frm As Form)
Dim ctl As Control

    For Each ctl In frm.Controls
        If IsNumeric(ctl.Tag) Then
            ctl.Value = FormFields(ctl.Tag)
        End If
    Next
End Function

This code is in the click event of the button and in the BeforeUpdate event to call the process and then to finish it if there are some not visible fields that need to be populated.

Private Sub cmdCopy2_Click()
  
    WhichCode = 1
    DoCmd.RunCommand acCmdSaveRecord
    If Me.EntityID & "" = "" Then
        MsgBox "Please select a record to copy.", vbOKOnly
        Exit Sub
    End If
    Call LoadFormFields(Me)
    DoCmd.GoToRecord , "", acNewRec
    Call FillFormFields(Me)

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If WhichCode = 1 Then
        Me.UpdtUserId = 111
        Me.UpdtPgm = Me.Name
        Me.UpdtStmp = Now()
    Else
        Dim temp1 As Variant
        gSp(1) = "": gSp(2) = 81
        Call sFormProc(FrmNm)
    End If

    WhichCode = 0
End Sub

I minimized the validation but you can add some to ensure that the tags are in the range of 1-255. I didn't bother because, you'll run into an error as soon as you run the code and can fix it immediately.

 

[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

 

[Pat Hartman]

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

No, but I told you how to change it. Instead of looping through the controls, you loop through the ControlSource. But beware, Access forms are now acting like Reports where the Access elves are being smarter than we are and rewriting the RecordSource to discard fields not bound to controls. So, if you don't bind a field to a control, Access might remove it from your control source when you least expect it because it is trying to make your SQL efficient.

Each step in the process has its own record that spawns a new record in a different table for the succeding life cycle record.

That is done with an append query that copies the common fields but replaces the ones that change such as the date with a new value. You would never have to visit each record MANUALLY in order to have it processed by a form.

 

[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?

 

[Pat Hartman]

That would be the people who do things in the dark of night and the next day (depending on your Office update cycle) something might work differently than it did before. I haven't put my finger on it yet so I can't document when it happens, but the RecordSource of a form is now working more like the RecordSource of a report so I have taken to binding fields to hidden controls when I want to ensure I can still reference them in code. Just a warning. You may find yourself unable to reference in code any field in the RecordSource that is not bound to a control of a form. Existing forms don't break but if you modify a form, it might break.

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.

 

[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.

 

[Pat Hartman]

As I've said more than once, if you want to copy the whole record, use an append query. You can build the query in VBA if you want to modify specific values.

Instead of asking if my sample will do something, read it or test it to see. Obviously since it is looping through the controls collection, it is not going to pick up any unbound field from the recordset. I told you to either bind all the fields you are interested in. Make them tiny and hidden. I always color the background of my hidden fields a bright yellow so I can see where they are when I need them. If you want all the fields, either loop through the fields collection of the RecordSet OR use an append query but heed the previous warning about the elves changing your Recordset to "help" you when you don't bind fields to controls.

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.

Please, if you have a new problem, start a new thread and close this one.