Re: strategy for data entry in multiple tables

Actually, the topic is covered very well. However, there is some added
difficulty because you omitted some information that is helpful in
determining what would be the best way to design this.

Stepping back, we need to look at the entire process for entering the
junction data. From what I've been able to gather from you so far, it
appears that some of the steps might be...

1. Enter a BirdNum, BandNum, and capture type
a) Does Bird exist in table?
b) Is BandNum correct for capture type?
2. If BirdNum does not exist, and BandNum/capture type are correct, create
new Bird record and a related capture record...enter additional data
associated with each of the record types....and then continue to subsequent
steps
Else continue to subsequent steps
3. Enter event
a) Does event exist?
4. If Event does not exist, create new event record...enter additional data
associated with this record type...then continue to subsequent steps
Else continue to subsequent steps
5. Enter junction data?

Given that you've said that the number of times that a new entry would be
required for Birds is high, this suggests to me that what you really need
then is a Bird form that can be used both for new entries and to
review/lookup existing entries. Given that the overall entry process seems
somewhat involved, a wizard type form may be appropriate. There is a tool
on web site below that helps create wizards (if you're interested, see
Add-ins/Extras/Tools). The wizard it creates allows for the possibility of
skipping steps, which seems appropriate given what you've described (i.e.,
if the Bird exists, move on).

Your insistance on multiple forms being opened simulateously that aren't
required will be as frustrating to your users as it is to me...you should
reevaluate that decision.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


"LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FE6FCAAF-FEB4-415B-AD8E-B980F21707D5@xxxxxxxxxxxxxxxx
> Hi Paul,
>
> Thanks for your help and interest. I am learning how to use combo boxes
> to
> enter data.
>
> However, this approach may not be as useful as multiple forms. New birds
> are captured throughout the year, so the user will be driven nuts by all
> the
> pop-ups. In addition, I think it would be better to have the data
> validation
> integrated with the data entry. For example, on the data entry form for
> tblBirds, the user will enter the bandnum and the capture.type (the latter
> a
> field in the junction table). There are two sources of error (wrong
> bandnum
> for capture.type, wrong capture.type for bandum) that will open a data
> entry
> form for tblError, with fields to that the user can go back to the data
> book
> and attempt to troubleshoot. This provides a record of problems that
> would
> otherwise be a real pain to detect. If there are no problems, the code I
> am
> writing will either put a new record in tblBirds (and the bandnum field in
> tblJunction form) or not put a new record in tblBirds, but copy the
> bandnum
> to tblJunction form).
>
> The data entry form for tblEvents is unlikely to have problems of this
> type.
> The code is very simple to form a recordset of tblEvents and form a sql
> string that will select site, date, and time from those variables entered
> on
> the form. Because EventID is an autonumbered field, the main issue is
> copying the field from an existing record into the data entry form for
> tblJunction or writing a new record and copying EventID from that record.
>
> I still need to work on hiding and unhiding forms, and possibly enabling
> and
> disenabling fields. But as long as the logic is correct, I hope to
> produce a
> data entry system that is seamless to the user (including me). I still
> think
> that data entry with referential integrity is one of the most
> underrepresented aspects of Access in the plethora of books that deal with
> development.
>
> All the best,
>
> LAF (Lenny Freed)
>
>
> "Paul Overway" wrote:
>
>> OK...big list...ditch listboxes and use combo boxes instead. A single
>> form
>> for the junction data. Bird and Event are combo and you say the bird has
>> a
>> number. Whenever the data entry person enters a bird number in the
>> junction
>> data form that is not in the combo list, the not in list event fires and
>> asks the user if they want to enter a new bird...it then opens up the
>> form
>> for entering the bird, with the number already entered...then the user
>> just
>> needs to make whatever other entries are appropriate for Bird, saves the
>> record and closes the form. If you use the not in list event correctly,
>> the
>> bird will now be in the list and the user can continue on with the other
>> junction data entries. Do the same for other fields like event and any
>> others that are.
>>
>> Here is an example of the code you'd need for the not in list event:
>>
>> 'This code goes in the Not In List event for the Bird combo in the
>> junction
>> data entry form
>> Private Sub cboBird_NotInList(NewData As String, Response As Integer)
>>
>> Dim i As Integer
>> Dim Msg As String
>>
>> Msg = NewData & " is not currently in the list." _
>> & vbCr & vbCr & "Do you want to add a new bird?"
>>
>> i = MsgBox(Msg, vbQuestion + vbYesNo, "Bird not found")
>> If i = vbYes Then
>> Response = acDataErrAdded
>>
>> 'Open form to enter bird
>> DoCmd.OpenForm "frmBird", , , , acFormAdd, acDialog, NewData
>>
>> 'See if user added the bird...if they did, the new number
>> will
>> be in the table
>> If IsNull(DLookup("BirdNum", "tblBird", "BirdNum=" &
>> NewData)) =
>> False Then
>> Response = DATA_ERRADDED
>> Else
>> Response = DATA_ERRCONTINUE
>> End If
>>
>> Else
>> Response = acDataErrContinue
>> Exit Sub
>> End If
>> End Sub
>>
>>
>> 'This code goes in the entry form for Bird
>> Private Sub Form_Load()
>>
>> If IsNull(Me.OpenArgs) = False Then
>> Me.txtBirdNum = Me.OpenArgs
>> End If
>>
>> End Sub
>>
>>
>>
>>
>>
>> --
>> Paul Overway
>> Logico Solutions
>> http://www.logico-solutions.com
>>
>>
>> "LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:08598629-0C89-49E3-AED1-D59EB2657EB3@xxxxxxxxxxxxxxxx
>> > Thanks, Paul,
>> >
>> > The problem I see with list boxes is that the database has about 9000
>> > birds
>> > and about 7800 events. Plus, for data verification purposes, the user
>> > should
>> > not need to know if the entry is "not in list". Maybe the thing to do
>> > is
>> > to
>> > keep the 2nd and 3rd forms hidden and become unhidden as necessary.
>> >
>> > For example, on the first form, the user would enter bird number (from
>> > its
>> > band) and capture.type (whether it is an initial capture or a
>> > recapture).
>> > The data validation would be to send the user to an error table with
>> > its
>> > own
>> > data entry form if capture is initial and the bird number already
>> > exists,
>> > or
>> > if capture is recapture and the bird number does not exist. These are
>> > errors. Assuming that there is no error, the next step is to determine
>> > if
>> > a
>> > new eventID is necessary or to bring up an existing one. While an
>> > existing
>> > event is probably from a recent record, I prefer to validate the data
>> > by
>> > testing whether a similar event exists or not.
>> >
>> > The majority of the data entry will be in the junction table and in
>> > smaller
>> > tables that have one to one relationship with it. Am I understanding
>> > your
>> > previous message?
>> >
>> > All the best,
>> >
>> > LAF
>> >
>> >
>> > "Paul Overway" wrote:
>> >
>> >> Yes...the simpler way is what I described previously...although if you
>> >> want
>> >> a clear method for users to add to the listboxes I described, you'd
>> >> need
>> >> to
>> >> include a "New" button for Birds and one for Events. An alternative
>> >> is
>> >> to
>> >> display only the junction data and use the not in list event to add
>> >> new
>> >> records for either Birds or Events as needed....the not in list event
>> >> would
>> >> cause the appropriate form to be displayed for the user to enter the
>> >> Bird
>> >> or
>> >> the Event if it isn't in the list.
>> >>
>> >> I think what you are really missing here is the utility of the Not In
>> >> List
>> >> event in this situation...I'm sure you can do some reading on that.
>> >>
>> >> I'd stay away from having 3 forms up at the same time...too
>> >> distracting
>> >> for
>> >> users. While you do need data entry forms for Birds and Events, they
>> >> should
>> >> be brought up as dialogs just when the user needs to add a new record
>> >> vs
>> >> always there cluttering up the screen.
>> >>
>> >>
>> >> --
>> >> Paul Overway
>> >> Logico Solutions
>> >> http://www.logico-solutions.com
>> >>
>> >>
>> >> "LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:961947EB-6F4E-4471-87D1-72E0C6688184@xxxxxxxxxxxxxxxx
>> >> > Thanks Paul and Bruce,
>> >> >
>> >> > This is what makes the community group so valuable.
>> >> >
>> >> > The issue of data entry for master tables and junction tables, with
>> >> > referential integrity and cascading update, has to be one of the
>> >> > most
>> >> > general
>> >> > in Access. It is completely neglected in the 5 or so books I have
>> >> > on
>> >> > Access.
>> >> >
>> >> >
>> >> > The following subissues are involved in every single access data
>> >> > base
>> >> > with
>> >> > referential integrity.
>> >> >
>> >> > 1. Need to determine if a new record needs to be written in
>> >> > neither,
>> >> > one,
>> >> > or both master tables.
>> >> >
>> >> > 2. The master table primary key values, whether for newly written
>> >> > record
>> >> > or
>> >> > previous record, need to be entered into appropriate fields of the
>> >> > junction
>> >> > table data entry form.
>> >> >
>> >> > I am currently experimenting with a 3-form model, one form for each
>> >> > master
>> >> > table and one for the junction table. All 3 will be on screen. The
>> >> > user
>> >> > will first use one of the master table forms to determine if a new
>> >> > record
>> >> > needs to be written. If so, the data entered on this form will be
>> >> > used
>> >> > to
>> >> > create the new record, and the primary key value will be copied to
>> >> > the
>> >> > foreign key field on the junction data entry form. If a new record
>> >> > does
>> >> > not
>> >> > need to be written, the primary key value that was inputted will
>> >> > copied
>> >> > to
>> >> > the foreign key field (through code).
>> >> >
>> >> > This procedure will occur for each master table in sequence. The
>> >> > user
>> >> > will
>> >> > simply be left with the larger junction table data entry form with
>> >> > the
>> >> > foreign keys filled in from the previous master table forms.
>> >> > Subforms
>> >> > can
>> >> > then be used for data tables that are in one to one correspondence
>> >> > with
>> >> > the
>> >> > junction table.
>> >> >
>> >> > This is a little involved, but the code that underlies this serves
>> >> > for
>> >> > data
>> >> > validation as well as entry. My question thus becomes: is there a
>> >> > simpler
>> >> > way to get the foreign key values on the junction table data entry
>> >> > form?
>> >> >
>> >> > All the best,
>> >> > LAF
>> >> >
>> >> >
>> >> > "Paul Overway" wrote:
>> >> >
>> >> >> It makes sense because birds are being captured collectively as a
>> >> >> group
>> >> >> in
>> >> >> nets. If they were being captured individually, the junction would
>> >> >> be
>> >> >> unnecessary....a bird would be related to events in a one to many.
>> >> >> But
>> >> >> because the birds are captured collectively, the junction is used
>> >> >> to
>> >> >> resolve
>> >> >> what birds were captured during an event and vice versa.
>> >> >>
>> >> >> --
>> >> >> Paul Overway
>> >> >> Logico Solutions
>> >> >> http://www.logico-solutions.com
>> >> >>
>> >> >>
>> >> >> "BruceM" <BruceM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> >> news:1F96B97E-6A9E-4A84-B911-C527B1EBD230@xxxxxxxxxxxxxxxx
>> >> >> > Interesting discussion, but it leaves me wondering how tblEvents
>> >> >> > and
>> >> >> > tblBirds
>> >> >> > are related many-to-many unless an individual bird could be
>> >> >> > captured
>> >> >> > several
>> >> >> > times. I see how each event could involve many birds, but not
>> >> >> > the
>> >> >> > other
>> >> >> > way
>> >> >> > around. What relationship is the junction table resolving? I
>> >> >> > would
>> >> >> > have
>> >> >> > imagined based on my understanding of the question that it would
>> >> >> > be
>> >> >> > Events
>> >> >> > >
>> >> >> > Birds > Bird Details in a cascading one-to-many relationship,
>> >> >> > with
>> >> >> > nested
>> >> >> > subforms. I don't really need an answer, but I am a bit curious
>> >> >> > as
>> >> >> > to
>> >> >> > how
>> >> >> > this discussion will unfold.
>> >> >> >
>> >> >> > "Paul Overway" wrote:
>> >> >> >
>> >> >> >> Ah...ok. I see what you're trying to do now.
>> >> >> >>
>> >> >> >> I'd have a single unbound entry form for this, with 2
>> >> >> >> listboxs...one
>> >> >> >> for
>> >> >> >> Birds, and another for Events. I'd have an Add button that
>> >> >> >> would
>> >> >> >> add
>> >> >> >> a
>> >> >> >> record to a subform for the Junction data. I'd use the
>> >> >> >> selections
>> >> >> >> from
>> >> >> >> the
>> >> >> >> 2 listboxes in the link master/link child property for the
>> >> >> >> subform,
>> >> >> >> which
>> >> >> >> would allow you to see how the 2 master tables are related and
>> >> >> >> the
>> >> >> >> junction
>> >> >> >> data for that combination.
>> >> >> >>
>> >> >> >> --
>> >> >> >> Paul Overway
>> >> >> >> Logico Solutions
>> >> >> >> http://www.logico-solutions.com
>> >> >> >>
>> >> >> >>
>> >> >> >> "LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> >> >> news:B666C61F-16CB-4053-A0F4-4D3A63A51E24@xxxxxxxxxxxxxxxx
>> >> >> >> > Hello All,
>> >> >> >> >
>> >> >> >> > Thanks for the stimulating discussion. Let me go back to the
>> >> >> >> > original
>> >> >> >> > problem. The data entry form is not to look at records, it is
>> >> >> >> > for
>> >> >> >> > entering
>> >> >> >> > (and validating) data. I don't see how a subform for the
>> >> >> >> > junction
>> >> >> >> > table
>> >> >> >> > can
>> >> >> >> > be used appropriately with a form for just one of the master
>> >> >> >> > tables.
>> >> >> >> > The
>> >> >> >> > foreign key for the other master table will be present, but
>> >> >> >> > the
>> >> >> >> > data
>> >> >> >> > entry
>> >> >> >> > needs to go into the primary key of the other master table.
>> >> >> >> >
>> >> >> >> > By the way, the database I am developing is for capturing
>> >> >> >> > birds
>> >> >> >> > in
>> >> >> >> > mist
>> >> >> >> > nets. We put numbered aluminum bands on the legs of birds we
>> >> >> >> > capture.
>> >> >> >> > tblBirds lists each individual by band number (=BirdID) as the
>> >> >> >> > primary
>> >> >> >> > key,
>> >> >> >> > with other fields such as species, leftleg, rightleg (for
>> >> >> >> > those
>> >> >> >> > birds
>> >> >> >> > on
>> >> >> >> > which we place color bands as well). tblEvents lists each
>> >> >> >> > capture
>> >> >> >> > event
>> >> >> >> > by
>> >> >> >> > site, date, and time. The EventID is an autonumbered field
>> >> >> >> > that
>> >> >> >> > is
>> >> >> >> > based
>> >> >> >> > on
>> >> >> >> > unique combinations of site, date, and time. The junction
>> >> >> >> > table
>> >> >> >> > deals
>> >> >> >> > with
>> >> >> >> > the many to many relationship between BirdID and EventID. The
>> >> >> >> > junction
>> >> >> >> > table
>> >> >> >> > has many fields that deal with data collected on birds
>> >> >> >> > captured
>> >> >> >> > in
>> >> >> >> > nets,
>> >> >> >> > and
>> >> >> >> > is in one to one relationship with tables with specialized
>> >> >> >> > data
>> >> >> >> > on
>> >> >> >> > particular
>> >> >> >> > birds. This is where I see a subform for data entry being
>> >> >> >> > useful.
>> >> >> >> >
>> >> >> >> > If both master tables could be used in the same data entry
>> >> >> >> > form,
>> >> >> >> > then
>> >> >> >> > subforms would be feasible for the junction table. Is it
>> >> >> >> > possible
>> >> >> >> > for
>> >> >> >> > two
>> >> >> >> > tables to be in the same form, each with their own primary
>> >> >> >> > keys,
>> >> >> >> > and
>> >> >> >> > then
>> >> >> >> > to
>> >> >> >> > use the junction table as a subform?
>> >> >> >> >
>> >> >> >> > Thanks,
>> >> >> >> >
>> >> >> >> > LAF
>> >> >> >> >
>> >> >> >> > "Paul Overway" wrote:
>> >> >> >> >
>> >> >> >> >> I think you're misusing or misinstanding keys. You should
>> >> >> >> >> have
>> >> >> >> >> a
>> >> >> >> >> PK
>> >> >> >> >> in
>> >> >> >> >> Event, another PK in Subject, and 2 FK (EventID and
>> >> >> >> >> SubjectID)
>> >> >> >> >> in
>> >> >> >> >> the
>> >> >> >> >> junction. You shouldn't be putting an FK in either of the
>> >> >> >> >> master
>> >> >> >> >> tables.
>> >> >> >> >> Example
>> >> >> >> >>
>> >> >> >> >> Event
>> >> >> >> >> 1 Gala
>> >> >> >> >> 2 Dinner
>> >> >> >> >>
>> >> >> >> >> Subject
>> >> >> >> >> 1 Funding
>> >> >> >> >> 2 Purpose
>> >> >> >> >>
>> >> >> >> >> Junction
>> >> >> >> >> Event Subject
>> >> >> >> >> 1 1
>> >> >> >> >> 1 2
>> >> >> >> >> 2 2
>> >> >> >> >>
>> >> >> >> >> In the example above, I've omitted one of the possible
>> >> >> >> >> combinations.
>> >> >> >> >> Nevertheless, we can see for the Gala the subject will be
>> >> >> >> >> Funding
>> >> >> >> >> and
>> >> >> >> >> Purpose, whereas for the Dinner the only subject will be
>> >> >> >> >> purpose.
>> >> >> >> >> If
>> >> >> >> >> you
>> >> >> >> >> have a subform for Junction on the Event form and on the
>> >> >> >> >> Subject
>> >> >> >> >> form,
>> >> >> >> >> you
>> >> >> >> >> can see what subjects will be discussed at the event or at
>> >> >> >> >> what
>> >> >> >> >> event
>> >> >> >> >> the
>> >> >> >> >> subject will be discussed.
>> >> >> >> >>
>> >> >> >> >> --
>> >> >> >> >> Paul Overway
>> >> >> >> >> Logico Solutions
>> >> >> >> >> http://www.logico-solutions.com
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> "LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> >> >> >> news:E136D420-8C1D-4B6E-AEC4-68EA6E0C1D97@xxxxxxxxxxxxxxxx
>> >> >> >> >> > Hi Paul,
>> >> >> >> >> >
>> >> >> >> >> > Thanks for the advice. Let us assume that there is one to
>> >> >> >> >> > many
>> >> >> >> >> > relationship
>> >> >> >> >> > between a master table and the junction table, for each
>> >> >> >> >> > master
>> >> >> >> >> > table.
>> >> >> >> >> > There
>> >> >> >> >> > is not necessarily one to one between the two master
>> >> >> >> >> > tables.
>> >> >> >> >> >
>> >> >> >> >> > In principle then, it appears that the best strategy is to
>> >> >> >> >> > combine
>> >> >> >> >> > data
>> >> >> >> >> > entry and validation for each master table separately, and
>> >> >> >> >> > then
>> >> >> >> >> > to
>> >> >> >> >> > copy
>> >> >> >> >> > the
>> >> >> >> >> > primary field to the data entry form for the junction
>> >> >> >> >> > table.
>> >> >> >> >> > I
>> >> >> >> >> > assume
>> >> >> >> >> > that
>> >> >> >> >> > there would be no problem with referential integrity to
>> >> >> >> >> > have a
>> >> >> >> >> > new
>> >> >> >> >> > record
>> >> >> >> >> > in
>> >> >> >> >> > a master field with no foreign key, yet, put in a new
>> >> >> >> >> > record
>> >> >> >> >> > in
>> >> >> >> >> > the
>> >> >> >> >> > junction
>> >> >> >> >> > table. Is this correct?
>> >> >> >> >> >
>> >> >> >> >> > Thanks,
>> >> >> >> >> >
>> >> >> >> >> > LAF
>> >> >> >> >> >
>> >> >> >> >> > "Paul Overway" wrote:
>> >> >> >> >> >
>> >> >> >> >> >> Comments in-line
>> >> >> >> >> >>
>> >> >> >> >> >> "LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> >> >> >> >> news:62C2C75F-0B93-47EE-8130-2728711D1134@xxxxxxxxxxxxxxxx
>> >> >> >> >> >> >A great strength of Access is the ability to base a form
>> >> >> >> >> >> >on
>> >> >> >> >> >> >a
>> >> >> >> >> >> >multi-table
>> >> >> >> >> >> > query and use the form for data entry. However, there
>> >> >> >> >> >> > are
>> >> >> >> >> >> > some
>> >> >> >> >> >> > important
>> >> >> >> >> >> > issues. Let us assume a simple database with two master
>> >> >> >> >> >> > tables
>> >> >> >> >> >> > and
>> >> >> >> >> >> > a
>> >> >> >> >> >> > junction table. One master table (subjects) has a
>> >> >> >> >> >> > primary
>> >> >> >> >> >> > key
>> >> >> >> >> >> > that
>> >> >> >> >> >> > is
>> >> >> >> >> >> > a
>> >> >> >> >> >> > text
>> >> >> >> >> >> > variable. The other master table is an event table with
>> >> >> >> >> >> > an
>> >> >> >> >> >> > autonumbered
>> >> >> >> >> >> > EventID as primary key. Assume for this example that
>> >> >> >> >> >> > tblEventID
>> >> >> >> >> >> > has
>> >> >> >> >> >> > fields
>> >> >> >> >> >> > EventID, Site, Date, Time.
>> >> >> >> >> >> >
>> >> >> >> >> >> > Issue 1. Should there be a single bound data entry form
>> >> >> >> >> >> > that
>> >> >> >> >> >> > has
>> >> >> >> >> >> > all
>> >> >> >> >> >> > fields
>> >> >> >> >> >> > from all 3 tables? For this to occur, code or macro
>> >> >> >> >> >> > must
>> >> >> >> >> >> > be
>> >> >> >> >> >> > written
>> >> >> >> >> >> > to
>> >> >> >> >> >> > run
>> >> >> >> >> >> > after the SubjectID is entered in the SubjectID field
>> >> >> >> >> >> > from
>> >> >> >> >> >> > tblSubjecst,
>> >> >> >> >> >> > to
>> >> >> >> >> >> > determine if a new record should be written in the
>> >> >> >> >> >> > subjects
>> >> >> >> >> >> > table.
>> >> >> >> >> >> > If
>> >> >> >> >> >> > a
>> >> >> >> >> >> > new
>> >> >> >> >> >> > record does not need to be made, then the SubjectsID
>> >> >> >> >> >> > field
>> >> >> >> >> >> > should
>> >> >> >> >> >> > be
>> >> >> >> >> >> > cleared,
>> >> >> >> >> >> > but the SubjectsID value placed in the SubjectsID field
>> >> >> >> >> >> > from
>> >> >> >> >> >> > tblJunction.
>> >> >> >> >> >>
>> >> >> >> >> >> Maybe? What is the relationship between Subject and
>> >> >> >> >> >> Event?
>> >> >> >> >> >> If
>> >> >> >> >> >> 1
>> >> >> >> >> >> to
>> >> >> >> >> >> 1,
>> >> >> >> >> >> ok.
>> >> >> >> >> >> But otherwise, it seems to me subject and event should be
>> >> >> >> >> >> separate
>> >> >> >> >> >> forms
>> >> >> >> >> >> with data from the junction table in a subform (if it
>> >> >> >> >> >> makes
>> >> >> >> >> >> sense
>> >> >> >> >> >> to
>> >> >> >> >> >> show
>> >> >> >> >> >> the related data in both places). You seem to be
>> >> >> >> >> >> concerned
>> >> >> >> >> >> about
>> >> >> >> >> >> duplicate
>> >> >> >> >> >> data in subject. Ok, so, do some validation before
>> >> >> >> >> >> update/insert
>> >> >> >> >> >> for
>> >> >> >> >> >> subject records....and if there is a duplicate, cancel the
>> >> >> >> >> >> update
>> >> >> >> >> >> and
>> >> >> >> >> >> go
>> >> >> >> >> >> to
>> >> >> >> >> >> the preexisting record or prompt the user to do so.
>> >> >> >> >> >>
>> >> >> >> >> >> >
>> >> >> >> >> >> > Issue 2. A bigger problem occurs when data are entered
>> >> >> >> >> >> > for
>> >> >> >> >> >> > tblEvent
>> >> >> >> >> >> > on
>> >> >> >> >> >> > the
>> >> >> >> >> >> > single bound entry form. Code or macro must be written
>> >> >> >> >> >> > to
>> >> >> >> >> >> > determine
>> >> >> >> >> >> > if
>> >> >> >> >> >> > the
>> >> >> >> >> >> > non-primary key fields are already present in tblEvents.
>> >> >> >> >> >> > If
>> >> >> >> >> >> > they
>> >> >> >> >> >> > are,
>> >> >> >> >> >> > then
>> >> >> >> >> >> > the EventID from the matching record should be copied
>> >> >> >> >> >> > into
>> >> >> >> >> >> > EventID
>> >> >> >> >> >> > field
>> >> >> >> >> >> > on
>> >> >> >> >> >> > the data entry form for tblEvents. If not, then a new
>> >> >> >> >> >> > record
>> >> >> >> >> >> > must
>> >> >> >> >> >> > be
>> >> >> >> >> >> > written
>> >> >> >> >> >> > into tblEvent and the autonumber copied into the data
>> >> >> >> >> >> > transfer
>> >> >> >> >> >> > form.
>> >> >> >> >> >> >
>> >> >> >> >> >>
>> >> >> >> >> >> This seems very similar to issue 1, and the solution is as
>> >> >> >> >> >> well.
>> >> >> >> >> >>
>> >> >> >> >> >> > The big question is: should there be separate data
>> >> >> >> >> >> > entry
>> >> >> >> >> >> > forms
>> >> >> >> >> >> > for
>> >> >> >> >> >> > the
>> >> >> >> >> >> > two
>> >> >> >> >> >> > master tables, possibly on unbound forms that pass
>> >> >> >> >> >> > appropriate
>> >> >> >> >> >> > values
>> >> >> >> >> >> > to
>> >> >> >> >> >> > the
>> >> >> >> >> >> > relevant data entry forms, or to an error event? The
>> >> >> >> >> >> > tblJunction
>> >> >> >> >> >> > data
>> >> >> >> >> >> > entry
>> >> >> >> >> >> > form would start out with the passed values in the
>> >> >> >> >> >> > foreign
>> >> >> >> >> >> > key
>> >> >> >> >> >> > fields.
>> >> >> >> >> >> > Is
>> >> >> >> >> >> > there a better strategy for dealing data entry with
>> >> >> >> >> >> > referential
>> >> >> >> >> >> > integrity
>> >> >> >> >> >> > and
>> >> >> >> >> >> > cascade updating.
>> >> >> >> >> >>
>> >> >> >> >> >> Yes 2 forms....with a subform for the junction data.
>> >> >> >> >> >> Whether
>> >> >> >> >> >> bound
>> >> >> >> >> >> or
>> >> >> >> >> >> unbound, you're going to need to do validation to ensure
>> >> >> >> >> >> that
>> >> >> >> >> >> any
>> >> >> >> >> >> new
>> >> >> >> >> >> record
>> >> >> >> >> >> is not a duplicate of an existing record. I'm not sure
>> >> >> >> >> >> why
>> >> >> >> >> >> you'd
>> >> >> >> >> >> have
>> >> >> >> >> >> the
>> >> >> >> >> >> junction data on yet another form, if it is related to the
>> >> >> >> >> >> master
>> >> >> >> >> >> tables....just use a subform if possible....then the
>> >> >> >> >> >> foreign
>> >> >> >> >> >> key
>> >> >> >> >> >> for
>> >> >> >> >> >> either
>> >> >> >> >> >> subject or event would be passed automatically.
>> >> >> >> >> >>
>> >> >> >> >> >> >
>> >> >> >> >> >> > LAF
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>


.