userform needs a subform

excel 2003 vba
access 2003

 What I have:
A userform but trying to display items being rectrned in a recordset(from access) like a subform. On my userform.

What I need for now...
I have a listbox to hold my records in the return recordset.
However it is not displaying them properly. Like a list...
they are just on one single line.

'Create a Snapshot Type Recordset from the SQL query
Set rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot)


'Move to the last record in the recordset
rstFromQuery.MoveLast

With frmiCart.ListBox1
    .ColumnCount = rstFromQuery.Fields.Count
          .Column = rstFromQuery.GetRows(rstFromQuery.RecordCount)
End With


Maybe the listbox is the only way to create a subform in excel ?

anyway I need all records to show as a list in listbox1 ?

Thanks
fordraiders






 
LVL 3
FordraidersAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rory ArchibaldConnect With a Mentor Commented:
Hmm, maybe DAO requires the number of rows to retrieve:
With frmiCart.ListBox1
     .ColumnCount = rstFromQuery.Fields.Count
 .List = Application.Transpose(rstFromQuery.GetRows(rstFromQuery.Recordcount))
End With

Open in new window


If that still doesn't work, can I see the full code, please?
0
 
Rory ArchibaldCommented:
Use:
 .List= Application.Transpose(rstFromQuery.GetRows())

Open in new window

0
 
FordraidersAuthor Commented:
not working...error
With frmiCart.ListBox1
 
    .ColumnCount = rstFromQuery.Fields.Count
          .Column = rstFromQuery.GetRows(rstFromQuery.RecordCount)
.List = Application.Transpose(rstFromQuery.GetRows())   <------  "no  current record"

End With
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rory ArchibaldCommented:
I meant use that instead of your .column =... line
0
 
FordraidersAuthor Commented:
rorya, I did.
STILL ERROR 3021    no current record...

this way:
With frmiCart.ListBox1
     .ColumnCount = rstFromQuery.Fields.Count
 .List = Application.Transpose(rstFromQuery.GetRows())
End With

this way:
With frmiCart.ListBox1
     .List = Application.Transpose(rstFromQuery.GetRows())
End With



0
 
Rory ArchibaldCommented:
You probably need to MoveFirst before you use GetRows.
0
 
FordraidersAuthor Commented:
ok tried that...  rorya..not working
0
 
FordraidersAuthor Commented:
ok thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.