Thanks again,
I think that when I use the expression "multiple forms" I am thinking of a
wizard, and the logic progression you have specified is correct. The only
thing that might be missing is getting the junction table foreign keys from
the appropriate record in the bird table and in the event table.
Indeed, I envision an unbound form for the initial step that, after checking
for error and finding none, will write a record in the bird table, as
appropriate, and also copy the appropriate field in the junction table data
entry form. The error checking for recaptured birds would include the
species, leftleg, and rightleg fields. With this, the user is entering all
fields for tblBirds, with validation if a recapture, and with simple new
record if an initial capture. All this could occur while the user sees just
the unbound form.
The same unbound form could have text boxes for fields: site, date, and
time. When the user enters these, and then clicks another control button,
the code will determine if these fields correspond to an existing ID or if a
new record needs to be written in tblEvents. Once the bandnum and EventID
have been copied to the data entry form for the junction table, the unbound
form will close and the junction table form open with appropriate bandnum and
eventID fields filled in.
Am I incorrect in assuming that all these events could occur behind the
scenes? The user would simply enter the data as if everything was a new
record in birds and event, and the code would determine if either was
approriate, and put the correct information in the foreign key fields in the
junction table form.
I have looked at Rick Dobson's book, Alison Balter's book, Getz et al.'s
book, and Cardoza et al.'s book. None of them appear to deal with data entry
problems of referential integrity except in list boxes and combo tables.
Dobson deals with lookup queries that use the criteria from a form text box.
I would appreciate knowing a reference that emphasizes issues associated with
data entry. PS I have reduced the size of the post because it is getting
too long.
Thanks,
LAF
"Paul Overway" wrote:
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" wrote in message
...
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" wrote in message
...
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