Help with Insert Into

[Mary]

Please help with Insert into statement or help update subform records based
on main form values.

Main form fields:
[oa_new].[ID]
[oa_new].[pcuser]

Subform fields to be updated:
[SLogSub].[EventID] = [oa_new].[ID
[SLogSub].[UID] = [oa_new].[pcuser]

I'm not sure if a subform is the best way to update the records. I have a
macro set up that adds one record for each System in the Systems table. The
SLogSub form displays that set of records where the EventID is null. When a
new event is added using the oa_new form, I want the EventID and UID fields
to be updated with the values for that new record for each record on SLogSub,
or use an insert into statement to update each record in the S_log table
where EventID is null.

Thank you in advance for any suggestions!

Mary

 

[Mary]

Here is my latest attempt - getting an error that all of the records in the
append query can't be added. In the debug window, the last line of code is
highlighted (DoCmd.RunSQL SQL).

DoCmd.RunMacro "addSystems"
Dim SQL As String
SQL = " INSERT INTO s_log (EventID, UID) " & _
" VALUES ( " & Me.ID & ", '" & Me.pcuser & "' ) "
DoCmd.RunSQL SQL

The cursor shows the values I'm looking for in the debug window. How can I
get them copied into the S_log table?

Thanks,
Mary

 

[John W. Vinson]

Please help with Insert into statement or help update subform records based
on main form values.

Main form fields:
[oa_new].[ID]
[oa_new].[pcuser]

Subform fields to be updated:
[SLogSub].[EventID] = [oa_new].[ID
[SLogSub].[UID] = [oa_new].[pcuser]

I'm not sure if a subform is the best way to update the records. I have a
macro set up that adds one record for each System in the Systems table. The
SLogSub form displays that set of records where the EventID is null. When a
new event is added using the oa_new form, I want the EventID and UID fields
to be updated with the values for that new record for each record on SLogSub,
or use an insert into statement to update each record in the S_log table
where EventID is null.

Thank you in advance for any suggestions!

Mary

It sounds like you're mixing levels. Data is stored in Tables, not in forms;
update queries update Tables, not forms. You can't run an update query on a
subform!

It *SOUNDS* like all you might need is to set the Child and Master Link Fields
of the Subform control to

EventID;UID
and
ID;pcuser

respectively.

This will cause the subform to inherit those values from the mainform as soon
as any *OTHER* control on the subform is "dirtied".

 

[Mary]

I think the best way to handle is using the insert into sql to update the
table and not use a subform. I've tried the links you suggested but it still
doesn't update.

How can I get the EventID and UID fields updated directely in the s_log
table after the ID field has been updated on the oa_new form? Eventually the
S_log will contain a set of records for every event. I will only want to
update the new set where the EventID and UID fields are null.

Thanks again,
Mary

Please help with Insert into statement or help update subform records based
on main form values.

Main form fields:
[oa_new].[ID]
[oa_new].[pcuser]

Subform fields to be updated:
[SLogSub].[EventID] = [oa_new].[ID
[SLogSub].[UID] = [oa_new].[pcuser]

I'm not sure if a subform is the best way to update the records. I have a
macro set up that adds one record for each System in the Systems table. The
SLogSub form displays that set of records where the EventID is null. When a
new event is added using the oa_new form, I want the EventID and UID fields
to be updated with the values for that new record for each record on SLogSub,
or use an insert into statement to update each record in the S_log table
where EventID is null.

Thank you in advance for any suggestions!

Mary

It sounds like you're mixing levels. Data is stored in Tables, not in forms;
update queries update Tables, not forms. You can't run an update query on a
subform!

It *SOUNDS* like all you might need is to set the Child and Master Link Fields
of the Subform control to

EventID;UID
and
ID;pcuser

respectively.

This will cause the subform to inherit those values from the mainform as soon
as any *OTHER* control on the subform is "dirtied".

 

[John W. Vinson]

I think the best way to handle is using the insert into sql to update the
table and not use a subform. I've tried the links you suggested but it still
doesn't update.

How can I get the EventID and UID fields updated directely in the s_log
table after the ID field has been updated on the oa_new form? Eventually the
S_log will contain a set of records for every event. I will only want to
update the new set where the EventID and UID fields are null.

Are you trying to create a new, empty "placeholder" record automatically? Why?
It's not necessary to create empty records ahead of the time that they're
needed for data.

Are there EXISTING records in s_log with NULL values in the EventID and UID
fields? If so, how can you tell *which* of the records with null values should
be updated?

Note that Append queries add new records, Update queries change the stored
values in existing records; which do you want to do?

 

[Mary]

I was using an append query to add the records to the S_log table as
placeholders, then planned to update the other two fields based on the main
form. I updated the append query to grab those values when the records are
appended, and that works great. Thanks!