get value of a field in a record set

[CarWashMike]

I would like to set the value of a field on a form to the the value of a
field in the last record of a recordset the recordset is a query whose
parameter is set from a control on the form. I need to know how to refer to
the field in the last record of the recordset.

 

[PJFry]

Can you post how you are creating your recordset?

 

[CarWashMike]

This is the code I used to get the recordset after the meter reading is
changed I want to put the last visit note in the trybox field. At the moment
the record count goes in fine but I don't knwo how to get the value of the
last VisitNote field in the recordset in there.


Private Sub meter_reading_AfterUpdate()

Dim rs4 As DAO.Recordset
Dim qry4 As QueryDef
Set qry4 = CurrentDb.QueryDefs("qryVisitNote")
qry4.Parameters("param").Value = Me.counter.Value
Set rs4 = qry4.OpenRecordset
If rs4.RecordCount > 1 Then
rs4.MoveLast
Me.trybox.Value = rs4.RecordCount

End If

End Sub

 

[CarWashMike]

Thankyou for youe help

I copied this into the code but I dont understand, the field I want from the
recordset is called VisitNote and I want to put the value in the field called
trybox on my main form so I entered

With Me.RecordsetClone
..MoveLast
Me.trybox = !VisitNote
End With

I get the error item not found when I hover over the visit note in the debug
window

Thanks Mike

 

[CarWashMike]

I would like to set the value of a field on a form to the the value of a
field in the last record of a recordset the recordset is a query whose
parameter is set from a control on the form. I need to know how to refer to
the field in the last record of the recordset.

 

[PJFry]

Give me some more information about:
qry4.Parameters("param").Value = Me.counter.Value

What kind of value should it return?

This could also be an error with qryVisitNote. Have you checked to see that
it is working properly?

 

[CarWashMike]

Thats fantastic it works thank you so much its sometimes impossible to find
these things out on your own Thanks again

 

[CarWashMike]

Hi PJ thanks for your help, Marsh fixed the problem but anyway here is the
backgound info you requested.
I have two tables, one is like a summary table and the other is the detail
table. The main table is where we record requested for repairs and the detail
table is where we want to store the details of each repair attempt. At the
moment we overwrite the information until we clear the call but when you go
back 2 months later you cant see the progression through the job. There may
be only one detail record for each one in the main table but sometimes there
are many (it depends how many attempts we need to fix the problem) what I
wanted to do was store the last detail record in the field in the main table
as this is the field all the reports etc look at, basically its the final
fix.

The code for the recordset opens a parameter query the parameter (param) is
the primary key of the table the main form is based on. This links all the
notes from the one to many relationship I may have gone about it all wrong
any suggestions / help would be terrific