Error when using subdatasheet/subform to display data

[jshaw1]

I'm working on creating an Access database that will be used to track projects for my company. Each project name ("Project Name") has an ID associated with it ("DSID", an autonumber), as well as a Group ID ("Group ID", a number) as some of the projects are related to one another; other data is present in the table but is irrelevant to this issue. The database also contains three main tables (the main project list; changes made to individual records - linked using "DSID"; and changes made to a group of records - linked using "Group ID").

In order to facilitate data entry, I've created a form where all information can be entered. I have all of the data from the main project list display, and then below it I have subforms for the individual and group changes. The idea is that changes can be entered into the subform and a button can be pushed to run an update query. When I attempt to open the form in Form View, I get the following error message and the subform for individual changes appears as just white:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

As a quick test to try to identify where problem lies, I tried creating a subdatasheet within the main project list table to display the individual changes associated with each record. I received the same error message as above.

I was wondering if anyone here could possibly help me out. The group change system works perfectly and isn't the cause of the problem (as far as I can tell). I've only been working with Access for the past month or so and while I've been able to work out most things, I'm not quite sure where to go from here.

[Micron]

First, see if you can open each form independently without error. 2nd, if a subform references a main form field via code or expression, this usually does not work because subforms load first. 3rd, it is usually a one form - one table relationship, unless you are prepared to base a form on a query with mixed tables - in which case you must design to prevent orphaned records, assuming that query is updatable in the first place.

other data is present in the table but is irrelevant to this issue.

Perhaps that is true, but your post suggests there may be a design issue with respect to projects and groups. I suspect group data should be in its own table with DSID as a foreign key (you have it the other way around) but can't be sure with the info given.
4th - did you link sub to main via child/master links or something else?
5th - disappearing subform controls can be caused by one or more incompatible settings - I believe allow additons/edits/updates are all no and there are no records. Fixing the expression error may fix that as well.

[jshaw1]

Thanks for the response!

1. Each form opens with no problem

2. I know that subforms load first, but what exactly do you mean by referencing a main form field?

3. Each form is only associated with one table: the main form is associated with the main list table, the individual change form is associated with a table for individual changes, and the group change form is associated with a table for group changes.

3.5 (the other data) The group table has the Group ID and other fields related to the changes. The main list also has a field where you can specify if a record is associated with a particular group (ex. DSID 5 could be within Group ID 00001). Two update queries are used to push the changes (the individual updater and the group updater) and those make the changes based on the relevant ID (the individual looks for specific DSIDs and only makes changes to records with that DSID; the group looks for specific Group IDs and makes changes to all records with that Group ID). I opted for this method because we need to be able to update a large group of items, while also maintaining the option to update individual records.

4. Yes, I used Child/Master links.

5. I can look into that. Where would I be able to find the "allow additions/edits/updates"? Property sheet? There's also currently only one record in the individual table for testing purposes, for reference.

[Micron]

1. this likely means the problem lies with an expression that involves a connection between one or more subforms, unless you're saying the error happens when you try to update a record. The message isn't very helpful either, as the cause can be as simple as a missing quote or parenthesis, making the expression something that you don't expect and are not seeing. If it's sql, try dumping it in a new query to see how it's handled.
2. e.g from a subform open or load event you try to refer to a main form field value or control.
3.5 - still seems backwards - if Project (the one) is main entity and Groups are children (the many) Project DSID should be in either a) groups table as a foreign key or b) ProjGroups linking table where ProjGroup has its own PK as well as DISID and GroupID as FK's. Otherwise, you need a field on the project table for every group (bad) or repeat all the project info, once for each group. IF a group is the parent (one group has many projects) then I missed that. If not, what I'm saying doesn't look right might be part of a deeper problem not yet exposed. It should have nothing to do with the error message. For that, my money is on incorrect syntax at the moment.
5. yes, property sheet. I know that if you research "ms access subform controls invisible (or similar) then you will find more. HOWEVER, at the risk of repeating myself as I so often do, I would expect no records if there's an expression raising the error. Stands to reason that if it can't be processed, you can't expect records. Whether or not that is a bad thing may depend on how you want your form to look - controls missing or not.

[jshaw1]

1. I agree that the error message isn't helpful at all. Not quite sure how I'd view any of this as SQL - suggestions?
2. I haven't touched any of the events for the subforms, so I'm assuming this probably isn't the issue.
5. Found this shortly after posting. They were all set to "Yes" and toggling them back and forth did nothing (in terms of resolving the issue).

3.5. Apologies for my lack of clarity, but yes, one group should be the parent. Each DSID refers to an individual project, but some of our projects are large-scale and it makes more sense for us to be tracking both the entire project as well as smaller aspects of it, hence the desire to group them together. This way if a change is made to an entire project we can reflect the changes across all of the records associated, but if a change is only made to a smaller section we can reflect the change on that record only.
a) But since there are multiple DSIDs for a singular GroupID, how would this work? I had originally thought of linking the group changes using the DSID only but wasn't quite sure how I could make one record with multiple DSIDs attach to each of the corresponding records with the different DSIDs (I hope that makes some sense).
b) To clarify, what you're suggesting is a PK for each individual record, as well as FKs to define which DSID and GroupID each record should be attached to?

[Micron]

1)after reviewing all, it's not clear if you know what expression the message is referring to. If you know but just can't see the problem in it it's possible that we may be able to spot an issue just by looking at it but I'm not real hopeful on that. If you don't and the form and some parts of it are based on a query or sql statement, see if you can run them as queries. You said the message displays on form opening, so some part of the form cannot resolve an expression before you even try to do anything else. Along with this sort of troubleshooting is the method whereby you continue to remove things (query fields, form controls, etc) until the message goes away. If the form is based on a query with calculations, I'd probably start there. OR you can post a compacted and zipped copy of your db.

With 3b yes. It might be good for you if you did post a db or at least a relationships pic. I now see that I had the situation backwards but your description sounds a wee bit like the data isn't normalized. That just might be me not really being able to decipher some of the terms and phrases you use. Without knowing the business and jargon there's probably not much good guidance I can give right now beyond speculating that for each single Project ID in a table there would be 1 group ID as a fk. If there are subs to a project, then I see them as being related to the projects table in the same fashion.

It may be more sensible to ensure your setup is correct, then worry about the error message, which in fact may be taken care of if table design changes are needed.

[jshaw1]

I managed to figure out the problem with a little bit of help from someone else. Thanks so much for all of your help though!

[Micron]

care to say what the fix was? It might help someone else in the future.