How to copy the record from the code?

[sabavno]

Hi,

On my new record entry form, I have a field "Quantity". If for example the quantity field has a value 3, when I add the new record, I need to add it three times, so I would get three identical records (only RecordID (autonumber) will be different for those record)

How would I do that?

Thanks.

 

[jebry]

Hi!

In the AfterUpdate Event of the form, use the following code:

Dim intNumberOfRecords As Integer
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)
intNumberOfRecords = 1
Do Until intNumberOfRecords = Me!Quantity
With rst
.AddNew
!YourFirstField = txtFirstTextBox
!YourNextField = txtNextTextBox
etc.
.Update
End With
intNumberOfRecords = intNumberOfRecords + 1
Loop

Set rst = Nothing

hth
Jeff Bridgham
bridgham@purdue.edu

 

[sabavno]

Thanks,

I have so many text fields on the form, I thought maybe I could avoid doing
.AddNew
!YourFirstField = txtFirstTextBox
!YourNextField = txtNextTextBox
etc.
.Update

Could I just copy the record?
Is there such SQL command or anything?

 

[jebry]

Hi!

I spent some time looking at this (I thought it might be useful at some point), but I didn't find any solution. You could set up an append query to run which would reference the forms, but setting up the query would take as much time as writing the code, though it would probably run faster than using the recordset. I tried to do a copy --> go to new record --> paste, but that change the Pk too so I got an error message.

Sorry I couldn't help more.

Jeff Bridgham
bridgham@purdue.edu

 

[sabavno]

Thanks a lot
I'll probably go with
.AddNew
ets.
.Update
scenario.

Thanks again.

 

[CVigil]

Hrrmmm... If you bound the form to a table independent of, but identical to, your actual destination table -- it would be a waystation for entered records, if you will -- then you could allow data entry as normal on the form. In fact, with what I'm about to propose, the form should be for data entry only, not for editing or viewing of already-entered records. Once a record was entered into this waystation table, you could copy the record from that table as many times as needed to the actual destination table. Then delete the record from the waystation table.

You could use the Fields collection, and loop through that, copying values. Then change the primary key values *before* using the .Update command. That way, you don't have to explicitly list/name any fields, except the primary key fields. (And if you didn't want to explicitly name the primary key fields, I'll bet there are properties -- probably related to the indexes of the recordset/table/whatever -- that you could check as you loop through the table fields.)

Something like (some pseudo-code coming):

'[...]
For intLoop = 1 to Me![Quantity]
    With rstDestination
        .AddNew
        For Each fld In rstWaystation.Fields
            'avoid errors - don't try
            'to change autonumber fields
            'I don't know if there is a
            'property that will tell
            'you if a field is an AutoNumber
            'field; there probably are 
            'properties that only exist
            'if a field is an AutoNumber
            'field, so you could test for
            'those.
            If (fld.Name <> AutoNumberFieldName) Then
                '.Value property is default for fields
                .Fields(fld.Name)= fld
            End If
        Next fld
        'Here's where you change any primary
        'key fields that aren't Autonumber;
        'you'll have to explicitly list them
        'and set their values yourself.
        .Update
    End With
Next intLoop
'[...]

That should be clearer in the module editor where you can see the text in different colors, and no wrapping problems.

Anyway, that's one other approach. And if your AutoNumber field is the only field that has to be different, your waystation table could simply be identical to the other table *except that it would be missing the AutoNumber field*. Then the code above, copying only the fields in the Waystation table, would do what you want without even trying to overwrite the Autonumber field. All Destination fields that have a counterpart in the Waystation table get values from the Waystation table. AutoNumber fields get a value automatically. Any others rely on you.

Hope this helps, or at least presents an interesting alternative to read about. ;-) -- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot

 

[MichaelRed]

