Inserting records into junction table (and subform) from popup form

[kheisler6]

I'm working on a database that holds information on perpetrators of
child abuse. Perpetrators are stored in tblPerps; victims are stored in
tblVictims. Because a perp can have many victims, and a victim can have
many perps, I also have a junction table: tblPerpsVictims.

After a user enters a perpetrator on the main form (frmPerps), I'd
like him to be able to assign any number of victims using a continuous
subform (fsubVictims, based on tblPerpsVictims). On the subform, the
user should be able to either select from a list of existing victims,
add a new victim, or both. I realize that the easiest way would be to
have a combo box on the subform (where he can select a victim from
tblVictims) with a NotInList event (so he can add a new victim). But
for a variety of reasons, I'd like to use a different interface:

1. The new record of the subform should have a default
'value'/hyperlink which reads "Assign new victim."

- e.g.: http://img516.imageshack.us/my.php?image=victim1ll1.jpg

2. Clicking this will call a pop-up expanding search form where the
user can search to see if the victim has already been entered.

- e.g.: http://img512.imageshack.us/my.php?image=searchvj0.jpg

3. If a match is found, he selects the victim, clicks an "Assign
victim to perp" button, and the victim is entered into
tblPerpsVictims (and the subform is updated).

- The subform would then look like this:
http://img512.imageshack.us/my.php?image=victim2dq4.jpg

4. If the victim he needs to assign has not already been entered in the
database, he clicks an "Add new victim" button on the search form,
enters the victim info. in a form that pops-up, closes the form, and
the victim is entered into tblVictims, tblPerpVictims (and the subform
is updated).

###

I'm having trouble with #1 - setting the default 'value' of a
new record on the continuous subform to be a hyperlink (or label with
OnClick code), which can call the search form. This seems like an easy
task but I'm not sure how to proceed.

And once a victim is selected in the search form, would using a SQL
Insert command be the best way to assign that person to tblPerpsVictim?

Thanks for any pointers.

Kurt

 

[John Nurick]

I'm having trouble with #1 - setting the default 'value' of a
new record on the continuous subform to be a hyperlink (or label with
OnClick code), which can call the search form. This seems like an easy
task but I'm not sure how to proceed.

It's much simpler to (a) get rid of the default "new record" by clearing
the subform's AllowAdditions property and (b) have an "Assign new
victim" button which launches the search form. The button could be on
the main form, or in the subform's footer or header.

And once a victim is selected in the search form, would using a SQL
Insert command be the best way to assign that person to tblPerpsVictim?

That's probably how I'd do it (build up the INSERT INTO statement in a
VBA string variable and then execute it); the alternative is to use
recordset operations.

BTW, I get the impression from my limited reading on this subject that
some perpetrators are themselves victims. Wouldn't it be better to have
one table tblPersons, with tblPerpsVictims being something like
PerpID* FK into tblPersons.PersonId
VictimID* FK into tblPersons.PersonID
CaseID* FK into tblCases.CaseID
other fields...