Populating listview on form_load [RESOLVED]

[Simply Me]

hello! I have searched the forum and found this Thread I tried it on my work but its giving me this "Argument not Optional" error and form_load event is highlighted.

What i would like to achieved is that on form load, the listview(which has 6 columns) will display the records in my access database.

When the records are already displayed, the user can single click any row and the same records displayed in that row will also display in 6 textboxes for editing purposes.

Here's my code:

Option Explicit
Dim rsStocks As ADODB.Recordset
Dim sSQL$
Private Sub cmdAdd_Click()
  If CheckNullValue = False Then Exit Sub
  
  cmdCancel.Enabled = True
  'lstStocks.Enabled = False
  txtCode.SetFocus
  
On Error GoTo errHandler
 
  With oConn
    .BeginTrans
    
    ' - save to tblStocks -
    '--------------------------------------------------------------
    .Execute "INSERT INTO tblStocks(Code, ProductDescription, UnitPrice, " & _
                  "Quantity, ReOrder)" & _
             "VALUES('" & txtCode.Text & "', '" & txtDesc.Text & "', '" & _
                txtUnitPrice.Text & "' ,'" & txtQty.Text & "','" & _
                txtROP.Text & "')", , adCmdText
    '==============================================================
    .CommitTrans
  End With
  
  Call ClearFunction(frmStocks, "TextBox")
Exit Sub
errHandler:
  oConn.RollbackTrans
  Call msgError(Err)
End Sub
 
 
Private Sub cmdCancel_Click()
  Call ClearFunction(frmStocks, "TextBox")
 
End Sub
 
Private Sub cmdClose_Click()
  Unload Me
End Sub
 
Private Sub Form_Load()
  Call openConnection
  Me.Left = LeftPos
  Me.Top = TopPos
  
  Set rsStocks = New ADODB.Recordset
  rsStocks.CursorLocation = adUseClient
  
  
  sSQL = "SELECT * FROM tblStocks"
  
  If rsStocks.State = adStateOpen Then rsStocks.Close
  rsStocks.Open sSQL, oConn, adOpenStatic, adLockOptimistic
  Call FillListView
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
  If rsStocks.State = adStateOpen Then rsStocks.Close
  Set rsStocks = Nothing
End Sub
 
'TODO : return true if all required field has been filled
Private Function CheckNullValue() As Boolean
  CheckNullValue = False
  
  If Len(Trim$(txtCode.Text)) = 0 Then
    Call msgMustBeFilled("Product Code")
    txtCode.SetFocus
    Exit Function
  ElseIf Len(Trim$(txtDesc.Text)) = 0 Then
    Call msgMustBeFilled("Description")
    txtDesc.SetFocus
    Exit Function
  ElseIf Len(Trim$(txtUnitPrice.Text)) = 0 Then
    Call msgMustBeFilled("Unit Price")
    txtUnitPrice.SetFocus
    Exit Function
  ElseIf Len(Trim$(txtQty.Text)) = 0 Then
    Call msgMustBeFilled("Quantity")
    txtQty.SetFocus
    Exit Function
  ElseIf Len(Trim$(txtROP.Text)) = 0 Then
    Call msgMustBeFilled("Re-Order Point")
    txtROP.SetFocus
    Exit Function
  End If
  
  'If cmdAddCategory.Enabled = True Then Call ReplaceQuotation(txtCategoryName)
  CheckNullValue = True
End Function
 
Sub FillListView(lstStocks As ListView, rsStocks As ADODB.Recordset, Optional ImgNum As Long = 0)
    lstStocks.ListItems.Clear
    If Not rsStocks.BOF Then
        rsStocks.MoveFirst
        Dim a As Long
        Dim lst As ListItem
        While Not rsStocks.EOF
            lst.Ghosted = True
            Set lst = lstStocks.ListItems.Add(, , rsStocks.Fields(0).Value, , ImgNum)
            For a = 1 To lv.ColumnHeaders.Count - 1
                lst.SubItems(a) = rsStocks.Fields(a).Value
            Next
            rsStocks.MoveNext
        Wend
    End If
End Sub

[iPrank]

You have declared FillListView as,

Sub FillListView(lstStocks As ListView, rsStocks As ADODB.Recordset, Optional ImgNum As Long = 0)

The third parameter, ImgNum, is optional. You may omit it.
But the first 2 parameters are NOT optional. You must pass them to the procedure.
But you aren't pasing anything to it in your call in Form_Load

You should call it like,

Call FillListView lstStocks, rsStocks 'the third parameter is Optional

Or,
as both the listview and the recordset are in same scope, you can change the definition like,

Sub FillListView(Optional ImgNum As Long = 0)

and then, your current call will work.

[si_the_geek]

The error "Argument not optional" means that you are trying to use a sub/function that has parameters, but have not given enough (or any in this case).

and form_load event is highlighted.

Next time, please also tell us which line is highlighted - as that is blatantly useful for us to know.

[Simply Me]

Thanks to you bought iPrank and si_the_geek.

I'll try what u said iPrank.

[Simply Me]

ok. I have done these:
on form_load

Call FillListView(lstStocks, rsStocks)

on sub FillListView

Sub FillListView(lstStocks As ListView, rsStocks As ADODB.Recordset)
    lstStocks.ListItems.Clear
    If Not rsStocks.BOF Then
        rsStocks.MoveFirst
        Dim a As Long
        Dim lst As ListItem
        While Not rsStocks.EOF
            lst.Ghosted = True
            Set lst = lstStocks.ListItems.Add(, , rsStocks.Fields(0).Value)
            For a = 1 To lstStocks.ColumnHeaders.Count - 1
                lst.SubItems(a) = rsStocks.Fields(a).Value
            Next
            rsStocks.MoveNext
        Wend
    End If
End Sub

I received this error "Object variable or With block variable not set"

lst.Ghosted=true is highlighted.

[si_the_geek]

Shouldn't that line be after the "Set lst = " line?

[Simply Me]

Thanks si_the_geek. PERFECT!

[Simply Me]

Now, on to my next question.

When the records are already displayed, the user can single click or double click any row and the same records displayed in that row will also display in 6 textboxes for editing purposes.

Is this possible? any sample please

[Simply Me]

Anyone can help me please? I would like that whenever the user double click or perhaps single click on any of the records displayed in the listview, the same record will be displayed in textbox (in 6 texboxes because there are 6 columns in my listview).

An example is much appreciated. Thanks!

[Simply Me]

I already figured it out! Thanks!

[Hack]

I already figured it out! Thanks!

Post your solution as it may benefit others.

[Simply Me]

Post your solution as it may benefit others.

ok here's my code when the user click on any of the row (record) in my listview and display the same (record) in textboxes.

Private Sub LSTSTOCKS_ItemClick(ByVal Item As MSComctlLib.ListItem)
    txtCode.Text = Item.Text
    txtDesc.Text = Item.SubItems(1)
    txtUnitPrice.Text = Format(Item.SubItems(2), "###,###,##0.00")
    txtQty.Text = Item.SubItems(3)
    txtROP.Text = Item.SubItems(4)
End Sub