dynamic row creation

[shrndegruv]

Hi

I am designing a DB to track inspections in an apartment building. I have a "unit creation" form that allows the user to enter a unit number, tenant, etc. The important issue is there is a select box allowing the user to specify an efficiency, 1 BR, or 2 BR apartment.

I have a table for unit, bedroom, kitchen, bathroom, etc -- these are all linked thru the unit.number field (foriegn keys). This seems to me to be quite normalized table design.

What I would like to do is given the selection for number of BRs, create the appropriate number of rows in the Bedroom table.

How would I go about doing this? I think it needs VBA, but I am not sure...

Thanx

 

[KenHigg]

You can use ADO for this or just do an append query in the query builder and run it x number of times...

???
ken

 

[shrndegruv]

ken thanx for the response -- in my mind easier is better, so can you refer me to more info on the append query?

On my form, there is a create unit button. When that is pressed, I would like Access to automatically create all the rows in the attached tables. So it needs to create one row in the kitchen table, one in the bathroom table, and x in the bedroom table, depending on the users selection...



mike

 

[KenHigg]

In the query builder, do a new qry, then simply select 'Append' query. Select the 'bedroom' table as the table to append to. Then on the top row of the query builder grid, put something like exp1: forms!frmMain!myunitnumber and have it put this data in the in the unit number fld in the bedroom table. When you execute this qry from the main form, it should append a new row to the bedroom table and put the current unit unit id number from the main form in the unit number fld in the bedroom table.

???
kh

 

[shrndegruv]

Ken

thank you again -- the problem with that approach is it needs to create a number of rows appropriate to the particular unit. So if a user selects the 2 BR option, 2 rows in the bedroom table need to be created. If user selects efficiency, no rows are created....

 

[KenHigg]

Then when you do a button (or whatever), to create the rows you would:

(pseudo code)

select case me.unittype
case '1bdrm'
docmd.runquery "appAddBedrooms"
case '2bdrm'
docmd.runquery "appAddBedrooms"
docmd.runquery "appAddBedrooms"
case '3bdrm'
docmd.runquery "appAddBedrooms"
docmd.runquery "appAddBedrooms"
docmd.runquery "appAddBedrooms"
end select

???
ken

 

[shrndegruv]

Ken

so are you saying that I need to use VBA code to do this? I would like to use a macro if possible, but I dont know if there is a way to say " do this append query a number of times equal to this form entry object value...."

mike

 

[Rich]

Are you saying that you have separate tables for each room, if so your design is not normalised

 

[shrndegruv]

Rich

I do *not* have seperate table for each room.

I have a Unit table, where each row represents one unit in the building. I then have a Bedroom, Bathroom, Kitchen, General tables, where there is one row in each corresponding to each row in Unit, except Bedroom, which could have 0,1, or 2 rows for each row in Unit.

 

[Rich]

OK then, you have separate tables for Bedroom,Bathroom, Kitchen?

 

[KenHigg]

Ken

so are you saying that I need to use VBA code to do this? I would like to use a macro if possible, but I dont know if there is a way to say " do this append query a number of times equal to this form entry object value...."

mike

I would suggest code. I've never seen a conditional looping macro...

kh

 

[shrndegruv]

Rich,

thats what I said..

mike

 

[Rich]

Your design is not normalised

 

[shrndegruv]

Rich

the point of this post is to garner help on inserting rows in other tables from a form dealing with a seperate table. The design is not completely normalized; I just mentioned it to indicate I had multiple tables working together, or to say it another way, I did not just design one big table for all the info this company wants to track...

mike

 

[Rich]

Would it not be better to normalise it first though? it would certainly make your life a lot easier

 

[KenHigg]

If the information about each type of room is close to the same, then maybe you should just have one table. However, if you need to store information such as refrigerator size or type of oven for the kitchen, then I don't know if I would put it in the same table as the bedroom information where you may want say, closet size info.

However - A room is a room is a room. So really, all rooms would have common attributes, say size and maybe color? And the refrigerator size and type of oven would then be an attribute of the unit. (?) Guess it really depends on your business requirements...

Seems you could easily over normalize this db...

???
kh

 

[shrndegruv]

rich

what are you suggesting? Be specific. How would it help?

mike

 

[shrndegruv]

ken

I am happy with my table design. What I need to do is figure out how to create rows in the tables that depend on the original Unit.

I do not know how to do that yet, mainly because I know nothing about VBA (Im a linux guy). Specifically, how do I pass the values from the option group (indicating number of BRs) to a VBA procedure that then creates rows in the Bedroom table. Should I write the sql and execute it from VBA, or should I just run a canned append query? If so, how do I pass the append query the value to insert...?

 

[KenHigg]

Did the post #4 of this thread help any? What parts were vague?

kh

 

[shrndegruv]

the psuedo code you posted was helpful -- the real problem here is that using VB is new to me. I am a pretty good programmer so I thought I could just jump right in.

Let me tell you where I am.

I have a form to create units. This form allows the user to enter all the units in the building. It needs to create rows in all the tables I have described before for each unit created. The Bedroom table is the only table that will have multiple rows for a unit, as apartments in this building have 0,1,2 bedrooms.

I have associated the After Insert event on the CreateUnit form with a Module. This module contains the code that needs to be written. I dont know how to pass the NumBedrooms value to the sub that creates the rows in the tables. I also dont know how to actually create those rows -- do I use a canned append query (for each table) or should I execute some SQL from within my VBA code?

I looked for tutorials on the web but couldnt find any...
thanx for your patience Ken...

mike