ListView control questions 2

[galorin]

I have a listview on one of my forms for issuing items to people. It has 3 columns, one for item names, one for version last issued, and one for the date of last issue. There is also a tickbox next to each item. When the box is ticked, and a button pressed, each ticked item is logged as issued, and the fields re-populated with the most up-to-date data.

I have two questions regarding this control.

The first is a minor one. When the first column is clicked on a few times, it becomes possible to edit the contents of that box. How do I disable this behaviour?

The second is a bit more complex. The information I was given at the start of the project stated that one of each kind of item would be issued. That has since changed, and now multiple copies of an item can be sent out. I currently have no way of dealing with this. Is it possible to have another field in the listview where the user can type in a quantity of items sent? If so, then I can just reference the contents of that in an insert query. If it's not possible, then I need to re-write the whole methodology for issuing items.

 

[kjv1611]

=>The first is a minor one. When the first column is clicked on a few times, it becomes possible to edit the contents of that box. How do I disable this behaviour?

In Form Design View
Right-click on the control (your "box" that is ticked)
Choose Properties
Data Tab
Change "Locked" to Yes.
If you want to make it to where people can't even click on it/select it at all, then change "Enabled" to No

==>The second is a bit more complex.
If you're talking about a list box, yes I think this can be done... or if you're just talking about a list view format on your form, you just need to add a new text box, or whatever other control you want to use... or whatever control type you want.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Hmm, just looked it up... the ListView is an activeX control that I apparently haven't knowingly used...

Here is one tek-tips FAQ written that might be of some help as well:
faq702-6025

From my best guess at this point, you may have to make all or at least many of your changes in the control's VBA code, which is fine as well.

If it ends up going VBA, it may be best moved to the VBA forum:
forum705

--

"If to err is human, then I must be some kind of human!" -Me

 

[galorin]

I'll take the second part to VBA as ListViews are populated and controlled by VBA (At least mine is).

I used the faq you linked to in the initial creation of the ListView control. It really is a useful tool when you need nifty stuff like line-by-line or individual "cell" conditional formatting. I can highly recommend it, although I have only used it once in my project, consisting of 55 forms, a dozen functions and I have no idea how much VBA code... not to mention views and Stored Procedures on the MySQL Server that I have entrusted with the safe keeping of our data.

 

[ZmrAbdulla]

1. Try changing
Right click ListView > Control Properties > LabelEdit =lvwManual/lvwAutomatic
Found it not working sometimes
2.
To get the Checked Items of the ListView you can use the following code

Sub CheckedGetItemsOfListView()
    Dim ChItms As Integer
    For ChItms = 1 To Me.ListViewMaster.ListItems.Count
        If Me.ListViewMaster.ListItems.Item(ChItms).Checked = True Then
            MsgBox Me.ListViewMaster.ListItems.Item(ChItms).Text
        End If
        If Me.ListViewMaster.ListItems.Count = 0 Then Exit For
    Next
End Sub

you can then use an update/insert query to save into table.

Remember the data returned as string. You need to convert to Integer (Using Val() function)if that is integer datatype.
The data you see in the ListView is disconnected form the DB. Any changes in the data after populating it is not reflected in the ListView. You must make sure that there is no change in the underlying data before you update/insert.

Hope this helps

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[ZmrAbdulla]

Here is the MSDN Link of Properties of Activex controls, may be useful to you.

http://msdn.microsoft.com/en-us/library/aa276570.aspx

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[galorin]

Following a short explanation, you will find my listview code for your perusal. Not the most robust or elegant(probably) but it does the job.

The subs ConnectDB, getData, putData, and CloseDB are used for interacting directly with MySQL. Wherever possible/practical, I interact directly with MySQL.

The IsCurrent1() used is a Stored Procedure that takes in a person's ID, and the name of an item, and returns a 0,1, or 2 (Status of issue), version issued, and date of most recent issue.

First, the code for setting properties and populating the listview

Private Sub popCurItems()
Dim lstItem As ListItem
Dim Item() As String
Dim I As Integer
Dim FirstName As String
Dim LastName As String
Dim Company As String

Call connectDB
[COLOR=green]'Find out how many items we are dealing with[/color]
SQLout = "select count(Item) from ItemVer where Item not like '%Dongle' and Item not like 'Not Issued'"
Call getData

ReDim Item(rst.Fields(0) - 1)


[COLOR=green]'get list of possible items[/color]
SQLout = "select Item from ItemVer where Item not like '%dongle' and Item not like 'Not Issued';"
Call getData

I = 0
[COLOR=green]'populate item() array with item names[/color]
If Not rst.EOF Then
 rst.MoveFirst
 Do
   Item(I) = rst.Fields(0)
   I = I + 1
   rst.MoveNext
 Loop Until rst.EOF
