Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Houston, Texas, USA
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autonumbers (2003)

    My jobs field automatically generates a new number and a new subjob number along with a county code if it's a new job. If it's a new subjob, you can select the old job number (which includes the county code) and a new subjob is generated. The problem is that I've inherited an old JOBS table where there are Job numbers already assigned automatically by the old database. Is there a way to make Access regenerate new job and subjob numbers that match the old Job and subjob numbers?

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

    Re: Autonumbers (2003)

    What do you mean by "regenerate new job and subjob numbers"? Please try to make your reply as specific as possible.

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Houston, Texas, USA
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumbers (2003)

    I have an old table from an old database (Filemaker) where job numbers have already been issued in one field. The numbers represent the job, the subjob, and the county. 0010-01-LE, and may have several subjobs where the number would be 0010-02-LE, 0010-03-LE, 0010-04-LE, and so on. Each county may have a job 0010 and a subjob 01, 02, 03, etc, so you might have 0010-01-LE, 0010-01-HT, 0010-02-LE, 0010-02-HT, and so on. Keep in mind, that these are presently in only one field in a table called JOBS. I'd like to convert this table to an Access table or tables where the Job Number and the subjob number are issued automatically like an autonumber. For instance, if I need a new subjob, which would be subjob 05 for job 0010 in LandsEnd County, I need to be able to pull up job 0010 in LandsEnd and create a new subjob 05. OR, I need a whole new job which is job number 4456 in LandsEnd, or job 1160 in HighTower.

    What I'd like to do is:

    1. Keep the job numbers (Old Jobs) and subjob numbers (Old Subjobs)
    2. Issue new subjob numbers for the old existing Jobs
    3. Issue new Job Numbers for any new jobs that we create
    4. Issue new Subjob Numbers for any new jobs

    If I can't pick up where I left off, can I cross-reference the old jobs numbers to the new ones somehow? The old job numbers can be set up as primary keys in the tables they are in now.

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

    Re: Autonumbers (2003)

    It would be best if you created separate fields for Job, Subjob and Country; the combination of these fields could be the new primary key. You could use update queries to populate the new fields from the existing field.

    If you prefer, you can keep the old field, and use VBA code to generate new values. Say that the field is named TheNumber, and that you have a combo box cboCountry on your form from which the user can select the country. This combo box may display the full name of the country (LandsEnd) but its bound column must be the abbreviation (LE).

    Here is code to generate a new job:

    Dim strJob As String
    Dim strNewNumber As String

    If IsNull(Me.cboCountry) Then
    MsgBox "Please select a country.", vbExclamation
    Me.cboCountry.SetFocus
    Exit Sub
    End If

    strJob = Nz(DMax("Left([TheNumber],4)", "Jobs", "Right([TheNumber],2)=" & Chr(34) & Me.cboCountry & Chr(34)), "00")
    strNewNumber = Format(Val(strJob) + 1, "0000") & "-01-" & Me.cboCountry

    And this is code to generate a new subjob:

    Dim strJob As String
    Dim strCountry As String
    Dim strSubJob As String
    Dim strNewNumber As String

    strJob = Left(Me.TheNumber, 4)
    strCountry = Right(Me.TheNumber, 2)
    strSubJob = Nz(DMax("Mid([TheNumber],6,2)", "Jobs", _
    "Left([TheNumber],4)=" & Chr(34) & strJob & Chr(34) & _
    " AND Right([TheNumber],2)=" & Chr(34) & strCountry & Chr(34)), "00")
    strNewNumber = strJob & "-" & Format(Val(strSubJob) + 1, "00") & "-" & strCountry

    You could use this code in the On Click event of command buttons, for example.

Posting Permissions

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