Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2009
    Posts
    5

    Unanswered: Help Making Number Field sequential

    Here's what I have going:

    The key to our DB are "Job Numbers" they are used throughout our company on multiple software solutions, I'm trying to get an Access DB started to integrate data, forms, Project Management etc all in one.

    I need the "Job Numbers" to be automatic, Sequential and be able to dictate the initial starting number as necessary as we reset after getting to 10k which takes a few years, and also because we are already at a given number.

    So here's the table and field in question:
    [tblShowOrders]![JobNumber]

    If someone could get me heading in the right direction with the "code", do I make a "qry", a "form", or right in the table etc, I would appreciate any help.

    I have Access2007.

    Thanks
    Tom

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    Quote Originally Posted by HAWGD View Post
    I need the "Job Numbers" to be automatic, Sequential and be able to dictate the initial starting number as necessary as we reset after getting to 10k which takes a few years, and also because we are already at a given number.
    Well, if you need to dictate what the number will be then it cannot be automatic. You're going to have to use the DMax() function (or better yet, an EMax() function) to look up what the current highest number is, and then add one (or whatever your sequence is) and assign that number when you create the record.

    Oh, and as a developer's suggestion:
    Quote Originally Posted by HAWGD View Post
    as we reset after getting to 10k
    That is a very bad design as it quite literally violates the first rule of relational database design (the unique key). I would suggest coming up with a new way to identify your records so as to avoid duplicate id's at ALL costs.

    Cheers!
    Me.Geek = True

  3. #3
    Join Date
    Dec 2009
    Posts
    5
    nckdryr
    Thanks for your advise, I've struggled with this Job Number thingy they do here for a while!

    I also have a field Job_ID (Primary Key) because I too was aware of the problem resetting numbers after a few years.
    The company tends to use Job Numbers in combination with Year and Client to file everything they do.

    I will try to make the pitch not to reset the numbers but I would still need to dictate a starting number if you can help me with it.

    Thx

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by HAWGD View Post
    I will try to make the pitch not to reset the numbers but I would still need to dictate a starting number if you can help me with it.
    See Post #2, give it a shot, let us know where you get stuck.
    Me.Geek = True

  5. #5
    Join Date
    Dec 2009
    Posts
    5
    nckdryr

    Ok, the company will not reset the number as previously mentioned, so that's one issue off the table.

    Here's what I have:
    Recap- I need to get the JobNumber field to automatically and sequentially update as records are added Using [frmProductionOrder].
    DB Table which needs the input info: [tblShowOders]![JobNumber].
    Current Job # in Excel: example- 8900, I would need to start my DB JobNumber at 8901.
    Also I know I'll need to make an Action Button to force a save to ensure no one opens the form and than decides not to move forward and exits leaving a job number with no other data.

    Question:
    1- Would it be best to do this in [frmProductionOrder] or a query?
    2- What would the expression look like (example)?


    Thanks
    Last edited by HAWGD; 12-09-09 at 13:08. Reason: More thoughts

  6. #6
    Join Date
    Nov 2009
    Posts
    49
    Hi,

    I noticed that you put 'Excel' into your last post, are you importing the records from Excel into the database each time there is a new job? In which case ignore what I am going to write because I hate importing so I don't know much about it. The old addage of "Know thy enemy" is blatantly being ignored by me.

    I suppose the first place to start is: are you using the standard New Record on the record selectors on the bottom of the form or are you using an append query? And I am guessing you are using the record selectors on the bottom of the form.

    Do you have all of those records in your database? If you do, all you have to do is set the JobNumber datatype to AutoNumber and you should be fine for now. The max number the AutoNumber goes up to is 2147483647. That would do for now until there is a better coding to help, I can think of a way to do this in coding but it is a bit longwinded.

    With the make sure they put in all the details before moving off the new record, if you go to the design view of the tblShowOrders, there is an option for each field to be "Required" put this on to "Yes". This should mean that a message will pop up if they have missed anything. Also while you are changing the "Required" property it will ask you if you want to apply this to records already in your database, I normally say no to this as you would have to go to each record and make sure nothing is missing, 8900 is a lot to go through.

    Phil

  7. #7
    Join Date
    Dec 2009
    Posts
    5
    Phil

    We use Excel at this time, but I'm not going to import those records.
    My intent is to start this DB at a Rounded Numbered point that has yet to be used, this is why I need to know how to insert the starting number than have it sequentially increase from there.

  8. #8
    Join Date
    Nov 2009
    Posts
    49
    Sorry about the delay, who would have thought it that at work I actually had to do some work!

    I kind of made a rush job on this coding so no error stuff in this:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strJob As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("summaryTable", dbOpenDynaset)

    Do While Not rs.EOF
    If rs![Jobnumber] = "" Then
    If rs.AbsolutePosition = 1 Then
    rs![Jobnumber] = "1"
    Else
    rs.MovePrevious
    strJob = rs![Jobnumber]
    rs.MoveNext
    rs![Jobnumber] = strJob + 1
    End If
    End If
    rs.MoveNext
    Loop

    Explanation:

    Set db = CurrentDb
    Set rs = db.OpenRecordset("YourTblHere", dbOpenDynaset)


    This opens a recordset that holds all the records to "YourTblHere", you only need to write the name of the table with " on either side.

    Do While Not rs.EOF
    ...
    Loop


    This means that coding inside of the recordset will be repeated over and over. The rs.EOF is the end of the records so therefore this event will keep looping around and around until it has gone through all the records.

    If rs![Jobnumber] = "" Then
    ...
    End If
    rs.MoveNext


    This bit means that if the record doesn't have a Jobnumber then something will happen (...) else it goes to the next record. The next line is the loop so then the process will start again.

    If rs.AbsolutePosition = 1 Then
    rs![Jobnumber] = "1"
    Else
    ...
    End If


    This bit is in the occurance that the very first record doesn't have a Jobnumber, which is unlikely but you never know.

    rs.MovePrevious
    strJob = rs![Jobnumber]
    rs.MoveNext
    rs![Jobnumber] = strJob + 1


    So if there is a record that has no Jobnumber and isn't the first record in the list then this coding is run. The imaginary cursor moves back a record, notes the Jobnumber as 'strJob' then moves back to the numberless record and puts the value as 1 higher than the strJob (Previous Record)

    I think this will do you, but not sure how to call this sub, whether it should be Form_AfterUpdate or Form_OnCurrent.

    If this doesn't work then we might have to add a simple append query in the coding. But first check if this is ok.

    Hope this helps and if anyone else sees another way to do this then by all means change/modify/slag it off

    Phil
    Last edited by pb190; 12-10-09 at 11:12. Reason: Ooops mucked up my MoveNext

  9. #9
    Join Date
    Dec 2009
    Posts
    5
    Wow

    Thx Phil I'll give it a try and let you know how it goes.....

Posting Permissions

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