Entry of half my form not working

[garyj]

I recognize that this is probably not a VBA question, but I am sure it is an easy answer. I don't know how to search for it, so am lost.

I have a subform built on two tables. The tables are joined such that the tblBasic gives the normal information required, and tblOther is only needed in about 5% of the times. If I joined them as one table, I know I would have no problem making it work... but then do I waste space on empty cells?

The crazy thing is that I just had it working in the form, but realized my data should be part of the subform, and moved it there. So now the behaviour is as follows.

I have a record with the first half of the subform filled out with data. I try to add something to any of the fields based on tblOther, and it won't accept an entry. The fields are all enabled and not locked. The subform is open to edits of course, because I can edit fields from tblBasic.

I tried to start a new record, and started with data in the second table... it worked. I was able to enter data in fields from both tables.
I tried in the other order, entering data in tblBasic fields first, and it still allowed me to enter data in the other fields.
The only issue is when I try to enter data in the tblOther fields on an existing record. No record exists in tblOther and for some reason an entry doesn't create one.

Ideas?


Relationship is based on an index field with showing all records from tblBasic and only those from tblOther where the index is equal.

Gary

[garyj]

Oops... I keep answering my own problems. Anyway, in case it helps someone else...

I had the wrong ID selected for the join. I found it by entering the data into a table instead, and then checking the subform to find that that data now filled every record on that side of the form. The reason was that it was using a join field that had previously connected it to main form, making it show for every record in the subform.

I fixed the relationship: have to joining the tblOther to tblBasic using the main key ID in tblBasic. This creates a one-to-one relationship. But of course the query behind the form is also then wrong, and needs a fix. It works fine now.

Gary

[OBP]

:yes Programming often works like that, by explaining a problem to someone else it quite often triggers you to look in the right place.