Hi, I have a list box being populated from an external database. There are 6 columns in the list box. My code goes out to a database runs a query and populates a list box. I have the ColumnsHeads property set to true, it shows the column head boxes but it won't populate them. My code is as follows: - Private Sub UserForm_Initialize()
-
-
Dim sdate As Date
-
Dim fdate As Date
-
-
fdate = ActiveProject.CurrentDate
-
fdate = Format(fdate, "Ddddd")
-
-
txtend.Value = fdate
-
txtstart.SetFocus
-
-
End Sub
- Public Function test(sdate, fdate) As String
-
-
Dim cmd As ADODB.Connection
-
Dim rs As ADODB.Recordset
-
Dim qry As ADODB.Recordset
-
Dim prm As ADODB.Parameter
-
Dim prm1 As ADODB.Parameter
-
Dim strconn As String
-
Set cmd = New ADODB.Connection
-
-
strconn = "Provider= microsoft.ace.oledb.12.0;" & "Data Source = Z:\TRS Commercial\TRS Prod\data bases\parts status1.accdb"
-
cmd.Open strconn
-
-
Dim k As New ADODB.Command
-
Set qry = New ADODB.Recordset
-
Set rs = New ADODB.Recordset
-
-
cmd.CursorLocation = adUseClient
-
-
Dim tskdat() As String
-
Dim i As Integer
-
Dim objfield As ADODB.Field
-
Dim icol As Integer
-
Dim ifld As Integer
-
Dim n As Integer
-
Dim m As Integer
-
-
'opening and running the query
-
Set k.ActiveConnection = cmd
-
k.CommandType = adCmdStoredProc
-
-
k.CommandTimeout = 8000
-
k.CommandText = "updatetest"
-
-
Set prm = k.CreateParameter
-
prm.Type = adDate
-
k.Parameters.Append prm
-
k.Parameters(0).Value = sdate
-
-
Set prm1 = k.CreateParameter
-
prm1.Type = adDate
-
k.Parameters.Append prm1
-
k.Parameters(1).Value = fdate
-
-
Erase tskdat
-
-
qry.Open k
-
-
If Not qry.EOF Then qry.MoveLast
-
n = qry.RecordCount - 1
-
m = qry.Fields.Count - 1
-
ReDim tskdat(n, m)
-
qry.MoveFirst
-
-
' populating the list box
-
i = 0
-
-
Do Until qry.EOF
-
'objfield.Name
-
-
For icol = 0 To m
-
tskdat(i, icol) = qry.Fields(icol)
-
Next
-
i = i + 1
-
qry.MoveNext
-
-
Loop
-
lbdata.ColumnHeads = True
-
lbdata.List() = tskdat
-
-
cmd.Close
-
Set qry = Nothing
-
Set k = Nothing
-
Set cmd = Nothing
-
Set rs = Nothing
-
-
End Function
Can anybody please help me to get these coulmn heads to populate?
Thank you.
6 7825 zmbd 5,501
Expert Mod 4TB
You have to use the "Row Source" property of the listbox control.
You cannot do this using the "additem" method.
Two examples
1) Table or query
Set the row source to point to the table name, query name, or enter the SQL in the RowSource property directly.
Set your number of columns, formating etc...
Set the column headings to yes.
The first row will now have the field names as the header.
Use a query and alias the field names as the rowsource to alter the names displayed.
2) Data list
Once again you will use the rowsource property.
Easy example.
Insert a listbox and set up for two columns, both shown, and the headers property is set to true, then in the row source property enter: Header1, Header2; row1cell1, row1cell2; row2cell1, row2cell2; row3cell1, row3cell2; You will see that the the first entries are in the header row and the remaining in the subsequent row.
This has been vetted on my test forms in
ACC2003 WinXPEnterprise (had several like this)
ACC2010(32bit)Win7Enterprise(64bit)
Tried it, the list box is on a form in a microsoft project file, and it won't let me set the record source property. I get an "Invalid row source property" error.
zmbd 5,501
Expert Mod 4TB
You didn't state you were using Microsoft Project. Which would have been most helpfull as this forum deals primarily with Access.
For MSP, you will not have a recordsource because it's not a database nor worksheet; therefor, there is no known way to populate the list box header row. If you spend a few days with the search engines you'll find that this is a very common frustration with VBA programmers since circa Win95 release.
There may be some API call that will work; however, that is a tad out of my knowledge base.
zmbd 5,501
Expert Mod 4TB
After reading a littlebit on MS-Project I have an additional question as the Rowsource Property should be available...
Are you attempting to use the .additem method for the listbox?
If so, that will NOT work. You can use one method or the other but not both.
You also need to properly set the rowsource type to either list or table/query.
If you look at my code, i'm using a ADODB connection to open a access database and run a query. The parameters are sent to the query, I then take the data the query provides and populate a list box in project. I think i can use the objfield.name property, but I don't know how to go about it.
zmbd 5,501
Expert Mod 4TB
Unfortunately I do not have an installation of MS-Project available; thus no way for me trouble shoot directly.
Yes I do see that how you've opened the database link.
I'm also assuming that tskdat is your listbox.
However, I think we're not using the same names for things; therefore, so that I have an idea as to how the UI for MSP is presenting the listbox control to you would you please do the following:
- In a new form (so that we do not damage any of your current work (°,°) )
- Insert a new listbox control, open the properties for the control:
-- Data tab: Please list for me the first four field names. For example:
[Data]![Control Source] = null
[Data]![Row Source]= entry1, entry2;
[Data]![Row Source Type]= Table/Query
Etc…
-- Format tab: Please list for me the first four and their settings as above; [Format]![Visible]=true, Etc…
It would be most helpful, if you would list those same properties and settings for the cotrol in your production form.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Broder |
last post by:
Hi there,
I am currently running into a somewhat weired problem and hope that this NG
is able to help ;-)
I have a table in a MSSQL Server in which there is one Column that stores a
date. The...
|
by: cloverme |
last post by:
Hi,
I need help populating a listbox from a database on a webform.
I created a an access database with a table, fields and data. Then I
created a WebForm in vb.net and added a DropDownList...
|
by: Chris Leuty |
last post by:
I am populating a multiselect Listbox from a dataset, with the content of
the listbox filled by one table, and the selections determined from another
table. So far, I have been keeping the dataset...
|
by: Ekoku |
last post by:
Hi any1 know how to populate a listbox from a textfile using split plz
10x alot
Below a code extract
Private Sub LoadAlbum()
With ofdAlbum
.DefaultExt = "txt"
.InitialDirectory =...
|
by: jack-b |
last post by:
Hi,
I have a list box which displays countries names and a second listbox
which displays their cites (based on the selection made in ListBox 1)
If the user selects USA i want to display cities...
|
by: JustJim |
last post by:
Well this has been fun, but I now elect to "phone a friend"
I have an Access application in which the operator can enter/choose the name of a training course and enter the number of teachers from...
|
by: DanAusitn |
last post by:
Hi forum,
I have been trying to figure this out for a while now, i thought it would be quite simple but has proved difficult as i am a learner. basicly i want to populate a list or combo box with...
|
by: dkohel |
last post by:
I have 2 list boxes on my form. I am trying to populate listbox B
with the selection from listbox A. I have set multi-select in both
boxes to Extended...
The user will select the items from...
|
by: tuxalot |
last post by:
I have a listbox with a row source using a union query:
SELECT .ReportID, .ReportName, .ReportCaption, .ReportCriteriaFlags, .ReportRemote FROM ORDER BY .ReportCaption;
The union query sql:
TABLE...
|
by: mcalex |
last post by:
Hi, I'm having trouble trying to populate a list box with data from a parameterized query.
If I set the rowsource property to the query name, when the form opens I get the parameter values dialog,...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |