creating parent record so child can link

  • Thread starter Thread starter Roland Alden
  • Start date Start date
R

Roland Alden

I have the following table structure in a database:

person table: myuid, some other data about the person

account table: myuid, uid of a person, credit-limit

credit card table: myuid, uid of an account, card number, other details

On top of this data I have a form/subform/subsubform situation as follows:

form is the base form for a person

subform is an account record which contains another datasheet subform for
rows of credit card records.

the subforms are linked up through the top level as you might expect.

When the user is filling out a form for a new person they visit fields in
the person form which cause a new person record to be created. When they go
to enter a credit card number for the first time there is no record in the
account table yet to provide a uid for linkage to the row that is about to
be created in the credit card table.

Right now, to solve this problem I have some code in a click handler for the
credit card number field. The code looks something like this:

Private Sub card_number_Click()
If Me.Parent.NewRecord Then
Me.Parent.[credit-limit].Value = 1000
Me.Parent.Recordset.Save
End If
End Sub

This seems to force the creation of a record in the account table such that
the linkage will work for the newly created card table row. It seemed as if
it was necessary to "dirty" the "pro forma" record by assigning a value to
credit-limit. Just doing the Recordset.Save was insufficient.

My question is, there has to be a better way, right? At best, if this code
is necessary it should be moved somewhere other than a click handler for one
field. I assume this is not an entirely uncommon problem and there is some
Access/VB idiom for such situations. In particular, if the very first thing
a person does when confronted with a blank form is click in the credit card
number box then the problem exists up the chain another level (and right now
bad things happen). I understand why, but don't know what the best way is of
dealing with such cases.
 
Hi Roland,

It's not clear whether you have a 1:1 or 1:M relationship between
persons and accounts. If 1:1, either avoid the problem by moving the
credit limit field into the Person record or write code that creates the
Account record as soon as the Person record is created.

If 1:M, set things up so that users are required to set up at least one
Account as soon as they finish creating a new Person record; until then,
the credit card subform should be disabled.

I have the following table structure in a database:

person table: myuid, some other data about the person

account table: myuid, uid of a person, credit-limit

credit card table: myuid, uid of an account, card number, other details

On top of this data I have a form/subform/subsubform situation as follows:

form is the base form for a person

subform is an account record which contains another datasheet subform for
rows of credit card records.

the subforms are linked up through the top level as you might expect.

When the user is filling out a form for a new person they visit fields in
the person form which cause a new person record to be created. When they go
to enter a credit card number for the first time there is no record in the
account table yet to provide a uid for linkage to the row that is about to
be created in the credit card table.

Right now, to solve this problem I have some code in a click handler for the
credit card number field. The code looks something like this:

Private Sub card_number_Click()
If Me.Parent.NewRecord Then
Me.Parent.[credit-limit].Value = 1000
Me.Parent.Recordset.Save
End If
End Sub

This seems to force the creation of a record in the account table such that
the linkage will work for the newly created card table row. It seemed as if
it was necessary to "dirty" the "pro forma" record by assigning a value to
credit-limit. Just doing the Recordset.Save was insufficient.

My question is, there has to be a better way, right? At best, if this code
is necessary it should be moved somewhere other than a click handler for one
field. I assume this is not an entirely uncommon problem and there is some
Access/VB idiom for such situations. In particular, if the very first thing
a person does when confronted with a blank form is click in the credit card
number box then the problem exists up the chain another level (and right now
bad things happen). I understand why, but don't know what the best way is of
dealing with such cases.
Click to expand...
 
It is 1:1 and yes, for all practical purposes I should create an account
record when a person record is created. That said, it is true that there
could be a large class of "persons" who are in the database but are not
customers (they could be vendors or friends-of, etc.) in which case they
would have no need for an account. Thus it would be nice to create the
account record only when truly necessary. But given that I should create it
before someone tries to create a credit card record the question remains,
where is the best place to do this?
 
I'd be inclined to tackle this along these lines:

1) Do away with the Account subform and put the Credit Card Details
subform directly on the main form, with its Visible property or Enabled
property set to False.

2) Put a textbox on the Person form to display the credit limit where
relevant (either by using an outer join in the query behind the form, or
by using a DLookup() call in the form's Current event.

3) Put a button on the Person form to open a pop-up form to create or
edit the Account record. Put code in the form's AfterUpdate event to
display (or enable) the Credit Card Details subform.

4) Put code in the Person form's Current event to display or hide the
Credit Card Details subform depending on whether the Person has an
Account.

By the way, if you're storing personal information and credit card
details, have you made certain that your database will comply with the
privacy and data protection laws in your jurisdiction?
 
Good ideas; thanks.
By the way, if you're storing personal information and credit card
details, have you made certain that your database will comply with the
privacy and data protection laws in your jurisdiction?
Click to expand...

If you mean "Have I been through the fully horror of Visa CISP and HIPPA?"
the answer is yes; it's very ugly :)
 
If you mean "Have I been through the fully horror of Visa CISP and HIPPA?"
the answer is yes; it's very ugly :)
Click to expand...

That's the kind of thing I had in mind. We seem to get quite a few
people posting here (more often in clinical than financial contexts,
admittedly) who are quite unaware of either the moral or the regulatory
aspects of privacy.
 
Back
Top