Updating Null Child Fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I'm a charity volunteer and am creating a database to keep track of
donations (payments). Among the many tables I have two called TDeposit (Key:
DepositID) and TPayment (Key: PaymentID), linked by a one to many
relationship. TPayment contains the field DepositID as the child.

The default value of TPayment.DepositID I have set to Null because until
payments are deposited, they cannot be related to a deposit record. There can
be one or many payments for each deposit.

I am having difficulty creating a macro to run once the deposits are made to
(1) create and populate a new record in TDeposit, and then (2) update the
value of TPayment.DepositID to equal the value of TDeposit.DepositID for all
of the relavent payment records to link them to the new donation.

I'm new to Access and teaching myself as I go along, but this is driving me
nuts. I'd be very grateful for anyone's help.
Thanks,
Mark
 
I am having difficulty creating a macro to run once the deposits are made to
(1) create and populate a new record in TDeposit, and then (2) update the
value of TPayment.DepositID to equal the value of TDeposit.DepositID for all
of the relavent payment records to link them to the new donation.
Click to expand...

I'd suggest that you're going about this backward! Normally, one would
create the "one" side record *first*, and then use a Form (based on
the Deposits table) with a Subform (based on TPayment) to enter the
payments. How do you determine which payment is "relevant"? Might it
not be easier to create the deposit record first (even though you
haven't yet taken the envelope to the bank), and add the payments
directly?

IF not... then I'dd suggest using an Update query, selecting the
"relevant" payment records (somehow, I don't know your data structure
and cannot suggest how) as criteria and updating the DepositID. A
Macro could be used to execute this query if you desire.

John W. Vinson[MVP]
(no longer chatting for now)
 
MarkC said:
Hi,
I'm a charity volunteer and am creating a database to keep track of
donations (payments). Among the many tables I have two called TDeposit (Key:
DepositID) and TPayment (Key: PaymentID), linked by a one to many
relationship. TPayment contains the field DepositID as the child.

The default value of TPayment.DepositID I have set to Null because until
payments are deposited, they cannot be related to a deposit record. There can
be one or many payments for each deposit.

I am having difficulty creating a macro to run once the deposits are made to
(1) create and populate a new record in TDeposit, and then (2) update the
value of TPayment.DepositID to equal the value of TDeposit.DepositID for all
of the relavent payment records to link them to the new donation.

I'm new to Access and teaching myself as I go along, but this is driving me
nuts. I'd be very grateful for anyone's help.
Thanks,
Mark
Click to expand...

Hi,

You seem to be doing things in reverse order here. It is usual to create a
parent record (Deposit), and then link child records to it (Payments) ..
hence the names .. parents come first.

It really doesn't matter that the payments haven't been deposited literally
... they make up the deposit so should be linked from the beginning of the
operation.

Making the ID field Null is going to be a complete nightmare. Null equates
to "Unknown", and can be thought of as mean just that. If you try to create
three deposits, for various people, you're going to have three sets of
payments with Null IDs, so you won't be able to work out which is which.

If you redesign the process with, say, an autonumber as the deposit ID ..
maybe include a "Deposited Date" to show when the actual deposit occurred ..
and link any payments to that deposit ID, you'll find the whole thing much
easier.

HTH

MFK.
 
Your replies have given me an idea that I might default all new payments to
a dummy deposit record, and then update the relationship to new deposit
records as and when the deposit happens using an update query. Do you think
this might work and how might I go about it? I'm worried that referential
integrity might cause all records to be updated, not just the ones I've
selected according to their date?
Click to expand...

I wouldn't call it a "dummy" record - it's a valid, real, deposit
action - just one which hasn't happened yet! I think you have a good
idea, but should just take it a bit further.

You could use a Form based on Deposits, with the deposit date field
*optional*; new payments would be entered on a Subform of this form.
When you in fact perform the deposit, all that is needed is to enter
the deposit date - *no update query needed whatsoever!* When you in
fact make a deposit, you'ld just move to a new record on the mainform
for the next deposit.

Referential integrity will NEVER "cause records to be updated". RI
*prevents* inappropriate data from being entered; it does not and
cannot enter data for you. It's a constraint, not an automagical data
entry robot! <g>


John W. Vinson[MVP]
(no longer chatting for now)
 
Thanks, John. I'll give this a try.

Re. your last point, what I meant was that the cascade updates option which
can be set when when opting to enforce RI might cause all my records to be
updated when in fact I only wanted a subset to be altered.

Regards,
Mark
 
Thanks, John. I'll give this a try.

Re. your last point, what I meant was that the cascade updates option which
can be set when when opting to enforce RI might cause all my records to be
updated when in fact I only wanted a subset to be altered.
Click to expand...

This is one good reason why I rarely use Cascade Updates, and try to
think through the consequences when I do!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top