Need Subform to automatically display 20 rows based on an ID field

[Brian]

Thanks in advance

I am trying to set up a tracking database that needs to track several stages.
Each stage captures the same type of information:
Stage1 = StaffName, StartDate, EndDate, Comment
Stage2 = StaffName, StartDate, EndDate, Comment

With help from this group, I have several tables:
tblStudies:
StudyID (PK)
StudyName

tblStaff:
StaffID
FName (PK)
LName (PK)

tblStages
StageID (PK) (this will be 1 -20)
StageName

tblStudyStages
StudyStageID
StudyID (FK)
StageID (FK)
StaffID
StartDate
EndDate
Comment

My question is. Is there a simple way to set up a form so that each time a
NEW record is created it automatically displays The StudyInfo and All 20
Stages (with entry available for the Dates, comments etc,, for each stage).

I can get the form to display a subform that displays a single row that
allows me to enter The StageID which in turns displays the correct StageName
and allows me to enter the rest of the data for that stage (StaffName,
StartDate, EndDate, Comment). I can then tab through these and get to the
next line to repaet the above for Stage2.

I need the form to automatically create the 20 stages within the subform
without the user having to enter the Stage Info.

Do I have to create 20 individual subforms in the main form for each stage?

Any Ideas????

Thanks

 

[Jeff Boyce]

Brian

Are you saying you want to create 20 "blank" records when you create a new
"master"? This usually isn't necessary or a good idea.

What business need are you trying to address by doing this?

Jeff Boyce
<Access MVP>

 

[Brian]

Hi Jeff,

I am trying to create a user form so that information on the various stages
of a study/project can be entered.

Each study has the same 20 stages and for each stage the following is
entered (stagename, contact, startdate, enddate, comment).

Stage 1: Study Initiated By.........
Stage 2: Initial Study review

and so on

It will be easier for the users if all 20 stages are displayed on the form
at the same time (stages are not completed in order)

Not sure if that is any clearer.

All suggestions gratefully recieved.

 

[Jeff Boyce]

Brian

OK, I can see how that might help, on a form.

An approach I've used before is to create a query that generates a list of
all available x's (in my case, it was products that could be ordered) for a
given "owner". I did this using a Cartesian Product query, in which I added
the "owner" table and the Product table, did NOT join them, selected the
"owner", where OwnerID = xxxx, and displayed OwnerID (and other owner info),
plus ProductID (and other product info).

This generates a list of Owner X Product (for a single owner). This gave me
a set of "records" I could use for display in a form. But when it came time
to actually save something, I only saved those Owner X Product combinations
that had entries.

Good luck!

Jeff Boyce
<Access MVP>

 

[Brian]

Jeff,

Thanks again for the reply.

I managed to track down an example on the internet that used some VB code to
create the records (code linked to After Insert on the main form). Seems to
be working.

I appreciate all your help.

Brian

 

[Meirav]

Brian,

Can you please share how you did it? I need almost the same thing and
cannot find the answer.

Thanks,

 

[Brian]

Hi,

I found an example that could be downloaded at:

http://www.techonthenet.com/access/forms/subform_data.htm

This is the code:
Private Sub Form_AfterInsert()
Dim db As Database
Dim LSQL As String
Dim LCntr As Integer

'Establish connection to current database
Set db = CurrentDb()

LCntr = 1

'Create SQL to insert item_numbers 1 to 24
Do Until LCntr > 22

LSQL = "insert into inventory_details (StudyNum, StageID)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & StudyNum & "', " & LCntr & ")"

'Perform SQL
db.Execute LSQL

'Increment counter variable
LCntr = LCntr + 1
Loop

'Requery subform that new records are listing

inventory_details_Subform.Requery

End Sub

Now when I typed this into my original database I kept getting an error on
the 'Dim db As Database' line. (My VB experience is limited).

As my database is fairly simple I have just modified the one I downloaded as
that one works. I'm working on why the code crashes when I just type in the
code.

Hope this helps

Brian