Subform Detail Missing

[geraldcor]

Hello helpful friends,

I have a main form with two subforms, both of which are continuous forms bound to different queries. One subform works as it should. The symptoms of the other subform are that when I create a new record with the main form, the subform just shows the form header and not the detail section. The detail section is where the user adds which tests belong to this invoice number. It has worked for 5 years but now it decides to quit. I can't think of any changes I have made recently so it is hard to track down.

Has anyone else had these symptoms? Older records show the detail section just fine (the detail has a combo box to open another form and then the subform reflects what the data that was entered in the form). I am at a major loss.

Greg

 

[RuralGuy]

What version of Access? Do you AllowEdits and AllowAdditions to your SubForm?

 

[geraldcor]

Access 2003 and yes to both of those.

 

[RuralGuy]

Any chance you have applied the Office SP3? If not then don't and wait for SP4. If you have then have you also applied the HotFix?
Description of the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007

 

[geraldcor]

I applied the hotfix and that didn't do it. I am pretty sure I installed SP3.

 

[RuralGuy]

About Access under Help should show you if SP3 is installed. If it is SP3 causing your problem, then the only other solution is to reinstall Access and bring the rev level up just short of SP3. Sorry.

 

[geraldcor]

It is SP2. I went into the backups and found a working copy. I imported that working copy into my current working copy and the problem shows up even though it was working with the same forms in an older version. Very bizarre. Any other thoughts?

 

[RuralGuy]

Can you change the SubForm to DataEntry as a test to see if you get the detail section back?

 

[RuralGuy]

There may also be some corruption. Have you imported everything into a fresh, empty db yet?

 

[geraldcor]

Yep. That didn't work either. I'm getting very nervous.

 

[RuralGuy]

Does the form act normal when you open it directly and not through the SubFormControl?

 

[geraldcor]

It displays normally. It displays all records for the query that the subform is bound to.

 

[RuralGuy]

Try making a copy of the form and deleting the offensive SubFormControl and create a new SubFormControl and see if the new SubFormControl works as you would expect.

 

[geraldcor]

That didn't work either. The link Master and Child fields are InvoiceNumber. The invoice number is blank when a new record is created and it is filled in by the user. This has never been a problem, but I wonder if the subform is having a problem making a new record in the associated table/query and is therefore not showing the detail section because it is confused. The other subform has the same Child Master links and it works fine so I don't know why this would be a sudden issue.

 

[geraldcor]

Ah, Suspicions lead to insights. I inspected the query, and I cannot add any records. There is no add record button on the record selectors and when I right click on a column, Add Record is grayed out. Curious. How did this happen and How do I change it? I tried rebuilding the query but I get the same symptoms. In the old copy that worked, the query acts as expected.

 

[RuralGuy]

It looks like you may have a non-updateable query. See if this link helps sort it out.

 

[geraldcor]

I don't think it is my query design because It still has problems when I import it into this database. There has to be something deeper that is making this query read only. The link didn't sort it out. I tried everything on the list and it all checks out. I am still mystified.

 

[geraldcor]

More info:
I tried adding one of the tables to a query. Didn't work. Took out some fields. Didn't work. Added a field (happened to be the PK) worked. Added another table, didn't work. I am going out of my mind here.

 

[RuralGuy]

How about posting the SQL for the query. Maybe we will see something.

 

[geraldcor]

Good idea. Here it is

SELECT DISTINCTROW tblTestsWithInvoices.ArbitraryIncrement, tblTestsWithInvoices.InvoiceNumber, tblTestsWithInvoices.TestID, tblTestsWithInvoices.seqInvoiceNumber, CheckIn.Contact, CheckIn.SampleRepresents1, CheckIn.CIQNumber
FROM CheckIn RIGHT JOIN tblTestsWithInvoices ON CheckIn.SampleID = tblTestsWithInvoices.TestID
ORDER BY tblTestsWithInvoices.TestID;

It seems pretty straight forward, but who am I to judge such a thing?