Updating data in a table with an action on form

  • Thread starter Thread starter alicia welton
  • Start date Start date
A

alicia welton

I have a form with an option group with 3 different
option buttons. I want to be able to select one of these
options and have the underlying value be written to a
table that is currently not associated with this form
(though I supposed it could be?) As the end user changes
the options over time, I want a new record to be written
to this "non associated" table each time it is changed.
(I want to be able to look at all of the changes that
have been made). I have a unique identifier for each
record in my form and want to have it be sent to
this "tracking" table as well as the underlying
identifier in the option group so that I can analyze
these changes.

I am an intermediate level user and cannot believe I
cannot figure this out. Thanks for any advice. Alicia
 
Alicia,

One method would be to construct an Append Query to add a record to
the unassociated table with the value of the Option Group as selected,
and the ID of your form's current record. You can use an OpenQuery
macro action to run the Append Query, and assign it on the AfterUpdate
event property of the Option Group.

- Steve Schapel, Microsoft Access MVP
 
I understand the concept of creating the append query and
running it on the "After Update" property of the option
group.

I don't understand how to identify the current record
that I am on (in the form) in the append query as well as
where/how to have the selected item in the option group
be identified to do the append. Does this make sense?
Is this all done in the append query or are these steps
to identify the record/option group selection to be done
in multiple actions in a macro with running the append
query last? Or maybe there is another approach? Thanks
again for any suggestions! Alicia
 
Alicia,

Base your Append Query on the table/query that the form is based on.
Include in the query your ID field and the Option Group field, and in
the criteria of the ID field refer to its value on the form's current
record by syntax equivalent to...
[Forms]![NameOfYourForm]![ID]
This assumes your table=>form have a unique identifier field named ID.
Adjust the above as required for your actual field and form names. I
am also assuming that the Option Group is bound to one of the fields
in this table. Now that I think about it, I think your macro may also
need to include a RunCommand, SaveRecord action before the OpenQuery
action. If my assumptions are not correct, you may have to give us a
bit more detailed information about the fields and form and the nature
of the option group.

- Steve Schapel, Microsoft Access MVP
 
I'm still having trouble. Here are the details:
You're assumptions are correct regarding uniquie ID's for
records. Here is my current configuration. I have a
form, Lookup by Acct Number Main, that has a subform
linked to it called Lookup by Acct Number Subform Event.
In this subform is where I want the option group
selection to update a table called Status Tracking. The
subform is based on a table called Event with a unique ID
called "Event_ID". Event_ID is on the subform (though
not visible) and the option group adds 1,2,3,etc to the
event table to a field Status_CD correctly. Maybe I am
not writing the append query correctly. I created the
new append query, added the event table and included
Event_ID and Status_CD. In the criteria of the Event_ID,
I added [Forms]![Lookup by Acct Number Subform Event]!
[Event_ID]. This query should append to the Status
Tracking table (mentioned above).

I then created a macro to RunCommand to save the record
and then run this append query on the AfterUpdate
Property of the option group. When I run the
macro/query, it pops up a box with the syntax "[Forms]!
[Lookup by Acct Number Subform" (cuts off here..I assume
it's a frame size limitation?) It wants me to enter
something or can't recognize what it needs to do. Do I
not have the syntax on the correct field (Event_ID)?
Thanks again, Alicia
-----Original Message-----
Alicia,

Base your Append Query on the table/query that the form is based on.
Include in the query your ID field and the Option Group field, and in
the criteria of the ID field refer to its value on the form's current
record by syntax equivalent to...
[Forms]![NameOfYourForm]![ID]
This assumes your table=>form have a unique identifier field named ID.
Adjust the above as required for your actual field and form names. I
am also assuming that the Option Group is bound to one of the fields
in this table. Now that I think about it, I think your macro may also
need to include a RunCommand, SaveRecord action before the OpenQuery
action. If my assumptions are not correct, you may have to give us a
bit more detailed information about the fields and form and the nature
of the option group.

- Steve Schapel, Microsoft Access MVP


I understand the concept of creating the append query and
running it on the "After Update" property of the option
group.

I don't understand how to identify the current record
that I am on (in the form) in the append query as well as
where/how to have the selected item in the option group
be identified to do the append. Does this make sense?
Is this all done in the append query or are these steps
to identify the record/option group selection to be done
in multiple actions in a macro with running the append
query last? Or maybe there is another approach? Thanks
again for any suggestions! Alicia

.
 
Disregard my last post. I figured it out. I did not
have the main form AND the subform in the syntax. Once I
added this, it worked. This message board is so
helpful! Thank you!

-----Original Message-----
I'm still having trouble. Here are the details:
You're assumptions are correct regarding uniquie ID's for
records. Here is my current configuration. I have a
form, Lookup by Acct Number Main, that has a subform
linked to it called Lookup by Acct Number Subform Event.
In this subform is where I want the option group
selection to update a table called Status Tracking. The
subform is based on a table called Event with a unique ID
called "Event_ID". Event_ID is on the subform (though
not visible) and the option group adds 1,2,3,etc to the
event table to a field Status_CD correctly. Maybe I am
not writing the append query correctly. I created the
new append query, added the event table and included
Event_ID and Status_CD. In the criteria of the Event_ID,
I added [Forms]![Lookup by Acct Number Subform Event]!
[Event_ID]. This query should append to the Status
Tracking table (mentioned above).

I then created a macro to RunCommand to save the record
and then run this append query on the AfterUpdate
Property of the option group. When I run the
macro/query, it pops up a box with the syntax "[Forms]!
[Lookup by Acct Number Subform" (cuts off here..I assume
it's a frame size limitation?) It wants me to enter
something or can't recognize what it needs to do. Do I
not have the syntax on the correct field (Event_ID)?
Thanks again, Alicia
-----Original Message-----
Alicia,

Base your Append Query on the table/query that the form is based on.
Include in the query your ID field and the Option Group field, and in
the criteria of the ID field refer to its value on the form's current
record by syntax equivalent to...
[Forms]![NameOfYourForm]![ID]
This assumes your table=>form have a unique identifier field named ID.
Adjust the above as required for your actual field and form names. I
am also assuming that the Option Group is bound to one of the fields
in this table. Now that I think about it, I think your macro may also
need to include a RunCommand, SaveRecord action before the OpenQuery
action. If my assumptions are not correct, you may
have
to give us a
bit more detailed information about the fields and form and the nature
of the option group.

- Steve Schapel, Microsoft Access MVP




.
.
 
Back
Top