code/query from form Access 2000

[jb33]

say, I'm getting into this access project from a
php/mySQL background so I'm banging my head over simple
things I can't figure out how to do. wondering if you
could help me out on this one.

I've got a form/sub-form set up as jobs/status. Every job
is made up of 20 phases (a third table) for which I am
reporting status.

Jobs
1....phase.....phase status,
....
20...phase.....phase status,

so, when I create a new job - or optionally press a
button, I'd like to run a script to add 20 status
records, each populated with a different phase from the
phases table.

By default, only one blank status record is present so
you have to add 20 records in the some form and select
all the proper phases before you can get going.

In case the phase table changes, I'd like to first
populate an array with items from the phase table. Then,
for each item in the phase array build a status record
with the phase_id from this array element, and the
appropriate job_id.

I think I launch an event and do all this in VBscript but
I'm not real clear on - well vbScript and how its gonna
interact with the Access data here.

Any pointers would be much appreciated. Probably the
first point is I ought to be asking this in a VBscript
forum, eh?

tia,jb

 

[Guest]

Try this code.. it does the same as what you are talking
about, but mine does invoices how many months I type in,
it make that many invoices, and the month changes on the
start day of the month..

Private Sub Create_invoices_Click()
On Error GoTo Err_Create_invoices_Click

Dim dbsSP As DAO.Database
Dim pts As DAO.Recordset
Dim ptsPM As DAO.Recordset
Dim dtDate As Date
Dim lngID As Long
Dim lngCID As Long

Dim MP As Integer
'link for two tables.
Dim lngPID As Long
Dim MTP As Integer
Dim HMM As Integer
Dim counter As Integer
Dim fPreviousRecs As Boolean

If IsNull(Me![StartDate]) Then
MsgBox "You must Specify a beginning date before
you can proceed with generating Invoices."
DoCmd.GoToControl "StartDate"
Exit Sub
Else
If IsNull(Me![how many months]) Then
MsgBox "You need to specify how many months
before you can generate Invoices."
DoCmd.GoToControl "How many Months"
Exit Sub
Else
If IsNull(Me![Down Payment]) Or IsNull(Me!
[Monthly Payments]) Then
MsgBox "You need to specify a dallor
amount before you can generate Invoices."
DoCmd.GoToControl "Down Payment"
Exit Sub
End If
End If
End If

Set dbsSP = CurrentDb()
Set pts = dbsSP.OpenRecordset("Payments",
dbOpenDynaset)
Set ptsPM = dbsSP.OpenRecordset("payment made")

dtDate = Me![StartDate]
lngID = Me![Payment Information ID]
lngCID = Me![Client ID]

'adds an one ivoice to the table.
pts.AddNew
pts![StartingDate] = dtDate
pts![Payment mgr ID] = lngID
pts![Client ID] = lngCID
'gets the link the two tables together.
lngPID = pts![payments ID]
pts.Update
fPreviousRecs = False

MP = Me![Monthly Payments]
MTP = Me![Down Payment]

'adds the monthly payment content to the invoice
one time.
ptsPM.AddNew
ptsPM![Unit Price] = MP
ptsPM![Client ID] = lngID
'links the two tables together.
ptsPM![payments ID] = lngPID
ptsPM.Update

'adds the Down payment to same invoice one
time.
ptsPM.AddNew
ptsPM![Unit Price] = MTP
ptsPM![Client ID] = lngID
ptsPM![payments ID] = lngPID
ptsPM.Update

'Loops the event the number of times the payment term
is.
HMM = Me![how many months]
counter = 0

Do
'creates invoice for monthly payments for the
amount of payment terms - 1.

counter = counter + 1

pts.AddNew
pts![StartingDate] = DateAdd("m", counter, dtDate)
pts![Payment mgr ID] = lngID
pts![Client ID] = lngCID
'gets the link the two tables together.
lngPID = pts![payments ID]
pts.Update
fPreviousRecs = False

ptsPM.AddNew
ptsPM![Unit Price] = MP
ptsPM![Client ID] = lngID
'links the two tables together.
ptsPM![payments ID] = lngPID
ptsPM.Update

Loop Until (HMM - 1) = counter

pts.Close
ptsPM.Close
dbsSP.Close
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_Create_invoices_Click:
Exit Sub

Err_Create_invoices_Click:
MsgBox Err.Description
Resume Exit_Create_invoices_Click

End Sub

 

[Kelvin]

See below:

say, I'm getting into this access project from a
php/mySQL background so I'm banging my head over simple
things I can't figure out how to do. wondering if you
could help me out on this one.

I've got a form/sub-form set up as jobs/status. Every job
is made up of 20 phases (a third table) for which I am
reporting status.

Jobs
1....phase.....phase status,
...
20...phase.....phase status,

so, when I create a new job - or optionally press a
button, I'd like to run a script to add 20 status
records, each populated with a different phase from the
phases table.

By default, only one blank status record is present so
you have to add 20 records in the some form and select
all the proper phases before you can get going.

I don't like to use code if I can stay away from it so here is what I would
do. Create a new table, I'll call it tblBlankJobs. The fields should be
Phase, PhaseStatus. Create 20 records just the way you would like the
default 20 records to show. Then on your form have the button append
tblBlankJobs to the table linked to the subform.

In case the phase table changes, I'd like to first
populate an array with items from the phase table. Then,
for each item in the phase array build a status record
with the phase_id from this array element, and the
appropriate job_id.

When you paste the info paste the JobID along with it based on the JobID in
the main form.

I think I launch an event and do all this in VBscript but
I'm not real clear on - well vbScript and how its gonna
interact with the Access data here.

I don't know VB so I can't comment on this.

Any pointers would be much appreciated. Probably the
first point is I ought to be asking this in a VBscript
forum, eh?

tia,jb

You don't really need VB to do this. The above should work without much
coding, or you can use the advice from Andrew and use pure VBA code.

Kelvin