Unbound Listbox

[naturalsn]

Hi..

I have a form bound to a table Called "Personell".
I would like to display a multiselect listbox that retrieves it data from a Table called TblEmailsSent. Both have a unique PersonellID in each table. And i can not get the data in the listbox to link with the data in my main form.

I have made a subform, where there is just one listbox in the form. Bound/Unbound but still does not correspond with my ID with the Data.

How can i in VBA say, Show in listbox "LstEmails" the records from Table, "EmailsSent", Only where the ID is related to what is currenlty being displayed in my form.

Any help would really be appreciated.

 

[danvlas]

You don't need a subform to keep only a listbox on it.
Place the listbox on the main form and use:

Private Form_Current()
strSQL = "Select * From EmailsSent Where tblEmailsSent.PersonnelID = " & Me.ID
Me.ListBoxName.RowSource = strSQL
End Sub

Or...create a tabular form on tblEmailsSent, place it as a subform and check the LinkMasterFields and linkChildFields properties.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[naturalsn]

Thanks for the advise.

What if I allready have code loaded on the on current event, of the main form.

Because currently i placed it at the end of the code (removed end sub)

(Code in blue what is currently on_current

I have

[blue]Private Sub Form_Current()
On Error GoTo err_Form_Current

    If Not Me!txtPicture = "" Or Not IsNull(Me!txtPicture) Then
        Me!Picture.Picture = Me!txtPicture
    Else
        Me!Picture.Picture = ""
    End If
       
exit_Form_Current:
    Exit Sub
    
err_Form_Current:
    MsgBox Err.Description
    Resume exit_Form_Current [/blue]
    
    End If
    
    Private Form_Current()
strSQL = "Select * From lstEmails Where tblEmailsSent.ID = " & Me.ID
Me.LstEmails.RowSource = strSQL
End Sub

{Table Name = TblEmailsSent , Listbox Name = lstEmails. And both tables have ID}

I have set the listbox rowsource Type to Table/Query and selected fields from rowsource. It does however with the above menioned come up with all the details in the TblEmailsSent but not assosciation to the id that is currently being displayed in the form..

thanks in advance.

 

[danvlas]

Just insert the new code before the 'exit' label:

Private Sub Form_Current()
On Error GoTo err_Form_Current
Dim strSql as String
' If Not Me!txtPicture = "" Or Not IsNull(Me!txtPicture) Then
' Me!Picture.Picture = Me!txtPicture
' Else
' Me!Picture.Picture = ""
' End If
'The entire If statement can be replaced with one line:
Me!Picture.Picture=Nz(Me!txtPicture,"")

strSQL = "Select * From lstEmails Where tblEmailsSent.PersonnelID = " & Me.ID
Me.LstEmails.RowSource = strSQL


exit_Form_Current:
Exit Sub

err_Form_Current:
MsgBox Err.Description
Resume exit_Form_Current

End If

You do NOT link the child records by ID, but by the foreign key, which should be PersonnelID...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[naturalsn]

Hi Danvlas

Brilliant, Thank you very much for all your help...