How can I update a listview control in real time to reflect an inserted record?

I have an application that stores list information in an Access database.  I have a listview control which displays the record number and a descriptive field of each record. (the record number is not stored, it comes from a record count).

I have a button which when clicked will allow me to fill out a form with record data and insert that record into the database after the record which I currently have hilighted in the tlistview box.

Now, the record inserts fine into the database, and the listview control shows the inserted record, however, I want the record number on every subsequent item in the listview control to increment by one.

If you want to see code, I'll see if I can post the relevant sections.  I'll try not toconfuse anyone.

Thanks,
Barry62
LVL 8
Barry62Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

grouper15Commented:
how do you display the record number and a descriptive field of each record in listview control
stevbeCommented:
Reset the subitem value that represents your counter for the items that are left?

    For Each itmX In Me.lvwPCSkill.ListItems
         itmX.SubItems(4) =itmX.SubItems(4) + 1
    Next


Steve
Barry62Author Commented:
Actually, now that I ran through it again, I don't think that my program is even updating the database right.  When I insert, the listview control places a new item right where it should, but the update routine overwrites the record after it.

Here is the relevant code:

Private Sub cmdInsert_Click()
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\novel.mdb")
Set rsOutline = db.OpenRecordset("select * from Outline where Novel='" & lblTitle.Caption & "' and Chapter='" & lstChapters.SelectedItem.Index & "'")
insflag = True
If rsOutline.EOF Then
    inschap = 1
End If
inschap = rsOutline("Chapter") + 1
txtChapter.Text = inschap
txtName.SetFocus
txtName.Text = ""
txtChars.Text = ""
txtTime.Text = ""
txtGoal.Text = ""
txtLoc.Text = ""
txtNewDev.Text = ""
End Sub



Private Sub cmdUpdateA_Click()
updateClick = True
Call updateIT
updateClick = False
End Sub


Private Sub updateIT()
Dim match As Boolean
match = False
updflg = True
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\novel.mdb")
Set rsOutline = db.OpenRecordset("select * from Outline")
While Not rsOutline.EOF
    If rsOutline!Chapter = txtChapter.Text Then
        rsOutline.Edit
        rsOutline!Novel = lblTitle.Caption
        rsOutline!Chapter = txtChapter.Text
        rsOutline!Name = txtName.Text
        rsOutline!Characters = txtChars.Text
        rsOutline!Time = txtTime.Text
        rsOutline!Goal = txtGoal.Text
        rsOutline!Location = txtLoc.Text
        rsOutline!Develop = txtNewDev.Text
        rsOutline.Update
        match = True
       
    End If
            rsOutline.MoveNext
Wend
If Not match Then
    rsOutline.AddNew
    rsOutline!Novel = lblTitle.Caption
    rsOutline!Chapter = txtChapter.Text
    rsOutline!Name = txtName.Text
    rsOutline!Characters = txtChars.Text
    rsOutline!Time = txtTime.Text
    rsOutline!Goal = txtGoal.Text
    rsOutline!Location = txtLoc.Text
    rsOutline!Develop = txtNewDev.Text
    rsOutline.Update
End If
rsOutline.Close
If Not match Then
Set lstChapter = frmOutline.lstChapters.ListItems.Add(, , txtChapter.Text & " - " & rsOutline!Name)
End If
If updateClick Then
MsgBox ("Chapter Updated")
End If
End Sub
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

stevbeCommented:
Option Compare Database
Option Explicit

Private db As DAO.Database
Private rs As DAO.Recordset
Private strSQL As String



Private Sub cmdInsert_Click()
    'see if the database has already been opened
    If db Is Nothing Then
        Set db = DBEngine(0).OpenDatabase(App.Path & "\novel.mdb")
    End If
   
    'build SQL statement to check for existing chapter
    strSQL = "SELECT Chapter " & _
             "FROM Outline " & _
             "WHERE Novel='" & Me.lblTitle.Caption & "' " & _
             "AND Chapter='" & Me.lstChapters.SelectedItem.Index & "'"
   
    'open dataset to see if there is already a chapter
    Set rs = db.OpenRecordset(strSQL)

    'assign new chapter number
    If rs.EOF Then
        Me.txtChapter.Text = 1
    Else
        Me.txtChapter.Text = rs!Chapter + 1
    End If
   
    'cleanup recordset
    rs.Close
    Set rs = Nothing

    'populate form controls
    Me.txtName.SetFocus
    Me.txtName.Text = vbNullString
    Me.txtChars.Text = vbNullString
    Me.txtTime.Text = vbNullString
    Me.txtGoal.Text = vbNullString
    Me.txtLoc.Text = vbNullString
    Me.txtNewDev.Text = vbNullString

End Sub



