Help - subform creating unwanted record in other subform

[Don Do]

Ok, this is my 2nd ever post. 1st one was solved quickly. Thanks.

Now I'm stuck again. My mdb has a main form with 3 subforms. Each
tied to the succeeding by single autonumberID/foreign fields. Each of
the subforms is in continuous form format. The forms seems to work
well as far as viewing or adding records in each of the subforms,
except sbf2. After I have added records in sbf4, everything still
looks good. I can click around, in the various fields of each of the
subforms and the correct records appear in each of the joined
subforms.

But, the instant I click in the add record box of sbf2, a new record
appears in sbf4 and locks me up. The new record in sbf4 is dirty and
required fields are, of course, missing. I get the error message: -
2147352567: The value you entered isn't valid for this field.

When I click down in sbf4 to delete the dirty new record, I get the
message again that: "The field 'tbl4.Component' cannot contain a Null
value because the required property for this field is set to True.
Enter a value in the field."

[Component] is a required field. Now I can esc out of the sbf4 dirty
record, which deletes it, and go back to sbf2 and add the new record.
Again, all of the fields and subforms seem to work ok until I add
another new record in sbf4. I can add more records in sbf2, sbf3,
more records in sbf2 without problem. But when I add a record to
sbf4, then I can not add new records to sbf2 without going through the
error process.

What have I done?

Very gratefully,

Don

[Rich P]

Greetings,

Your problem comes from having multiple linked subforms. A trick you
can do to over come this is to use unbound subforms when using multiple
subforms and populate each subform using a user-defined function that
fires when you move to the next record (or previous record) where each
subform has its own datasource - these would be temp tables which get
filled and emptied as you move from record to record in the main form.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Don Do]

Thanks Rich, but ouch!

This sounds like a major do-over approach. The code that I have now
essentially copies the "customers" form with its 2 subforms in the
Northwind sample file. Except I added a 3rd subform. Each of which
has a one-to-many relationship with the prior form. I guess at some
point there is the straw that breaks the camel's back (or breaks
Access). But I never dreamed I had approached that level of
complexity.

I'll try studying how to implement your suggestion. But any further
elaboration to help nudge me into this direction would be appreciated.

Thanks again,

Don

[Don Do]

Rich,

Oops, sorry. Not "Customers" but "Customer Orders" form from
Northwind.

Also, I wasn't very specific about what I didn't understand about your
suggested approach. Are you saying to make 4 more tables that have
fields for all of the fields on my forms but no records, and then when
I search or otherwise select a specific level 1 record I copy these
records over into the temp tables, make changes or add new level 2 - 4
entries, and then copy these records back into my real tables? Would
my auto-number key fields that I use to hook up the tables still
work?

Guess I'm in over my head.

But I truly appreciate your taking the time to respond.

Don

[Rich P]

Hello again,

In answer to your questions - basically - yes. Create a table for each
subform. Say subform1 has 5 fields, subform2 has 7 fields, subform3 has
10 fields. The main form is based on tbl1. When you cycle through the
records in the mainform you will do something like this:

Functin SynchSubformTables()
Dim intID As Integer
Dim strSql As String
intID = txtID '--this is the current ID you are on in the
'--main form
strSql = "Delete * From tblSubform1"
DoCmd.Run strSql
strSql = "Delete * From tblSubform2"
DoCmd.Run strSql
strSql = "Delete * From tblSubform3"
DoCMd.Run strSql

DoEvents

strSql = Insert Into tblSubfrom1 Select t1.fld1, t1.fld2, t2.fld3,
t2.fld4 From tbl1 t1 Inner Join tblwhatever1 t2 On t1.ID = t2.ID And
t1.Name = t2.Name and t1.Date1 = t2.Date1 Where t1.ID = " & intID
DoCmd.RunSql strSql

strSql = Insert Into tblSubfrom1 Select t1.fld1, t1.fld2, t2.fld3,
t2.fld4 From tbl1 t1 Inner Join tblwhatever2 t2 On t1.ID = t2.ID And
t1.Name = t2.Name and t1.Date1 = t2.Date1 Where t1.ID = " & intID
DoCmd.RunSql strSql

strSql = Insert Into tblSubfrom1 Select t1.fld1, t1.fld2, t2.fld3,
t2.fld4 From tbl1 t1 Inner Join tblwhatever3 t2 On t1.ID = t2.ID And
t1.Name = t2.Name and t1.Date1 = t2.Date1 Where t1.ID = " & intID
DoCmd.RunSql strSql

subform1.Requery
Subform2.Requery
subform3.Requery
Me.Requery

End Function

Remember - the subforms are displaying detail data. For each ID in the
master table, you should only have a few detail rows (well, a few as in
less than 100 rows of detail per master row). This is how it is done.
Assuming you don't have a ton of detail rows per ID, each time you move
to another record in the main form - the subforms will update very
quickly - within milliseconds.

Call the function from a button that moves to the next or previous
record.

hth
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Don Do]

Wow! what a post!

I haven't had a chance to try yet, but if I can't follow those
suggestions, I better give up.

Again I thank you for your help. I hope to get in to this again
before the weekend.

Don