Listview Control

[Middlemarch]

I'm having trouble setting a checkbox in Column 0 in my Listview
In vb6 there is a Properties setting for Checkboxes and setting to Yes immediately shows a checkbox in Design View. (and at run time)
Access has the same Property but setting it Yes does not see it work and the next time you look at Properties it has reverted back to No


Also the instruction With Me.ListView0 .ShowCheckBoxes = True goes to error.
Has anyone found what to do to get around this ? I tried Google but nothing specific found and a lot of examples are for other platforms.

[Micron]

Confusing for an Access guy (at least me), and here's why.
What does a checkbox have to do with a ListView? Terminology might be an issue here, because a ListView is an ActiveX control in Access, and I think many developers prefer to avoid them.

In vb6 there is a Properties setting for Checkboxes

This one too - there are over 90 properties for an Access checkbox.

I'm having trouble setting a checkbox in Column 0

Checkboxes don't have columns and controls with columns don't have checkboxes (save for combos whose recordsource is a multi-value field).

[Middlemarch]

The First column in a Listview control can be a checkbox (for each row)
There's a property setting that shows/Hides it.
I can't get the checkbox column to work in Access (how it does in vb6) so asking here in hopes someone may know.
I believe it's same control for both Access and Vb6 called Microsoft ListView Control, version 6.0

[Micron]

Gotcha, thanks. You're just reaffirming my reluctance to ever learn more about them after my prior meager attempts.
Maybe something like this will help
https://stackoverflow.com/questions/...ft-access-form

[June7]

I set up a simple listview just for the exercise. My code:

Private Sub Form_Current()

    On Error GoTo ErrorHandler
'Set Reference to Microsoft DAO 3.xx Library.

    'set variables
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim lstItem As ListItem
    Dim strSql As String

    Set db = CurrentDb()
    strSql = "SELECT * FROM Holidays"
    Set rs = db.OpenRecordset(strSql)

    With Me.ListView1
        .ListItems.Clear
        'Set ListView style
        .View = lvwReport
        'This is not supported by ListView 5
        .Gridlines = True
        .FullRowSelect = True
        'Clear Header and ListItems
        .ListItems.Clear
        .ColumnHeaders.Clear
        .CheckBoxes = True
        .Font.Size = 13
    End With
    'Set up column headers
    With Me.ListView1.ColumnHeaders
        .Add , , "Hol ID", 1000, lvwColumnLeft
        .Add , , "Hol Name", 2000, lvwColumnLeft
        .Add , , "Qty", 500, lvwColumnRight
        .Add , , "Hol Date", 1500, lvwColumnRight
    End With
    ' Add items and subitems to list control.
    rs.MoveFirst
    Do Until rs.EOF
        Set lstItem = Me.ListView1.ListItems.Add()
        lstItem.text = rs!HolID
        lstItem.SubItems(1) = rs!HolName
        lstItem.SubItems(2) = rs!Qty
        lstItem.SubItems(3) = rs!HolDate
       'Next row
        rs.MoveNext
    Loop
    'close recordset
    rs.Close
    DoCmd.Echo True
ErrorHandlerExit:
    Exit Sub
ErrorHandler:
    If Err = 3021 Then    ' no current record
        Resume Next
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume ErrorHandlerExit
    End If

End Sub

[Middlemarch]

Hmm sometimes like just ain't fair! I'm sure I did nothing wrong but mine doesn't work and Junes does!
So I will find out why.
Thanks all round - and Macron I was working through that link. Pity he made it so specific as difficult to test run here. The instruction NewLine.Checked = True seemed a clue
but did nothing here. I see June has .CheckBoxes = True but that also didn't work here when i tried it.

June I see you are setting the recordset in the same routine. What if you had to pass data in and the Form was Modal ?
i found it not possible to use Property Let in that case so used a Global recordset. But it didn't seem the correct way.
My Form is model as the listview replaces an Input box.

[June7]

I spent an afternoon couple years ago building that listbox. Haven't done anything with it since. I had no idea Modal would be an issue. My code doesn't use Let anywhere. I don't see a Let property on Property Sheet. How are you using it?

[Middlemarch]

I could not establish a definite reason why your code worked and mine didn't. I think I had .ShowCheckboxes = True instead of .Checkboxes = True but after changing back to confirm I got an error that wasn't there initially.
I'm using a modal Form to halt code execution in the calling routine. The form closes after a selection in the listview.
My dilemma was how to pass the recordset to the Form with Property Let before it was open, as I can't pass it after open as it's modal.
Is there an 'Access method. for that? My global is working but it'd be good to know if other ways of doing that exist.
Your example was great btw, I couldn't find anything like it on Google. Tons of C+ and complicated voids etc.

[Micron]

What if you used a temporary querydef? Don't know if you can set a form recordsource property to an object variable (probably not) but you can create a recordset from the temp querydef. Then there's a temp tabledef if you don't mind the coding for it.

[June7]

That did not clarify how to code "Property Let".

Is Modal set in form design or in OpenForm command?

Provide your code for analysis.

Form can have code that sets its own RecordSource. Pass some info, even an entire SQL string, via OpenArgs. The ListView form reads OpenArgs and acts on the info passed, even in Modal state.

Or as Micron suggested, use QueryDefs to modify query object then open ListView form which always uses that same query object.

So why do you need to pass dataset to form after it opens?

[Micron]

Or as Micron suggested, use QueryDefs to modify query object

Not sure if we're on the same page with that or not. Sure, a temp query def is an object, but it only exists in memory and only as long as it remains in scope (so not sure if you create it at the module level or not). If not, it would have to be created each time the code runs because it's not like a query that you see in the nav pane.

[June7]

Okay, I misread Micron's post. I don't really know what a temporary querydef is. I meant use QueryDefs to modify query object seen in Nav pane.

[Middlemarch]

I now have querydef and use that as the Forms record source in Form Properties. This nicely solved the problem. And yes it is created each time as the Listview Form gets a new set of data to display.
June, modal is set in Form properties and in DoCmd.OpenForm "frmListview", acNormal, , , , acDialog
The Property Let is

Private Sub ListView0_ItemClick(ByVal Item As Object)
    With Form_frmMain
        .ListViewData = Item.SubItems(3) & Chr$(0) & Item.SubItems(4)
    End With
    DoCmd.Close acForm, "frmListview"
End Sub

And in frmMain

Property Get ListViewSelection(z) As String()
    ListViewData = z
End Property

I didn't need the recordset after the From opened, but beforehand.
Thanks for all the feedback and help.

[Micron]

You can also create temporary QueryDef objects. Unlike permanent QueryDef objects, temporary QueryDef objects are not saved to disk or appended to the QueryDefs collection. Temporary QueryDef objects are useful for queries that you must run repeatedly during run time but do not not need to save to disk, particularly if you create their SQL statements during run time.

https://learn.microsoft.com/en-us/of...def-object-dao

To create, use a zls as the qdef name: db.CreateQueryDef("", etc.)