Unbound txtbox to display memo field based on listbox record selection (1 Viewer)

[brharrii]

My table:

tblHeatTreatment
- HeatTreatmentID - PK
- HeatTreatmentDesc - Text
- HeatTreatmentDetails - Memo

My form has a listbox (lstHeatTreatments - Multi-Select disabled) that displays Heat Treatment descriptions and an unbound textbox (txtHTDetails) that I would like to have display the corresponding memo field when a description is selected from the listbox.

This is my code so far:

Private Sub lstHeatTreatments_AfterUpdate()
    Dim myConnection            As ADODB.Connection
    Dim myRecordSet             As New ADODB.Recordset
    Dim mySQL                   As String
    Dim selectedRequirementKey  As Long
 
 
    Set myConnection = CurrentProject.AccessConnection
    Set myRecordSet.ActiveConnection = myConnection
 
    selectedRequirementKey = Me.lstHeatTreatments.ItemData(Me.lstHeatTreatments.ListIndex)
 
    mySQL = "SELECT HeatTreatmentDetails FROM [tblHeatTreatment] " & _
    "WHERE [tblHeatTreatment].[HeatTreatmentID]=  " & selectedRequirementKey & "  "
    MsgBox mySQL
    myRecordSet.Open mySQL
 
    Me.txtHTDetails = myRecordSet.Fields
 
    myRecordSet.Close
    myConnection.Close
    Set myRecordSet = Nothing
    Set myConnection = Nothing
End Sub

When I run the code I get an error:

Run-time error '-2147352567 (80020009)':

The Value you entered isn't valid for this field

When I debug, it highlights:

Me.txtHTDetails = myRecordSet.Fields

Any ideas?

Thanks in advance

Bruce

 

[spikepl]

Lookup the fields collection in the docs

 

[brharrii]

Hi Spikepl, Thanks for responding to my question... I must be lacking some contextual background because I don't understand what you're asking me to do. Can you explain what you mean by "fields Collection" and "docs"? I haven't come accross these terms before, is this something I do within the VBA editor?

Thanks

 

[brharrii]

I figured out what was wrong:

i had to add a column number after .fields

I changed:

Me.txtHTDetails = myRecordSet.Fields

to

Me.txtHTDetails = myRecordSet.Fields (0)

And it seems to be doing exactly what I wanted it to now.