How to link a separate form to records associated with a subform?

  • Thread starter Thread starter Jim Moore
  • Start date Start date
J

Jim Moore

In my application, there is a main form and a subform within the main form.
The subform has 35 rows and I need to pick data elements that will not
conveniently fit within the subform - 5 of the 35 rows have distinctly
different information from one another. On a separate form, I have 5 sets
of Option Buttons corresponding to the appropriate rows - one set of option
buttons has a different shade background to indicate the appropriate set for
data entry. The form is working as desired BUT, I can't get the data to
link back to the correct row. The separate form is activated by a Option
Button on the Subform - the On Got Focus event which sets the colors of the
separate form. I set a bookmark when I enter the On Got Focus
(variantBookMark = rec.BookMark). Then, when I am ready to write
edit/update the collected data, I set the bookmark in reverse (rec.BookMark
= variantBookmark).

What happens is that 36 records are added to the database for the 35 records
and all information goes into the first of the 35. I have worked 3 days
trying to get the new data to link to the records in the subform with no
success. I am probably making a simple, stupid error, but I am clueless. I
am working with Access 2002.

Thanks for any help.

Jim Moore
 
In my application, there is a main form and a subform within the main form.
The subform has 35 rows and I need to pick data elements that will not
conveniently fit within the subform - 5 of the 35 rows have distinctly
different information from one another.
Click to expand...

What is the structure of your Tables? It sounds suspiciously like
you're working backwards, designing the Form first and designing the
tables to fit the Form. This is like assembling the walls and windows
of your house, and then pouring the foundation!

In particular, a Table should be "homogenous" - all records have the
same status and the same "shape"; you should not have 30 of one kind
of record, and 5 of a different kind of record. It sounds like you
should have TWO "many" side tables, probably displayed each in its own
subform!

What happens is that 36 records are added to the database for the 35 records
and all information goes into the first of the 35. I have worked 3 days
trying to get the new data to link to the records in the subform with no
success. I am probably making a simple, stupid error, but I am clueless. I
am working with Access 2002.
Click to expand...

I'd suggest that you post your code (we obviously can't tell what
you're doing wrong if you don't tell us what you're doing), but I hate
to waste time fixing code which supports an incorrect table structure.
Let's get those foundation piers in place FIRST. Could you describe
your table structures, and how the mainform relates to the thirty and
the five records?
 
The main table has 12 fields including an index field person's name, job
title, location, date and time, etc. The second table, 35 records per 1
main table record, with the related index field. For the second table, two
fields are related to lookup tables - the first (FieldA) being a 35 record
table that has the text for the field, and the second (FieldB) having 3
records that are tied to the option button that is used to trigger the
additional form I am trying to use. There are 6 additional fields whose
data comes from the additional form (FormDetail).
FormDetail is only called if OptionButton2 (value of 2 for FieldB) is
selected

for 30 of the 35 values of FieldA, only one response is collected

for the other 5 values there are 6 sets of option buttons 5 option
button sets select the value of field Detail_1. The sixth set selects a
value for field Detail_2 which only applies to this one category.

There are also 3 text boxes which apply to all 35 records.

I didn't design the database, structure, etc. The main table has about 1400
records and the second table has over 42,000 of historical data and we would
prefer not to make major changes to the data base. I am just trying to pick
up the pieces - the original programmer has left the company.

I hope I have clarified my problem. I think the data structure is OK, but I
can't lock the data from FormDetail to the correct record in the second
table.

The code I am trying to use before going to FormDetail is:
varBookmark = rcdBehaviors.Bookmark
Then in FormDetail, I use
rcdBehaviors.Bookmark = varBookmark
before Edit/Update of the collected information. varBookmark and
rcdBehaviors are declared as Public.

Thanks for your help,
Jim Moore
 
Back
Top