GUIDO22
Registered User.
- Local time
- Today, 22:23
- Joined
- Nov 2, 2003
- Messages
- 515
I have an invoice form - selecting a unique CONTRACT number for a completed ORDER, prefills the subsequent fields in that row with the contract information / price etc. (see below)
The VBA code retrieves the appropriate recordset and prefills the controls with the appropriate values.
If Me.NewRecord And Not IsNull(Me.Controls!Job) And Me.Controls!Job <> 0 Then
With Me
Set rs = CurrentDb.OpenRecordset("SELECT DRG.DrgNo, ORDERDETAILS.Price, DRG_DESCRIPTIONS.Description " & " FROM DRG_DESCRIPTIONS INNER JOIN (DRG INNER JOIN ORDERDETAILS ON DRG.ID = " & _
" ORDERDETAILS.DrgId) ON DRG_DESCRIPTIONS.ID = DRG.DescriptionId WHERE ORDERDETAILS.JobNumber= " & Controls!Job)
If Not rs.EOF Then
Controls!DrgNo = rs![DrgNo]
Controls!Desc = rs![Description]
Controls!UnitPrice = rs![Price]
End If
End With
However, a contract may have additional costs incurred and as a result there may be additional invoice line items to consider for the invoice totals. If the above recordset contains more than one record for the selected CONTRACT (ie. there are additinoal charges to cost) - how can I prefill the contents of more than one sub-form row ie. is it possible to force the current record pointer to the next record and prefill with the additional charge info?
Thanks
Guido
The VBA code retrieves the appropriate recordset and prefills the controls with the appropriate values.
If Me.NewRecord And Not IsNull(Me.Controls!Job) And Me.Controls!Job <> 0 Then
With Me
Set rs = CurrentDb.OpenRecordset("SELECT DRG.DrgNo, ORDERDETAILS.Price, DRG_DESCRIPTIONS.Description " & " FROM DRG_DESCRIPTIONS INNER JOIN (DRG INNER JOIN ORDERDETAILS ON DRG.ID = " & _
" ORDERDETAILS.DrgId) ON DRG_DESCRIPTIONS.ID = DRG.DescriptionId WHERE ORDERDETAILS.JobNumber= " & Controls!Job)
If Not rs.EOF Then
Controls!DrgNo = rs![DrgNo]
Controls!Desc = rs![Description]
Controls!UnitPrice = rs![Price]
End If
End With
However, a contract may have additional costs incurred and as a result there may be additional invoice line items to consider for the invoice totals. If the above recordset contains more than one record for the selected CONTRACT (ie. there are additinoal charges to cost) - how can I prefill the contents of more than one sub-form row ie. is it possible to force the current record pointer to the next record and prefill with the additional charge info?
Thanks
Guido