Number of rows in a subform by combo box

[outhwaik]

Ok, I'll try to explain this as best as I can... bear with me!

This is the situation. I have a series of drawings (identified by a drawing number) and each drawing can have a number of bars across it. There will usually be between 0 and 9 bars on the drawing, but I can't rule out there being more. For each bar, there are other pieces of information, but thats not that relevant to my problem.

So far: Drawings (by drawing number) with bars (0 to 9 usually).

Now, I want to include a subform on the main drawings form displaying the information about the bars for that drawing.

So far I have a table for the drawings, and a table for the bars, with a one-to-many relationship (one drawing, many bars) by DrawingNo.

Ideally, what I would like is to be able to select the number of bars required for the drawing in a combo box, and then the correct number of rows appear in my subform. Similarly, if the number of bars is changed in the combo box, I'd like the number of rows in the subform to reflect this (deleting entries or adding new ones as required).

So: select number of bars in combo box, correct number of rows appear in subform.

I've got the combo box and subform set up ok, just not the functionality to get the right number of rows dependant on the combo box.

I'd really appreciate any help on this, sorry for the long post!

Thanks

outhwaik

[rpeare]

You would have to do this with VB code but I have a couple of questions first.

You mentioned adding and deleting rows, is there data associated with these rows?

let's say you change the number of rows from 9 to 4, how are you determining which rows are being deleted? If there is additional information for each row you are entering you stand to delete information you need if you just perform a delete.

Let's say you want to add 2 rows to an existing 4 row drawing, do those two rows necessarily get tacked on at the end or is there going to be a need to insert records prior to the end of the existing records?

I'm asking because I am always very hesitant to delete items from my databases because once they are gone, they are gone for good with no hope of recovery unless you have daily/hourly backups. I usually add a 'void' type option then exclude any record with the void checked off.

[outhwaik]

Pretty much the only time rows will be added/deleted is when the record is first created - the number of bars on a drawing will not change once added to the database, so I'm not worried about losing information.

One of the pieces of information for a bar will be position, eg. if there are 4 bars, the bars will have position 1, 2, 3 and 4. If the number of bars was then changed to 2, positions 3 and 4 would be deleted. Similarly, if changing from 4 to 9 bars, positions 5 to 9 would be added on the end.

I'm fine with using VB, I've been learning as I go along and I've understood all of the stuff I've done so far

[rpeare]

Let's assume you have these tables:

Tbl_Maps
MapID MapName MapLines
1     Map A   9
2     Map B   7
3     Map C   5

Tbl_MapLines
LineID (autonumber primary key)
MapID (inherited from tbl_maps)
LineNumber (starts at 1, ends at maplines)

The code you would use (either by creating a module, addiing it to the ON CLICK EVENT of a button, etc) is this

dim db as database
dim sSQL as string
dim rst as recordset
dim iMapID as long
dim iTotalLines as integer
dim iCurrLine as integer

set db = currentdb
ssql = "SELECT * FROM Tbl_Maps" 
'you may enter a criteria if you are doing this for one map at a time 
'when running this code you also want to make sure you are only running 
'for maps that have no lines generated in the Tbl_MapLines table
set rst = db.openrecordset ssql

iCurrline = 1
do while rst.eof <> true
    iMapID = rst.fields("MapID")
         iTotalLines = rst.fields("maplines")
    for  i = 1 to itotallines
        ssql = "INSERT INTO Tbl_MapLines (MapID, LineNumber) VALUES (imapid, i)
        db.execute ssql
        i = i + 1
    next i
    rst.movenext
loop
rst.close
set db = nothing

NOTE: I didn't test this fully but if I've missed something post back

[outhwaik]

Sorry I've not posted back until now - I've been away and haven't had chance to test the code out until now!

I've copied the code across to run when a button is pressed, however the line

set rst = db.OpenRecordset ssql

throws up a syntax error when it trys to compile. It's probably just a small alteration that's needed, but using the Database and Recordset types isn't something I'm familiar with, so I'm not sure what needs adjusting!

Hope you can help me out - I know it's been a while since you looked at this thread!

[rpeare]

You have to substitute in your table name, my test table was tbl_Maps you have to put in your own table/field/database names to make the code work.

[outhwaik]

I've created a completely separate database to run this example in, so I don't get confused for exactly that reason! All names etc. are exactly as written in your example.

[rpeare]

Check the references in each database then, from your VBA window it's tools>references. Look at the ones I'm using vs the ones that are in your version of the database and add references until you find the right one.

[outhwaik]

Ok, the references I've got already are Visual Basic for Applications, Microsoft Access 11.0 Object Library, OLE Automation, Microsoft DAO 3.6 Object Library, and Microsoft ActiveX Data Objects 2.1 Library. I've tried just going down and adding them until the line stops causing a problem, but adding them all just causes name conflicts. Have you got the example to hand, and could tell me what other references are needed?

[rpeare]

Add only 1 reference at a time not the whole bunch and see if the code will run I don't know off the top of my head which ones you needs specifically. You usually only get the conflict between ADO and DAO references (at least the ones I've run in to). Alternately you can enable only the references I have see that it runs then start adding in the ones that you currently have to see which one causes the conflict.

[outhwaik]

Alternately you can enable only the references I have see that it runs

Where have you put which ones you're using? Sorry if I'm being a bit slow here!

[rpeare]

Same place I mentioned before, open your VBA scripting window, go to tools> references it should list all the references you're using at the top of the window.