incremental numbering in forms

  • Thread starter Thread starter t venet
  • Start date Start date
T

t venet

i have a form we'll call "Concomitant Medications" which
has two controls comprising the primary key: "Patient
Number" and "Med Number". this form is intended to collect
information from patients who enroll in a study over a
period of time having to do with particular medications
they might be taking. each record in the underlying table
is intended to collect only information about one drug and
so the 'Med Number' field is intended to differentiate
between one drug and the next for each patient. 'Med
Number' is not intended to identify the drug per se,
merely to allow for 'repeats' with the same 'Patient
Number', so for example, Aspirin might have a value
of 'Med Number' 1 for one patient and a value of 'Med
Number' 4 for another patient because the data enterer
entered it first for the first patient and fourth for the
other one.

the point of this is to serve as a prelude to this 'how
to' question: basically, i think it'd be kind of neat to
have ms a2k handle the job of incrementing the values
of 'Med Number'.

Patient Number Med Number
321331 1
321331 2
321331 3
091431 1
091431 2
877861 1

so in the above example the three hypothetical patients
would've had med number 1~3, 1~2 and just 1 entered
automatically in the Med Number field after the data entry
clerk selected the value of Patient Number from a combobox
of registered patient numbers.

thanks in advance!
 
I think that making Med number meaningful (the pk for Medications table)
would be the neatest, so that the enterer would have an autolookup when
they start typing "Aspirin" the 1000th time and wonder, why wouldn't it
just pop up!
To answer the autoincrement qustion, you can to it like this:

NextMedNumber = DMax("MedNumber", "PatMedNumbersTable", _
"PatientNumber = " & CurrentPatientNumber) +1

where CurrentPatientNumber is the ID of the patient the enterer is
currently working with.

Again, I strongly suggest you make Aspiring number the same for all
patients! Or you will have to eventually figure out why some patients
are affected by Aspirin, while a few have the same symptoms from Apsirin.

Good luck,
Pavel
 
what my original posting does not do is tell the reader
that medications are entered into a combobox control but i
do understand/appreciate your concerns.

i have tried variations of the code you contributed and i
seem to be running up against a wall -- namely that
because 'Patient Number' and 'Med Number' controls are
defined as comprising the primary key in the underlying
table, that 'Med Number' cannot be tested for nullity
(which would intuitively be true) when creating a 'Patient
Number' for the first time, and therefore converted to
have the value of 'Med Number' equaling '1', because, a
control that is required (as in one serving to define a
primary key) cannot be null!

i wish it were as simple as my posting probably got you to
believe.

thx :-)
 
If you create a new patient and insist on restarting the MedNumber from
1, I think you should follow the basic logic:

1) Check if it is new record, if yes, go to 2
2) Check if it is a new patient by whatever means you use (last name,
last + first name, SSN - anything)
2a) If new patient, MedNumber = 1
2b) if not new patient, NextMedNumber = DMax("MedNumber",
"PatMedNumbersTable", "PatientNumber = " & CurrentPatientNumber) +1

Since you are making your own PKs, you have full control over them.
Since the design is denormalized, be careful not to run into used
combinations and it should work.

As far as
i wish it were as simple as my posting probably got you to
believe.
Click to expand...
goes, for me it usually ends up being simpler than it seems at first :-)

Good luck,
Pavel
 
Back
Top