Re: incrementing records in linked subform

Tech-Archive recommends: Speed Up your PC by fixing your registry

Reasons (and resolutions) you see #NAME?

a.. Make sure that the field specified in the control's ControlSource
property hasn't been removed from the underlying table or record source
b.. Check the spelling of the field name in the control's ControlSource
property.
c.. If you specified an expression in the control's ControlSource
property, make sure that there is an equal sign preceding the expression.
"Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C9940BC7-8F61-42C1-B1D8-DB6F6022082C@xxxxxxxxxxxxxxxx
> 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" wrote:
>
>> Yes you are correct, and I MISread what you required (too much coffee)...
>> pardon the interuption...
>>
>> "Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:E91D27DB-4C38-489D-A8A1-613EDAA4DA8A@xxxxxxxxxxxxxxxx
>> >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
>> >
>> >
>> > "Gina Whipp" wrote:
>> >
>> >> Well, if you have a problem let me know, I got the sample database
>> >> that
>> >> it
>> >> actually works in...
>> >>
>> >>
>> >> "Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:0ED308BB-D52F-4FD5-AA58-D076F6CE7370@xxxxxxxxxxxxxxxx
>> >> > it's probably worth a try, gina. i'll just have to see what happens
>> >> > (when
>> >> > i
>> >> > 'map' into my controls etc.).
>> >> >
>> >> > thanks,
>> >> >
>> >> > -ted
>> >> >
>> >> > "Gina Whipp" wrote:
>> >> >
>> >> >> 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
>> >> >>
>> >> >> "Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> >> news:D45035AD-651C-4801-A9BB-792FE88547AD@xxxxxxxxxxxxxxxx
>> >> >> > 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?
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > "David C. Holley" wrote:
>> >> >> >
>> >> >> >> 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
>> >> >> >> >
>> >> >> >> > "David C. Holley" wrote:
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >>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.
>> >> >> >> >>>Since
>> >> >> >> >>>the
>> >> >> >> >>>records
>> >> >> >> >>>from the pair of forms are linked, we would want to reset the
>> >> >> >> >>>counter
>> >> >> >> >>>to 1
>> >> >> >> >>>when 'Visit #' has never been created for a patient. I'm very
>> >> >> >> >>>sure
>> >> >> >> >>>that this
>> >> >> >> >>>is doable but would like to know if there's some template to
>> >> >> >> >>>get
>> >> >> >> >>>me
>> >> >> >> >>>started.
>> >> >> >> >>>
>> >> >> >> >>>With thanks in advance,
>> >> >> >> >>>
>> >> >> >> >>>-ted
>> >> >> >> >>>
>> >> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>


.

Quantcast