Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Unanswered: New record based on last record

    In this code It's not taking me to my new record. I see it add the data with the new number WIL-216. Then I start to edit the sheet and I find I am in record WIL-215. I can scroll and get to WIL-216, but the sheet doesn't automatically go there? Any help would be great.

    Code:
    Private Sub CopySample_Click()
       Me!SampleNumber.SetFocus
       DoCmd.RunCommand acCmdSelectRecord
       DoCmd.RunCommand acCmdCopy
       DoCmd.RunCommand acCmdPasteAppend
       DoCmd.GoToControl "SampleNumber"
       a = Split([SampleNumber], "-")
       a(1) = a(1) + 1
       [SampleNumber] = Join(a, "-")
    End Sub
    I guess I need something to take me to the new record??

    Thank You,
    Michael
    Gotta to do some code

  2. #2
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Hi,

    If i understand your question, you basically want to set the new samplenumber based on the last.

    You can do it with various ways:

    1) If you Codification of sample number is "WIL-###########" then:
    you can use the count records to do it,

    [VarSample]="WIL-" & Me.CurrentRecord

    This can cause you two problems, one easy to control is, if you eliminate one previous record, you will notice the error of duplicated values, if the field is set to no duplication. You can handle this by creating a table for deleted records wich is updated when you delete the record you want, and when you insert a new record, the first thing you have to put in code is to lookup the table of deleted records table first, if any record exists, then the last deleted sample number is assumed, and then deleted from the deleted records table.
    The other problem is if you want a realy sequential number, therefor you can not assume an old deleted number to be the new samplenumber.

    2) create a field, (or if you already have), with autonumber, and use it to make the number of your codification.

    [VarSample]="WIL-" & autonumber

    3)use the, lookup and last functions, to get the last record in the table and generate the new sample number based on the last, and lookup if the new generated code already existes. If so increases 1 more.

    Dim Lst,Lkup as variant
    Dim Spltd as long
    Lst=Dlast("[SampleNumber]","[your table]")
    if not Isnull(Lst) then
    Spltd=Spli(Lst,"-")
    Spltd(1)=Spltd(1)+1
    [Samplenumber]=Join(Spltd,"-")
    If [Samplenumber]=Dlookup("[Samplenumber]","[your_ table]","[Samplenumber] =" & Forms![your form].[Samplenumber]) then
    Spltd=Spli(Lst,"-")
    Spltd(1)=Spltd(1)+2
    [Samplenumber]=Join(Spltd,"-")
    End If
    Else
    [SampleNumber]="WIL-" & 1
    End If

    Be sure to base the form on ordered by a numerical field, to ensure that the last record is actualy the last.
    Last edited by Miguel; 01-27-05 at 22:03.

  3. #3
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    I am very new to coding but I really am enjoying it.
    Let me clarify
    We make a sample # for our customers. The first 3 letters are their company name, then we add 3 numbers to end, starting with 201.

    I have a control called SampleNumber which has an input mask of:
    >LLL\-000;0;_
    can I still add the code you gave me? And where should I add it to the code I showed you?

    Thank You for your help,
    Michael
    Gotta to do some code

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Unhappy

    Quote Originally Posted by surfacesys
    In this code It's not taking me to my new record. I see it add the data with the new number WIL-216. Then I start to edit the sheet and I find I am in record WIL-215. I can scroll and get to WIL-216, but the sheet doesn't automatically go there? Any help would be great.

    Code:
    Private Sub CopySample_Click()
       Me!SampleNumber.SetFocus
       DoCmd.RunCommand acCmdSelectRecord
       DoCmd.RunCommand acCmdCopy
       DoCmd.GoToRecord , ,acNewRec
       DoCmd.GoToRecord , ,acLast
       DoCmd.RunCommand acCmdPasteAppend
       DoCmd.GoToControl "SampleNumber"
       a = Split([SampleNumber], "-")
       a(1) = a(1) + 1
       [SampleNumber] = Join(a, "-")
    End Sub
    I guess I need something to take me to the new record??

    Thank You,
    Michael
    Hi Michael, try adding one of the two bold lines above and see if that gets you what you want. Just for curiosity sake, how do you trigger that code? CommandButton??

    have a nice one,
    BUD

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by surfacesys
    I am very new to coding but I really am enjoying it.
    Let me clarify
    We make a sample # for our customers. The first 3 letters are their company name, then we add 3 numbers to end, starting with 201.

    Michael
    Hi again, Also, how are you creating the last 3 numbers? Automatically? Or is the user having to search for the last number used? Is the number sequentially in order regardless of the CompanyName...(WIL) ?? What I mean by that is, for the customer WIL, can have numbers 201, 202, 203 OR can customer WIL only have whatever trailing number is next according to the last one used by previous company. Like CompanyName (JOS has number 201, will the very next order for CompanyName WIL get 202 or does that CompanyName have it's own individual series of numbers)???

    BUD

  6. #6
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Hi,

    Working with DataEntry=true is better then false. So you can not use DoCmd.GoTo Record,, AcLast because the form is set to DataEntry.

    For you to get better preformance, work with Lookup and/or Last functions. Don't put your records moving fowards and backwards. This is terrible when your database have a considerable size.

    Setting the DataEntry to true in the form, avoids your database to read the entire table, therefor it is quicker to insert a new record.

Posting Permissions

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