A2007 ADP/SQL Parent Child form

[TR21Mark]

Access 2007 ADP/ SQL Server 2005 BE / Win7 x64

Most of my app will have alot of parent/child forms. My question is what is the best preferred method of first creating the parent record and obtain the ID and pass to the child datasheet record.

Just curious to see what I need to do before I spend alot of wasted time on a wrong method.

Thanks in advance for any advice on this topic

 

[Montez659]

Not knowing how your data is structured, what I have always done is merely disabled any sub forms and added a warning message that was active as long as the record was New. Once the record was no longer new (i.e., after the info began to be filled out and an autonumber id was created) then I dropped the message and enabled the controls.

I have always used the wizard when embedding a sub form into the parent form, and I know that the parent record ID was passed automatically. You might want to work through the wizard to see how the ID is passed. It will contain an SQL statement somehow in there (you will see it running the wizard) and it will ask how you want the parent to relate to the sub.

Hope this is what you were looking for.

 

[TR21Mark]

I am converting a FE/BE JET DB to adp/sql. My biggest concern is this for example:

1. Main form is bound form to table and the form is set to 'Data Entry'. In order to add any child records a separate form pops up to let the user pick an item in a listbox, combobox, etc. I would then have the subform's record source be set to a query that would have criteria on the foriegn key of the subform's table to the main form textbox control (ie: forms!frmMain!txtFKID)


How would I accomplish the same thing using stored procedures. I will still have the main form bound to the sql table, but how could I pass the form control reference to the child record and also the record from the separate pop up form's control.

I know I could use ADO command and use the .Parameters.Append from the pop up form so I guess I could use the same reference as a parameter and do it there. I think that typing this out made me realize what I need to do, but is this the best practice way?

 

[Montez659]

I am not sure 100% what you are laying out here, but if you are wanting to reference a field on the parent from the child, here is a resource that might help you out.

http://access.mvps.org/access/forms/frm0031.htm

If this is not what you are looking for, then it might be over my head.

 

[TR21Mark]

What I am looking for is basically this:

- A Form withe the Parent record and a subform with child details (datasheet view)

1. User inputs the data in the main parent record.
2. Stored Procedure is executed to create record and return the PK-ID
3. From the PK-ID that was returned, when the user inputs child records the stored procedure for the child records are added with the referenced PK-ID that was returned.
4. How do I reference the returned variable?
5. What is the prefered method to accomplish this.

Note - I have the full blown app in a FE/BE Jet4.0 on terminal server that everyone in our network connects to for the last 2 years, even remote offices. It makes the app very, very slow if more than 2 people are logged in. I have been tasked to upgrade to ADP with SQL Server with Stored Procedures. I am just very new to stored procedures and the methods to call and execute them.


Thanks for your help in advance.

 

[Montez659]

If the form is bound to the table, the PK will automatically be created (provided you use an Autonumber PK). All you would need is a field that displays the PK (this can be hidden if you so wish) then you can reference that field wherever you want (as shown by the link above).

But again, if this table is bound, and you use the wizard to add the child form, they will automatically be linked via an SQL statement that you see in thw wizard.

 

[xenou]

I'm not very familiar with SQL Server but I think if you are binding the form to the table you might just need to make the ID an "Identity" field - i.e., an autoincrement field. Then the ID is assigned as an Access autonumber field would be and you can use the form in the same way as before ...

Just a guess though.

ξ