[RESOLVED] Re-using a form

[osemollie]

I have a form for registering new movies (frmMovies). On this form I have several comboboxes representing genre, actor, producer, director etc. On the side of each combo, there is a commnad button (cmdAdd) which enables the user to add more items into the respective tables that populate the comboboxes. Now, the form for adding the items (frmAddItem) has a ListView control which lists all the existing items in the respective table. Eg if I click on cmdAdd next to the actor combo, it should open frmAddItem form but in the listview, all the existing actors are listed. Should I click on cmdAdd next to producer, then frmAddItem's ListView should list all existing producers from the producer table etc.

Instead of having each category having it's own form can I re-use the frmAddItem to serve all these categories?

[ganeshmoorthy]

yes, ofcourse...you can do that...have a tag in some control's property, set the tag to required category and then based on the tag generate the query and display the frmAddItem...
currently how are you doing it...

[osemollie]

This is the code in the command button for opening a new window:-

Private Sub cmdDirector_Click()
Dim frmSel As New frmAddItem ' frmAddItem is the form that I want to re-use
 
frmSel.Show vbModal
 
Unload frmSel
Set frmSel = Nothing
 
End Sub

In the frmAddItem form_load event runs the following code:-

Private Sub Form_Load()
 
Dim rst As ADODB.recordset
    Dim db As ADODB.Connection
    
    Set db = New ADODB.Connection
    
    db.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "./databases/Visualtek.mdb;Persist Security Info=False")       
    
    Set rst = New ADODB.recordset
    With rst
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
    .Source = " SELECT producer FROM producer"
    Set .ActiveConnection = db
    .Open
       End With
           
    ' populate listview control
    Call PopulateList(ListView1, rst)   
        
End Sub

PopulateList is the function for populating the ListView and it's code is:

Private Sub PopulateList(pList As ListView, _
                         pRst As ADODB.recordset)
    'On Error Resume Next
    Dim i As Long
    Dim iColCount As Long
    Dim sColName As String
    Dim sColValue As String
    Dim oCH As ColumnHeader
    Dim oLI As ListItem
    Dim oSI As ListSubItem
    Dim oFld As ADODB.Field
 
    With pList
        .View = lvwReport
        .GridLines = True
        .FullRowSelect = True
        .ListItems.Clear
        .Sorted = False
        pRst.MoveFirst
        
        ' set up column headers
        For Each oFld In pRst.Fields
            sColName = cn(oFld.Name)
            Set oCH = .ColumnHeaders.Add()
            oCH.Text = sColName
            iColCount = iColCount + 1
        Next oFld
 
        Do Until pRst.EOF
            i = 0
            ' setup fiprst column as a listitem
            sColValue = cn(pRst.Fields(i).Value)
            Set oLI = .ListItems.Add()
            oLI.Text = sColValue
            
            pRst.MoveNext
        Loop ' Next record
        ' refresh it all
        .Refresh
        ' make sure 1st row can be seen
        .ListItems(1).EnsureVisible
    End With
End Sub

[osemollie]

yes, ofcourse...you can do that...have a tag in some control's property, set the tag to required category and then based on the tag generate the query and display the frmAddItem...
currently how are you doing it...

ganeshmoorthy, mind telling me how I can set a tag in my control?

[jcis]

You can also use a property to do what you want, just add a property to frmAddItem, this property will modify a private variable value, this variable will determine the action to take (list actors, list directors, list producers) it will depend on the variable value.

[osemollie]

If you dont mind just explain a little bit more on how I can do that. Am new to VB an just learning. How can I do what you have suggested? I will appreciate some sample code. Thanks.

[jcis]

Better use an Enum, In a VB module:

Public Enum enumListType
    enuGenre = 0
    enuActor = 1
    enuProducer = 2
    enuDirector = 3
End Enum

This is your new FrmMovies..

Option Explicit
 
Private Sub cmdDirector_Click()
    ShowList enuDirector
End Sub
 
Private Sub cmdGenre_Click()
    ShowList enuGenre
End Sub
 
Private Sub cmdActor_Click()
    ShowList enuActor
End Sub
 
Private Sub cmdProducer_Click()
    ShowList enuProducer
End Sub
 
Private Sub ShowList(pType As enumListType)
Dim frmSel As frmAdditem
 
    Set frmSel = New frmAdditem
    
    frmSel.ListType = pType
    frmSel.LoadList
    frmSel.Show vbModal
    Set frmSel = Nothing
