check my code - adding child records to sub form

[jb33]

So thanks to some help from a couple of you I'm this far.
I have a jobs summary form/subform. Status records are
children of the jobs records. And there are roughly 20
phases to each job for which status is tracked. So I want
to press a button and automatically create 20 status
records in the subform - each a different phase from the
phases table.

'****************************

Dim dbs As DAO.Database
Dim qdf As QueryDef
'will contain phase records

Dim strSQl As String
Dim status As DAO.Recordset
'records to be added

Dim job_id As Long
'need to pass job_id from my form
Dim ws As Workspace
'thought this might be required to access form items


Set ws = DBEngine.Workspaces(0)
Set job_id = Forms!Job Summary!job_id
Set strSQL="SELECT * FROM phases;"
Set dbs = CurrentDb()
Set status = dbsSP.OpenRecordset("status", dbOpenDynaset)
Set qdf = dbs.CreateQueryDef("phases", strSQL)

'Now i think I have my Parent Record ID (job_id),
'control for adding records to the status table,
'and an array object containing all my phases (qdf).
'Or have i not actually run qdf yet?
'Need to step through qdf

counter=0
Do
status.AddNew
status![job_id] = job_id
status![phase_id] = qdf.[counter].[0]
'trying to assign the first element at 'counter'
'in the array.
Loop Until (qdf.[size]) = counter
'how do you return the array size?


Am I getting close?
tia, jb

 

[John Vinson]

So thanks to some help from a couple of you I'm this far.
I have a jobs summary form/subform. Status records are
children of the jobs records. And there are roughly 20
phases to each job for which status is tracked. So I want
to press a button and automatically create 20 status
records in the subform - each a different phase from the
phases table.

Such mostly-empty "placeholder" records are usually not a very good
idea: it's *very* easy to overlook updating one of them! Could you not
instead create a new status record as each job attains that status,
filling in the Job_ID using the Master/Child Link Field property of
the subform?

If you do want to create twenty empty records to start with, it's
easier than you're making it. Just create a single Append query:

INSERT INTO Status (Job_ID, Phase, <other phase fields>)
SELECT [Forms]![yourformname]![Job_ID], Phases.Phase, Phases.This,
Phases.That FROM Phase;

and execute it using something as simple as a one-line macro call, or
at the most

Private Sub <whatever event you use>
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.Querydefs("AppendStatus")
qd.Execute dbFailOnError
Set qd = Nothing
Proc_Exit: Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in <procedurename>:" & vbCrLf & _
Err.Description
Resume Proc_Exit
End Sub

 

[Guest]

john, i like the simplicity of your solution. and despite
your good advice I have to create the blank records at
the users request. This is really just a data entry form
to duplicate items already marked off on a field check
list. So its a time saving step not to have to create
each status record

What I'm missing in your solution, or it may be it is -
where is it stepping through the phases table so that
each new status record is prepopulated with a different
phase?

-----Original Message-----

So thanks to some help from a couple of you I'm this far.
I have a jobs summary form/subform. Status records are
children of the jobs records. And there are roughly 20
phases to each job for which status is tracked. So I want
to press a button and automatically create 20 status
records in the subform - each a different phase from the
phases table.

Such mostly-empty "placeholder" records are usually not a very good
idea: it's *very* easy to overlook updating one of them! Could you not
instead create a new status record as each job attains that status,
filling in the Job_ID using the Master/Child Link Field property of
the subform?

If you do want to create twenty empty records to start with, it's
easier than you're making it. Just create a single Append query:

INSERT INTO Status (Job_ID, Phase, <other phase fields>)
SELECT [Forms]![yourformname]![Job_ID], Phases.Phase, Phases.This,
Phases.That FROM Phase;

and execute it using something as simple as a one-line macro call, or
at the most

Private Sub <whatever event you use>
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.Querydefs("AppendStatus")
qd.Execute dbFailOnError
Set qd = Nothing
Proc_Exit: Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in <procedurename>:" & vbCrLf & _
Err.Description
Resume Proc_Exit
End Sub



.

 

[Guest]

-----Original Message-----

So thanks to some help from a couple of you I'm this far.
I have a jobs summary form/subform. Status records are
children of the jobs records. And there are roughly 20
phases to each job for which status is tracked. So I want
to press a button and automatically create 20 status
records in the subform - each a different phase from the
phases table.

Such mostly-empty "placeholder" records are usually not a very good
idea: it's *very* easy to overlook updating one of them! Could you not
instead create a new status record as each job attains that status,
filling in the Job_ID using the Master/Child Link Field property of
the subform?

If you do want to create twenty empty records to start with, it's
easier than you're making it. Just create a single Append query:

INSERT INTO Status (Job_ID, Phase, <other phase fields>)
SELECT [Forms]![yourformname]![Job_ID], Phases.Phase, Phases.This,
Phases.That FROM Phase;

and execute it using something as simple as a one-line macro call, or
at the most

Private Sub <whatever event you use>
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.Querydefs("AppendStatus")
qd.Execute dbFailOnError
Set qd = Nothing
Proc_Exit: Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in <procedurename>:" & vbCrLf & _
Err.Description
Resume Proc_Exit
End Sub



.

 

[John Vinson]

What I'm missing in your solution, or it may be it is -
where is it stepping through the phases table so that
each new status record is prepopulated with a different
phase?

You're thinking procedurally. Access - and relational databases - ARE
NOT PROCEDURAL! They work on "sets" of data; an Append Query based on
the Phases table will append every single record in the Phases table
in a single operation. No stepping (on your part - the Jet database
engine takes care of it for you) is needed.