Build a table based on user input

[Vie]

Hi all,

I need to build a table (tblApartments), through code, based on the user's input in a series of textboxes and one option group.

Basically the aim is to build a table for a building (literally) where the columns in the table are ApartmentID, Floor, Apartment, ProjectID, and BuildingID.


The hierarchy is something like:

ProjectID = corresponds to a project name (from linked database)

BuildingID = there may be more than 1 building for a given project

Floor = floor number

Apartment = apartment letter or numeric designation

ApartmentID is an autonumber field for each unique row in tblApartments.


Pretending for the moment that all project have only one building, the user is first asked on what floor do apartments begin. This value is stored in intFloorStart.

Then the user is asked which floor apartments end on and this value is stored in intFloorEnd.

Next the user is asked how many apartments there are per floor. This value is stored in intApartments.

Finally, the user is asked to select from an option group whether the apartments are designated numerically (e.g. 32, 33, 34, etc.) or by a mix of numbers and letters (e.g. 32-A, 32-B, 32-C, etc.).

So, the user might enter:

intFloorStart = 3
intFloorEnd = 38
intApartments = 12
Designation = Mixed

What I need to do with this info is translate it into a table like:

ApartmentID Floor Apartment
1 3 A
2 3 B
3 3 C
......etc.
454 38 J
455 38 K
456 38 L

The real situation is actually a bit more complicated than this but I'm hoping to start with the least complex scenario, get it to work, and then build from there.

I'm open to any suggestions. I figure using an array might be best (though I'm still unclear on how to do this) but if anyone thinks using a class might be better, I'm open to that too.

Thanks for any and all help!
Vie

 

[PHV]

You may consider 2 nested loops, something like this:
[tt]For iFloor = intFloorStart To intFloorEnd
For iApart = 1 To intApartments
...
Next iApart
Next iFloor[/tt]
When the Designation is Mixed, you may consider to play with the Chr function.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884