End Sub

And this is your new FrmAdditem

Option Explicit
 
Private mListType As enumListType
 
Public Property Let ListType(pnewVal As enumListType)
    mListType = pnewVal
End Property
 
Public Sub LoadList()
    Dim rst As ADODB.Recordset
    Dim db As ADODB.Connection
    
    Set db = New ADODB.Connection
    
    db.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        App.Path & "./databases/Visualtek.mdb;Persist Security Info=False")
    
    Set rst = New ADODB.Recordset
    
    With rst
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        
        Select Case mListType
            Case enuProducer
                .Source = " SELECT producer FROM producer"
            Case enuDirector
                .Source = " SELECT director  FROM director "
            Case enuActor
                .Source = " SELECT actor  FROM actor "
            Case enuGenre
                .Source = " SELECT genre  FROM genre "
        End Select
        
        Set .ActiveConnection = db
        .Open
    End With
           
    ' populate listview control
    Call PopulateList(Me.ListView1, rst)
    
    rst.Close
    db.Close
    Set rst = Nothing
    Set db = Nothing
End Sub
 
Private Sub PopulateList(pList As ListView, pRst As ADODB.Recordset)
    'On Error Resume Next
    Dim i As Long
    Dim iColCount As Long
    Dim sColName As String
    Dim sColValue As String
    Dim oCH As ColumnHeader
    Dim oLI As ListItem
    Dim oSI As ListSubItem
    Dim oFld As ADODB.Field
 
    With pList
        .View = lvwReport
        .GridLines = True
        .FullRowSelect = True
        .ListItems.Clear
        .Sorted = False
        pRst.MoveFirst
        
        ' set up column headers
        For Each oFld In pRst.Fields
            sColName = cn(oFld.Name)
            Set oCH = .ColumnHeaders.Add()
            oCH.Text = sColName
            iColCount = iColCount + 1
        Next oFld
 
        Do Until pRst.EOF
            i = 0
            ' setup fiprst column as a listitem
            sColValue = cn(pRst.Fields(i).Value)
            Set oLI = .ListItems.Add()
            oLI.Text = sColValue
            
            pRst.MoveNext
        Loop ' Next record
        ' refresh it all
        .Refresh
        ' make sure 1st row can be seen
        .ListItems(1).EnsureVisible
    End With
End Sub

Thats all. Note, this is part in FrmAdditem:

Select Case mListType
            Case enuProducer
                .Source = " SELECT producer FROM producer"
            Case enuDirector
                .Source = " SELECT director  FROM director "
            Case enuActor
                .Source = " SELECT actor  FROM actor "
            Case enuGenre
                .Source = " SELECT genre  FROM genre "
        End Select

Replace those queries with the real queries you need.

[osemollie]

THANKS jcis, it worked!! But having achieved that, my idea becomes a little more complicated since on this second form (frmAddItem), I have other buttons cmdAdd and cmdEdit which also open another common form (frmAddEdit).

Private Sub cmdEdit_Click()
 With frmAddEdit
        .Caption = .Caption & " Add Record"
        .Show vbModal
    End With
End Sub

And

Private Sub cmdEdit_Click()
With frmAddEdit
        .cmdAddSave.Caption = "&Save"
        .cmdFinishCancel.Caption = "&Cancel"       
            .txtAddEdit.Text = ListView1.SelectedItem.Text
 .Show vbModal
    End With
End Sub

And on the form frmAddEdit, I have the following code for adding?updating the items

Private Sub cmdAddSave_Click()
    Select Case cmdAddSave.Caption
        Case "&Add"
            MsgBox "Code still under construction.", vbInformation, ""
        Case "&Save"
            '#### Update the database
            strSql = "" & _
                "UPDATE producer " & _
                "SET " & _
                    "producer = '" & txtAddEdit.Text & "', " & _                    
                "WHERE " & _
                    "producerId = " & ttxtAddEdit..Text & " "
            adoCn.Open
            adoCn.Execute (strSql)
            adoCn.Close
cmdFinishCancel_Click
    End Select
End Sub

Now, this being the case, how will I ensure that am updating the right table based on what I have listed in the ListView? Thanks in advance

[jcis]

