Save Subform records to separate table

[Roy via AccessMonster.com]

I have a sub-form(continuous) on my main form.

The sub-form has information that the User is editing during data entry.
The sub-form is a work area only and not to be saved to the sub-form table.
How do I copy the fields from the sub-form to another table that I am using
as the detail table when I save the record?

Thx Roy,,,,,,

 

[tina]

well, if your subform is set to ContinuousForms display, then i take it that
your user can edit multiple records at the same time? if so, where are these
records being stored, even temporarily, if not in the detail table? and how
are you populating the subform with data, so that the user can edit existing
data - where is it coming from? and again, where are you storing it, even
temporarily?

 

[Roy via AccessMonster.com]

Tina, the data for the sub-form is temporily being stored in the vendorPkg
table. When I pick a vendor from the drop-down list, the sub-form is filled
with the vendor special packages and a check box. I want to store this info
from the vendorPkg table along with the id# from the main form to an event
table. The vendorPkg table will be used over and over again and I want to
keep it unchanged. The changes should go to the events table.

well, if your subform is set to ContinuousForms display, then i take it that
your user can edit multiple records at the same time? if so, where are these
records being stored, even temporarily, if not in the detail table? and how
are you populating the subform with data, so that the user can edit existing
data - where is it coming from? and again, where are you storing it, even
temporarily?

I have a sub-form(continuous) on my main form.

[quoted text clipped - 4 lines]

Thx Roy,,,,,,

 

[tina]

one way is to create an Append query to add the data as a new record in the
events table, as

INSERT INTO TableName ( Field1Name, Field2Name )
SELECT [Forms]![MyMainForm]![SubformCONTROLName]![Control1Name],
[Forms]![MyMainForm]![SubformCONTROLName]![Control2Name];

if the form reference syntax doesn't work, try

[Forms]![MyMainForm]![SubformCONTROLName].Form![Control1Name]

another method would be to open a recordset of the destination table, in VBA
code, and add the values from the subform controls into the corresponding
fields recordset.

or, if you're temporarily saving changes to several records in the vendorPkg
table, you can run an append query directly from that table into the events
table.

hth

Tina, the data for the sub-form is temporily being stored in the vendorPkg
table. When I pick a vendor from the drop-down list, the sub-form is filled
with the vendor special packages and a check box. I want to store this info
from the vendorPkg table along with the id# from the main form to an event
table. The vendorPkg table will be used over and over again and I want to
keep it unchanged. The changes should go to the events table.

well, if your subform is set to ContinuousForms display, then i take it that
your user can edit multiple records at the same time? if so, where are these
records being stored, even temporarily, if not in the detail table? and how
are you populating the subform with data, so that the user can edit existing
data - where is it coming from? and again, where are you storing it, even
temporarily?

I have a sub-form(continuous) on my main form.

[quoted text clipped - 4 lines]

Thx Roy,,,,,,

 

[Roy via AccessMonster.com]

I tried the code, but I’m getting a complile error “expected Case” error on
the first select [forms]! ............ Here’s my code that I have in a
standard module. When the user clicks the Add Record button on the form:

DoCmd.RunSQL "INSERT INTO tblEventJOVerification ( Companyid, JobOrder,
custpkg, check, dateRecd )"
SELECT [Forms]![frmDataEntry]![Companyid]
[FORMS]![frmDataEntry]![JobOrder]
[Forms]![frmDataEntry]![stblVerificationCkList].Form![custpkg]
[Forms]![frmDataEntry]![stblVerificationCkList].Form![check]
[Forms]![frmDataEntry]![stblVerificationCkList].Form![dateRecd]

one way is to create an Append query to add the data as a new record in the
events table, as

INSERT INTO TableName ( Field1Name, Field2Name )
SELECT [Forms]![MyMainForm]![SubformCONTROLName]![Control1Name],
[Forms]![MyMainForm]![SubformCONTROLName]![Control2Name];

if the form reference syntax doesn't work, try

[Forms]![MyMainForm]![SubformCONTROLName].Form![Control1Name]

another method would be to open a recordset of the destination table, in VBA
code, and add the values from the subform controls into the corresponding
fields recordset.

or, if you're temporarily saving changes to several records in the vendorPkg
table, you can run an append query directly from that table into the events
table.

hth

Tina, the data for the sub-form is temporily being stored in the vendorPkg
table. When I pick a vendor from the drop-down list, the sub-form is filled