Private Sub cmdUpdate_Click()
    'see if the database has already been opened
    If db Is Nothing Then
        Set db = DBEngine(0).OpenDatabase(App.Path & "\novel.mdb")
    End If

    'build SQL to get current chapter data
    strSQL = "SELECT * " & _
             "FROM Outline " & _
             "WHERE Chapter = '" & Me.txtChapter.Text & "'"

    'get current chapter data
    Set rs = db.OpenRecordset(strSQL)
   
    'update any existing resords for this chapter
    If rs.RecordCount > 0 Then
        Do While Not rsOutline.EOF
            rs.Edit
                rs!Novel = Me.lblTitle.Caption
                rs!Chapter = Me.txtChapter.Text
                rs!Name = Me.txtName.Text
                rs!Characters = Me.txtChars.Text
                rs!Time = Me.txtTime.Text
                rs!Goal = Me.txtGoal.Text
                rs!Location = Me.txtLoc.Text
                rs!Develop = Me.txtNewDev.Text
            rs.Update
            rs.MoveNext
        Loop
        MsgBox ("Chapter Updated")
    Else
        'no existing data for this chapter so add a new one
        rs.AddNew
            rs!Novel = Me.lblTitle.Caption
            rs!Chapter = Me.txtChapter.Text
            rs!Name = Me.txtName.Text
            rs!Characters = Me.txtChars.Text
            rs!Time = Me.txtTime.Text
            rs!Goal = Me.txtGoal.Text
            rs!Location = Me.txtLoc.Text
            rs!Develop = Me.txtNewDev.Text
        rs.Update
        'add new chapter to chapters listview
        Me.lstChapters.ListItems.Add , , Me.txtChapter.Text & " - " & rs!Name
        MsgBox ("Chapter Added")
    End If
   
    'cleanup
    rs.Close
    Set rs = Nothing
   
End Sub


Steve
Barry62Author Commented:
OK,

My table is being updated correctly, now.  However, your insert sub still doesn't work the way I want it to.  After all was said and done, the record was inserted correctly, but I had to exit the form and go back in to see the final change.  Your insert sub updated the currently selected list item while I typed.  I want the listview control to display the inserted chapter as soon as I hit the insert button (which my subroutine already does).  What my subroutine does NOT do is renumber every item below the inserted one.  Now we're back to my original question.

Barry62
stevbeCommented:
back to my original post ...

where 4 = the column that contains your record number


add this to the end of the cmdUpdate_Click

 Dim itmX As ListItem


   For Each itmX In Me.lstChaptersListItems
        itmX.SubItems(4) = itmX.SubItems(4) + 1
   Next

Steve
Barry62Author Commented:
OK, I'm getting confused.

I wasn't even using subitems.  I have tried to modify my code to use them, and I'm getting lots of new and exciting errors.  AND, when the listview updates to reflect an iserted chapter number, the subsequent chapter numbers still do not increment.  Here is my modified code, including the Form_Load subroutine and lstChapters_Click subroutine:


Private Sub Form_Load()
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\novel.mdb")
Set chptrhead1 = lstChapters.ColumnHeaders.Add(, , "Chapter Number", 200)
Set namehead1 = lstChapters.ColumnHeaders.Add(, , "Chapter Title", lstChapters.Width - 200)
Set rsOutline = db.OpenRecordset("select * from Outline where Novel='" & NovelMain.lvTitles.SelectedItem & "'")
While Not rsOutline.EOF
    If rsOutline!Novel <> "" Then
        'LstChar.AddItem (rsChars!Name)
        'frmOutline.lstChapters.AddItem (rsOutline!Chapter)
        Set lstChapter = frmOutline.lstChapters.ListItems.Add(, , rsOutline!Chapter)
        lstChapter.SubItems(1) = " - " & rsOutline!Name
    End If
rsOutline.MoveNext
Wend
'lstChapters.SelectedItem = lstChapters.
rsOutline.MoveFirst
txtChapter.Text = rsOutline!Chapter
txtName.Text = rsOutline!Name
txtChars.Text = rsOutline!Characters
txtTime.Text = rsOutline!Time
txtGoal.Text = rsOutline!Goal
txtLoc.Text = rsOutline!Location
txtNewDev.Text = rsOutline!Develop
rsOutline.Close
End Sub


Private Sub lstChapters_Click()
curChapter = lstChapters.SelectedItem.Index
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\novel.mdb")
Set rsOutline = db.OpenRecordset("select * from Outline where Novel='" & lblTitle.Caption & "' order by Chapter")
While Not rsOutline.EOF
If Mid$(lstChapters.SelectedItem.Text, 1, 1) = rsOutline!Chapter Then
    txtChapter.Text = rsOutline!Chapter
    txtName.Text = rsOutline!Name
    txtChars.Text = rsOutline!Characters
    txtTime.Text = rsOutline!Time
    txtGoal.Text = rsOutline!Goal
    txtLoc.Text = rsOutline!Location
    txtNewDev.Text = rsOutline!Develop
