Recordset manipulation

[longhair]

morning all,
have done a search but have not found any solutions to my problem.
i have a recordset returned from a query. before i open the next form (and set that forms recordset = the recordset that the query returned0 i need to make some changes to the fields in the recordset (and not make any changes to the tabels that provided the data.

Private Sub Command26_Click()
On Error GoTo Err_Command26_Click
Dim dbs As DAO.Database
Dim qdfPostDtl As DAO.QueryDef
Dim intRecCount1 As Integer
Dim i As Integer
'txtName = Me.Form.Text4
'txtName = Me.Form.Text4
Set dbs = CurrentDb
intVoucher = Me.Form.voucher

Set qdfPostDtl = dbs.QueryDefs("VendDtl")
qdfPostDtl.Parameters("pQueryVoucher") = intVoucher
'rstPostDtl declared as public at module level
Set rstPostDtl = qdfPostDtl.OpenRecordset(dbOpenDynaset)

intRecCount1 = rstPostDtl.RecordCount
If intRecCount1 > 0 Then
    'rstPostDtl2 declared as public at module level
    Set rstPostDtl2 = rstPostDtl
    rstPostDtl2.MoveFirst
    With rstPostDtl2
    For i = 1 To intRecCount1
        If .Fields(1) <> "V" Then
            .Edit
            .Fields(3) = ""
            .Fields(4) = .Fields(10)
            '.Update
            
        End If
        MsgBox .Fields(1) & " " & .Fields(3) & " " & .Fields(4)
       
    Next i
    End With
    stDocName = "VendDtl2"
    DoCmd.OpenForm stDocName, , , acFormReadOnly
Else: MsgBox "No Voucher Details Found for " & intVoucher
    rstPostDtl.Close
    Set rstPostDtl = Nothing
End If
Exit_Command26_Click:
    Exit Sub

Err_Command26_Click:
    MsgBox Err.Description
    Resume Exit_Command26_Click
    
End Sub

any suggestions?
regards,
longhair

 

[Golom]

So what's the question exactly?

Are you getting an error?

Incorrect results?

Why is .Update commented out?

 

[longhair]

Golom,
no errors.
.update is commented out because i do not want to modify the fields in the actual table(s) where the data is being pulled from.
i guees the question is:
once i retreive the records how do i manipulate them (for example putting data from some fields into others).
in the code posted above i'm attempting to do the following:
if field 1 is not 'V' (for voucher) field 3 should be blank - field 4 (Inv # / Check #) should show the data from field 10 (Check #).
although if if field 1 is 'V' it should show the correct data from the table.
so:
Field1 field2 field3 field4
V 112233 998877 01722-84364
P 112233 665544
P 112233 667890

field3 is populated for each record in the table.
field4 contains the inv# but should be replaced with the check # (field10) if the transaction is a 'P'.
the table contains ap detail transaction data and the end users would like it to resemble our MRP system as close as possible.
regards,
longhair

 

[PHV]

Why not having a query doing the job ?
Either an union of the V and the P rows, or a single one playing with IIf.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[longhair]

Golom,

Figured it out - just added an expression to my query to show the proper field.
i do have 1 further issue and think it still fits under this thread title.
i need to represent the net due (which will be a calculated field)
i have an inv-amt field and an amt-paid field.
so far i cannot get the net due to calculate correctly.
the table is like this:
type inv-amt amt-paid
V 559.68 0.00
P 0.00 559.68
P 0.00 -559.68
P 0.00 559.68
there are 2 calculated fields - Amount & Net Due they should look like the following (based on the 4 records shown above):
Amount Net Due
-559.68 0.00
559.68 559.68
-559.68 0.00
i can get the Amount to look correct with the following in my query:

amt: IIf([aptrxp].[type]="V",[aptrxp].[inv-amt],[aptrxp].[amt-paid]*-1)

however i cannot get the math for the Net Due to show correctly. it currently looks like:
Net Due
559.68
-1119.36
1119.36
-1119.36
with the code as follows (only the 1st record is correct):

Net Due: [amt]-[aptrxp].[amt-paid]

obviously incorrect. what i need to do is get the data from the first record into a variable and then use the Amount calculated field to do the math and show that.
does this make any sense?

thanks,
regards,
longhair

 

[longhair]

PHV,

it was an IIF expression thought of it just before your post.
can the Net Due calculation be done in the query? that is what i've been trying to do with no success.
regards,
longhair

 

[PHV]

I (and others) have already posted many running total queries in Forum701

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[longhair]

PHV,

will look further at my previous post. i have the header part working thanks to your help.
working on the detail now.
thanks for your help.
regards,
longhair