2 Subforms--Link the first Form to a specific field in the Second Form 1

[MattMeat]

Hello,

I was wondering if I could get help on this problem I've been trying to solve for a while.

I'm making a Service Order Entry Form for our Warehouse to use. I have a form with 2 subforms on it. The first subform is in datsheet view and contains about 8 fields. The last field in this form is called "notes". Due to the datasheet view used in this form, only part of the "notes" are visible.

I would like the Second Subform (which sits below the first) to contain and show the exact information from the "notes" field in the first subform, and only this information. Also, when I scroll through the datasheet Subform, the other Subform (below) should update and show the corresponding record's entire "notes" field.

I hope I have been clear enough.

Thanks,
Matt

 

[hsitz]

In the On Load (or maybe On Open?) event of the second subform, you might try setting its recordset to be the recordset of the first subform:

set me.recordset = [mainform]![subform1].form.recordset


If you do this, you may need to undo any linking between the second subform and a record in the mainform; that is, delete the parent and child fields for the second subform control. There's no need for them, since the second subform's record will simply be whatever record is current in the first subform. -- Herb

 

[hsitz]

Just tried it out and it works a little different than I thought. Here are the steps:

1. Make the second subform and place on it a textbox that is bound to the field you want, in your case "Notes".

2. Place it on the main form, but don't have any parent or child fields linking the two.

3. In the "On Current" event of the _Main Form_, put the following line of code:

set forms!mainform!subform2.form.recordset =
forms!mainform!subform1.form.recordset

That simple piece of code should do the trick. I now have a form that does exactly what you describe.

The rationale for the on current event of the main form: Making the two subforms' recordsets identical has to be done every time you move to a new record on the main form, not just when subform 2 is initially initially opened, because the first subform actually changes its recordset every time a new record is moved to on the main form. So you also have to reset the second subform's recordset every time you get a new record on the main form. -- Herb

 

[MattMeat]

Herb,

First of all, thanks for the persistent help.

Secondly, I'm not quite sure what to insert for the last half of this code. What do I use in place of "form" and "recordset"? The part I'm talking about is bold.

set forms!mainform!subform2.form.recordset =
forms!mainform!subform1.form.recordset


Thanks a lot Herb,
I've been at this problem for a couple days.
Matt

 

[hsitz]

Those should stay as they're written. The '.form' part directs Access to the properties of the form in the subform control, rather than to the properties of the subform control itself. The '.recordset' part just refers to the underlying recordset of the form. What you're doing is making the recordset used by the second subform identical to the recordset used by the first. You don't reference them by name anywhere, just by the generic reference to '.recordset'.

So in the line of code, the only things that will change are the references to the mainform and the two subforms, which are between brackets in the line below. Everything else should stay exactly as written:

set forms![mainform]![subform2].form.recordset =
forms![mainform]![subform1].form.recordset

 

[MattMeat]

Herb,

Thanks to your help, I feel like I'm really close to getting this done. I have done everything exactly as you said but I'm getting a error.
It says:

Run-time error '2465' Application-defined or object-defined error.

Here is how the my code looks:

Private Sub Form_Current()
'ScheduledCompletionDate.SetFocus
Set Forms![SSO]![NotesUpdateSubform].Form.Recordset =
Forms![SSO]![StepsForm].Form.Recordset

End Sub

I am also getting a compile error (Expected= expression)
Do you have any ideas on why this is happening?

Today's my birthday, so thanks for the present you are giving me. My boss will be happy too.

Thanks,
Matt

 

[hsitz]

No problem, today's a little bit of a slow day for me.

I think your problem is that you've entered the code on two lines. It should all be on one line; I just had it on two because it wouldn't fit on one.

That is the 'set ____.Recordset = _____.Recordset' statement should all be on one line; no break after the '='. Sorry, should've been more clear in my posts.

That, at least, would explain the 'expected = expression' error. Not sure about the other one, but hopefully it will go away when it's all on one line. Let me know. . .

Also, feel free to let me know if you have more problems on it. -- Herb

 

[MattMeat]

Hello again Herb,

You were exactly right. The expected=expression error was solved but the other is still ocurring when I load the main form. The error is the one I posted above:

Run-time error '2465' Application-defined or object-defined error.

Here's how my code looks as of now:

Private Sub Form_Current()
'ScheduledCompletionDate.SetFocus
Set Forms![SSO]![NotesUpdateSubform].Form.Recordset = Forms![SSO]![StepsForm].Form.Recordset (all on one line)
End Sub

Any ideas?

Thanks again,
Matt

 

[hsitz]

Hmm. Not sure. You do have this line of code the "On Current" event of the main form, right? I don't know what the problem is. If you want, you could email the database (or a subset with relevant tables and forms) to me and I'll take a look at it. Or I could email you a working prototype that I have. Email: hsitz@nwlink.com

 

[MattMeat]

Herb,

I sent you an email with the database included.

Thanks,
Matt

 

[hsitz]

I got your database in email. I have only Access 2000, which can open Access 97 databases in native format (but without design functionality) or can convert them into Access 2000 format.

Strange thing is your form seems to work fine on my machine both ways. I do get a compile error, but I don't think that's from any code related to this functionality.

And I don't get any errors when the form is merely opened. The notes field displays the memo field for the current row in the datasheet just fine.

Maybe there's some difference in syntax or functionality between Access 97 and Access 2000 regarding the Recordset property. But I have no idea what it might be. Sorry couldn't be more help. -- Herb