Using pre-assigned case numbers

[emalcolm_FLA]

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

[Larry Linson]

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
<em**********@comcast.netwrote in message
news:11**********************@r29g2000hsg.googlegr oups.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

[malcolmliz]

On Oct 8, 4:16 pm, "Larry Linson" <[e-mail]:

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

<[e-mail] in message

news:11**********************@r29g2000hsg.googlegr oups.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.

[Larry Linson]

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

<ma********@gmail.comwrote in message
news:11*********************@o80g2000hse.googlegro ups.com...

On Oct 8, 4:16 pm, "Larry Linson" <[e-mail]:

>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

<[e-mail] in message

news:11**********************@r29g2000hsg.googleg roups.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.