IncrementingValue based on Record

Attached below is a problem that I posted earlier, and kind of
understand how it works - but can't get it to run.

I guess I don't understand how to use the Dcount function. Do I place
it in the beforeupdate portion of the subform?

The field I need to increment is PatientID (attached to PatientID,
table: Patient). So that when a person clicks new record, the PatientID
increases.

CaseID's look like : 20070001, based on year and then incrementing from
there. So the composite ID would look like 20070001.1 for first
patientid, 20070001.2 for 2nd patientid and so on. Next case might
just be 20070002.1.

How Kingston described it is exactly how I want it to function. CaseID
and PatientID are a composite key currently.

Ideas?

___________________________________
Hello,

First, I know automatic value increment has been talked about a lot,
but I can't seem to get this particular issue resolved. Basically what

I have are "Case numbers" (automatically assigned) and then "patient
id's" within that case number. So if we want to enter in 3 patients
that came in at the same time, they'd all be under the same case
number.


I've got a form with the "case number" that is automatically
incremented when a case comes in, and then a subform with the "patient
id" and patient info that needs to increase when people click new
record. The "Patient ID" field should match the record number in the
navigation bar. Currently, whenever someone clicks new record they
have to manually change the "patient id" since it does not
automatically increment.


I hope this makes sense! I want "patient id" to increase automatically

for each "case number" in the short!


Any direction to examples, etc? Thanks so much!


Reply


From: kingston via AccessMonster.com - view profile
Date: Thurs, Nov 30 2006 11:01 am
Email: "kingston via AccessMonster.com" u27511@uwe
Groups: microsoft.public.access.forms
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


Your description of what you want to happen would lead to duplicate
Patient
IDs:

Case 1
Patient 1
Patient 2
Patient 3


Case 2
Patient 1
Patient 2


