Save different form control data to separate tables

[dcdimon]

I'm not sure if this is possible - which is why I'm here - but I'd like to be able to have controls on the same form (or form and subform) save to two separate tables.



What I'm specifically trying to do is have data entered by an operator and then have access indicate whether that reading is in compliance by filling in a text box with an OutofRange or InRange. The data would go to one table and the compliance data to another. I'm want to use the record ID to link between the two. I have relationships set up, but I'm experiencing trouble getting the compliance data to save the record ID when I press the form complete button.

The form is set up with the data fields on a main form and the compliance fields on a subform. The issue seems to be with the ID number and I just realized as I'm typing this that it may have something to do with the fact I'm on a SQL server backend. The ID shows up in the main form, but doesn't always show in the subform and when I press the complete button, which runs Application.RunCommand acCmdSaveRecord and is located on the main form, I get the compliance data saved, but no ID number. Without that ID number I won't be able to link the records later during our data out phase.

Any ideas?
DD

[ItsMe]

I do not know how you connect to SQL server but, if all things are created equal, use the Bang to get the ID value from the subform's recordset. Me!SubformName!Form![FieldID]

I imagine the ID would be in the subform's recordset. Maybe the Main/Child link is on another field. You could use DAO to create a recordset clone of the subform's recordset. Then, using a second DAO recordset that includes the ID field to retrieve the ID field. Join the two DAO recordsets on the Master/Child link.

[dcdimon]

The ID is coming from the main form record and I'm trying to link it into a subform. The mainform is sourcing TableA, while the subform is sourcing TableB. The ID on the mainform isn't being generated until the data hits the table up on the SQL server, either by tabbing into the subform or clicking my Save button.

The problem I'm having is having the ID populate on the main form and then get pulled/pushed to the ID on my subform. I'm not sure how I'd work in a recordset solution since everything is on the same form and happening all at once. I thought about working that way, and I'm going to give it a shot since nothing else I've done is working.

Thanks for the idea.

DD

[ItsMe]

I mentioned clone because it is efficient. I imagine using a variant from the main form in a FindFirst for the Subform's clone. Then retrieve a value/field from that Row/Record that you would use as a unique identifier in another process, maybe an UPDATE query that would execute on a table in the SQL server.

[dcdimon]

Okay, after trying multiple ways to do this I found a solution that works.

I'm having the ID control on the main form look at the max ID number on the data table and add a 1 to that, so the main form starts out with an id. I then have the subform ID control pull the ID number from the main form control....an voila I have records with matching ID's. I'm also including the entry date as a 2nd reference point between the tables.

I added a second autonumber field with a primary key so I can edit the table on the server, but that's not being referenced by anything. I'm sure this a work around, but my head is starting to hurt and I'm cursing at my computer more than I should in an office environment.

DD

[ItsMe]

I hope you drew a picture for the next guy that might come along!