Problem with subform in Access 2007 - works fine in Access 2003

[CinBar]

I have a subform linked to the main form with a bidder number field. In the
subform, I added an LS header, with the 'Live or Silent' field in that header
so that the form would list all Live records first under a Live heading, and
then the Silent records under a Silent heading. The subform works perfectly
by itself, but when I put in in the main form, I get the error "Access can't
find the field 'Live or Silent' referred to in your expression" repeated
multiple times (I'm guessing for each matching record). All the while I'm
clicking Ok, the form appears correctly in the background, but when I click
the final OK, all data disappears in the subform. If I remove the header, or
leave the header but move the Live or Silent field into the detail section,
it works perfectly, but I don't want Live or Silent repeated for each record.
I would also note that if I open the file in Access 2003, it works perfectly.
I tried converting the database to 2007 to see if that would correct the
problem, but it doesn't. Any ideas?

 

[NetworkTrade]

If all works well in Access2003 and it is not working in Access2007; then I
would recommend in 07 that you look at the queries that are the record
sources for the Form/Report and its Sub Form/Report.

1. Be sure that the key field is in the queries
2. Be sure that the key field is in the actual form/report/subform/report -
even if
not visible.

I found this to be the fix of several similar issues involving forms/reports
that had subforms/reports. Where it worked fine in 03 it did not work right
in 07; it is as if 07 is more strict.....I went thru and made sure that the
key field was in all queries and in all forms/reports - even when the field
was not needed by the human and often made invisible - - - but this
worked...try it. It was advice from one of the MVPs to me and worked....

 

[CinBar]

Thanks for your reply, but unfortunately, that didn't help. Here's what I did:

The subform is based on a table, not a query. The key field, which is the
item# field, was in the subform.

The main form is basically just a heading, showing the winning bidder number
derived from a query, a bit of text, and three subforms. The other two are
fine. The query that the main form is based on has only one field, the
winning bidder number. I made that a key, even though it didn't match the key
on the other table, but that didn't fix the problem, either. It would not be
possible, anyway that I can see, to add the item number to the query
collecting the winning bidder numbers as there is a one to many relationship
in most cases between the winning bidder number and the item numbers, and I
won't have than info until much later.

Any other ideas?

 

[NetworkTrade]

I do also remember being perplexed by an 07 behavior where the form/report
was sourced directly on the table. Out of desperation I made a query that
had all the fields and sourced the form/report on the query rather than the
table itself.....and this fixed it....

I see that you state that the subform is sourced right on a table and this
triggered that thought.

I do not know why it worked and asked on this forum but never got a reply.
Try it and if still isn't a fix I would recommend you begin a new thread that
maybe will attract new eyes....

 

[CinBar]

I created a query, pointed the subreport to that query and it now works fine.
Thanks so much for the suggestion. Is it just me, or does it seem like this
is an issue with Access 2007 rather than "the correct method" of doing things?

 

[NetworkTrade]

glad it worked.

yes the fact that a form/report must always source to a query and never
directly onto a table surely seems like a thing that should be better
explained....not sure if it is a bug or if there is some rationale toward it
that I don't yet understand...but it is a major change.

also you will find in 07 that putting a Close Form button on a form will
often generate an error because it now has code attempting to manage the
form's dirty state where many times it is inappropriate....