Button Control And Referencing

[HopeHal]

I have a form which references two tables.
The second table has a one-to-many relationship with the first table.

I have a button which is intended to copy the key value from the second table to the first.

This button works when the second table is arranged into the form as a subform, but I do not need to see the entire default subform.

I only need a small subset of the fields from the subform.

Attempts:
1) The button does not work if I hide the subform and add in linked text boxes. The text box as well does not link to the subform.
2) The button does not work if I simply add the desired fields at text boxes with the 'Add Existing Fields' tool from the design tab.

As I understand the problem now, it is an issue of getting my field reference(s) to work correctly in the button's VB code:

Example-1)

Private Sub Command34_Click ()
'INSERT CURRENT ARRAY ID.

Me.[ARRAY_ID_ARRAYS].SetFocus

DoCmd.GoToRecor d , , acLast

Me![ARRAY_ID] = Me.[ARRAY_ID_ARRAYS]

Me!NOTES.SetFoc us

End Sub

Example-2)

Private Sub Command34_Click ()
'INSERT CURRENT ARRAY ID.

Forms![CLEAN DATA]![ARRAY_ID_ARRAYS].SetFocus

DoCmd.GoToRecor d , , acLast

Me![ARRAY_ID] = Forms![CLEAN DATA]![ARRAY_ID_ARRAYS]

Me!NOTES.SetFoc us

End Sub

I am using MS ACCESS 2007 on Windows XP.

Motivation:
I am a deep SAS user, but building forms in SAS is too time consuming and often clients lack the software. Being able to push ACCESS onto SharePoint and websites looks 'easier' as well. So I am now learning 'yet another software'.

Thank you,
Hal

[Stewart Ross]

Hi HopeHal. For me at least your post is proving difficult to understand; the most basic concern for me is if there is a one-to-many relationship already in existence between two tables, why do you need to copy the key back from the many side to the one side? If Access has been set to enforce relational integrity it simply won't let you have a secondary key value in a many-side table with no one-side primary key to match.

Leaving this to one side, you haven't told us anything about the fields in the tables, and which fields you are referring to in your code. Without that it is difficult to advise you on how to get what you need to do done. It is not at all clear what the field you refer to as an array is; if it is the subform you are not referring to any of its controls in either example. If it is a textbox, what is its source?

I would be happy to help, as I am sure would other contributors, but you really do need to provide some clarification to help us to understand your problem, and in turn to help you.

Regards

Stewart

[HopeHal]

Yes, I am crazy

Thanks for your patience! Seems that I do not yet have the appropriate vocabulary.

Big picture:
The purpose of the form is to clean and organize a raw data set for future analysis in another software. The data set to be cleaned has 12000+ variables in 21 topic sections. The form displays the variables one at a time and the 'data janitor' (usually me...) assigns and checks characteristics for each variable. This work is largely automated with buttons. When one of the 21 sections is completed, the data is read by SAS for statistical analysis and modeling.

Why more than one table:
The main table is the one with 12K variables (Tabl1). The second table (Tbl2) is related to Tbl1with a one-to-many numeric key. Large collections of variables within a topic are further sub-grouped into a text string on Tbl2, hence one row in Tbl2 represents many rows in Tbl1; This Tbl2 text string is simply an array of the sub-grouped variables in SAS programing syntax. When a new row on Tbl2 is created, this key value needs to be placed in Tbl1 and when a new variable name is added to this text string the Tabl2 key needs to be placed in the corresponding Tbl1 variable row.

What works:
Everything works when Tbl2 is placed on the form as a subform. Everything reads into SAS with great success.

What does not work:
The subform is a spreadsheat and it is very cumbersome to view and use. Why not just have a text boxes on my form for the three Tbl2 fields needed on the form instead of the whole clunky subform Tbl2 spreadsheet? The whole idea is to improve worker efficiency.

I think we communicated successfully here:
"If it is a textbox, what is its source?" Yes, this is my question! How do I source the text boxes from Tbl2?

If this big picture description of the problem is adequate, I can reply with the ACCESS coding details. Please suggest specifics.

Thanks!

[Stewart Ross]

Thanks for your clarification, HopeHal. You have indeed made things much clearer.

It can be difficult to go from a free-form data analysis table to a relational database, because the applications of these products are so entirely different. Relational databases model relationships between objects; free-form tables hide the objects with the ease of simply adding additional columns of data. It makes true relational analysis of existing analytical tables a daunting task, as the deconstruction of the implicit relationships can be very time consuming.

Please feel free to post your code. I would also welcome a list of the essential fields you need to access within the spreadsheet-like table, as this is clearly one of the key items you need to be able to connect to and use in your application.

-Stewart