Force a user to enter records in a subform

[BigDawg]

I need to make the user enter a record in the subform when they enter a
record in the main form. Ex. the data base is for customer claim inquiries
and I have a customer claim main form with an inquiry subform and a inquiry
status subform and status activity subform. Ofcourse hinesite is better than
foresite and I know now that I sould have put the status info in the table
with the inquiry itself but I didn't and I have too much data to change it
now. My problem is that I have inquiries with no status and I need to force
the user to enter a status. I have a work around as a macro but it is very
slow and not allways relaible. I'm not sure how to accomplish this in VBA as
I am just starting to learn it.

 

[Allen Browne]

You have a chicken'n'egg problem here.

The subform normally holds related records. You cannot create a *related*
record until there is something to relate it to. If follows that you cannot
require a related record as a condition for accepting the primary record.

One option would be to use the AfterInsert event of the main form to execute
an append query statement that adds the related record to the subform's
table, using some default status value. Then requery the subform so the new
record shows up.

 

[tina]

if the inquiry status data in one table is linked to the parent records in
the inquiry table, and if each inquiry record only has one related status
record, it should be easy enough to create the status field(s) you need in
the inquiry table and then run a single Append query to copy the records
from the status table into the related records in the inquiry table, and
then delete the status table entirely. then you don't need a convoluted form
solution; just get rid of the status table's subform (because the status
table no longer exists), and add the inquiry table's status fields to the
inquiry form's RecordSource; then add controls bound to those fields so you
can enter the data. suggest you try it on a COPY of your database, though,
just in case...

hth

 

[BigDawg]

Actualy I have thought of that how ever I still have the status activity link
to the status, How would I carry these over when moving the status to the
Inquiry?

 

[tina]

well, it depends on the current setup, and the business process. currently,
is the StatusActivity table a "child" of the InquiryStatus table? so that
the current InquiryStatus primary key field is a foreign key in the
StatusActivity table? and does each inquiry status record only have *one*
related status activity record?

also, my previous post was based on the premise that "each inquiry record
only has one related status record". is that correct?

hth

 

[BigDawg]

The first two questions are true also they are set to one to one relationship
the easy part. How ever the status activity is set to a one to many with the
status and there are multiple activity entries on some.

BD

 

[tina]

well, "fixing" the existing data is a little trickier, then - but again, you
only have to do it once, so no need to go through a big automation thing,
and just be sure to back up your db before you begin the "fix".

so each inquiry record only has one related status record, however each
status record may have multiple status activity records. here's what i would
do:

1. start by opening the Relationships window and deleting the links between
the three tables.

add the fields from the Status table table to the Inquiry table.
include the primary key field, but if its' data type is Autonumber, change
it to Long Integer when you add it to the Inquiry table.
note: do NOT include the foreign key field that is currently linking the
Status table back to the Inquiry table.
the easy way to do this is to simply open the Status table in design view,
highlight the fields you want to add to the Inquiry table, press Ctrl+C.
then open the Inquiry table and paste (Ctrl+V) the fields wherever you want
them. this action puts the fields, including their properties, into the
table - but NOT the data. we'll get to that.

2. run an Append query to append the data from the Status table into the
Inquiry table. again, make sure you also append the primary key field of the
Status records.

3. in the StatusActivity table, add a new field to the table, i'll call it
fkInquiryID. this field will hold the primary key value from the Inquiry
table, as a foreign key. so you need to make sure the data type matches the
Inquiry table pk field's data type (remember, when the pk field is
Autonumber, the fk field is Long Integer).

4. create a SELECT query using the Inquiry table and the Status Activity
table. link the two tables on the Status pk field (that was originally in
the Status table) and the Status Activity's fk field. open the query in
datasheet view and review it to make sure the records are matched up
correctly.

5. when you're satisfied that the SELECT query is correct, go back to query
design view and turn the query into an Update query. Update the fkInquiryID
field with the primary key from the Inquiry table (the "real" primary key,
not the pk field from the Status table).

6. now the Status Activity records are linked directly to the Inquiry table.
delete the "old" foreign key from the table - the one that linked it to the
"old" Status table.
in the Inquiry table, delete the pk field from the "old" Status table.
delete the old Status table. go back to the Relationships window, and link
the Status Activity table to the Inquiry table.

if you understood all this, you can probably do the whole thing in about 10
minutes. but if it was clear as mud, let me know and i'll throw together a
demo db that i can email to you so you can see how to set it up.

hth