Set value of a field based on a value in a subform

[Guest]

Hello -

I have a form to add a new record to a record set. I would like to create an
AfterUpdate procedure that will set the value of one of the fields in the
main form to the value of the related field in the sub form. The forms
are linked by the BrdSN field.

I have:

Private Sub BrdSN_AfterUpdate()

' set the value of the BGA# field in the main form
' to the value in the BGA# field in the sub form

Me![BGA#] = Me![MySubForm].Form![BGA#]

End Sub

but nothing is updating.

Thanks for your help!
sandy

 

[Ken Snell \(MVP\)]

In which form's module is this code located? How is the BrdSN control
updated -- manually? programmatically? by inserting a new record in the
subform?

We need more details about your setup and what you're actually doing.

 

[John W. Vinson]

I have a form to add a new record to a record set. I would like to create an
AfterUpdate procedure that will set the value of one of the fields in the
main form to the value of the related field in the sub form.

And if there are 31 records displayed in the subform... WHICH ONE?

This really sounds like you're storing data redundantly in two tables, never a
good idea. Why?

John W. Vinson [MVP]

 

[Guest]

Thanks for the replies -

The objective is as follows:

I keep track of all the modifications that are made to our serialized
products in a BoardTraveler table.
There are two types of records that go into the BoardTraveler table.
1- when we send the boards out to replace the BGA - we need to know which
version of the BGA is currently on the board
2- when our lab applies modifications to the boards, not related to the BGA

The mods done in the lab are tracked in a ModTracker table.
Each mod state is made up of a group of mods- Mod A, Mod B, etc
When a mod state is applied to a board, the ModState_ID is entered into the
BoardTraveler table
However, since the BGA has not changed, I need to copy the BGA from the
previous record and put it into the current record at the same time the mod
is added as leaving the BGA blank causes other problems.

The "apply mod to board" form [which is a sub set of fields from the Board
Traveler] form has a small sub form that finds the latest entry in the
BoardTraveler for that board and tells me what the latest/current BGA is

I just want to copy the BGA_ID from the sub form and enter it automatically
into the BGA-ID on the main form when i create a new. (which is also a sub
form in the ModTracker form.)

hope this long winded explanation helps.
thanks!

 

[Ken Snell \(MVP\)]

I may be alone in this, but I am confused... normally, in a main form /
subform setup, the main form feeds the linking field data to the subform
through the built-in programming that defines the form objects -- through
the LinkChildFields and LinkMasterFields properties of the subform control.

Therefore, if you start a new record in the main form, it seems unlikely
that there is a corresponding record already in the subform's data? How do
you have a record in the subform's table before you create a record in the
main form's table?

My initial impression is that your table structure may be storing redundant
data in more than one table, and/or you do not have a well normalized data
table structure and thus you're having to "jump through hoops" to try to
make the data structure work? A good structure would be to have a table to
track modifications being made to a board, linked to the board table via a
1-to-many relationship; do you have this type of setup in your database?

Perhaps it would help if you could post more details about your tables and
fields, and how they relate to each other.
--

Ken Snell
<MS ACCESS MVP>

Thanks for the replies -

The objective is as follows:

I keep track of all the modifications that are made to our serialized
products in a BoardTraveler table.
There are two types of records that go into the BoardTraveler table.
1- when we send the boards out to replace the BGA - we need to know which
version of the BGA is currently on the board
2- when our lab applies modifications to the boards, not related to the
BGA

The mods done in the lab are tracked in a ModTracker table.
Each mod state is made up of a group of mods- Mod A, Mod B, etc
When a mod state is applied to a board, the ModState_ID is entered into
the
BoardTraveler table
However, since the BGA has not changed, I need to copy the BGA from the
previous record and put it into the current record at the same time the
mod
is added as leaving the BGA blank causes other problems.

The "apply mod to board" form [which is a sub set of fields from the
Board
Traveler] form has a small sub form that finds the latest entry in the
BoardTraveler for that board and tells me what the latest/current BGA is

I just want to copy the BGA_ID from the sub form and enter it
automatically
into the BGA-ID on the main form when i create a new. (which is also a sub
form in the ModTracker form.)

hope this long winded explanation helps.
thanks!

Hello -

I have a form to add a new record to a record set. I would like to create
an
AfterUpdate procedure that will set the value of one of the fields in the
main form to the value of the related field in the sub form. The
forms
are linked by the BrdSN field.

I have:

Private Sub BrdSN_AfterUpdate()

' set the value of the BGA# field in the main form
' to the value in the BGA# field in the sub form

Me![BGA#] = Me![MySubForm].Form![BGA#]

End Sub

but nothing is updating.

Thanks for your help!
sandy