Edit listbox from subform.

[Klingest]

Hello people

I have been searching for this, but as I am a newbee in access, I find it difficult to understand the solutions given for other peoples projekts. So I will ask the question as if it was related to my problem only.

I have mainform with a listbox, and subform with some textfields. The listbox shows the data from a table named "tblUsers".

I want to make a button that opens the subform (I know how to open the subform with a button), and displays the data from a selected record in the listbox, in the textfields of the subform.

How can i do that?

 

[Ranman256]

I use this exact form for users....
the subform is connected to the master form.
the master form could show all records of tUsers (single record detail)
The listbox shows all users

user clicks the list box person,
filter the master form,
since subform is linked to masterform, you get data for that 1 person.

sub lstbox_afterupdate()
  me.filter = "[userID]=" & lstBox
  me.filterON = true
end sub

the subform should filter along with the master form.

 

[arnelgp]

you can filter the Subform or change its RecordSource.
depends on what flavour you like.

my flavour, change Recordsource, since
when you use the Filter technique and you
want to filter by form, the filter that you
put is removed.

Put this code on the AfterEvent of your Listbox
remember to replace List1 with the name of your listbox,
subFormName with the name of your subform.

Private Sub List1_AfterUpdate()
    Dim var As Variant
    Dim varCriteria As Variant
    Dim strSQL As String
    Dim nPos As String
    For Each var In Me.List1.ItemsSelected
        ' add selected items in listbox to string
        varCriteria = varCriteria & var & ","
    Next
    If varCriteria & "" <> "" Then
        ' Criteria is not empty string
        ' remove extra "," from string Criteria
        varCriteria = Left(varCriteria, Len(varCriteria) - 1)
        ' get subform RecordSource
        strSQL = Me.subFormName.Form.RecordSource
        ' remove ";" at the end of string, if there is one
        strSQL = Replace(strSQL, ";", "")
        ' remove the existing Criteria
        nPos = InStr(strSQL, "WHERE ")
        If nPos > 0 Then strSQL = Left(strSQL, nPos - 1)
        ' check if Recordsource is Table Direct or a Select statement
        If InStr(strSQL, "SELECT ") > 0 Then
            ' it is a SELECT statement
            Me.subFormName.Form.RecordSource = strSQL & " Where ID IN (" & varCriteria & ")"
        Else
            ' it is a Table name or Query Name
            Me.subFormName.Form.RecordSource = "SELECT * FROM " & strSQL & " Where ID IN (" & varCriteria & ")"
        End If
    End If
End Sub