End If
rsOutline.MoveNext
Wend
End Sub


Private Sub cmdInsert_Click()
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\novel.mdb")
Set rsOutline = db.OpenRecordset("select * from Outline where Novel='" & lblTitle.Caption & "' and Chapter='" & lstChapters.SelectedItem.Index & "'")
insflag = True
If rsOutline.EOF Then
    inschap = 1
End If
inschap = rsOutline("Chapter") + 1
txtChapter.Text = inschap
txtName.SetFocus
txtName.Text = ""
txtChars.Text = ""
txtTime.Text = ""
txtGoal.Text = ""
txtLoc.Text = ""
txtNewDev.Text = ""
txtNewDev.Text = ""
End Sub


Private Sub cmdUpdate_Click()
   'see if the database has already been opened
   If db Is Nothing Then
       Set db = DBEngine(0).OpenDatabase(App.Path & "\novel.mdb")
   End If
   'build SQL to get current chapter data
   strSQL = "SELECT * " & _
            "FROM Outline " & _
            "WHERE Chapter = '" & Me.txtChapter.Text & "'"
   'get current chapter data
   Set rsOutline = db.OpenRecordset(strSQL)
   'update any existing resords for this chapter
   If rsOutline.RecordCount > 0 Then
       Do While Not rsOutline.EOF
           rsOutline.Edit
               rsOutline!Novel = Me.lblTitle.Caption
               rsOutline!Chapter = Me.txtChapter.Text
               rsOutline!Name = Me.txtName.Text
               rsOutline!Characters = Me.txtChars.Text
               rsOutline!Time = Me.txtTime.Text
               rsOutline!Goal = Me.txtGoal.Text
               rsOutline!Location = Me.txtLoc.Text
               rsOutline!Develop = Me.txtNewDev.Text
           rsOutline.Update
           rsOutline.MoveNext
       Loop
       MsgBox ("Chapter Updated")
   Else
       'no existing data for this chapter so add a new one
       rsOutline.AddNew
           rsOutline!Novel = Me.lblTitle.Caption
           rsOutline!Chapter = Me.txtChapter.Text
           rsOutline!Name = Me.txtName.Text
           rsOutline!Characters = Me.txtChars.Text
           rsOutline!Time = Me.txtTime.Text
           rsOutline!Goal = Me.txtGoal.Text
           rsOutline!Location = Me.txtLoc.Text
           rsOutline!Develop = Me.txtNewDev.Text
       rsOutline.Update
       'add new chapter to chapters listview
       Set lstChapter = frmOutline.lstChapters.ListItems.Add(, , rsOutline!Chapter)
        lstChapter.SubItems(1) = " - " & rsOutline!Name
       MsgBox ("Chapter Added")
   End If
   'cleanup
   rsOutline.Close
   Set rs = Nothing
   Dim itmX As ListItem
For Each itmX In Me.lstChaptersListItems
     itmX.SubItems(1) = itmX.SubItems(1) + 1
Next
End Sub

stevbeCommented:
A quick summary to make sure I understand what exactly it is we need to do...

What are displaying in the listview? ChapterNumber & ChapterName or just ChapterNumber?
Where and what are the errors?

Private Sub Form_Load:
Populates complete list of all chapters and  displays 1st chapter info for novel selected in NovelMain?

Private Sub lstChapters_Click()
Displays selected chapter info

Private Sub cmdInsert_Click()
Set inserted chapter number and display blank fields

Private Sub cmdUpdate_Click()
If chapter already exists then increment chapter number and all higher numbered chapters by 1
Create a new record if chapter.

Steve
Barry62Author Commented:
ChapterNumber & ChapterName in the ListView

I am only getting one actual error: in the For loop at the end of the Update sub. The line is: itmX.SubItems(1) = itmX.SubItems(1) + 1

I get a type mismatch error.

otherwise, the listview object should increment the Chapter numbers following the one that is inserted.

Private Sub Form_Load
Correct.

Private Sub lstChapters_Click()
Correct.

Private Sub cmdInsert_Click()
Correct, but also increment the Chapter numbers following the one that is inserted.

Private Sub cmdUpdate_Click()
Actually, this sub should simply update the table.

Sorry if I've confused you.
MYLimCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- PAQ and point No Refund

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

MYLim
EE Cleanup Volunteer
Barry62Author Commented:
This question was not answered to my satisfaction.  Why mark it PAQ?
MYLimCommented:
so,pls make up your decision...
Computer101Commented:
PAQed, with points refunded (250)

Computer101
E-E Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.