But if you follow CVigil's general approach, just bind the Form to the temp (waystation?) table -at least for the fields which are part of your 'main' table. Include the control (textbox?) which decides how many records you want, and a command button for the actual creatrion of the main table records. In the on click even of this command button, do all the necessary V&V stuff. When the record is acceptable, append the record to the temp table. Next, just run a standard append query form the temp table to the main table for the number of records required. Finally, do the delete of the single record in the temp table. If you attempt this in a multiuser environment, place the temp table in the front end.

On the other hand, 'data replication' for the fun of it is generally discouraged, so I can only hope you have done a THOROUGH review of the relational database necissities for this DRACONIAN approach.

The 'clue' seems to be the name of the field ("Quantity&quot, which 'looks like' an inventory table where you are adding an identical record for each item even htough they ave the same SKU. This is radically inefficient and difficult to manage, not to mention counter to many /any / all inventory db approaches I have seen (not that I've seen all possible or even all good ones, but there have been quite a few).

On the last note I will interject, you can -with CLEVER naming of your form controls to a pesudo match to the field names, you CAN avoid the tedious typing of listing each control:

Public Function basAddMultiRecs()

    'Borrowing LIBERALLY from Jerby's code

    Dim rst As DAO.Recordset
    Dim Idx As Long
    Dim fldName As String

    Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)
    Idx = 1

    'The following PRESUMES that each BOUND field has a txtbox with the NAME _
     which is the SAME -except for the prefix "Txt".  Thus the table FIELD _
     'MyFooBarField' has the FORM control 'txtMyFooBarField' bound to it. _
     Further, NO control on the FORM has the prefix 'txt' UNLESS it is bound _
     to a table field.  And FINALLY, all V&V is accomplished PRIOR to calling _
     this routine

    Do Until Idx = Me!Quantity
        For Each Ctrl In frm.Controls
            fldName = Right(Ctrl.Name, Len(Ctrl.Name) - 3)
            rst.AddNew
                rst!(fldName) = Ctrl
            rst.Update
            Idx = Idx + 1
        Next Ctrl
    Loop

    Set rst = Nothing

End Function

Since I have not actually run this specific incarnation of the concept, I'm sure there are some issues / problems. It is intended ONLY to illustrate that -with some clever naming convention- that the exhaustive listing of the filed assignment to control value is not just possible, but is a convenient approach to populating a recordset from a form. Generally, this is one of the more common approaches when using unbound forms for data entry. The only real variation here is the already mentioned (in the NEGATIVE sense of 'mention') multiplicity of the record entry (HISSSSSSSSSSSssssssssssss bbbbbbbbbbOOOOOOOOOOOOOOOO!!!!)



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CVigil]

Sooo, MichaelRed, tell us how you really feel .


What does "V&V" mean, please?

You're right about, "But if you follow CVigil's general approach, just bind the Form to the temp (waystation?) table -- at least for the fields which are part of your 'main' table." I quote myself, " If you bound the form to a table independent of, but identical to, your actual destination table -- it would be a waystation for entered records, if you will -- then you could allow data entry as normal on the form." Good to see two great minds thinking alike .

I agree that it would be interesting to see what sabavno's project is; but I've also learned that there are indeed all kinds of projects out there, with all kinds of needs. And of course, being helpful human sorts, we'll suggest what strategic improvements we think we notice. (While I'm addressing this, readers will please note that MichaelRed *did* offer help, as well as wonderment .) Perhaps all sabavno needs is a field containing the count, appended to the record information, but we don't know enough to be sure of much yet. Who knows what we'll learn about what happens to the individual records later ? Who likes a good mystery, raise your hands!

MichaelRed himself notes that, "Since I have not actually run this specific incarnation of the concept, I'm sure there are some issues / problems." With that said to avoid the appearance of criticism, I'll continue to help with the group project. One detail I noticed in a quick read was that, in the "For Each Ctrl" loop, there wasn't any checking of the form control name to make certain that we *only* process controls whose names begin with "txt", since MichaelRed's strategy and code comments state that,

