S
ScubaSteve
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.
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.