wait, don't go yet, it gets interesting.
i thought i'd work with the idea of using an expression in the 'Default
Value' property of the 'Visit #' field on the subform ("DaysView") which
used
the DMax expression, so i whipped (no pun intended) up this one:
=Nz(DMax("[RecordNumber]","[DaysView]"),0)+1
and viewed the result. i found that for a condition where my sub-form had
8
pre-existing values (1,2,3,...,8), that there was 'lo and behold a 9 in
the
ninth record. wunderbar, but for the fact that every other subform had a 9
in
the same control as well. (there are only three mainform records having
any
meaningful subform records in this test version of my mdb file, and the
others had 3 and 5 records in the subform respectively, so the function up
there is failing to filter for the PK values i reasoned and globally
identifying 8 as the max and popping a 9 everywhere).
i then decided i'd beef the expression up a bit and whipped (there i go
again) up the next one:
=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & [Me]![Last Name]
&
"[First Name] = " & [Me]![First Name] & "[MI] = " & [Me]![MI] &
"[MR_Number]
= " & [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB Number]),0)+1
which filters out those subform records not belonging to the one that the
user is viewing (or at least that's my theory) and what happens is that
the
user sees "#Name?" (w/o the quotations) in the "Visit #" control's field.
any ideas you'd like to share?
Gina Whipp said:
Yes you are correct, and I MISread what you required (too much coffee)...
pardon the interuption...
i think what it does is automatically create twenty four records having
the
same serial number and item_numbers like 1,2,3,.....24. am i correct?
if
so,
this isn't quite what i had in mind: i.e., the ability of the user to
click a
cmd button on the main form which added a new sub-form record to it and
launched some code that incremented the value of 'Visit #' based on the
maximum value of the preceding visit.
-ted
:
Well, if you have a problem let me know, I got the sample database
that
it
actually works in...
it's probably worth a try, gina. i'll just have to see what happens
(when
i
'map' into my controls etc.).
thanks,
-ted
:
Ted,
I found this database online (and I REALLY wish I could remember
where
so
I
could at least acknowledge the person who wrote it) that increments
records.
I have not tested or debugged it but I do know it works in the
databse
example I downloaded. Perhaps it will help?
Private Sub Form_AfterInsert()
Dim db As Database
Dim LSQL As String
Dim LCntr As Integer
'Establish connection to current database
Set db = CurrentDb()
LCntr = 1
'Create SQL to insert item_numbers 1 to 24
Do Until LCntr > 24
LSQL = "insert into inventory_details (serial_number,
item_number)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & serial_number & "', " & LCntr & ")"
'Perform SQL
db.Execute LSQL
'Increment counter variable
LCntr = LCntr + 1
Loop
'Requery subform that new records are listing
frmInventory_details.Requery
End Sub
thanks but what did this have to do with my question, i.e. the
job
of
sequentially numbering records per combination of MR and IRB
numbers?
:
Then you should be using the MR# to link the parents to the
children
and
omit the names as they are an unncessary level.
I would still go with identifying the visits by Date/Time as
you're
not
going to have two visits occur simulatenously (unless your
dealing
with
pan-demensional, alternate realities that intersect at a
specific
space/time) and abandon trying to number them. If you set the
..DefaultValue of the field that will contain the Date/Time to
NOW(),
the
users will not have to enter it as it will be added when a new
record
is
created. Also, by using Date/Time data you'll be able to provide
information on the last visit and whatnot. Additionally,
separate
from
all of this you can do a DCount() to provide a count of the
number
of
visits that a person has had and the Date/Time of the last visit
using
DMax()
David H
Ted wrote:
i agree, fn, mi, ln are insufficient to guarantee uniqueness,
agreed.
mr#
(medical record number, akin to SSN) is unique, though.
regarding your 2nd point, the record number is there to
differentiate
one
record from the other and also for appearance's sake -- given
the
almost
aesthetic rationale for its existence, i figure i'd unburden
the
user
from
the 'task' of entering it manually.
there is no appointment calendar to this database; apparently
that
is
being
done w/in another software venue; there is however some
interest
on
the
part
of some users to have a few components regarding the study
visits
in
this
database. currently, when they are entered they are entered
into
a
global
comments field.
-ted
:
I would HIGHLY recommend that you reevaluate how you're
linking
the
forms. The problem with linking using a name is that you can't
guarantee
that a full name (first, middle, last) will be unique. Trust
me I
made
that mistake one and it hurt and hurt bad. The more common
approach
is
to create a field in the parent table (tblPatients) whose
datatype
is
set to Autonumber and to set this field as the primary key.
Then
in
the
child table (tblVisits) to create another field which captures
the
value
of the primary key as the records are added.
Second, is there a SPECIFIC need to know that what took place
on
visit
#3 - as opposed to the visit that occurred on 6/17/2005 at
5:15
PM?
Its
a lot easier to document the visit by Date/Time as opposed to
sequence
number since you don't have deal with issues related to a
multiple
user
environment and situations where a visit was deleted (by
design
or
accident). Not to mention that if there's a component of the
database
that handles appointments, that the date/time information has
already
been captured.
Ted wrote:
I have a pair of linked (parent and child) forms; the child
is I
guess
what
you'd call the subform. They are linked by LN, FN, MI,
MR_Number,
IRB
Number,
where the first three fields record the last name, first name
and
middle
initial of each patient.
The main form is called 'Screening Log (Edit Only)', the
subform
is
called
'DaysView'. Their respective record sources are 'Screening
Log
Query
For Form
(Revised)' and 'DaysView'.
There is a control on the subform/child I call 'Visit #'
which I
would
like
to automatically increment, as in 1,2,3,4,5,..,etc. when I
click
an
'Add
Record' button I'm going to create on the main/parent form.
