Proper Data Entry form/subform design when data goes to multiple tables.

[Wombat]

I thought I was being clever and created a seemless-looking form where data is being entered into multiple tables by placing subforms on the main form. Now I'm running into the issue where the "cancel" button doesn't work once focus enters a subform because the record has been saved.

I guess my question is whether or not this is a frustrating limitation for most people. If it's not, it must be my design that is flawed. I don't see any functional reason why I couldn't put more of my data into one table since it would always be 1-to-1 anyway, that would solve it. Or I could try to work on some kind of temporary table solution.



If a user creating a record is always going to fill in details that go into multiple tables in a 1-to-1 relationship, how do you handle it?

[Wombat]

An unbound form could also be a solution, but I feel like I'm just re-creating the functionality that Access already gives me, so maybe I'm doing something wrong.

[TG_W]

Consolidating is usually the best method. Remember the "KISS" rule - "Keep It Simple, Stupid". Why will most of the data need to go into multiple tables if one could suffice? If you need to view the data in multiple ways, why not query out the information to view it that way? If you HAVE to use multiple tables, why not create some update queries that will copy the information over to the other tables?

I have a table with 50+ columns that I do not need to see all at once, so I have multiple forms to view the groupings I need to update data, etc. Now, that is an extreme case, and I only have one table like that in the 4 or 5 databases I am currently working with, but it might be an option for you. I hope that helps some.

[Wombat]

I appreciate the reply. I ended up consolidating my two main tables and everything seems to be working smoothly.

When I was originally planning this one, I had trouble deciding how the normalization should work because my two main tables are so closely related, I couldn't decide which one of them some fields should go into. Now I don't have to worry about it. I'm still pretty new to this, I'm mostly an Excel guy, so I think sometimes I'm trying my hardest to make it more "sophisticated", when really I'm probably just making it more convoluted.

I'll try to stay out of my own way from now on.

[TG_W]

I was the same way when I made the transition from Excel. It's tough to not want to overcomplicate a database because there are so many cool, new tools to use. You'll get the hang of it shortly.

[xeifohShoh2]

I am experiencing this same problem. I have a linked table in my database to get data from another database. I have to use a query to convert at "PartNumber" text field to a "PartNumber" number field. When I use the "PartDescription" field from this query on a data entry form, all of my records are displayed and I am unable to add new records. If I use the "PartDescription" from a table copied from the linked table instead of the query, then I am able to use data entry mode. I really need to use the "PartDescription" from my query, so that it is updated along with any changes to the original database.

Did you find a solution to your problem? I believe we are having the same issues and I hope I explained my database well enough. If you have any queryf or data Conversion too then you must read this article. I have found this while finding some good stuff for my Data Entry issues.


regards
---------