1. You need to add the same property to frmAddEdit, so frmAddEdit will know which table to use..

Private mListType As enumListType
 
Public Property Let ListType(pnewVal As enumListType)
    mListType = pnewVal
End Property

2. Your ListView in FrmAddItem should have a column with IDs (it could be invisible if you want), something like..

[Producer name] [ProducerId]
... ...
(the same for producers, directors, genres and actors.)

Then when the user selects a row and click EDIT, you pass the ID to frmAddEdit as a property, the same way you did before.

3. frmAddEdit needs to know if it has to ADD or EDIT, you can pass that as a property also. Consider adding Remove/DELETE also.

[osemollie]

1. You need to add the same property to frmAddEdit, so frmAddEdit will know which table to use.

Can I re-use the property I had used in the previous example?

2. Your ListView in FrmAddItem should have a column with IDs, something like..

[Producer name] [ProducerId]
... ...
(the same for producers, directors, genres and actors.)

Then when the user selects a row and click EDIT, you pass the ID to frmAddEdit as a property, the same way you did before.

I have included the id columns in my query. But I don't know how call an ID as a property when a user selects and clicks Edit or clicks on ADD button.

Am a bit confused when it comes to what I should put in the cmdAdd and cmdEdit buttons in the first form (frmAddItem) . Whats should it be? ShowList ???

And what about in the cmdOK or cmdEdit buttons in the second form (frmAddEdit)

[si_the_geek]

1. Not quite, you copy the propertty code (the code in jcis's last post) to frmAddEdit, then in FrmAddItem (before frmAddEdit is shown) set the value, eg:

With frmAddEdit
  .ListType = mListType
..

By using a property, you treat it just like you would treat other properties (eg: a Caption).


2. What jcis meant is that you should create another property (as above) in frmAddEdit, which stores the ID. As the ID does not contain a small range of values you should not use an Enum - just a normal data type that is appropriate (eg: Integer or Long).

You only need to set the value of this property if you are editing, as a new record will get a new ID when it is added.


For the code in cmdAddSave_Click, you need to read the value of the properties - which are stored in your variables (mListType), and work with them appropriately.

This will probably mean making another Select Case block (a bit like the end of post #7) inside each of the "&Add" and "&Save" blocks, so that you are building the right SQL statement for each table.

[osemollie]

2. What jcis meant is that you should create another property (as above) in frmAddEdit, which stores the ID. As the ID does not contain a small range of values you should not use an Enum - just a normal data type that is appropriate (eg: Integer or Long).

You only need to set the value of this property if you are editing, as a new record will get a new ID when it is added.


For the code in cmdAddSave_Click, you need to read the value of the properties - which are stored in your variables (mListType), and work with them appropriately.

This will probably mean making another Select Case block (a bit like the end of post #7) inside each of the "&Add" and "&Save" blocks, so that you are building the right SQL statement for each table.

I have tried understanding this bit but I can't get it. Anyone try putting it in code format for m to understand. Thanks

[si_the_geek]

The code in post #9 is a property - make another one in frmAddEdit for storing the ID.

Things to change from that code:

• The property name (ListType) needs to be something appropriate (eg: TheID)
• The data type needs to be Long instead of enumListType.
• The variable name (mListType) needs to be something relevant (eg: mTheID).

Once you have added the property, you can use it just like I used ListType in "1" above.

[osemollie]

From my first form (frmAddItem) I have:

Private Sub cmdAdd_Click()
With frmAddItem
        .Caption = .Caption & " Add New Item"
        .TheID = mTheID
        .Show vbModal
    End With
 
End Sub

and

Private Sub cmdEdit_Click()
With frmAddItem
        .Caption = .Caption & " Add New Item"
        .TheID = mTheID
        .cmdOK.Caption = "&Save"
        .cmdCancel.Caption = "&Cancel"
        .Show vbModal
    End With
End Sub

In my second form (frmAddEdit) I have

Option Explicit
Private mTheID As Long
 
Public Property Let TheID(pnewVal As Long)
    mTheID = pnewVal
End Property
 
Private Sub cmdCancel_Click()
Unload Me
End Sub
 
Private Sub cmdOk_Click()
Dim rst As ADODB.Recordset
    Dim db As ADODB.Connection
    
    Set db = New ADODB.Connection
    
    db.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        App.Path & "./databases/Visualtek.mdb;Persist Security Info=False")
    
    Set rst = New ADODB.Recordset
    
    With rst
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        
    Select Case mTheID
            Case enuProducer
                .Source = " INSERT INTO producer (producer) VALUES (" & "'" & txtItem.Text & "') "
            Case enuDirector
                .Source = " INSERT INTO director (director) VALUES (" & "'" & txtItem.Text & "') "
            Case enuWriter
                .Source = " INSERT INTO writer (writer) VALUES (" & "'" & txtItem.Text & "') "
            Case enuGenre
                .Source = " INSERT INTO genre (genre) VALUES (" & "'" & txtItem.Text & "') "
End Select
End Sub
 
 
Private Sub cmdUpdate_Click()
 
Dim rst As ADODB.Recordset
    Dim db As ADODB.Connection
    
    Set db = New ADODB.Connection
    
    db.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        App.Path & "./databases/Visualtek.mdb;Persist Security Info=False")
    
    Set rst = New ADODB.Recordset
    
    With rst
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
Select Case mListType
            Case enuProducer
                .Source = " UPDATE producer SET [producer] = '" & Trim(txtItem.Text) & "' WHERE producerId = " & mListType & ""
                ase enuDirector
                .Source = " UPDATE director SET [director] = '" & Trim(txtItem.Text) & "' WHERE directorId = " & mListType & ""
            Case enuWriter
                .Source = " UPDATE writer SET [writer] = '" & Trim(txtItem.Text) & "' WHERE writerId = " & mListType & ""
            Case enuGenre
                .Source = " UPDATE genre SET [genre] = '" & Trim(txtItem.Text) & "' WHERE genreId = " & mListType & ""
End Select
            
End Select
 
End Sub

But right from the first form (frmAddItem) when i click on cmdAdd or Edit, i get an error "variable mTheID not set.

Where am i going wrong?

I also need to add cmdDelete button on form(frmAddItem)

[si_the_geek]

Your property TheID is good, but you seem to have lost the other one (ListType), and a few other bits too (eg: cmdUpdate_Click has two End Select's, and no End With).


In frmAddItem, you are setting TheID in the wrong sub.
It is pointless for an Add (as the ID doesn't exist yet), but is right for an Edit (as the value is known, and will let you work with the correct record).


As to the value you use, you cannot simply use a variable (especially one which only exists in another form), you need to find the value from your form. Presumably it will be from the selected row in a list.

[osemollie]

As to the value you use, you cannot simply use a variable (especially one which only exists in another form), you need to find the value from your form. Presumably it will be from the selected row in a list.

Am slightly lost here.

[osemollie]

2. Your ListView in FrmAddItem should have a column with IDs (it could be invisible if you want), something like..

[Producer name] [ProducerId]
... ...
(the same for producers, directors, genres and actors.)

Then when the user selects a row and click EDIT, you pass the ID to frmAddEdit as a property, the same way you did before.

3. frmAddEdit needs to know if it has to ADD or EDIT, you can pass that as a property also. Consider adding Remove/DELETE also.

I now have this in my code

Option Explicit
Private mListType As enumListType
Private mTheID As Long
 
Public Property Let TheID(pnewVal As Long)
    mTheID = pnewVal
End Property
 
Public Property Let ListType(pnewVal As enumListType)
    mListType = pnewVal
End Property
 
Private Sub cmdCancel_Click()
Unload Me
End Sub
 
Private Sub cmdOk_Click()
Dim rst As ADODB.Recordset
    Dim db As ADODB.Connection
    
    Set db = New ADODB.Connection
    
    db.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        App.Path & "./databases/Visualtek.mdb;Persist Security Info=False")
    
    Set rst = New ADODB.Recordset
    
    With rst
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        
    Select Case mListType
            Case enuProducer
                .Source = " INSERT INTO producer (producer) VALUES (" & "'" & txtItem.Text & "') "
            Case enuDirector
                .Source = " INSERT INTO director (director) VALUES (" & "'" & txtItem.Text & "') "
            Case enuWriter
                .Source = " INSERT INTO writer (writer) VALUES (" & "'" & txtItem.Text & "') "
            Case enuGenre
                .Source = " INSERT INTO genre (genre) VALUES (" & "'" & txtItem.Text & "') "
End Select
End With
End Sub
 
Private Sub cmdUpdate_Click()
 
Dim rst As ADODB.Recordset
    Dim db As ADODB.Connection
    
    Set db = New ADODB.Connection
    
    db.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        App.Path & "./databases/Visualtek.mdb;Persist Security Info=False")
    
    Set rst = New ADODB.Recordset
    
    With rst
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
Select Case mListType
            Case enuProducer
                .Source = " UPDATE producer SET [producer] = '" & Trim(txtItem.Text) & "' WHERE producerId = " & mListType & ""
                ase enuDirector
                .Source = " UPDATE director SET [director] = '" & Trim(txtItem.Text) & "' WHERE directorId = " & mListType & ""
            Case enuWriter
                .Source = " UPDATE writer SET [writer] = '" & Trim(txtItem.Text) & "' WHERE writerId = " & mListType & ""
            Case enuGenre
                .Source = " UPDATE genre SET [genre] = '" & Trim(txtItem.Text) & "' WHERE genreId = " & mListType & ""
    End Select
End With
End Sub

What else should I do/change to get it right?

[jcis]

Am slightly lost here.

FrmAddItem has Listview, in that Listview you load names, you should also load IDs, you need those IDs to make Updates. I assume each table (director, genre, writer, producer) has an Autonumeric field ID, you should load this into your Listview.

After you did that, your events would be..

Private Sub cmdAdd_Click()
    With frmAddItem
        .Caption = .Caption & " Add New Item"
        .ListType = mListType
        .Show vbModal
    End With
End Sub
 
Private Sub cmdEdit_Click()
With frmAddItem
        .Caption = .Caption & " Add New Item"
        .ListType = mListType
        .TheID = [B][Here you add the ID in the selected row in the Listview][/B]
        .cmdOK.Caption = "&Save"
        .cmdCancel.Caption = "&Cancel"
        .Show vbModal
    End With
End Sub

Then in FrmAddEdit..

Private mTheID      As Long
Private mListType   As enumListType
 
Public Property Let ListType(pnewVal As enumListType)
    mListType = pnewVal
End Property
 
Public Property Let TheID(pnewVal As Long)
    mTheID = pnewVal
End Property
 
Private Sub cmdCancel_Click()
    Unload Me
End Sub
 
Private Sub cmdOk_Click()
Dim db      As ADODB.Connection
Dim StrSql  As String
    
    Set db = New ADODB.Connection
    
    db.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        App.Path & "./databases/Visualtek.mdb;Persist Security Info=False")
    
        
    Select Case mListType
            Case enuProducer
                StrSql = " INSERT INTO producer (producer) VALUES ('" & txtItem.Text & "') "
            Case enuDirector
                StrSql = " INSERT INTO director (director) VALUES ('" & txtItem.Text & "') "
            Case enuWriter
                StrSql = " INSERT INTO writer (writer) VALUES ('" & txtItem.Text & "') "
            Case enuGenre
                StrSql = " INSERT INTO genre (genre) VALUES ('" & txtItem.Text & "') "
    End Select
    db.Execute StrSql
    
    db.Close
    Set db = Nothing
