Intermittently subform doesn't show..

[TCS222]

I have 3 related tables in a back end database (Access 2003). Each booking can have several members. A member can also have more than one bookings. So have a members table, booking table, and member_booking table.



The front end is copied to each user's desktop. Several users use a data entry form to add new bookings.

The booking form is data entry but has a sub-form to enable them to add multiple members to the booking. The subform is based on a query which has fields from members and member_booking. The subform has a combo box so they can choose the members to add. Also a button to open a form to add members to the database if they are not on the list.

It all works find but intermittently, some users open the form to add a new booking and the subform is just empty. No controls whatsoever. Then the next day when they open it, it opens fine.

I'm just so lost with this! Does anyone have any ideas of what I can check. Record locks is No locks on all forms.

Any help would be just great - thank you...

[khhess]

Sound like a missing relation,
If you want, send me the database without data, will take a look

[TCS222]

Many thanks for taking a look. That would be great.
I've attached the front ent.
I've also attached a picture of the relationships in the back end.
The main form is frm_report_req - you'll see the subform that holds the client info. at the bottom of the form on a tabbed control.

You're a star - thanks.

[June7]

Don't need data but need data structure. The tables are all linked. These means we can't test the forms without creating tables. Provide the backend (remove confidential data).

Your post talks about 'members' and 'bookings', however, the db is for 'clients' and 'requests'.

[Missinglinq]

The Back End is very likely to be the heart of the problem that you've described! Controls don't appear in Form View when three conditions exist at the same time:

• The Form is Bound to a Table or Query
• There are No Records in the underlying Recordset or the Recordset cannot be accessed
• The Form cannot have New Records added

The reasons that a Bound Form cannot have New Records added include:

1. AllowAdditions for the Form is set to No
2. The underlying Query the Form is based on is Read Only
3. User doesn't have Read and/or Write Permission for the Folder where the Data resides or there is a connection problem with the network.
4. Folder the File resides in (in versions 2007/2010) not having been declared as 'Trusted'
5. Form's Recordset Type is set to Snapshot
6. All Controls on the Form being Locked or Locked and Disabled.

Given the on-again-off-again nature of your problem, and the fact that this is a split app, it is likely that #1, above, or the second half of #3 is where your problem originates. Assuming that the Records for the Main Form are present, and hence accessible, and Records for the Main Form and Subform are in the same location, the former is likely to be the culprit.

Linq ;0)>

[TCS222]

I've attached the back end with no data in it. If anyone has the time to look and spot any immediate issues that would be great. I'm new to code and have copied some off forums to achieve what i've needed it to...perhaps thats it? Thanks for any help.

[June7]

Forms that are bound to unfiltered, unsorted, unjoined queries could just bind directly to tables.

The RecordSource for the subform is joining tables with INNER join, needs to be RIGHT - 'Include all records from tbl_request_client ...' Then set the controls bound to tbl_client fields as Locked Yes, TabStop No.

You have fields in tbl_request_client seems to me should be in tbl_client as they are info about the client, not the request, i.e., ClientHealth, PreferredProvider, AttitudeToRisk. Why do you even need the first two items?

(Sorry, I should have been able to identify these items without the backend, guess I was still waking up.)

BTW, advise no spaces in field names, better would be ClientHealth or Client_Health.

[TCS222]

Thank you for your comments. I hope the right join between the client and request_client will fix the issue. Time will tell!! I very much appreciate the help and advice.