End If

[COLOR=green]'set properties for ListView[/color]
With Me.CurItems
 .View = lvwReport
 .GridLines = True
 .FullRowSelect = True
 .ListItems.Clear
 .ColumnHeaders.Clear
 .Checkboxes = True
 .Width = 6350
End With


[COLOR=green]'Add column headers and set column widths[/color]
With Me.CurItems.ColumnHeaders
 .Add , , "Item Name", 3000, lvwColumnLeft
 .Add , , "Issued Version", 1500, lvwColumnLeft
 .Add , , "Date Issued", 1500, lvwColumnLeft
 .Add , , "Issue Code", 0, lvwColumnRight
End With

[COLOR=green]'For each item, get it's status and relevant dates[/color]
For I = 0 To UBound(Item)
 SQLout = ""
 SQLout = SQLout & "call IsCurrent1(" & Per_ID
 SQLout = SQLout & ",'" & Item(I) & "');"
 
 Call getData

[COLOR=green]'For each returned value, add an item to the list and populate fields with appropriate data[/color]
 rst.MoveFirst
 Do Until rst.EOF
  Set lstItem = Me.CurItems.ListItems.Add()
  lstItem.text = Nz(Item(I))
  lstItem.SubItems(1) = Nz(rst.Fields(1))
  lstItem.SubItems(2) = Nz(rst.Fields(2))
  lstItem.SubItems(3) = Nz(rst.Fields(0))
  rst.MoveNext
 Loop
Next I

Call closeDB
Call format

End Sub

The format sub is this: Simply applies conditional formatting based on value contained in hidden column

Private Sub format()
Dim Item As ListItem
Dim counter As Long
Dim stat As Integer

For counter = 1 To Me.CurItems.ListItems.Count
Set Item = Me.CurItems.ListItems.Item(counter)
stat = Item.SubItems(3)

With Me.CurItems.ListItems

Select Case stat
 Case 0
  .Item(counter).ForeColor = vbRed
  .Item(counter).Bold = True
  .Item(counter).ListSubItems(1).ForeColor = vbRed
  .Item(counter).ListSubItems(1).Bold = True
  .Item(counter).ListSubItems(2).ForeColor = vbRed
  .Item(counter).ListSubItems(2).Bold = True
 Case 1
  .Item(counter).ForeColor = vbBlack
  .Item(counter).ListSubItems(1).ForeColor = vbBlack
  .Item(counter).ListSubItems(2).ForeColor = vbBlack
 Case 2
  .Item(counter).ForeColor = 8421504
  .Item(counter).ListSubItems(1).ForeColor = -2147483633
  .Item(counter).ListSubItems(2).ForeColor = -2147483633
End Select

End With
Next counter
Me.CurItems.Refresh

End Sub

the sub used to put data back in(ListView has a checkbox for each item):

Private Sub IssueItems_Click()
Dim I As Integer

For I = Me.CurItems.ListItems.Count To 1 Step -1
    If Me.CurItems.ListItems.Item(I).Checked = True Then
    Call do_issue(Me.CurItems.ListItems.Item(I))
    Me.CurItems.ListItems.Item(I).Checked = False
    End If
    If Me.CurItems.ListItems.Count = 0 Then Exit For
Next I

Call popCurItems

End Sub

the do_issue sub referenced above:

Private Sub do_issue(Item As String)
SQLout = "select Version from ItemVer where Item = '" & Item & "';"

Call connectDB
Call getData

SQLin = "insert into issueditems (Per_ID, Item, Version, Date) values (" & Per_ID
SQLin = SQLin & ",'" & Item & "','" & rst.Fields(0) & "', NOW());"

Call putData
Call closeDB

End Sub

The code works, but I just need that one addition of an editable numeric field.

 

[ZmrAbdulla]

you need to set an additional column for that purpose

With Me.CurItems.ColumnHeaders
 .Add , , "Item Name", 3000, lvwColumnLeft
 .Add , , "Issued Version", 1500, lvwColumnLeft
 .Add , , "Date Issued", 1500, lvwColumnLeft
 .Add , , "Issue Code", 0, lvwColumnRight
[b] .Add , , "Copies", 500, lvwColumnRight[/b]

End With

Then with an Inputbox you can set the value to the cell
You need to have errorhandler to check if the dat entered is your prefered type and length.

Private Sub CurItems_DblClick()
    Dim s As String
    s = InputBox("Enter value")
    Me.CurItems.SelectedItem.SubItems(4) = s
End Sub

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[galorin]

Will come back to this quite soon, thanks for the pointer.