If this is the case, Patient ID is not really an ID in the sense of a
data
table key (it can be part of a composite ID but I'm not sure if that's
what
you're trying to do). Do you have a real ID for patient information?
If so,
good, and we'll call the number you want PatientCount instead. You can
get
the current PatientCount using something like:
DCount("*","CasePatientTable",
"[CaseID]=12"). Naturally, you need to substitute the real table name
and
field name. If CaseID is not numeric, you'll need to add single quotes

around the value. If you place this in a form, you can replace the
criteria
with something like: "[CaseID]=" & Me.CaseID. Thus, the next
PatientCount
would simply be DCount(...)+1.


In summary, CaseID will be an autonumber, PatientID should be an
autonumber
(hopefully), and PatientCount will be calculated. There are potential
problems and inefficiencies with adding a PatientCount field (what if a

patient is deleted from a case - do you reassign all the numbers?).
So, it
would be much better if you could work with two true IDs (Case and
Patient)
that are automatically assigned.

Any time you want to increment a field, use the form's current event and the
DMax function. The DCount really is not a good fit. The problem you have
here is it appears you want 2 incrementing fields, CaseID and PatientID.
Since I don't know the rules as to when a new CaseID is created, I can't
give an exact example. You have to know the CaseID before you can create a
new PatientID. Here is an example that assumes you already have the Case ID.

It is important to know the Case ID since the incrementing is based on that
field:

Normally, this is done in the form's Current event:

If Me.NewRecord Then
Me.txtPatientID = Nz(DMax("PatientID", "Patient", "[CasedID] = " &
Me.txtCaseID),0) + 1
End If

--
Dave Hargis, Microsoft Access MVP


"ScubaSteve" wrote:

Attached below is a problem that I posted earlier, and kind of
understand how it works - but can't get it to run.

I guess I don't understand how to use the Dcount function. Do I place
it in the beforeupdate portion of the subform?

The field I need to increment is PatientID (attached to PatientID,
table: Patient). So that when a person clicks new record, the PatientID
increases.

CaseID's look like : 20070001, based on year and then incrementing from
there. So the composite ID would look like 20070001.1 for first
patientid, 20070001.2 for 2nd patientid and so on. Next case might
just be 20070002.1.

How Kingston described it is exactly how I want it to function. CaseID
and PatientID are a composite key currently.

Ideas?

___________________________________
Hello,

First, I know automatic value increment has been talked about a lot,
but I can't seem to get this particular issue resolved. Basically what

I have are "Case numbers" (automatically assigned) and then "patient
id's" within that case number. So if we want to enter in 3 patients
that came in at the same time, they'd all be under the same case
number.


I've got a form with the "case number" that is automatically
incremented when a case comes in, and then a subform with the "patient
id" and patient info that needs to increase when people click new
record. The "Patient ID" field should match the record number in the
navigation bar. Currently, whenever someone clicks new record they
have to manually change the "patient id" since it does not
automatically increment.


I hope this makes sense! I want "patient id" to increase automatically

for each "case number" in the short!


Any direction to examples, etc? Thanks so much!


Reply


From: kingston via AccessMonster.com - view profile
Date: Thurs, Nov 30 2006 11:01 am
Email: "kingston via AccessMonster.com" u27511@uwe
Groups: microsoft.public.access.forms
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


Your description of what you want to happen would lead to duplicate
Patient
IDs:

Case 1
Patient 1
Patient 2
Patient 3


Case 2
Patient 1
Patient 2


If this is the case, Patient ID is not really an ID in the sense of a
data
table key (it can be part of a composite ID but I'm not sure if that's
what
you're trying to do). Do you have a real ID for patient information?
If so,
good, and we'll call the number you want PatientCount instead. You can
get
the current PatientCount using something like:
DCount("*","CasePatientTable",
"[CaseID]=12"). Naturally, you need to substitute the real table name
and
field name. If CaseID is not numeric, you'll need to add single quotes

around the value. If you place this in a form, you can replace the
criteria
with something like: "[CaseID]=" & Me.CaseID. Thus, the next
PatientCount
would simply be DCount(...)+1.


In summary, CaseID will be an autonumber, PatientID should be an
autonumber
(hopefully), and PatientCount will be calculated. There are potential
problems and inefficiencies with adding a PatientCount field (what if a

patient is deleted from a case - do you reassign all the numbers?).
So, it
would be much better if you could work with two true IDs (Case and
Patient)
that are automatically assigned.

Thanks for the help!

That worked once when there already was Case ID record generated (I
searched a case), but failed when I tried to create a new record and
every other time I tried. I get a 3075 error that I'm missing an
operator in the query expression Max (Patient ID).

The steps I do to create a new Case ID is click a button which brings
up a form w/ the subform containing the patient id and patient
information.

ANy ideas?

The button to create a new record resides on the the switchboard -
taking the last used caseID from a table "CastLastNo", incrementing it
by 1, and then placing it into the form. (I didnt' write that!). If
you need any other information, please let me know!


ScubaSteve wrote:
Thanks for the help!

That worked once when there already was Case ID record generated (I
searched a case), but failed when I tried to create a new record and
every other time I tried. I get a 3075 error that I'm missing an
operator in the query expression Max (Patient ID).

The steps I do to create a new Case ID is click a button which brings
up a form w/ the subform containing the patient id and patient
information.

ANy ideas?

Not knowing exactly how your forms work, I can't give a specific answer;
however, if you recall, I did say in my previous post you have to have the
CaseID before you can use the code to find the next patientid.

Does the button on your switchboard create a new record that will contain
the CaseId and patientId? If so, I would put the code to create a patientId
right after the code that gets the next CaseID.
--
Dave Hargis, Microsoft Access MVP


"ScubaSteve" wrote:

The button to create a new record resides on the the switchboard -
taking the last used caseID from a table "CastLastNo", incrementing it
by 1, and then placing it into the form. (I didnt' write that!). If
you need any other information, please let me know!


ScubaSteve wrote:
Thanks for the help!

That worked once when there already was Case ID record generated (I
searched a case), but failed when I tried to create a new record and
every other time I tried. I get a 3075 error that I'm missing an
operator in the query expression Max (Patient ID).

The steps I do to create a new Case ID is click a button which brings
up a form w/ the subform containing the patient id and patient
information.

ANy ideas?