[Resolved using UPDATA query] Create an array from a field in a database

[A441OTA]

Thank you everyone for your replies and pointing me in the right direction!!!! Based on Feetstinks' suggested code I played with it enough to get it to work!! Thank you Thank you Thank you Feetstinks!!!!

Here is what finally worked:

UPDATE tableupdatetoname, tableupdatefromname, SET tableupdatetoname.label = tableupdatefromname.Rpt_Order
WHERE tableupdatefromname.label = tableupdatetoname.label

This updates the label values in the main table with the report order values from the shell table!!!

I cannot thank everyone enough for helping me get through my very first VB project -- especially since it seems to require more complicated stuff then I remember from class!!!

I took this job under the impression that it would be writing macros in Word to make data "Look pretty" -- at least that was what I was told!!! The data output (which is not even a database -- I'm having to reorganize it to make it useful!) is missing half of what is needed to create the reports. I have had to build a user interface to collect the missing information then everything else is supposed to happen automatically!! It's the automation part that is giving me grief!!!

I would not even be close to having something useable if it weren't for this forum!!! Thank you to all that own, operate, and maintain this forum... and thank you to all of you experts out there that read these postings and offer assistance. I admire you and aspire to be like you one day!!!!! You unselfishly give of your time and effort to help us "newbies" and "novices" and encourage us all the while!!

Many thanks,
Mary

***************************************************
Good afternoon everyone!

I need to create an array via VB6 based on fields in a table in an Access 2002 database (arrays have always baffled me anyway!! Don't remember doing well on this assignment in school... over two years ago!) .

I have a table ("Shell") with a several fields and I want to create an array from two of them. The first field "Rpt_Order" is numeric starting from 1 to ?? (depending on the table this number may change but will always equal the recordcount) and the second field corresponds to this number and is "Label" (which if I can get away with a one simple array it may be better -- the first label corresponds with 1, the second label with 2, etc.). I need to use these two fields to search another table (QstData) that has the "Label" field and replace the matching Label.values in the second table with the corresponding Rpt_Order.value (hope that makes sense) from the first table. Example as follows:

Shell.Rpt_Order Shell.Label QstData.Label*
1 Total find all that match and change to 1
2 Army find all that match and change to 2
3 Marine find all that match and change to 3

*thousands of records but duplicated labels

I'm having trouble simply creating the array from the fields so I haven't even gotten to the find and replace yet. All of this needs to be done behind the scenes without user involvement.

Currently I am using ADO and DAO (was trying to use ADO strictly but ran into some things I needed to do that I couldn't figure out how to do with ADO...later when I have time I will go back and figure out how to do it with ADO but until now I have to get this working next week!!) so whichever method gets me there is fine!!

Thanks in advance for any assistance anyone can offer!!
Mary

[jayarajCN]

Hi,
If you want an array just for updating a second table based on the first table then,instead of an array,you could use a query which will be more efficient.

[A441OTA]

I'm so new to this that I have no clue how to do that with an update query??

I'm sorry... do you have a suggestion on where to begin?

Thanks,
Mary

[Feetstink]

I think you would use something like this.

UPDATE QstData SET QstData.Label = Shell.Rpt_Order
WHERE QstData.Label = Shell.Label

I'm not positive if you can use QstData.Label in the SET and the WHERE clauses at the same time. But logically that sounds like what you're trying to do.

[A441OTA]

Hey Feetstinks (I'm sure they don't!!!), thanks for the advice. From my novice understanding of SQL (VB, and everything else I need to know immediately!!) this certainly sounds like a much better approach!

However, I have tried coding it but it is not updating the data. I'm not receiving any errors so I don't know what the problem is. I made sure the fields were the same type - they are. Neither one of the fields are primary or foreign keys. Is is because there are multiple rows that need to be updated???

Thanks,
Mary

I will keep researching in the meantime!