Pop up form doesn't link correctly, PLEASE HELP!!!

[Sandra]

On a tabbed main form (PeoplePlaces)I have a datasheet
subform (DonationsList), this subform is from a query
which filters for donation records from a table
(Donations) based upon the PK (PPID)in master
PeoplePlaces table. This form cannot be edited and is
used to scroll through a list of records. The OnClick
event for DonationsList opens a form (DonationsForm)
filtered by the donation transaction id (DID) of the
clicked record. This form allows for all the edits, etc.
and works just fine.
I created a new form DonationsNew, identical to
DonationsForm, but set to DataEntry. I have a NEW button
which opens this form. The first new record entered is
linked to the PPID record in PeoplePlaces, but after that
the PPID is not being passed to the form. Here's what I
have in the OnClick of the NEW button:

Private Sub NewRecord_Click()
On Error GoTo Err_NewRecord_Click
MyID = Me.PPID 'get the PPID from the main form
Dim stDocName As String
stDocName = "DonationsNew"
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
Forms!DonationsNew.PPID = MyID
Exit_NewRecord_Click:
Exit Sub
Err_NewRecord_Click:
MsgBox Err.Description
Resume Exit_NewRecord_Click
End Sub

The first problem I ran into is that a record is
automatically created once the PPID field is filled. If
the user closes the form without entering any real data I
still have a garbage record in my Donations table.

The second problem is that only the first new record
receives the PPID value. Trying to enter a subsequent
new record will cause an error "Cannot save record"
because there is no link back to the PeoplePlaces table.

And one last thing I can't see to get right...how
do I refresh the DonationsList datasheet form after a new
record is added to the pop up form?

TIA for any advice,
Sandra

 

[tina]

try this:
amend your NewRecord_Click procedure, as

Private Sub NewRecord_Click()
On Error GoTo Err_NewRecord_Click
Dim stDocName As String
stDocName = "DonationsNew"
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
Exit_NewRecord_Click:
Exit Sub
Err_NewRecord_Click:
MsgBox Err.Description
Resume Exit_NewRecord_Click
End Sub

removed the PPID assignment from the code.
add the following code to the pop-up form's BeforeInsert event, as

Private Sub Form_BeforeInsert(Cancel As Integer)

Me!PPID = Forms!PeoplePlaces!PPID

End Sub

the PPID foreign key value will only be inserted when the user starts typing
a new record. if the user hits the Esc key, or "undoes" the data entry, the
PPID will be undone also.
add the following code to the pop-up form's OnClose event, as

Private Sub Form_Close()

Forms!PeoplePlaces!NameOfSubformCONTROL.Form.Requery

End Sub

this will requery the subform, when the pop-up form closes. *Note*: you
have to use the name of the subform CONTROL, not the name of the subform. to
make sure you use the correct control name, do the following: open the
mainform in design view. open the Properties box and click on the Other tab.
click *once only* on the subform *in the mainform design view*. look at the
Name property on the Other tab. that's the subform control's name.

hth

 

[Sandra]

Oh Tina! THANK YOU THANK YOU THANK YOU...It works!
I have been stuck on this one thing for a week, otherwise
my db is looking pretty good! This is a donor base for
the Ronald McDonald House in Harlingen, TX, and we have
been working on this for several months. I can't wait to
tell my volunteers we are ready to rock and roll!

Thank you so very much,
Sandra Grawunder
Chairman of the Board (and Accidental IT Manager)

 

[tina]

you're very welcome, glad i could help! small country - i'm currently
working on a db for another nonprofit organization, in Dallas.

 

[Sandra]

Good for you Tina! A labor of love, right? Very
rewarding when you're done, but the pay is lousy LOL!
Thanks again - Sandra G