Form Prefill-> 2 rows?

[GUIDO22]

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

 

[gromit]

Hi change your If Not rs.EOF to a while statement...

While NOT rs.EOF
    rs.Edit
    <fill appropriate fields>
    rs.Update
wend

hth,

- g

 

[GUIDO22]

Hi change your If Not rs.EOF to a while statement...

While NOT rs.EOF
    rs.Edit
    <fill appropriate fields>
    rs.Update
wend

hth,

- g

I am afraid, this is not what I need - I am more than familiar with While/Do Loop constructs!

I need to know how to 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!

 

[gromit]

Sorry, my example is missing something. If you have a multi-record recordset then the following moves through each of the records

    rs.Edit
    <fill appropriate fields>
    rs.Update
    rs.MoveNext

It is also possible to declare a bookmark in a recordset, but the documentation probably explains this better than I can.

- g

 

[GUIDO22]

I understand what you have sugested but this is not the problem I have.....

When the user selects a contract from the drop down box and focus moves from the combo box - the lostfocus() event retrieves the appropriate recordset for the contract selected. Ordinarily this will only have one record and as such, it is simple to update the remaining rows in the subform with the data from the single record in the recordset.

My problem arises when there is more than one record in the source recordset for the selected contract. i.e. the subform will need to populate with not only the basic invoice record info. but also the next record in the subform to show say, 'additional charges' that have been incurred for the contract.

With one record - when the combo selection is made the physical subform record pointer knows to prefill the fields in the row with the data. If I then move the RECORDSETpointer (rs.movenext()) to the next record in the recordset (as you have detailed), in order to prefill the appropriate fields in the NEXT subform row - I need to move the physical record pointer (in the subform view) to the next line and then fill these fields with the second row data.

Is this clearer?

 

[gromit]

Okay, that's beyond me. Sorry for the irrelevant posts!

 

[neileg]

I'm not sure I understand the problem fully, but I would have been tackling this with the subform based on a saved querydef, not the VBA that you are using. This would surely return all records that are related to the order without any need to step through the recordset.