Listview in Access Project / SQL

[Guest]

Hi,

I'm sorry, but my English isn't very good, i'm a Dutch man. I will try to
tell you what's my problem. I hope you can't understand me.

I have a problem with programming with a ListView Control in Microsoft
Access. I have an Access project. My backend is SQL-server and my frontend is
Access. I have searched on internet and I can't find a resolution for my
problem. I don't know how to program/fill the ListView control with data from
SQL-server such as a table or a query.

Can somebody send me a sample to fill the ListView control in MS Access with
data from a database in SQL-server?

I hope there is someone who can help me!

Thanks in advance!

Bye,

Dirk

 

[Guest]

I assume you're talking about the ListBox control. Access does't have a
ListView. Assuming you have a linked table, you can directly link the data to
the control. On the Data tab of the listbox's property sheet, set the
RowSourceType to "Table/Query" and set the RowSource property to the name of
the table. On the format tab, you can change various properties to affect how
the data is displayed.

HTH,
Barry

 

[Guest]

Thank you for your answer.

I understand what you mean, and indeed, that's possible. But I have than the
problem that I can't sort by column such as Windows Explorer. Also I can't
easy search in a column for a value.
Can you help me with that problem?

Thanks,

Dirk

 

[Douglas J Steele]

Try something like the following:

Private Sub lvDetails_Load()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim lstitmCurr As ListItem
Dim strSQL As String

' Clear the ListView
Me.lvDetails.ListItems.Clear

' Set the style of the ListView
' (Note that this doesn't actually have to be done
' in code, unless you're trying to change it)
Me.lvDetails.View = lvwReport

' Ensure we have columns for 3 fields defined
If Me.lvDetails.ColumnHeaders.Count > 0 Then
Me.lvDetails.ColumnHeaders.Clear
End If
Me.lvDetails.ColumnHeaders.Add _
Index:=1, _
Key:="Field1", _
Text:="Field1"
Me.lvDetails.ColumnHeaders.Add _
Index:=2, _
Key:="Field2", _
Text:="Field2"
Me.lvDetails.ColumnHeaders.Add _
Index:=3, _
Key:="Field3", _
Text:="Field3"

Set dbCurr = CurrentDb()

' Create a recordset that contains the file information.
strSQL = "SELECT Field2, " & _
"Field2, " & _
"Field3 " & _
"FROM MyTable " & _
"ORDER BY Field1"

Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do Until rsCurr.EOF
' We construct an artificial value for the Key by concatenating
' the letter F with the value of Field1
Set lstitmCurr = Me.lvDetails.ListItems.Add( _
Key:="F" & rsCurr!Field1, _
Text:=Nz(rsCurr!Field1, "????") _
)
lstitmCurr.SubItems(1) = Nz(rsCurr!Field2, "????")
lstitmCurr.SubItems(2) = Nz(rsCurr!Field3, "????")

rsCurr.MoveNext
Loop
rsCurr.Close

End Sub

Just a comment on the Key when create the lstitmCurr object. The Key not
only must be unique and a string, but it must contain at least 1 letter. In
the example above, I've assumed that Field1 is numeric, so I'm prepending
the letter F to the key. If Field1 is a unique text value that will always
have at least 1 letter in it, you could get away with Key := rsCurr!Field1

 

[Albert D.Kallal]

Are you sure you need a listview contorl?

You might conisder using a cointues form, or perahps a listbox.

Here is some screen shots...they should give you some ideas....

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

A listbox does not have sort abilities..but a continues form,or datasheet
view does...

 

[Guest]

Thank you for your answer.

When I put your code in my project I get the following failure at this rule:
Set rsCurr = dbCurr.OpenRecordset(strSQL)
The failure is: Objectvariable of blockvariable With is not set!

Can you help me?

Thanks!

Dirk

 

[Douglas J Steele]

Show the exact code you're using. There are no With blocks in the sample I
posted...

 

[Guest]

Here is the code that I am using:

Private Sub Form_Load()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim lstitmCurr As ListItem
Dim strSQL As String

' Clear the ListView
Me.ListView0.ListItems.Clear

' Set the style of the ListView
' (Note that this doesn't actually have to be done
' in code, unless you're trying to change it)
Me.ListView0.View = lvwReport

' Ensure we have columns for 3 fields defined
If Me.ListView0.ColumnHeaders.Count > 0 Then
Me.ListView0.ColumnHeaders.Clear
End If
Me.ListView0.ColumnHeaders.Add _
Index:=1, _
Key:="Achternaam", _
Text:="Achternaam"
Me.ListView0.ColumnHeaders.Add _
Index:=2, _
Key:="Adres", _
Text:="Adres"
Me.ListView0.ColumnHeaders.Add _
Index:=3, _
Key:="Postcode", _
Text:="Postcode"

Set dbCurr = CurrentDb()

' Create a recordset that contains the file information.
strSQL = "SELECT * FROM qryrelatiestbvlistview"

Set rsCurr = dbCurr.OpenRecordset(strSQL) <-- failure
dbCurr.OpenRecordset (strSQL)
Do Until rsCurr.EOF
' We construct an artificial value for the Key by concatenating
' the letter F with the value of Field1
Set lstitmCurr = Me.ListView0.ListItems.Add( _
Key:="F" & rsCurr!Field1, _
Text:=Nz(rsCurr!Field1, "????") _
)
lstitmCurr.SubItems(1) = Nz(rsCurr!Field2, "????")
lstitmCurr.SubItems(2) = Nz(rsCurr!Field3, "????")

rsCurr.MoveNext
Loop
rsCurr.Close

End Sub


Thanks!

Dirk

 

[Douglas J Steele]

I don't see anything obviously wrong with your code.

Does qryrelatiestbvlistview perhaps require a parameter to run?

I can guarantee that the original code I posted works: it's from a working
example for a magazine article I just completed.

 

[Guest]

But have you also a sql-server with an Access-project? I think there is the
problem. In a standard MDB-file I have no problem too.

Thanks,

Dirk

 

[Douglas J Steele]

I don't believe you mentioned anywhere that you were using an ADP!

I would have expected the code to fail on the use of the DAO objects, as I
didn't think you could use DAO with ADPs.

Sorry, I have no experience with ADPs.