Data entry on a popup from a subform

[Aaron G]

Hi all,

Using 2003 - I'm working with three forms. "Main", "JournalSub" and
"JournalPopup". JournalSub is a subform of Main. I want to be able to click
a button on the JournalSub that brings up the data entry popup JournalPopup
for entering new journal records.

I've read though this forum and found that I should add code to the
"BeforeInsert" to the effect of:

Me.MainID = Forms!Main!MainID

This doesn't seem to be working. Could it be the fact that I'm trying to
open from a subform?

TIA

Aaron G
Philadelphia, PA

 

[tina]

This doesn't seem to be working.

are you getting an error code? if so, what? and when? if no, what is or
isn't happening?

 

[Aaron G]

Tina,

I do not get any error. The Journal table does not contain any of the main
contact's information, so to verify the journal is going to the right person,
I added the "LastName" field from the Main table on the popup. It never
populates UNLESS I'm going though journals that have already been entered.
It just doesn't work for new records.

Aaron G
Philadelphia, PA

 

[tina]

whoa, did we change subjects in mid-conversation? your first post asked how
to insert a main form's primary key value (MainID) into another form's
foreign key field when a new record is entered (BeForeInsert) in the second
form. doing this makes sense when the main form is bound to a parent table,
and the 2nd form is bound to a related child table; because the second form
is not a linked subform, you have to add the foreign key value because
Access can't do it automatically.

your reply to my post talks about populating a name field in the 2nd form,
which works on existing records but not on new records. the short answer is:
if the main table and child table are already linked with an ID number, you
*don't* want to add duplicate data like a name to the child table. that
value is already saved in the main table.

the longer answer is: let's clarify what you're actually trying to do, and
why, before we figure out how to accomplish it.

hth

 

[Aaron G]

What I was saying originally just had to do with what I had already found on
the posts. I just didn't want to post a question to have somebody post what
I already found. Sorry for the confusion. Let's start over...

I have two tables - Main and Journal. It has a respective one-to-many
relationship. I have one form "Main", one subform called "JournalSub" and
one popup called "JournalPopup".

The subform JournalSub works fine as a child object of the main form.
However, for several reasons, I want to be able to click a command button on
the subform that would open up the JournalPopup form and allow me to add a
new record. I can't get this to work because the relationship between the
Main and Jornal tables is not translating when I try to add a new record. If
I set the "data entry" property for the popup to no/false then I can see the
journals already created for the contact, but when I get to the final record
plus one (to enter a new journal) the relationship is lost.

How do I keep this relationship?

Thanks.

Aaron G
Philadelphia, PA

 

[tina]

okay. you're correct that there's no "automatic" link between the Main form
and the JournalPopup form, so you do have to supply the foreign key value
yourself.

I've read though this forum and found that I should add code to the
"BeforeInsert" to the effect of:

Me.MainID = Forms!Main!MainID

well, if the name of your foreign key field in the Journal table is
"MainID", and if the names of the main form and its' primary key field are
correct, then i don't see any reason why the code wouldn't work, in the
popup form's BeforeInsert event. i would run it from that event, or from the
form's BeforeUpdate event.

you say it "doesn't work", but you're not getting an error message. what are
you checking to determine that the code is not, in fact, populating the
popup form's foreign key field correctly?

 

[Aaron G]

Tina,

You lead me in the right direction to find the error. I wasn't putting the
foreign id first in the line of code! All better now. Thanks for your time.

Aaron G
Philadelphia, PA

 

[tina]

you're welcome

Tina,

You lead me in the right direction to find the error. I wasn't putting the
foreign id first in the line of code! All better now. Thanks for your time.

Aaron G
Philadelphia, PA