Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Duplicate Record (with unique field) (2002 SP-2)

    Anyone know of a "trick" way to duplicate records that have a unique field?
    I have a cmdbtn (pretty archaic) to create duplicate orders, but there is no way to automatically "amend" the unique field (i.e. change each duplicate progressively by ".1"). This unique field has a text data-type (can be numeric or alpha-numeric).
    <pre>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    Can you explain in more detail what you mean by increasing a text field progressively by ".1"? What are the potential start values, etc.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    Hans:

    Sorry about that, this problem runs pretty deep so I was trying to keep the question as short as possible.
    txtRelease1No (there are three of these, but we need only be concerned with the first - I think) is a number provided to me by the customer. It is usually something like SIM123456.1 This works just fine if it is a single order, but in this case there were seven (only different by the decimal digit). I made a huge error yesterday by duplicating the orders and then forgetting to go back and amend them to .1, .2, .3, . . . I was woke up at 1:30AM with seven different trucks trying to pick up the same release number. This has never been a unique field because it always prevented me from using the CmdDuplicateOrder. Now I have the issue of needing to make that field unique in order to avoid a repeat disaster. Maybe it isn't possible?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    Bryan,

    Please try to be as precise a possible. Your remark
    <hr>It is usually something like SIM123456.1<hr>
    makes me wary. Does usually mean that there exceptions? If it is always of the general form <alphanumeric>.<sequence number> and never blank, it is possible to write code to determine the next available <sequence number>. But there is little point in doing so now only to discover that it won't work in all situations.

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    Hans:

    I had not realized the depth you required. I went and found some actual numbers to consider.

    SJ4357
    OAK15365
    SIM586423.1
    7253641.1
    121703.1-7

    What a mess, huh? That last one is a doozie! I found it back in June of last year where they required a separate release number for each product on a single load. So, in this case, the main number (121703.x) was repeated seven times, with a separate decimal indicator for each product. As I think of this particular case, their application must run in to the same issue because, while a single shipment usually has one Bill of Lading, this load had seven.

    In looking at tblMaster (where I retrieved the above examples) I see (by Find Duplicates query) that there are only two sets of duplicates among thousands of entries; one set where an order was canceled (but not deleted) and the number was re-used a week later, and another set of three where I had entered "Pending" in the release number entry, but the orders were never finalized. Armed with the Find Duplicates query experience, I wonder if what I might really want to do is just alert the operator to the fact that they are using a number that already exists in the table. In order to make that a workable solution, however, it seems that I would need to have a popup/modular form to show where the duplicate exits (date/customer name), and whether the operator wishes to repeat it. My thought in that is if it occurred in the distant past, and with another customer, it's probably OK to allow the duplicate. If it occurred yesterday with the same customer, it's probably an entry error and requires consideration on the part of the operator. Of course I have no idea how one might go about running a Find Duplicates query in the midst of an order entry. (The OnGotFocus and OnLostFocus events of txtRelease1No are being used, but I don't have to (they just highlight the field). The BeforeUpdate event of frmOrderNew is being used to assign a unique ProNo (my in-house control number).

    Geesh, even I'm getting lost in this jumble of issues. I suppose the release number does not really have to be unique, but I need to know if it has been used. The duplicate records method is really "clunky" because I have to hit a CmdBtn for as many times as I want duplicates, remembering all the while how many times I have hit it, rather than just having a form where I can enter how many records I want, and to add the appropriate suffix (i.e. .1, .2, or maybe even .a, .b, .c. . .). I don't see that there would be any harm in using a "consistant" suffix number, even if it were not required by the customer.

    Never being one to shrink from a challenge, you have my sympathies. (Incidently, I spoke with my brother on the phone last night and their system ($22K in software alone) is incapable of what I'm trying to do here).
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    You could put an unbound text box next to the command button in which you can fill in the number of duplicates you want to create. Set the default value of this text box to 1. The On Click event of the command button would append as many records as specified in the text box.

    But I am confused about the release numbers. Apparently you can assign them yourself (as you want the command button to do so for you), so why are they not just a simple sequential number, or even an AutoNumber? And if they have to stay the way they are, what should the code do? After SIM586423.1 comes SIM586423.2, but what comes after SJ4357? SJ4358, or SJ4357.1?

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    Hans:

    A way back up the thread I mentioned the release number "is a number provided to me by the customer", but perhaps what I didn't make clear is that the decimal number is not always provided, and for some of the customer's it's not even required (from their perspective). Example:

    SJ1834 (there may be 10 loads on this one number for this customer, but I need a decimal to show there are ten loads)
    201345.1 ( this particular customer uses decimals, and in this case there is only one load)
    201346.1 thru 10 ( same customer, but for ten loads)
    SIM456982.1 thru 5 (different customer, five loads)

    If we drop the duplication issue for just one second. I enter the order manually with whatever decimal the customer wants, or none at all if it's just one load. (I currently have no way to trap whether that number may have been used before). If I need to make duplicates of that number (and assuming we were to change the property of the txtbx to "No Duplicates") then I would have to add a unique decimal to any order that was duplicated - whether the decimal was actually given to the driver or not. Am I making any sense? Also, I don't have a clue what you mean by adding an unbound text box to the form (unless you just haven't gotten into the details yet), don't understand where the OnClick event addresses the txtbx.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    You did mention that the number was provided by the customer. It's just that the whole situation makes me dizzy. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    What if two different customers provide you with the same release number? Are you free to change one of them to a unique number?

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>. . .you and me both my friend!
    I didn't do a "like" comparisons, so I'm not certain that has (or has not) occurred in the past, but I'm reasonably certain it hasn't. Different customers generally have a different series of numbers.

    4567897.1
    265432.1
    12548.1
    SJ3548

    If the rare situation arose where there was a duplication of the number (in both the whole number and the decimal), then I believe I would have the liberty of assigning a unique decimal. This, of course brings up the issue of how I would know which decimal to use (CmdBtn to run FindDuplicates query?). Won't the form get "hung" OnClose by the Access ErrHandler? If the entry isn't accepted, there won't be any duplicates.

    ACCESS <img src=/S/argue.gif border=0 alt=argue width=50 height=25> BRYAN
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    Bryan,

    Could you test the attached database to see if it does what you want? On the form, you can specify a number of duplicates to create, and the code will try to assign the new numbers. Of course, it is not a finished product, just a rough demo.
    Attached Files Attached Files

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    Hans:

    I could see the eleven records, but couldn't enter any of my own to experiment. For what you have entered in the recordset, I'd say you have a solution. These are the pops I get and the error occurrs at

    <pre> ' Create new records
    For i = intSeq To intSeq + Me.txtDuplicates - 1
    strSQL = "INSERT INTO tblOrders ( ReleaseNo ) VALUES ( " & Chr(34) & strProposed & i & Chr(34) & " )"
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">CurrentDb.Execute strSQL</span hi>
    Next i
    ' Re-read records
    Me.Requery
    End Sub</pre>


    Lot of code there friend, I hope you're deriving some satisfaction in this.
    (Hans making mental note not to reply to posts from bfxtrfcmgr) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    I can't explain that. I just tested the database again, and I can add new records, and duplicate existing records. I'm using Access 2002 SP-2 too. It's too late now to investigate further.

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    Hans:

    Understood. I'm going to have at it for another hour and call it quits myself. I may try to "steal" the code from your db and see if I can recreate a new db. Thanks for all the help so far! Just viewing the code behind your form reveals a huge effort on your part, and one very much appreciated.
    <img src=/S/doze.gif border=0 alt=doze width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    FYI, Hans' database run fine on my 2 systems (Access XP SP2 and Access 2000 SP3).
    Have you make the database read-only ?
    Francois

  15. #15
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Duplicate Record (with unique field) (2002 SP-2)

    Francois:

    Nice to hear from you again! Last night (it's 5AM Sunday morning now) the whole read-only db thing just seemed academic, so I imported everything into a new db. The rather bizarre thing is that Hans' original version works just fine this morning. Stranger things have happened to me; but I was much younger, and there was a pub involved, and a lady in a red dress. . . <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •