DAO Diplay or populate Help

[Bigmo2u]

I have search the net and the forum and I can't come up with a solution. Who knows i maybe making this harder than it need be.

What i am trying to do is diplay the data in listbox. I can get it to print in an Immediate window, but not in the list box:

This will call the Function

Private Sub txtEndDate_Enter()

Dim r As String

r = Right(Me.txtSSN, 5)

Me.lstSDN = ShowSDN(r)

End Sub

This is the fuction:

Public Function ShowSDN(rSSN As String) As String

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

strSQL = "Select SDN, Status, Obl, Liq, Adv, Updated from tblSDN Where Right(SSN, 5) =  '" & rSSN & "'"
Set rs = CurrentDb.OpenRecordset(strSQL)

Debug.Print rs.Fields(0).name, "", rs.Fields(1).name, rs.Fields(2).name, rs.Fields(3).name, rs.Fields(4).name, rs.Fields(5).name

Do While Not rs.EOF
    Debug.Print rs!SDN, rs!Status, Format(rs!Obl, "Currency"), Format(rs!Liq, "Currency"), _
    Format(rs!Adv, "Currency"), rs!Updated
    rs.MoveNext
Loop

rs.Close
Set db = Nothing
Set rs = Nothing

End Function

Can someone help me with populating a listbox with the data.

 

[pbaldy]

Try

Me.ListboxName.RowSource = strSQL

 

[Husan]

try the following, you have already gota working select statement, this is all you need to assign to the list box.

Private Sub txtEndDate_Enter()

Dim r As String

r = Right(Me.txtSSN, 5)

Me.lstSDN.rowsource = ShowSDN(r)
me.lstSDN.requery

End Sub


Public Function ShowSDN(rSSN As String) As String

Dim strSQL As String

strSQL = "Select SDN, Status, Obl, Liq, Adv, Updated from tblSDN Where Right(SSN, 5) = '" & rSSN & "'"


showSDN=strsql

End Function

 

[Bigmo2u]

Okay, so using a DAO is not needed? I was wondering, because I already had it set with strSQL, but I was looking for something quicker with response time. It lags on the form right there.

There is FE and BE across a network with a lot of traffic. Just was looking for a way to speed the form up.

Thanks for your help...