CODEWITHSUNDEEP
formsvbams-accesstextbox
safron6
safron6

Reputation: 149

MS Access: Link listbox to textbox

I have a textbox and listbox, one is for entering a new topic in a help form while the other looks up those new topics. I would like to be able to present the finished topics in both the textbox and listbox simultaneously to edit or lookup as well write new records in the help form. The listbox provides functionality to view which records there are now. I find if I put nothing in and I go to a new record the prev/next buttons will stop working, maybe there is a control I need to add to keep it from freezing or to refresh? Normally I press esc to get out of a new record edit and return to others but that does not work as usual.

Or how else may I point to the listbox's current record source?

I currently have this code:

Private Sub List35_AfterUpdate()
  DoCmd.GoToRecord acDataForm, "Help Form_Editor2", acGoTo, Me.List35.ListIndex + 1
    Me.List35 = Me.List35.Column(0, Form.CurrentRecord - 1)
    Dim index As Integer
    index = Form.CurrentRecord - 1
    Me.Text53 = Me.List35.Column(Me.List35.ListIndex + 1, index)
End Sub

I keep getting some of the items to read but others are null. I have about 8 items in the source table... what is going wrong? Why would there be nulls?

Another issue after getting this updated. When the code is setup the recordset starts at new when I allow additions and edits on the form. The code displays the list item as it should but the other items will not activate from the requeried listbox item. What might correct this issue?

Private Sub List35_AfterUpdate()
    Dim myTitle As String
    With Me.List35
        If .ListIndex > -1 Then
            'Use this one if you are using bound column
            myTitle = .Column(1, Form.CurrentRecord)

            'use this if you want something other than the bound column
            'and you have more than one column in the list (hidden or not)
            'nId = .Column(1, .ListIndex)

           Me.RecordSource = "SELECT * FROM FormsHelpTable WHERE HelpTitle = '" & myTitle & "'"
            Me.Text53.Value = myTitle
        Else
           Me.RecordSource = "SELECT * FROM FormsHelpTable WHERE HelpTitle IS NULL"
            'Me.Text53.Value = "(New)"
        End If

    End With

   Me.Requery
End Sub

Upvotes: 0

Views: 2048

Answers (1)

Don Jewett
Don Jewett

Reputation: 1977

This checks for ListIndex. It will be -1 if you don't have anything selected.

Private Sub List35_AfterUpdate()
    Dim index As Integer

    With Me.List35
        If .ListIndex > -1 Then
            DoCmd.GoToRecord acDataForm, "Help Form_Editor2", acGoTo, .ListIndex + 1
            .Value = .Column(0, Form.CurrentRecord - 1)
            index = Form.CurrentRecord - 1
            Me.Text53 = .Column(.ListIndex + 1, index)
        End If
    End With

End Sub

I'm not sure what all your code is trying to do, so I didn't make any other adjustments other than to reduce all references to List35 to a single With statement.

I normally do something like this:

Private Sub List35_AfterUpdate()
    Dim nId As Long
    With Me.List35
        If .ListIndex > -1 Then                
            'Use this one if you are using bound column
            nId = .Value 

            'use this if you want something other than the bound column
            'and you have more than one column in the list (hidden or not)
            'nId = .Column(1, .ListIndex)

            Me.RecordSource = "SELECT * FROM TableName WHERE Id = " & nId
        Else
            Me.RecordSource = "SELECT * FROM TableName WHERE Id IS NULL"
        End If

    End With

    Me.Requery

End Sub

Upvotes: 1

Related Questions