[quoted text clipped - 15 lines]

 

[Roy via AccessMonster.com]

Okay, I was able to fix the syntax error , but it appends only one record to
the events table. The subform is a continuous form. There may be 12 vendor
packages that need to be saved to the event table.

How do I get the append to save multi records from the continuous form??
Thanks,
Roy

I tried the code, but I’m getting a complile error “expected Case” error on
the first select [forms]! ............ Here’s my code that I have in a
standard module. When the user clicks the Add Record button on the form:

DoCmd.RunSQL "INSERT INTO tblEventJOVerification ( Companyid, JobOrder,
custpkg, check, dateRecd )"
SELECT [Forms]![frmDataEntry]![Companyid]
[FORMS]![frmDataEntry]![JobOrder]
[Forms]![frmDataEntry]![stblVerificationCkList].Form![custpkg]
[Forms]![frmDataEntry]![stblVerificationCkList].Form![check]
[Forms]![frmDataEntry]![stblVerificationCkList].Form![dateRecd]

one way is to create an Append query to add the data as a new record in the
events table, as

[quoted text clipped - 22 lines]

 

[tina]

yes, that method, and the OpenRecordset method that i mentioned, only append
the current subform record to the table.

"if you're temporarily saving changes to several records in the vendorPkg
table, you can run an append query directly from that table into the events
table." (from my previous post)

just set the Append query up directly, table-to-table. then open the query
in your code or macro, using the OpenQuery action. since the query is an
action query, it will execute, rather than opening to display a dataset.

hth

Okay, I was able to fix the syntax error , but it appends only one record to
the events table. The subform is a continuous form. There may be 12 vendor
packages that need to be saved to the event table.

How do I get the append to save multi records from the continuous form??
Thanks,
Roy

I tried the code, but I'm getting a complile error "expected Case" error on
the first select [forms]! ............ Here's my code that I have in a
standard module. When the user clicks the Add Record button on the form:

DoCmd.RunSQL "INSERT INTO tblEventJOVerification ( Companyid, JobOrder,
custpkg, check, dateRecd )"
SELECT [Forms]![frmDataEntry]![Companyid]
[FORMS]![frmDataEntry]![JobOrder]
[Forms]![frmDataEntry]![stblVerificationCkList].Form![custpkg]
[Forms]![frmDataEntry]![stblVerificationCkList].Form![check]
[Forms]![frmDataEntry]![stblVerificationCkList].Form![dateRecd]

one way is to create an Append query to add the data as a new record in the
events table, as

[quoted text clipped - 22 lines]

Thx Roy,,,,,,

 

[Roy via AccessMonster.com]

Thank you Tina!! It works great.

yes, that method, and the OpenRecordset method that i mentioned, only append
the current subform record to the table.

"if you're temporarily saving changes to several records in the vendorPkg
table, you can run an append query directly from that table into the events
table." (from my previous post)

just set the Append query up directly, table-to-table. then open the query
in your code or macro, using the OpenQuery action. since the query is an
action query, it will execute, rather than opening to display a dataset.

hth

Okay, I was able to fix the syntax error , but it appends only one record to
the events table. The subform is a continuous form. There may be 12 vendor

[quoted text clipped - 5 lines]

I tried the code, but I'm getting a complile error "expected Case" error on
the first select [forms]! ............ Here's my code that I have in a
standard module. When the user clicks the Add Record button on the form: [quoted text clipped - 12 lines]

Thx Roy,,,,,,

 

[tina]

you're welcome

Thank you Tina!! It works great.

yes, that method, and the OpenRecordset method that i mentioned, only append
the current subform record to the table.

"if you're temporarily saving changes to several records in the vendorPkg
table, you can run an append query directly from that table into the events
table." (from my previous post)

just set the Append query up directly, table-to-table. then open the query
in your code or macro, using the OpenQuery action. since the query is an
action query, it will execute, rather than opening to display a dataset.

hth

Okay, I was able to fix the syntax error , but it appends only one record to
the events table. The subform is a continuous form. There may be 12

vendor
[quoted text clipped - 5 lines]

I tried the code, but I'm getting a complile error "expected Case"

error
on

the first select [forms]! ............ Here's my code that I have in a
standard module. When the user clicks the Add Record button on the

form:
[quoted text clipped - 12 lines]

Thx Roy,,,,,,