Variable Name in Recordset DIM

[Sorrells]

In a form in my program, I may need from 1 to 7 listboxes based on the days of the week depending on how many the user has selected in an earlier form. For each listbox, I need to create a temporary table to hold text data that is to be drag-n-dropped from one table to another.

I would like to create a Recordset for each listbox that would hold the records of each table. I was thinking along the lines of rst_temp1, rst_temp2 etc.

A query is returning the number of days and their names to the form. With the number of days, I was thinking [this is where it got sticky!] of concatenating a string " rst_temp" with an integer index (1,2,3) for a DIM Recordset statement within a FOR loop.

Several attempts in various ways and looking around leads me to the feeling that I don't have a snowball's chance ….. But I would like one of you experienced folks to confirm that another approach is warranted, and perhaps what that approach might be.

At this point in the mental contemplation of the design, I am thinking of DIMing all the Recordsets, Tabledefs, etc. but somehow leveraging the maximum number of days to limit activating (SET) them all. Perhaps I could have a 7-clause CASE Statement within a FOR loop limited by the count returned of selected days….

Any thoughts will be greatly appreciated!

 

[jjturner]

I've been working with list boxes lately, and have come to prefer them to subforms to a large extent.

Just a suggestion, but why I've preferred list boxes is because I can populate them with arrays rather than recordsets, which saves me the trouble of having to create temporary tabledefs/querydefs, etc.

If you set up your arrays, then you could use a function(s) to programmatically fill your list boxes with the values from those arrays and just Refresh the form after you've assigned the values into your arrays.

Your project sounds pretty involved, but I'd have to say that arrays would be the least monstrous solution for you.

Regards,
John

 

[jjturner]

Oops, sorry about that . . . I think I might be misunderstanding your post - are you actually trying to move records to and from tables that already exist? If so, I don't see a way around using recordsets/querydefs/tabledefs. If not, though, I still think arrays are worth a try.

 

[Sorrells]

jjturner ,

Actually, no I am not trying to move records. Once the user has designated which room he/she wants for a specific day and clicks on a command button, I will be modifying a single table by default program rules.

The information in all the listboxes is solely for the final calculations in an entirely different table and the intent is do close the tables with no changes to any data that was displayed.

Since posting I have been declaring 7 tables, links to tables, tabledefs, set, and so on. The code is so long that it would equal a chapter in a book. I know it is inefficient! Your comments about arrays are most interesting. Unfortunately, tonight is not the time to get into details but I would like to pursue this with you.

 

[jjturner]

Sorrells,

I'd be glad to offer my experience with using arrays (I'd have to say that from a newbie perspective, arrays are probably my favorite part of programming). Whenever you want to take this up is fine. I'll keep checking in.

Regards,
John

 

[Sorrells]

jjturner,

I am sorry, somehow I just lost this thread and never returned. I did solve the problem by creating a temp database and building tables there that I manipulate from the listboxes.

I found the way to assign captions to them via a loop on their names (listbox_1, listbox_2, etc).

All this turned out to be acceptable to Peter's Drag-N-Drop, especially the new version.

I should note that most of the code inspiration came from Tony Toews and his website http://www.granite.ab.ca/accsmstr.htm

The only problem I did not resolve was how to kill the temp database created. Now I have grown accustomed to living with it and as long as the MS Developer will allow me to create it on the fly, I consider this a done deal.

The process is lengthy but in a single user environment, the response on a newer PC is completely acceptable. I would be pleased to share this code if interest is shown.