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
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
grouper15
how do you display the record number and a descriptive field of each record in listview control
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
For Each itmX In Me.lvwPCSkill.ListItems
itmX.SubItems(4) =itmX.SubItems(4) + 1
Next
Steve
ASKER
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).Ope
Set rsOutline = db.OpenRecordset("select * from Outline where Novel='" & lblTitle.Caption & "' and Chapter='" & lstChapters.SelectedItem.I
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).Ope
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.Lis
End If
If updateClick Then
MsgBox ("Chapter Updated")
End If
End Sub
Here is the relevant code:
Private Sub cmdInsert_Click()
Set db = DBEngine.Workspaces(0).Ope
Set rsOutline = db.OpenRecordset("select * from Outline where Novel='" & lblTitle.Caption & "' and Chapter='" & lstChapters.SelectedItem.I
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).Ope
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.Lis
End If
If updateClick Then
MsgBox ("Chapter Updated")
End If
End Sub
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(A
End If
'build SQL statement to check for existing chapter
strSQL = "SELECT Chapter " & _
"FROM Outline " & _
"WHERE Novel='" & Me.lblTitle.Caption & "' " & _
"AND Chapter='" & Me.lstChapters.SelectedIte
'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(A
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.A
MsgBox ("Chapter Added")
End If
'cleanup
rs.Close
Set rs = Nothing
End Sub
Steve
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(A
End If
'build SQL statement to check for existing chapter
strSQL = "SELECT Chapter " & _
"FROM Outline " & _
"WHERE Novel='" & Me.lblTitle.Caption & "' " & _
"AND Chapter='" & Me.lstChapters.SelectedIte
'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(A
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.A
MsgBox ("Chapter Added")
End If
'cleanup
rs.Close
Set rs = Nothing
End Sub
Steve
ASKER
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
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
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
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
ASKER
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).Ope
Set chptrhead1 = lstChapters.ColumnHeaders.
Set namehead1 = lstChapters.ColumnHeaders.
Set rsOutline = db.OpenRecordset("select * from Outline where Novel='" & NovelMain.lvTitles.Selecte
While Not rsOutline.EOF
If rsOutline!Novel <> "" Then
'LstChar.AddItem (rsChars!Name)
'frmOutline.lstChapters.Ad
Set lstChapter = frmOutline.lstChapters.Lis
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.I
Set db = DBEngine.Workspaces(0).Ope
Set rsOutline = db.OpenRecordset("select * from Outline where Novel='" & lblTitle.Caption & "' order by Chapter")
While Not rsOutline.EOF
If Mid$(lstChapters.SelectedI
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).Ope
Set rsOutline = db.OpenRecordset("select * from Outline where Novel='" & lblTitle.Caption & "' and Chapter='" & lstChapters.SelectedItem.I
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(A
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.Lis
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
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).Ope
Set chptrhead1 = lstChapters.ColumnHeaders.
Set namehead1 = lstChapters.ColumnHeaders.
Set rsOutline = db.OpenRecordset("select * from Outline where Novel='" & NovelMain.lvTitles.Selecte
While Not rsOutline.EOF
If rsOutline!Novel <> "" Then
'LstChar.AddItem (rsChars!Name)
'frmOutline.lstChapters.Ad
Set lstChapter = frmOutline.lstChapters.Lis
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.I
Set db = DBEngine.Workspaces(0).Ope
Set rsOutline = db.OpenRecordset("select * from Outline where Novel='" & lblTitle.Caption & "' order by Chapter")
While Not rsOutline.EOF
If Mid$(lstChapters.SelectedI
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).Ope
Set rsOutline = db.OpenRecordset("select * from Outline where Novel='" & lblTitle.Caption & "' and Chapter='" & lstChapters.SelectedItem.I
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(A
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.Lis
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
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
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
ASKER
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.
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.
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
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
ASKER
This question was not answered to my satisfaction. Why mark it PAQ?
so,pls make up your decision...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.