Hi,
I'm first going to digress about Autonumber datatypes and then suggest
a significant change in your schema. There will be no append query
required in the new schema.
The Autonumber datatype is intended only to provide uniqueness for use
as surrogate Primary Keys. They have no meaning. They are not
guaranteed to be sequential. They are *not* record numbers. They
should never be seen by anyone but the application designer. If you
need to create a sequence that you can modify as necessary use the
"DMax() +1" on that field in that table. You will get an unbroken
sequence unless someone manually interferes with the process.
What purpose is your application intended to serve in the real world?
If this attempt to help you resolve your issue doesn't do the job then
please post back with a description of your intended application and
provide the existing (or envisioned) tables and their fields and
datatypes.
Your schema could be re-arranged (re-designed) such that a single
form/subform could suffice. It seems that, for business reasons, you
have broken the workflow into two small processes: Detail Gathering
and Detail Categorization. If you trust the Gatherers to simply
gather the information then you can use a single form for both
processes. If they can't be trusted to that extent then two versions
of the Detail form can be provided; one without a subform for the
Gatherers and another with the subform for the Categorizers. Other
presentation/user interface schemes could be used....
I propose a simple scheme with three tables:
tblDetail: This table is used to store the details
of this real world event.
tblCategory: This table holds the list of assignable
Categories - just now that's 3 but you
could easily add to the list without
requiring a re-design such as adding
checkboxes and altering code to
handle them...
tblDetailsCategories: This table will have one entry
for each intersection of a Detail and a
Category.
If you still have problems then post back with your issues. In your
use of "Autonumber" I'm not sure if you're sequencing the records or
simply referring to the Autonumber PK that Access creates for you?
HTH
--
-Larry-
--
ladybug via AccessMonster.com said:
Here is more info:
I have a form where the first user enters the basic detail. This detail is
assigned an autonumber. The entry is then closed. Another user opens this
information on another form. This time the form has three checkboxes. This
second user has to select what category that original information falls under
(they can select more then 1). Once they select the category(s) I need that
original Autonumber to dump into its corresponding table (if the second user
selects Adverse Event then that Autonumber will dump into the Adverse Event
Table and create a new Autonumber. I tried an Append Query, but it keeps
trying to add that entry as well as all other entries that have adverse event
selected again. I want it to just append the entry open on the form. This
is my first time using Append Queries so if this makes sense please walk me
through what to enter in criteria.
Larry said:
Is this Steve S. wearing a new disguise?
I suggest that a more convenient approach might be to use a
switchboard with three items being the ones that open the secondary
forms.
The only way I see an advantage to the paradigm you suggested in your
post is if you are trying to keep track of the events in tblDetail.
There is no point in doing that nor in having tblDetail. The exact
same information is available to you by running a query on the three
other tables.
To follow up on your suggested paradigm you really need to make
explicit whether the three choices are mutually exclusive. There are
drastically different ways of achieving the desired results but you
have to spell things out in detail.
If the events are mutually exclusive then I suggest that you change
from three CheckBoxes to a single OptionGroup with three choices. In
the code behind the button to execute you'd use a Select...Case
statement on the value of the option group. That way you never get
into the situation of missing the right event because an earlier wrong
checkbox was checked.
If a single event can have two or more of the named conditions then
you can put code behind the command button to execute that will
explicitly check each checkbox and open the target form for that
checkbox in dialogue mode. That will keep each form up until it has
been addressed.
Post back for clarification or with new issues but be sure to explain
in much greater detail what you are trying to achieve and trying to
avoid.
HTH
I have a table called tblDetail. In this table each entry has an autonumber
assigned to it. Within this table there are 3 yes/no boxes
(AdverseEvent,
[quoted text clipped - 4 lines]
would appear and another autonumber assigned.
Can someone walk me through how I could do this with great
detail. Thank you.