End Sub
 
Private Sub cmdUpdate_Click()
Dim StrSql  As String
Dim db       As ADODB.Connection
    
    Set db = New ADODB.Connection
    
    db.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        App.Path & "./databases/Visualtek.mdb;Persist Security Info=False")
        
    Select Case mListType
          Case enuProducer
               StrSql  = " UPDATE producer SET [producer] = '" & Trim(txtItem.Text) & "' WHERE producerId = " & mTheID & ""
          Case enuDirector
               StrSql  = " UPDATE director SET [director] = '" & Trim(txtItem.Text) & "' WHERE directorId = " & mTheID & ""
          Case enuWriter
              StrSql  = " UPDATE writer SET [writer] = '" & Trim(txtItem.Text) & "' WHERE writerId = " & mTheID & ""
          Case enuGenre
              StrSql = " UPDATE genre SET [genre] = '" & Trim(txtItem.Text) & "' WHERE genreId = " & mTheID & ""
    End Select
 
    db.Execute StrSql 
 
    db.Close
    Set db = Nothing
End Sub

[jcis]

I edited the code in my last post to remove TheID from cmdAdd_Click event (when you ADD you dont have to pass TheID, I assume this is an autonumeric field)

[osemollie]

Am using

Private Sub cmdEdit_Click()
With frmAddItem
        .Caption = .Caption & " Add New Item"
        .ListType = mListType
        .TheID = ListView1.SelectedItem.Index
        .cmdOK.Caption = "&Save"
        .cmdCancel.Caption = "&Cancel"
        .Show vbModal
    End With
