Fields on a form driving me nuts.. Why can't I type into them?!

[thebatfink]

Ok.. So this is a form I am using for inputting new records.

We have 3 main tables that are being updated from this form (I will simplfy this as much as possible):

Table1.Product (PK - Text)
Table1.ProductDetail1
Table1.ProductDetail2

Table2.Process (PK - Text)
Table2.ProcessDetail1
Table2.ProcessDetail2

Table3.QualityPK (PK - Autonumber)
Table3.Quality
Table3.QualityDetail1
Table3.QualityDetail2

Fields - Table1.Product (PK - Text) + Table2.Process (PK - Text) + Table3.Quality - all contain exactly the same value for each record.
No tables have any relationships defined at the table level.

Now before you freak out and tell me that breaks every rule of design and doesn't even seem logical in the slightest.. I know but I have inherited this and whilst the roadmap is to rebuild from scratch, I can not invest the time into it right now. It simply isn't my choice to make. I have to try and work around this issue as is if possible.

On my form I have a tab control. Each of the three tables above, has its fields input on a single tab each.

The RecordSource for the form -

Select
[Table1].*, [Table2].[Process], [Table2].[ProcessDetail1], [Table2].[ProcessDetail2], [Table3].[Quality], [Table3].[QualityDetail1], [Table3].[QualityDetail2]
FROM
([Table1]
LEFT JOIN
[Table2] ON [Table1].[Product]=[Table2].[Process])
LEFT JOIN
[Table3] ON [Table1].[Product]=[Table3].[Quality]
;

So my issue.. out of the three fields which the joins are made on, I can not type into the fields [Table1].[Product] or [Table3].[Quality]? I am forced to type into the field for [Table2].[Process] and then the other two fields on the form autopopulate with what is typed in.

What I am trying to make it do is allow me to type into [Table1].[Product] field on the form and its proving impossible!! I'm also now very curious to understand what is controlling this behaviour.
I have a similar form using different tables and its working the way I want (although you have to type the value into all three fields, not just one) and the SQL looks comparable. I've also tried to compare as many of the fields and forms property values as I can to see if theres some random setting different between these two forms and the also look identical.

So what is controlling this? Any help with a solution or a better understanding of this I would be forever grateful for!

Thanks!

 

[jdraw]

Can you post a dumbed down version of your database in mdb format? (no personal or confidential data)
Do you have a relationship diagram (Tools->Relationships)

 

[thebatfink]

Hey thanks for the reply.

Its difficult because its a split front end / back end with a mdw file securing the front end and the mdw / backend is on the network.

There are 0 relationships set on the tables. Its as un-normalised as you can possibly imagine and it would seem many illogical design choices got made when it was created.

I was hoping there would be a simple answer as to what mechanisms would prevent typing into an enabled bound text box. As its affecting the joining fields I'm assuming its got something to do with the relationships, albiet the lack of them being defined in the backend, or they are being incorrectly joined in the recordsource query of the form and Access doesn't like it. The form isn't even a main form with subforms for each tables data. Its one form handling the updating of 3 tables and the displaying of 2 more tables data (the all have a relationship through either their primary keys or with a foreign key, so the relationship can be defined in a query).

What I have just noticed is that Table3 actually has a relationship of sorts to 2 other tables. Each individual table when looked at individually is a 1 to 1 relationship. But there are duplicates because these 3 tables have no relationship between them so god knows how the queries are working. They must just return the first found match <-- Its hard to describe :/

I'm beginning to feel like I want to just give up trying to administor it. Trying to battle the poor design is presenting me problems just outside my ability.

 

[jdraw]

It's very difficult to advise anything in your situation. You know your situation and environment better than any of the readers of your post. If this is an important database then some design issues (as you have noticed) should be considered. But you'll likely need some sort of management support for whatever decision is taken.

I like data models and design. They can help with communications, design changes etc. but it seems you have little to work with unfortunately.

 

[boblarson]

The problem is likely trying to include 3 tables in a single updateable recordsource. Check this out:
http://allenbrowne.com/ser-61.html
and then you CAN try this, but it isn't the best solution. You can go into the query in design view, right click on the gray area where the tables show up and select PROPERTIES. Then change the Recordset Type from DYNASET to DYNASET(Inconsistent Updates).