Using pre-assigned case numbers

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • emalcolm_FLA@comcast.net
    #1

    Using pre-assigned case numbers

    Hello and Thanks in advance for any help.

    I have been tasked with rewriting a christmas assistance database
    using Access 2003. The old system used pre-assigned case numbers to
    identify applicants applying for assistance (with a specific date and
    time).

    I have created an applicant table and form, PK is an autonumber,
    CaseNbr is long integer, applicant name/address etc.

    I have created a case number table, PK is autonumber, case number is
    long integer, appt date/time as date time fields and an assigned date
    as date field.

    I need to look up the first unassigned case number, assgn this case
    number, date/time to the applicant and then mark this case number as
    assigned.

    I can look up the case number in a query using first in the total row,
    but cannot figure out how to generate the code to do the updates and
    call everything with a command button.

    I did something similar using DMAX for a batch number, but didn't have
    to save any info, this scenario needs to use the preassgned numbers.

    Any help would be greatly appreciated.

    Liz

    Tags: None
    • Larry Linson
      #2
      Re: Using pre-assigned case numbers

      Is this a single-user application, or are there multiple concurrent users?

      If single user, or with appropriate use of transactions, you can use the
      DMAX approach to create a new Case Number. Use DMAX against the Applicant
      table... that will get you the highest number used, and you'll increment it
      to be the Case Number for the new applicant.

      Larry Linson
      Microsoft Access MVP


      <emalcolm_FLA@c omcast.netwrote in message
      news:1191874220 .367452.203790@ r29g2000hsg.goo glegroups.com.. .
      Hello and Thanks in advance for any help.
      >
      I have been tasked with rewriting a christmas assistance database
      using Access 2003. The old system used pre-assigned case numbers to
      identify applicants applying for assistance (with a specific date and
      time).
      >
      I have created an applicant table and form, PK is an autonumber,
      CaseNbr is long integer, applicant name/address etc.
      >
      I have created a case number table, PK is autonumber, case number is
      long integer, appt date/time as date time fields and an assigned date
      as date field.
      >
      I need to look up the first unassigned case number, assgn this case
      number, date/time to the applicant and then mark this case number as
      assigned.
      >
      I can look up the case number in a query using first in the total row,
      but cannot figure out how to generate the code to do the updates and
      call everything with a command button.
      >
      I did something similar using DMAX for a batch number, but didn't have
      to save any info, this scenario needs to use the preassgned numbers.
      >
      Any help would be greatly appreciated.
      >
      Liz
      >

        Comment

        • malcolmliz@gmail.com
          #3
          Re: Using pre-assigned case numbers

          On Oct 8, 4:16 pm, "Larry Linson" <boun...@localh ost.notwrote:
          Is this a single-user application, or are there multiple concurrent users?
          >
          If single user, or with appropriate use of transactions, you can use the
          DMAX approach to create a new Case Number. Use DMAX against the Applicant
          table... that will get you the highest number used, and you'll increment it
          to be the Case Number for the new applicant.
          >
          Larry Linson
          Microsoft Access MVP
          >
          <emalcolm_...@c omcast.netwrote in message
          >
          news:1191874220 .367452.203790@ r29g2000hsg.goo glegroups.com.. .
          >
          >
          >
          Hello and Thanks in advance for any help.
          >
          I have been tasked with rewriting a christmas assistance database
          using Access 2003. The old system used pre-assigned case numbers to
          identify applicants applying for assistance (with a specific date and
          time).
          >
          I have created an applicant table and form, PK is an autonumber,
          CaseNbr is long integer, applicant name/address etc.
          >
          I have created a case number table, PK is autonumber, case number is
          long integer, appt date/time as date time fields and an assigned date
          as date field.
          >
          I need to look up the first unassigned case number, assgn this case
          number, date/time to the applicant and then mark this case number as
          assigned.
          >
          I can look up the case number in a query using first in the total row,
          but cannot figure out how to generate the code to do the updates and
          call everything with a command button.
          >
          I did something similar using DMAX for a batch number, but didn't have
          to save any info, this scenario needs to use the preassgned numbers.
          >
          Any help would be greatly appreciated.
          >
          Liz- Hide quoted text -
          >
          - Show quoted text -
          Thanks Larry, this is a multiple concurrent users application. The
          case numbers have to run between 1001 and 1370 for the first date with
          30 numbers used every 15 minutes, when 1370 is assigned the case
          number has to change to 2001 and run up to 2450, then change to 3001
          and run up to 3370. The preassigned numbers correspond to the date and
          time the applicant comes to received their Christmas assistance.

            Comment

            • Larry Linson
              #4
              Re: Using pre-assigned case numbers

              You should be able to do this in code... using a transaction or
              self-implemented locking approach; this is what I'd try as a start.

              Call the code that implements your algorithm, Begin a Transaction, Read the
              most recent Case Number, create the new one according to your algorithm,
              write the new Case Number back to the Case Number table, End the
              Transaction, and return the Case Number you calculated to the user.

              You might be able to do it a little easier by using a Table Lock on your
              Case Number table.

              I worked on an application where we did something similar (though it was
              just a monotonically increasing unique id) with a server database, with the
              new id function implemented as a stored procedure and accessed via a
              passthrough query.

              Larry Linson
              Microsoft Access MVP



              <malcolmliz@gma il.comwrote in message
              news:1191948792 .016297.94320@o 80g2000hse.goog legroups.com...
              On Oct 8, 4:16 pm, "Larry Linson" <boun...@localh ost.notwrote:
              >Is this a single-user application, or are there multiple concurrent
              >users?
              >>
              >If single user, or with appropriate use of transactions, you can use the
              >DMAX approach to create a new Case Number. Use DMAX against the
              >Applicant
              >table... that will get you the highest number used, and you'll increment
              >it
              >to be the Case Number for the new applicant.
              >>
              > Larry Linson
              > Microsoft Access MVP
              >>
              ><emalcolm_...@ comcast.netwrot e in message
              >>
              >news:119187422 0.367452.203790 @r29g2000hsg.go oglegroups.com. ..
              >>
              >>
              >>
              Hello and Thanks in advance for any help.
              >>
              I have been tasked with rewriting a christmas assistance database
              using Access 2003. The old system used pre-assigned case numbers to
              identify applicants applying for assistance (with a specific date and
              time).
              >>
              I have created an applicant table and form, PK is an autonumber,
              CaseNbr is long integer, applicant name/address etc.
              >>
              I have created a case number table, PK is autonumber, case number is
              long integer, appt date/time as date time fields and an assigned date
              as date field.
              >>
              I need to look up the first unassigned case number, assgn this case
              number, date/time to the applicant and then mark this case number as
              assigned.
              >>
              I can look up the case number in a query using first in the total row,
              but cannot figure out how to generate the code to do the updates and
              call everything with a command button.
              >>
              I did something similar using DMAX for a batch number, but didn't have
              to save any info, this scenario needs to use the preassgned numbers.
              >>
              Any help would be greatly appreciated.
              >>
              Liz- Hide quoted text -
              >>
              >- Show quoted text -
              >
              Thanks Larry, this is a multiple concurrent users application. The
              case numbers have to run between 1001 and 1370 for the first date with
              30 numbers used every 15 minutes, when 1370 is assigned the case
              number has to change to 2001 and run up to 2450, then change to 3001
              and run up to 3370. The preassigned numbers correspond to the date and
              time the applicant comes to received their Christmas assistance.
              >

                Comment

                Previous template Next
                Working...