I'm using Access 2007 and have developed an application to aid a co-worker in his tasks. He's old-school, so the views he looks at have to resemble a spreadsheet or he gets lost pretty easily. And I'm transitioning him from what was basically an excel spreadsheet with 25 years of data, so it's not normallized.

In bits and pieces, I'm making the changes necessary to normalize the db.

Anyway, one compromise along the path to normalizing was to break things up a little bit more logically than they were before. Such as separating remarks into a separate, but related, table; since one record can have multiple remarks entered at different times in it's life cycle. (previously, he just kept putting everything in a single cell...quite a mess with 25+ years of data!). To keep the datasheet view, i'm using what I call a container form with subforms. Each subform is datasheet view, and the related details requery to show applicable data each time a record in the "main" subform is selected. I've attached an example of what I'm talking about, for clarity's sake. To see the operation I'm talking about, open "frmContainer".

This works pretty well for the most part, but there are two things I'm not happy about that I can't explain.
1) despite the code in the on_load event of the main subform that tells it to select the first record displayed, I still get the #NAME result in the detail subforms. Until another record is selected.
2) changing the recordsource for the detail subforms does work, depite the fact that it raises an error. I've "brute forced" it and gotten around this issue by using the dreaded "On Error Resume Next".

If anyone can help me figure this out, I'd really appreciate it!

Oh, I should add that this example is simplified. My actual db has multiple details subforms. If it was just the one, I'd just use a datasheet view with a subdatasheet and be done with it.

I think your not on the right path.
try this.

Ok, I think I see what you're doing.

Check me on this:

You're setting the value of an unbound hidden textbox on the container form. Then the details forms are linked to that textbox with a master/child field relationship, right?

Is there anything I missed?

Gotta say, I like this approach. Much cleaner, thanks!

You got it!
Anyway your approach is also correctly but you will notice the difference in speed.

This post has been edited by arnelgp: Mar 21 2012, 12:34 PM

Arnel,

Ran into a problem with the new approach. Using the reference to the unbound hidden control on the parent container form, I'm unable to add new records to the details subforms if there wasn't one there to begin with.

Been scratching my head on that for a day or two now.

Any thoughts?

Thanks much!

that's exactly the catch, you cannot add detail records first when there is no header record.
say for example you start recording the ins and out date of an employee.
you must first record all the information for that particular employee before you can record his in and out times, right.

in real world a children cannot be born without a parent.

I'm in agreement with arnelgp. I've found that getting my users accustomed to this idea has been somewhat difficult. They want to open a new Parts Purchase Order and enter items before they've filled out header information like vendor, work order no., customer, etc. Why they think you can create children first and then declare the parent later doesn't make sense to my logical brain.

then on that case you just create a Temporary number for your header and create the detail records. If the information on the header is available, then you just edit the temporary number.

This post has been edited by arnelgp: Mar 24 2012, 07:22 AM

You misunderstand me. The parent record exists already and I *still* can't add details unless a detail already exists for that parent.

Ofcourse you can, you set up tblNotes with field noteDate default value to Date() and field noteTime to Time().
On the note subform you notice there is an asterisk (*) mark on the record selector, that means "New record".
All you have to do is replaced the noteDate and the noteTime and fill up the Note field.
Then a new record will appear again (asterisk (*) mark).

Arnel, I understand that is what *should* be happening. But it's not. The details subforms for a new parent record are showing as completely blank. It will not let me add a new detail record with your method.

on the example_3.zip it is working.
if you have any db please, if you can, upload it in zip file.
i would like to resolve this issue once and for all.