End Sub

in the first form but when it opens the new/second form, the value of the selected item is not displayed in the textbox. Is this the best way of determing the ID of the listed item in the Listview control?

[jcis]

Example, you edit a producer name, then the FrmAddEdit needs 2 things:
- A Producer ID: You are sending ListView1.SelectedItem.Index, are you sure its ok?, you should pass the ID of the producer not the index of the row.
- A Producer Name: You are not sending it, send it to the textbox, its in the Listview also, something like (inside the With block):

.txtItem.Text = [get the producer name from the row selected in the ListView]

By the way, do you know how to extract data from different cells in a Listview???, im not very familiar with this control, maybe somebody else could help you with this.

[osemollie]

For some strange reason, am unable to display info in two columns using this code:-

Public Sub LoadList()
    Dim rst As ADODB.Recordset
    Dim db As ADODB.Connection
    
    Set db = New ADODB.Connection
    
    db.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        App.Path & "./databases/Visualtek.mdb;Persist Security Info=False")
    
    Set rst = New ADODB.Recordset
    
    With rst
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        
        Select Case mListType
            Case enuProducer
                .Source = " SELECT producerId, producer FROM producer"
            Case enuDirector
                .Source = " SELECT directorId, director  FROM director "
            Case enuActor
                .Source = " SELECT actorId, actor  FROM actor "
            Case enuGenre
                .Source = " SELECT genreId, genre  FROM genre "
            Case enuWriter
                .Source = " SELECT writer, writerId FROM writer "
            
        End Select
        
        Set .ActiveConnection = db
        .Open
    End With
           
    ' populate listview control
    Call PopulateList(ListView1, rst)
    
    rst.Close
    db.Close
    Set rst = Nothing
    Set db = Nothing
End Sub

For populating the LsitView, I use

Private Sub PopulateList(pList As ListView, pRst As ADODB.Recordset)
    'On Error Resume Next
    Dim i As Long
    Dim iColCount As Long
    Dim sColName As String
    Dim sColValue As String
    Dim oCH As ColumnHeader
    Dim oLI As ListItem
    Dim oSI As ListSubItem
    Dim oFld As ADODB.Field
 
    With pList
        .View = lvwReport
        .GridLines = True
        .FullRowSelect = True
        .ListItems.Clear
        .Sorted = False
        pRst.MoveFirst
        
        Do Until pRst.EOF
            i = 0
            ' setup fiprst column as a listitem
            sColValue = cn(pRst.Fields(i).Value)
            Set oLI = .ListItems.Add()
            oLI.Text = sColValue
            
            pRst.MoveNext
        Loop ' Next record
        ' refresh it all
        .Refresh
        ' make sure 1st row can be seen
        .ListItems(1).EnsureVisible
    End With
End Sub

What could be wrong?

[si_the_geek]

Your PopulateList sub is designed to only add one column. To add more you need to use code like this:

oLI.SubItems(1) = "your text"

[osemollie]

si_the_geek, sorry, maybe you could elaborate more, I have been using this code to add even more than 5 columns on its own when I call the function in the form_load event. It is only that this time it is not in a form_load event but in another function (Public Sub LoadList()) that it is behaving funny. I have tried

sColValue = cn(pRst.Fields(1).Value)
            Set oLI = .ListItems.Add()
            oLI.Text = sColValue
            oLI.SubItems(1) = cn(pRst.Fields(2).Value)

but I get an error "Item can not be found in collection .." How do I set it properly?

[si_the_geek]

The Fields collection starts at 0, so the first column is 0, and the second column is 1.

[osemollie]

Si, how can I do without you? ... Am almost there!!

Now, after opening a new form and adding a new entry, how can I ensure that the ListView control in the first form is refreshed to reflect the new entry as soon as I close the second form?

[si_the_geek]

As you are showing the form Modally (using .Show vbModal) no more code in that sub will run until the form is closed - so simply re-fill the Listview after that line.

[osemollie]

Guys, you were wonderful in this thread. Thanks for your patience and understanding. Special thanks to jcis and si_the_geek. God bless!!