Re: incrementing records in linked subform

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

it might be simpler if you could re-write it to conform to what you're saying
(if you wouldn't mind). i'm having a small problem understanding the
calligraphy and i know it'd be simpler to just cut and paste the finished
product and then worry about understanding the reason it works.

best,
-ted

"Gina Whipp" wrote:

> Had to make it make sense, I sent while I working and realized I got it a
> bit mixed up...
>
>
> first thing that came to mind because SOMETIMES its' the obvious and the
> only thing I saw that MIGHT be wrong is if this is a number
> [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB Number] ....then would
> have to be changed because numbers require "[field]=' " & Me![field] &" ' "
> formatting
>
>
> > "Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx >
> >> wrote in message
> >> news:02933919-96FB-4362-8AD9-C470E3EDCAB0@xxxxxxxxxxxxxxxx
> >>i think those are some reasons you cut and pasted from the help doc'n, am
> >>i
> >> wrong? thanks, i don't think it's a or c. i'm kind of thinking that
> >> there's
> >> something about the way the criteria was written (i.e. in b) that's
> >> giving it
> >> heartburn but alas 'what'?
> >>
> >> "Gina Whipp" wrote:
> >>
> >>> 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
.

Relevant Pages

  • Re: Whats the Problem?
    ... I still haven't seen a good reason. ... People in Biblical times supposedly saw God. ... To have piece of mind and make you feel good. ... Yes, according to evolutionary theory. ...
    (talk.origins)
  • Re: You know
    ... mind and one's emotions not being in conflict. ... sizable portion of human beings go through life incredibly conflicted ... > Every great lyric has insight beyond reason, ...
    (rec.arts.theatre.musicals)
  • Re: OT: Ive not met this lunacy before
    ... But I mean well - if I'm irritating, please do bear in mind the fact ... I can't see any reason for forming that opinion about me. ... rocket science is very simple: ... bloody hard keeping my head above water, ...
    (uk.comp.sys.mac)
  • Re: Intelligence - one of degree?
    ... You have given me no reason to think that your ... You trust what you find in your mind. ... It's my conditioning which ... >> the brain-washing society has done to you. ...
    (comp.ai.philosophy)
  • Re: This Just Has To Be Said
    ... Which would also reason that you were ... you can't change your mind over time? ... in Iraq have been wasted...a view that is so basically offensive to this ... going to succeed the elation of victory. ...
    (rec.sport.football.college)