Create an Array of 3 variables (1 Viewer)

[3divine]

In my table I have one field called Description and each record has unique description. I also have a field called Category with values called Level1, Level2 and level3 and this field Category are what separates the 3 from each other.

In my form called Main (temporary container that holds the values for different variables), I have 3 textboxes that stores the values from the 3 types of levels called: txtCuttingToolsDesc, txtInsertDesc and txtAdapter.

Everyone knows that Access does not allow duplicate names, meaning I can't have a description textbox 3 times.

These values are being saved in another table/form called Layout as a recordset collection then mail-merge into Word document via automation.

When creating a recordset, one complete record may or may not require all these 3 types of levels but for sure at least 2 types are required.

So, in my table/form Layout where these records are saved as a recordset collection, I also have 3 fields that referenced these 3 types of descriptions.

When making a change in one of the descriptions in my underlying table how can I synchronize the update into the recordset using a command button?

Any help with a code is very much appreciated.

 

[Fornatian]

As I recall the fields you are talking about are linked via search boxes etc on the main form layout.

what i don;t recall is if you have automatically forged relationships between the tables. if so - then all you need to do is change the relationship to cascade update related records and that should update the foreign keys in your main table. However - if you are just copying values which are not the primary key then you will need to perform an update query based on the old field value so you can update it to the new field value - not majorly difficult but unnecessary should you store the primary key instead and pull the data through using a select query.

See you Trini- I mean Gemma.

Ian

 

[3divine]

Hello there! and Happy New Year!

Thanks for the reply and good to hear from you! Glad that you recognized my field names.

To answer your questions:

I changed my mdb a bit where I combined all the 3 tables into one table where before I have it setup to draw data from 3 separate tables ( bad idea?). Now I'm really pulling my hair!

If you still have my mdb, recall that Form1 is the main entry form to create the recordset then records are saved in form Layout.

I was successful changing the description in my underlying table but to synchonize the change also into the recordset table is another story.

When testing my form, I was able to make changes in the recordset collection table except I have it hard-coded thats why I was leaning towards using the Array variables to represent the 3 description textboxes ie:

Dim desc(2)
desc(0) = "txtCuttingToolsDesc"
desc(1) = "txtInsertDesc"
desc(2) = "txtAdapterDesc"

'Do something here

I'll give it a shot what you suggested, and if I get stuck do be ready to bail me out!

.....Meaning "I'm all ears"

btw: inadvertently deleted my Username so I'm stuck with Gemma (middle name)

 

[Fornatian]

.....Meaning "I'm all ears"

Nobody's perfect.