"'The following PRESUMES that each BOUND field has a txtbox with the NAME _
     which is the SAME -except for the prefix "Txt".  Thus the table FIELD _
     'MyFooBarField' has the FORM control 'txtMyFooBarField' bound to it. _
     Further, NO control on the FORM has the prefix 'txt' UNLESS it is bound _
     to a table field."

The idea of using a command button to initiate the copying is a decent one, MichaelRed. sabavno, that way you would *know*, explicitly, when the record copying was taking place. My thought before that was to simply have the record duplicated when it was added; say, in the Form_AfterUpdate. But that was also with the caveat that the form be used *only* for the addition of new records, not for viewing already-entered records. So each record would only ever be updated (in this form) once.

For that matter, with your duplication of records, and in your situation, what happens if a change needs to be made to a record after entry? For instance, I'll refer to a Record1 as a record that I have just entered with a Quantity value of 3. So I entered Record1, and Record2 and Record3 have just been created and added to the same table. The three records now exist in my Destination (main) table. With this hypothetical instance, I think of a few possibilities. 1) I made a legitimate typo during entry -- the Manager (he says, making up a field) should be Dawn, not Greg; I just got distracted during entry, but all three records should have Dawn in the Manager field. 2) Some piece of information related to Record1 actually *changes*, and should be changed for all records "spawned from" Record1, because of their relationship to each other and reality; in this case, Dawn *replaces* Greg as the Manager with respect to Record1 and all records "spawned from" Record1, meaning Record2 and Record3. 3) Some piece of information related to Record1 actually *changes*, but should *not* be changed for records "spawned from" Record1, because of their relationship to each other and reality; in this case, once created, the records are independent of each other with respect to the Manager field. In this case, Dawn *replaces* Greg as the Manager with respect to only Record1, but *not* with respect to Record2 and Record3, in which Greg remains the Manager.

Case 1 would be difficult to avoid in most any database. Where people enter data, they make mistakes ... ummm ... *we* make mistakes. (In one project I built, records represent revisions of materials. There is a form for entering new material revisions [i.e. updates to old revisions], which also forces updates to revision level of other records using the material just changed. But I also had to provide a form for changing a material revision *without* forcing an increment to the revision level of the material or to the revision level of the records using the material -- essentially allowing changes that were not "actually changes to the material (revision)". This second form was for correcting typos, where the material revision's information hadn't actually changed, but the info in the database had to be corrected.)

But Case 2 and Case 3 might or might not each apply, and with respect to different fields in the record. In other words, some fields might have to be kept synchronised (or not), and some fields perhaps should *not* be kept synchronised (or not). But I point them out for consideration; for future readers, if sabavno already has these things in mind.

Well, I'm done for now . Looking forward to replies... -- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot

 

[MichaelRed]

Hmmmmmmmm,

Yes, the missing check occured to me in the dark of my re-generation cycle ... in the harsh light of sunrise, it did appear to have (again) escaped my attention. So, thanks for the reminder. I will assume that those interested in the approach will be able to implement the check w/o additional instruction.

It also escaped the earlier post that the 'choice' of prefix (on my part) was more or less purely reflexiv, and -perhaps- a poor one at that, since it is a common choice from several of the popular naming conventions in use.

A somewhat more optimistic aproach to the overall soloution might even attempt to use the ControlSource property of each control to detemine which Control on the Form was related to the individual fields of the RecordSource. While this works well for simple textboxes, combo boxes, option groups and other controls may not have un-ambigious control sources, and require subnstantial additional code to determine what, if any, value is inteded for the field.

Again, I can only re-itterate that these approaches while valid and useful are intended here ONLY to suggest avenues, and are NOT 'plug-and-play' procedures.


"V&V" refers to Verification & Validation. Checking values, in this case, BEFORE insertion.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CVigil]

Thaks for the definition, MichaelRed! I went back and read the relevant portions of the post, and understand now .

"Read you around," -- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot