How to display certain number of rows on form...

[pablavo]

Hi there, hope everyone's well.

I'm perplexed by a problem with a requirement.

the db I'm developing is for projects that are reported on each year. there are 4 projects. Each project has 7 milestones. so tblProject has a one to many relationship with tblMilestone.

All the names for each project are different, for example, project1's first milestones name does not have the same name as project2's.

tblMilestone has an MilestoneID(PK), MilestoneNameID(which is the FK of the lookup table that lists the names of the milstones), ProjectID(FK) and 6 fields i.e. date, comment.


What I'm trying to do is to create a form that has the 7 milestone rows automatically on the form. The user will pick a project from a drop down list and the 7 seven rows MilestoneNameID field will automatically populate the 7 rows.

This is so the user doesn't have to add each milestone name from each rows drop down e.g.

Does anyone know of the best way to achieve this?

Hope someone can help.

Thanks

 

[wazz]

you'll want to select all the MilestoneIDs from tblMilestone where ProjectID = yourcombo and put the results into an array (0 to 6). then create a sql append/add stmt (or "with recordset") that loops six times (for i = 0 to 6) adding records and setting field values, including the MilestoneID = YourArray(i). hth.

 

[pablavo]

Great, Thanks Wazz, this'll be my first use of a loop statement. Don't know how I avoided it til now.

I'll give it a go.




paul

 

[wazz]

no prob. it's a pretty rough sketch but think it should work. something to change would be the loop-count. you want one record for each value in the array, so instead of 'for i = 0 to 6' you could (should) use the number of elements in the array, 'for i = 0 to UBound(yourarray)'. something like that.