?
Solved

userform needs a subform

Posted on 2011-02-23
8
Medium Priority
?
491 Views
Last Modified: 2012-05-11
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






 
0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34965788
Use:
 .List= Application.Transpose(rstFromQuery.GetRows())

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
ID: 34966520
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34967923
I meant use that instead of your .column =... line
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Author Comment

by:fordraiders
ID: 34969863
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34969869
You probably need to MoveFirst before you use GetRows.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 35036869
ok tried that...  rorya..not working
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35036922
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
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 35112345
ok thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question