Form & Subform Error

[blemons]

This is my first request for help, so forgive any newbie mistakes...

I'm working in Access 2016, and have built a database that, among other items, has two tables that are linked: tblClient and tblCaregiver. The primary key for tblClient is stxCaseNumber. The primary key for tblCaregiver is idsCaregiverID. In addition, there is a field stxCaseNumber in tblCaregiver. In the Relationships diagram, tblClient is joined to tblCaregiver at stxCaseNumber, in a one-to-many relationship.

I initially set up a main form to enter client data into, with a caregiver subform. Up till last week, every time I entered client data into the main form, then entered data into the caregiver subform, the entries were linked and working correctly. However, late last week, I tried entering a new client & caregiver, and instead got an error:

The LinkMasterFields property setting has produced this error: 'Expected: identifier or bracketed expression'

The error occurred after I entered the client data and had started typing in the caregiver subform. When I open the Property Sheet for the subform, the master field is the stxCaseNumber from the tblClient, while the child field is the stxCaseNumber from the tblCaregiver. To my knowledge, I've never manually edited those master/child field settings prior to this error, I had instead let the Form Wizard build the forms for me.

I did some Googling last week and tried to manually enter both the master and child fields, but got the same error. I have also tried: creating a new subform from scratch; deleting the existing fields in the subform and re-adding them; and creating a whole new main form with a new subform. All resulted in the same error.

To my knowledge, the only thing I did between the forms working correctly and now is clean up some queries that were designed wrong, and then redesign some reports. I haven't deleted any tables.

I would appreciate any help or suggestions people have...



Brett

 

[CJ_London]

I believe the error is usually generated when you have spaces in table and/or field names. However from your description, this is not the case, but do check the other controls and fields in the subform. In particular an event associated with a control with a space in the name e.g. 'my control' will have an underscore added for any event so you get 'private sub my_control_afterupdate'.

It is also possible the db is suffering from some corruption - is it split? have you tried compact and repair?

 

[JHB]

Else post your database with some sample data, (zip it) + a description how to reproduce the error.

 

[blemons]

I believe the error is usually generated when you have spaces in table and/or field names. However from your description, this is not the case, but do check the other controls and fields in the subform. In particular an event associated with a control with a space in the name e.g. 'my control' will have an underscore added for any event so you get 'private sub my_control_afterupdate'.

It is also possible the db is suffering from some corruption - is it split? have you tried compact and repair?

I did not have time today to check all fields to make sure there are no spaces, but I believe that to be the case. Hopefully tomorrow I can check. That said, I have run the compact & repair option with no success. The db is not split, that I know for sure.



Brett

 

[blemons]

Else post your database with some sample data, (zip it) + a description how to reproduce the error.

Assuming I can find the time to work on this tomorrow, I will post the db with some sample data and a description to reproduce the error.

 

[blemons]

Good morning all...

I double checked my db and it is not split. I ran the compact & repair and that did not resolve the error. There are no fields in the form or related tables that have spaces in their names; they are all either CamelCase or use underscores.

I exported my db into a new db, with only the following items:

1. tblClient
2. tblCaregiver
3. tblOffender
4. _MainForm
5. frmCaregiver_ComboSource
6. frmOffender_ComboSource

While this is not the full database, it is enough to reproduce the error. I have one sample client entered into tblClient. I did so by using the _MainForm object. I get the error as soon as I try to enter data into the Caregiver subform. It doesn't matter how much data I enter into the Client main-form, as soon as I select the subform and start typing, the error fires...

(As a reminder, the error is: The LinkMasterFields property setting has produced this error: 'Expected: identifier or bracketed expression' )

 

[JHB]

Something was corrupt in the _MainForm.
Open the WorkingForm.

 

[jdraw]

Start with a description of the "business" from the 30,000 foot level and gradually add some detail.
From that you can identify the entities and relationships and create a draft data model that you can test before building the physical database in Access. This will help you to understand the business and it will help readers understand the issues in context.

You have tables Caregiver Client and Offender, but only 2 tables in your relationships window. It also seems from a very quick review that you have Cases that has not been clearly dealt with in your tables/relationships.

You may get some insight and ideas from this link on Database Planning and Design.
Good luck with your project.

 

[blemons]

Something was corrupt in the _MainForm.
Open the WorkingForm.

Thanks very much, JHB. Were you able to tell what it was? I'd like to be able to list this thread as solved and so I know in the future what to do (or not do).

The WorkingForm object works perfectly, thank you again.




Brett

 

[JHB]

Thanks very much, JHB. Were you able to tell what it was? I'd like to be able to list this thread as solved and so I know in the future what to do (or not do).

You're welcome.
No sorry, I don't know what it was, it happens rarely, (but it happens).
When it happens, create a new form, open the old form in design view, mark all the controls in it and copy them, close the old form and paste the controls into the new form, it takes only a few seconds. Then you can't copy the whole form because the fault is copied also.

In the top of the thread you can mark it as Solved.