Problems with 2 Subforms

[hermit22]

Just as a warning, I might have difficulty explaining this situation. I picked up a client's database after it was already completed, when it just needed to be updated to reflect changing circumstances. Access is also not my best db/programming environment.

The main form, which is an employee information type form, has two subforms. When the main form goes into edit mode, it creates a copy of the main form, pointed only to the current record. When the main form comes back from edit mode, it creates a copy of one of the sub-forms and cycles through each record in it. My changes merely added extra checks to each record check in the subform. When these are done, it updates a control on the main form and closes both created forms.

When you go back into edit mode again, the second subform, which previously showed data, only shows #Name in each control. Additionally, the user begins to get a never-ending series of "Object or variable not set," usually timed at regular intervals, which appears to me to have something to do with Access refreshing data.

I'm kind of stuck. My changes are necessary, but they do not (as far as I can see) affect anything structurally. Basically, it performs checks of a similar nature to the ones already there.

I'd really, really appreciate any help. I hope I haven't confused anyone with this.

 

[willir]

Hermit22

First, for the form to be doing what you described strongly suggests that there is some coding on the form tied to certain events.

You need to review the code and decipher what it is doing. If the there are called modules (check the module object tab in Access), you will need to review this as well.

Look "behind" combo boxes, list boxes, buttons and form events to see what is happening.

Next, you need to learn the data. Hopefully, your predecessor used the database design tool to define relationships. Also, take a look at the way the tables are designed.

Then review what changes you are trying to make. It sounds to me when your are seeing "#Name" displayed in fields and/or subforms, either the link has been broken, or Access is trying to write the wrong type of data to the field.

This is the problem with a hone-grown custom application. The custom application provides more usefullness and functionality to the customer but becomes difficult to support when the "Mac truck" comes by.

Oh yea, did you create a backup??

Richard

 

[hermit22]

Richard, thanks for the response.

Without getting too much into the details of the clinet's database (since it contains sensitive information) here's what happens:

When clicking into edit mode, the program has traditionally made a copy of the main form to work in. Presumably, this also means that a copy of the two subforms are made as well.

When saving, the program cycles through each line item to calculate totals, and then updates a field on the main form with the total. I simply added an extra category field to each item, and provide a running total of each - which then get thrown into their own fields on the main form. Save is the only way to exit edit mode, so this calculation is done everytime you go into edit mode. After saving, if you go back into edit mode, the textboxes in the second subform, which until now have show accurate data, turn into #Name. In case that's confusing, here's what gets done:

1. Edit.
2. Save.
3. YOu're in regular mode, and the second subform has worked just fine up until this point.
4. Edit again - and now the second subform seems to disconnect from the data, or the mainform.

And, of course, this worked fine until I added the secondary running totals - in other words, nothing that should directly affect the second subform or any data connections.

I hope this is clearer. The only workaround I can see is editing the running totals on the main form in real time, ie. as each record is added and deleted. Which can be done, but the rationale for this weird Access quirk is bothering me. Can anyone help?

 

[willir]

hermit22

Based on what you have provided, the developer has set up a form or forms to be in query mode and edit mode. This is not the default setup of an Access form (where Access allows full edit access to the form). This is a good thing because the user has to consciously select the edit mode.

But this also means that there is some VBA code in the form.

(You have made a backup of the database - right, not the form)

You need to look at the properties for the form and for the field. Look at the forms in design mode. If the properties box is not visible, click on "View" and select "Properties".

Provided you have not started clicking here or there, the form depicts properties for the form. On the event tab, you will events for "Current Record", "Before Inset", "After Insert". Scroll through the events to see if any have been created. You will see an entry [Event Procedure]. If you click on the "..." icon to the right of the event entry, you will be placed in the VBA editor at the location of where the specific code has been create.

Also, look at the "Data" tab; specifically, the "Record Source". This is table or query used to populate the form.

Some properties of internest also include Allow edits, additions, deletions, data entry, recordset type. By manipulating a form, one can control how a form operates - data entry, edits, query only type of thing. If the same form is used, or different forms are used for query and edit will depend on the skills and style of the developer.

Having reviewed the properties for the form as a whole, click on the various fields and any command buttons. Look in the "Event" tab for any place where the developer may have added code.

It sounds like you have a subform where the link to the data is occasionally broken. Repeat the aforementioned looking specifically at the subform object (without having opened the main form -- you will be able to select this). Then look at the subform within the main form. You should see on the Data tab properties, Link Child and Link Master.

Since you have reviewed the source of the master and subform, you should realize how the two forms link to each other.

This seems to be one source of your problem. It is unfortunate that you can not see the values of the child and link master on the properties box.

But you can do this...
Typically, a subform will store the primary key of the main form as a foreign key. Moreover, this field is often made invisible - a property of the field. You can make the field visible by changing the property for the field using the property box (format tab).

Now for diagnosing the problem...
- You may have orphans. Child records without a parent. A data integrity issue.
- You may have duplicate records. A table was designed not to have unique records which causes another type of data integrity issue.
- You may have code that is errounously breaking the link, or changing the record source, etc.

If you are lucky, all you have to do is make sure / reset the property linking the master and subform.

One more thing...
From your description, it looks like you are using a continuous form. Although do-able, calculations, such as running totals on a continuous form are sometimes problematic. Access displays each record but loves to use the last, first or current record to set the calculation for all displayed records. It is easier to display a total on the main form.

Richard

 

[OhioSteve]

I just read this and I have to log out. I will offer my opinions tomorrow.

One quick word of advice: backup before doing any experimentation.

 

[OhioSteve]

Willr's advice is good. This is how I would approach the issue.

1) backup the database.

2) learn about the form.

3) attempt to change the form.

4) test the new form

5) If the testing works, you are done. If the testing does not work, return to step two.

_________________
On step two, here are some hints for learning about the form. I think that you know how to put a form into design view. To view an object's properties, choose properties from the view menu. You cen either view the properties for the whole form or for one object on the form.

At first it is hard to understand why you get the form's properties sometimes, and another object's properties at other times. Notice that when a form is in design view, the upper left corner of the design view window MAY have a black square in it. If the black square appears, you will get the properties for the whole form. If the black square does not appear, you will get the properties for the selected object on the form. You can toggle the black square.

Obviously, you want to focus on the "event" properties.

You also want to learn the data source for each form and subform. Make a diagram to help keep track of them.


That is enough advice to get you started.