Saving one field in a form to multiple related tables, et al. 1

[MSWhat]

I am working on creating an Access database, with a form based on two tables. I have had no prior experience with Access until I began working on this project. I have three questions, in order of importance:

1. My two tables--Bank Account info & Master List--are joined in a one-to-one relationship by the field "Transaction Name." (Master List containing hte PK, Bank Account Info containing the FK.) I have the relationship set to enforce referential integrity, and to cascade update all modified and deleted records of the PK. I've made it an inner-join, including only those data where the transaction name is the same for both tables (though it may make more sense for me to include ALL those of Master List and only the matching ones from Bank Account?! Don't really know.)

With that background, my question: how do I make it so that the transaction name text field on my form not only saves to the Master List Table, but also saves to the Bank Account table, so that if no info is entered on the form whcih is linked to the Bank Account table, it will at least save the transaction name to both tables, and that way it will show up on the form the next time I open it! Currently the only way to have it save to both tables or to make it show up in the form once I close out of it is to enter data in the bank account fields--but my clients won't always be doing that on the first time that they enter data.

I tried an afterupdate macro of SetValue from the form field "Transaction Name" as the expression, and the item being "Tables![Bank Account Info]![Transaction Name]" but Access was unable to identify the field.

2. I know that each table can only contain 255 columns, but it is my understanding that the form is not limited to this. Now, whenever I try to add one more field to a table, it allows me to. However, when I then try to open the form, an error message pops up, telling me that it there are too many fields to open. This doesn't make sense to me since, while I've addeda new column to the table, I haven't added a new field to the form, with the new column as its control source. Perhaps this has something to do with the fact that the Record Source for my form is a query which contains both tables. I'm not even sure if that's the proper way to have a form be based off of two tables. But that's just a guess. In any case, I definitely need to be able to add more fields, so this is a serious problem.
>>>The SQL statement of my query, Conglomerate, which is the recordsource of my form is:
SELECT [Master List].*, [Bank Account Info].*
FROM [Master List] INNER JOIN [Bank Account Info] ON [Master List].[Transaction Name] = [Bank Account Info].[Transaction Name];

I tried compacting the database (several times, in fact), but that was absolutely no help.

3.Finally (and thanks for bearing wiht me), before I expanded into a second table, I had a combo box at the top of the page to find values based off of what they began to type in. This served as a really easy way for my clients to toggle between the hundreds of records without having to scroll through wiht their mouse wheel or do a Ctrl>F. However, since I've linked tables, I haven't been able to set up this combo box. Whenever I do, the list comes up in the combo box when I view it in form view, however whenever I select a record form the list, instead of taking me to that record an error message reading "Run-time error 3345: Unknown or Invalid Field Reference [MasteList.Transaction Name] pops up.

I'm very confused, very delayed, and in desparate need of help. Thanks to anyone who can shed some insight on my woes!

 

[MSWhat]

COMPLETELY ignore question 1. A workaround that works (though is definitely a back-door trick) is to just set a default value of " " for one of the fields in the form which links to the Bank Account Info table. In so doing it creates a data value (of " ") in the Bank Account table and as such, saves to both tables and shows up in the form!

 

[randy700]

Why are you posting the same question multiple times. You should really take a closer look at the link PHV provided in one of your other posts on this same subject (question #2).


Randy

 

[MSWhat]

Sorry for the repeat posts. I, after posting, discovered that it was taboo to post multiple, relatively unrelated questions all in one post (for archiving purposes) so I bifurcated it all.

If the answer to question 2 is contained in PHV's link, I couldn't find it. Do you have any clue?

Thanks again, and I do apologize for the repeats.

 

[randy700]

I believe PHV was suggesting that your database is not normalized. I personally have never seen a table that required anywhere near 255 columns.

I strongly suggest you re-read the link he provided.


Randy

 

[MSWhat]

Hey, Randy--

I re-read the link PHV posted and still am lost and confused. Sorry for the ignorance--I'm not too familiar with Access yet--but to my understanding, my tables are 3NF normalized. Every column is completely atomic (indeed that's why I have so many columns!), each column is entirely linked and dependent on the primary key of the table, and they are all mutually indpendent (except for the fact that there are 2 calculated fields which are populated by a SetValue macro from my form). So, to that extent, the fields are normalized but I still can't add a column to a table.

I think I was unclear in my post. I do not have a table with 255 columns. I have two tables--one with about 150, and the other with about 100. So in aggregate, there's around 250. However, I cannot now add a column to either table without disabling my form (it says "Too many fields defined). Though it will let me save the column to the table without the error message, it just won't let me open the form then.

Again, really sorry to be so confused, and I appreciate your help.

 

[missinglinq]

I suspect the problem comes down to the

one with about 150, and the other with about 100

What is the exact total number of fields? If you have fields from 2 tables in your form, you have a Query as you RecordSource, and while Access forms don't have a limit on fields, per se, the number of fields in a recordset of a Query is 255.


This answer, of course, only addresses the strictly technical portion of the problem; the questions raised about normalization of your data are still very valid and need to be addressed!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[MSWhat]

That's for the help, Missinglinq.

So you're saying that if my recordsource is a query, and the query is composed of two tables which, when added together, have more than 255 fields, I'm not able to use the form? If that's the case, then is there a work around?

If not, is what you're saying that there's absolutely no way to make a form based off of more than 255 fields?

Thanks again. I really appreciate it.

 

[randy700]

You cannot make a QUERY that consists of more than 255 fields. If you truly need that many fields in your form, you may be able to work around it by creating multiple queries and using subforms.

However, consider this.... with that many fields on one form, isn't it a little crowded and cumbersome for your users?


Randy

 

[MSWhat]

hmmmm.

Thanks, Randy. I appreciate you weighing in. The form contains multiple tabs, and all of the information simply has to be in there for my users.

I guess subforms and multiple queries it is, then. Thanks so much for your help.

 

[missinglinq]

That's my understanding, unless you want to got the unbound form route, and in my wildest dreams I can't imagine the programming involved in writing data from an unbound form with the kinds of nubers of controls you're speaking of!

I'd either use Randy's idea of creating multiple queries and using subforms, with each one placed on a tabbed page (which would solve the crowding problem he was speaking of) or would use a series of forms to enter dtat into the tables.




The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[MSWhat]

Thanks to all! I'm nearly there.