Add record to mainform when user enters data in subform?

[Daryl Mhoon]

Didn't get any help with my first post so I'll try again. I need to be able
to add a record to the mainform as soon as a user enters data in the subform.

I've tried using the before insert even on the subform and Access doesn't
like it telling me that I can't add a record on the many side of the
relationship without first adding a record on the one side of the
relationship.

I'm really at a loss here. Any questions comments or ideas are welcome.

Thank You

 

[Rick Brandt]

Didn't get any help with my first post so I'll try again. I need to
be able to add a record to the mainform as soon as a user enters data
in the subform.

I've tried using the before insert even on the subform and Access
doesn't like it telling me that I can't add a record on the many side
of the relationship without first adding a record on the one side of
the relationship.

I'm really at a loss here. Any questions comments or ideas are
welcome.

You cannot do what you are attempting. The parent record must exist FIRST.

Is there a reason you want to do it in reverse order?

 

[Daryl Mhoon]

Yes there is! One form is frmPayments, the second is subfrmPaymentMethods.
1 to many relationship. One payment can have multiple methods, (cash, check,
credit). Each payment has a date and a memo field (Balance due no later than
7/15/05 etc...) Each method has an amount pmntMethod and its own memo field
(3rd party check #1234).

Because the default value of the PmntDate field is set to Date(), the user
wants to start adding records to the subform before the main form. I've
tried setting the PmntDate and adding a record to frmPayments on the
BeforeInsert event of subfrmPaymentMethods. Didn't work...

Thank you VERY MUCH for responding. There has to be a way I can do this. I
could force the user to enter the date before entering the methods, but that
seems a waste of keystrokes when most payments will be processed the day they
are paid.

Is my logic twisted? Any suggestions?

 

[Daryl Mhoon]

OK I can forsee another question. "Why don't you just put all that info in
one table?"

I'm in property management. Tenants can pay their rent in very unorthodox
ways. I have one guy that gets paid twice a month. He purchases a money
order with his 2nd paycheck so that he won't spend it on something else.
Then he signs over his 1st paycheck of the month and pays the rest with cash
and coin. I have some girls from France that paid six month's ahead with
traveler's checks. Then I have roomates that pay with two checks.

(I had one fella rob a 7-11 then pay his rent in cash. He was arrested an
hour later in his living room eating stolen potatoe chips and watching porn.
Fortunately I was able to keep the money.)

Then of course some checks bounce. If I can look up all checks and memos for
each check it's easier to reverse the payment and create an NSF charge.

I've thought long and hard on the structure of this database and I know what
I need. Unfortunately I'm learning VBA from scratch.

 

[Rick Brandt]

Yes there is! One form is frmPayments, the second is
subfrmPaymentMethods. 1 to many relationship. One payment can have
multiple methods, (cash, check, credit). Each payment has a date and
a memo field (Balance due no later than 7/15/05 etc...) Each method
has an amount pmntMethod and its own memo field (3rd party check
#1234).

Because the default value of the PmntDate field is set to Date(), the
user wants to start adding records to the subform before the main
form. I've tried setting the PmntDate and adding a record to
frmPayments on the BeforeInsert event of subfrmPaymentMethods.
Didn't work...

Thank you VERY MUCH for responding. There has to be a way I can do
this. I could force the user to enter the date before entering the
methods, but that seems a waste of keystrokes when most payments will
be processed the day they are paid.

Is my logic twisted? Any suggestions?

Just give them a button to press that automatically populates the PmntDate field
instead of using a default value and then have them press that before they try
to enter sub-records.

 

[Rick Brandt]

OK I can forsee another question. "Why don't you just put all that
info in one table?"

No, that is a bad idea given your description.

 

[Daryl Mhoon]

I was trying to avoid buttons...

I really don't want to add a child before I add the parent. I want Access to
realize when the user attempts to enter the subform then generate a parent
record with the default info then allow the user to continue entering info on
the subform.

Can I use code to add a recordset on BeforeInsert?

Can I place an unbound subform on the main form and place a "Process Payment"
button that will add a recordset to both main and sub form? If so do I have
to Change DataEntry for the form to "No?"

I'm SO sorry I cannot ask more intelligent questions. I've done some
incredible things with Access, since I've subscribed to this forum I've done
much more. I'm not that stupid, I'm just hard headed and inexperienced.

Thanx,

 

[Daryl Mhoon]

Rick,

I just had a fabulous idea involving a control button. It's gonna take some
time (have I mentioned how inexperience I am..?) Please check back on this
post and I'll let you know how it goes!

It's amazing how a little bit of outside input can help!!! Sometimes you have
to step back and take a deep breath.

DM

 

[tina]

you can "trick" Access into adding a record in the main form when you
*begin* adding a record in the subform. first, remove the DefaultValue
setting in the date control in the main form. then add the following code in
the *subform* BeforeInsert event, as

Private Sub Form_BeforeInsert(Cancel As Integer)

With Me.Parent
If .NewRecord Then
.MyDateField = Date
.Dirty = False
End If
End With

End Sub

substitute the correct name for your date control, of course.
i did see your post where you said you tried this and it didn't work for
you. i'm guessing you tried some kind of SaveRecord command, which doesn't
work. i've tested the above code, and so has a user who posted in May asking
for a solution to essentially the same issue - it works for both of us.

note, if your main form record's primary key is generated programmatically
(rather than being an Autonumber), you'll have to include that code in the
If statement, *before* the line ".Dirty = False".

hth

 

[Daryl Mhoon via AccessMonster.com]

Thanks Tina,

Worked beautifully! I had the feeling I was using the right event. I just
didn't have the right code.

Rick,

The button I worked on last night worked perfect. I set the subform.
AllowAdditions to False, added a button called "ctlToday" that would update
the date field, allow additions to the subform then dissapear when clicked.


I prefer to use a few buttons as possible. Users see buttons and they start
playing with their mouse. When they keep their hands on the keyboard they
are much more effecient.

Thanks to everybody. I've been fumbling around with that delimna for too
long. I can't express how happy I am for having found this website...

Daryl

 

[tina]

you're welcome