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 snowballs 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!
Regards, Sorrells

 

[cmmrfrds]

Why do you need to create a temporary table since all the data is already in the listbox? Maybe explain a little more on the control flow you are doing.

 

[Sorrells]

cmmrfrds,

That certainly is a reasonable question but I don't think I can reply briefly! The objective of the program is to create a home cleaning system that is based on the rooms in the house and the tasks associated with each room. Any task can have one of many frequencies (daily, weekly, monthly and more). These frequencies are assigned in this form but are restricted to the days identified in a Cleaning Schedule kept in another table.

The form for which my question is articulated, is titled the Weekly Schedule Planner. It is to be of a drag-n-drop functional form. The concept is that all the rooms that have been entered into the system for a client are displayed in a listbox at the top of the form.

The remainder of the form will have 1 to 5 listboxes, dependent on the days that the cleaning is performed based on the Cleaning Schedule. The number of days will differ by client. Thus I start with all the listboxes invisible and make them visible after querying the Cleaning Schedule and obtaining the number and names of the days.

I want to enable the user to drag and drop the rooms from the initial list box to any of the 'Day' listboxes to hopefully balance the cleaning workload for the various days of the week. There is only a single table that holds all the room information for what could be many clients.

The client's data is contained in both a ROOMS and TASK tables, which are related. In order to leverage the drag-n-drop, I need listbox RowSources similar to the one used to display the Rooms. However there is only one ROOM table in the database and I do not want to change any of the data in that table. Ultimately only the data in the TASK table will be updated.

My thought was to create temporary tables as RowSources for these 'Day' listboxes to hold both the Room Name for the user to work by and the field relating the two tables for final calculations.

On the form, once the user was happy with the 'balanced' home, he/she would click a command button that would perform the complex activity of updating the TASK table. For a room, any bi-monthly or monthly tasks associated with a room will be assigned, by rules, a week and the day the listbox is identified with

The advantages of temporary tables in a temporary database are the relatively small bloating of the program database.

By giving them all a common, generic name and numbered one up, I hoped to leverage their creation, assignment, use in calculations/manipulations and eventual closure with variable names in some sort of loops. I also hoped to limit the creation of the number of these objects to the amount or count returned with the number of days was queried in the Cleaning Schedule. My hope was to use the loop index as the suffix of the name (changed to text data type) within the loops.

I think the above is a good starting description of the environment and objective. Since I am so close to it and you have not encountered this before, please feel free to ask additional questions that will enable you to see what I hope to accomplish.

I believe I have a good method for creating the temporary database and tables that I obtained from the kindness of Tony Toews, Granite Consulting,

http://www.granite.ab.ca/accsmstr.htm.

I just hate to code for or create un-necessary objects, tables, etc. and if the client has Monday and Wednesday in their schedule, I would rather avoid and work with Tuesday, Thursday or Friday!
Regards, Sorrells

 

[cmmrfrds]

Does the ROOMS/TASK table contain the schedule? I am envisioning Rooms which describes the room and Task which describes the task, and RoomTask which links the task to the room. I am assuming Schedule is a separate table that shows days that are available by client or worker. How is time tied to the room/task? Is it related from a schedule table?

Is the focus of the Form where the worker allocates their time, or where a scheduler allocates various workers time. If a scheduler, do you need to control for over/under scheduling a worker.

This may be getting too complex for me in a short amount of time. To answer some questions on creating the temporary recordsets conditionally.

If you can determine which tables to create in the initial listbox then once the listbox is shown there could be another button that says show schedule and you could create the temporary recordsets when they click the button. Actually, I don't think it takes a lot of memory just to Dim the recordsets, so you could take a reverse approach. Dim all the recordsets up front and then when you know they are not needed destory them.
rst1.close
Set rst1 = Nothing
etc..

You can check to see if a dimmed object has content by checking for Nothing. If the object is not dimmed it will produce an error, so, error trapping is a way to check if the recordset was dimmed.

If rst1 Is Nothing Then
rst1.close
Set rst1 = Nothing '- destroy
End If

Hopefully, these are some ideas to work with.

 

[Sorrells]

cmmrfrds,

Here are some answers to your question. I understand that it is hard to visualize what I am up to.

1) Does the ROOMS/TASK table contain the schedule? No, for the purposes of this conversation there are 3 tables, Rooms, Tasks and Schedule.

2) Each Task record contains the schedule for the task among other information.

3)How is time tied to the room/task? Is it related from a schedule table? The working hours are entered into the Schedule table but other than a schedule report, play no other function in the table.

4) Is the focus of the Form where the worker allocates their time, or where a scheduler allocates various workers time? No, the focus is concerned with balancing the workload of cleaning the various rooms over the scheduled days each week. There could be 18 to 24 rooms over a 3-day workweek. We have already considered scheduling by Task within a room but one house racked up 260 tasks! It was too much data for a person to organize.
The User (called a Director) is working with one client home in the form. By moving the room to one of the 'Day' listboxes, the intent is to assign all weekly, bi-weekly, monthly tasks for that room to that day.

As you can see, the program is very involved by the time this form is needed for use. Going back to my first post, I was hoping that I could do something like this pseudocode:

dim rstName as string
counter = 1
rstname = "rstTemp"
Do while counter <= RecordCount
dim (rstName & counter) as Recordset
counter = counter + 1
Loop

Again, I think the above is a fundamental conflict for Access constructs but it would have allowed me to provide a minimum of recordsets if only say 2 instead of 4 days are needed.

Your suggestions.............

Actually, I don't think it takes a lot of memory just to Dim the recordsets, so you could take a reverse approach. Dim all the recordsets up front and then when you know they are not needed destroy them.
rst1.close
Set rst1 = Nothing
etc..

You can check to see if a dimmed object has content by checking for Nothing. If the object is not dimmed it will produce an error, so, error trapping is a way to check if the recordset was dimmed.

If rst1 Is Nothing Then
rst1.close
Set rst1 = Nothing '- destroy
End If

...........have great merit and I'll add them to my tools as I move forward on this. This conversation has enabled me to think clearer on how to approach this coding!
Regards, Sorrells

 

[cmmrfrds]

It appears your design is coming together. Here is what a select case could look like, which should be okay.

Dim counter as integer
counter = rst.RecordCount

Select Case counter
Case 1
Dim rst1 as Recordset
Case 2
Dim rst1 as Recordset
Dim rst2 as Recordset
Case 3
Dim rst1 as Recordset
Dim rst2 as Recordset
Dim rst3 as Recordset
Case 4
etc.........
Case Else
Debug.Print &quot;No Records&quot;
End Select