Help with Sub Forms...

  • Thread starter Thread starter Robbie Baquiran
  • Start date Start date
R

Robbie Baquiran

This one's bothering me - any suggestions will be appreciated.
Below I am trying to explain the table layout - I'm only identifying the
fields which relate to each other...

tblAccount:
intAID = Account ID

tblAddress:
intAdID = Address ID

utblAddress_Account:
intAID
intAdID

Ok so basically I have a main form - which is based off of tblAccount. Now
I am trying to add a subform of addresses related to the particular account.
Now since addresses and accounts are stored seperatly in their respective
tables, I am using utblAddress_Account to act as a sort of union. Since
accounts can be tied to many addresses.

So from a top down view of the form - I have an Account Form, then the Union
subform linked by intAID, then the Address subform linked by intAdID from
the Union subform. This works well if we are just going to change address
data that is already tied to an account. But if a new address need's to be
entered or added - I run into issues.

Since and Account ID already exists, the Union table get's intAID from the
link. But an address can't be added since there isn't an intAdID for it
yet...

Any ideas are greatly appreciated...
Thanks in Advance!
 
Wow, OK.

I don't know if I am tired or if this is confusing.

One question to start here - Do you really need to store addresses separately?

It almost seems like you are leaving out a couple nuggets of info here.

Just a simple approach to your problem seems like it would be:

tblAccount:
intAID = Account ID

tblAddress:
intAdID = Address ID
intAID = Account ID

You would relate the Address table to the Account table as one to many,
based on the the intAID field. Viola - you would have a functioning subform
record source.

Let me know what you think.

Seth
 
Thanks Seth - That would be an idea.. but the address table is also related
to other tables like payees, companies, and banks.

One major drawback my company was having storing address information like
that was for example... Bank XYZ would have a change of address - now the
data entry person will have to hunt down all accounts which are tied to Bank
XYZ and manually change all occurances of it. Which has proven ineffective
since it's prone to typos or accounts overlooked.

I myself have been programming web applications and have been able to
successfully use this table layout at other facilities - but since I am
required to do this via access, I'm running into road blocks I have never
come accross before.

Robbie
 
Robbie:

I thought this might be the case. I am glad to see you recognize good
normalization practices.

The three table approach is proper for your solution.

You have two or three issues going at once here and if you know how you want
to display the data on the Address subform this is actually pretty
straightforward.

What fields do you want to display on your subform? Do you have to be able
to edit an address on the subform or would you be open to having a button
that opens the address form and takes the user to the spedific address where
they can then edit the data? I ask this because I have learned over the
years if you leave editing access that open in this type of layout, you are
only a couple days away from getting the shaft from users. They will
'accidentally' press keys, delete things, type in garbage, fall asleep on
some random key. If it can happen, a user will find it.

Just thinking here . . .
If your subform is simply a combo box and a button. You could use the combo
box for the user to choose the Bank or Address and the button to the right of
the combo could be used to open the Address form to the address record found
in the combo box.

Then, in your main form (or your subform's header or footer) you could place
another button that allows users to open the Address form for entry of new
addresses. When the user closes the Address form you could have your Address
combo box requeried so the new addresses are present for selection
(otherwise, the new addresses will not show until you close and reopen you
form/subform). These things will take a little code, but nothing difficult.
I could share with you what I have done.

Let me know where you want to go from here.

Just for your info, after today I will not have access to a computer til
next week. So if I don't respond I am not ignoring you.

Seth
 
Back
Top