update listview to reflect current update to access database

[KGB007]

Hi Epxerts,

This is my problem. I have a form which has a listview which displays some records from the access database based on the search criteria. A customer table by which a search is done by first or last names. It displays the records that meet the criteria. However, the form has also a button which when clicks display a form to fill and add new customer. But after updated I wish the listview to update to reflect the current matches by the same search criteria.

Please help how to do?

Thanks

KGB007

 

[Phathi]

Hi KGB007,

What you need to do is separate the process of populating the list view into a procedure (and maybe put it in a module)... then add a refresh button (or/and call the procedure each time a record is added.

E.g.

Private Sub Form1_Load()
strSQL = "Select blah,blah_blah from somewhere"
Showdata me,"somewhere",strsql,"blah","This is It"
End Sub

'in a module

Public Sub ShowData(sForm As Form, sTable As String, sViewQuery As String, _
sMenuItem As String, Optional sWindowTitle As String, _
Optional ImageName As String, Optional SortNumerics As Boolean)

sForm.m_strTableName = sTable
sForm.m_strViewQuery = sViewQuery
sForm.m_strWindowTitle = sWindowTitle
sForm.Caption = sWindowTitle
sForm.m_strMenuItem = sMenuItem

If ImageName <> &quot;&quot; Then
RefreshData sForm, ImageName, SortNumerics
Else
RefreshData sForm, , SortNumerics
End If
End Sub

Public Sub RefreshData(sForm As Form, Optional inImage As String, Optional SortNumerics As Boolean)
On Error GoTo RefreshDataErr
Dim objItem As ListItem
Dim loopHeader As ColumnHeader

Dim iLoop As Integer
Dim intNextSubItem As Integer
Dim intHeaderLength As Long

Dim a_iFieldLength() As Long
Dim iRecordCount As Long

Dim strAndKey As String
Dim Item As ListItem
Dim lngCount As Long
Dim intNumericSize As Integer
Dim strFiller As String

'clear data

sForm.ctlListView.ColumnHeaders.Clear
sForm.ctlListView.ListItems.Clear

'open data and set array length
Set m_rsdata = New ADODB.Recordset
m_rsdata.Open sForm.m_strViewQuery, DBConn

ReDim a_iFieldLength(0 To m_rsdata.Fields.Count - 1)
iRecordCount = 0

'name column headers
For iLoop = 0 To m_rsdata.Fields.Count - 1
sForm.ctlListView.ColumnHeaders.Add , COLUMN_KEY & iLoop, AddSpacesToName(m_rsdata.Fields(iLoop).Name), 3000
Next iLoop

'add data by row
Do While Not m_rsdata.EOF
strAndKey = Format(m_rsdata.Fields(0))
For Each Item In sForm.ctlListView.ListItems
If Item.Key = LISTITEM_KEY & strAndKey Then strAndKey = strAndKey + &quot;0.01&quot;
Next Item

If IsNumeric(m_rsdata.Fields(0)) And SortNumerics Then
intNumericSize = Len(Format(m_rsdata.Fields(0)))
strFiller = &quot;&quot;
Do Until Len(strFiller) = (7 - intNumericSize)
strFiller = strFiller & &quot;0&quot;
Loop
Set objItem = sForm.ctlListView.ListItems.Add(, LISTITEM_KEY & strAndKey, strFiller & Format(m_rsdata.Fields(0)))
Else
Set objItem = sForm.ctlListView.ListItems.Add(, LISTITEM_KEY & strAndKey, NullVal(m_rsdata.Fields(0)))
End If

If inImage <> &quot;&quot; Then
objItem.Icon = inImage
objItem.SmallIcon = inImage
End If
intNextSubItem = 1

For iLoop = 1 To m_rsdata.Fields.Count - 1
objItem.SubItems(intNextSubItem) = NullVal(m_rsdata.Fields(iLoop))
intNextSubItem = intNextSubItem + 1
Next iLoop

'keep an eye on field length
For iLoop = 0 To m_rsdata.Fields.Count - 1
a_iFieldLength(iLoop) = a_iFieldLength(iLoop) + Len(m_rsdata.Fields(iLoop) & &quot;&quot
Next iLoop
iRecordCount = iRecordCount + 1

m_rsdata.MoveNext
Loop

'check to see if any data was loaded
If iRecordCount = 0 Then iRecordCount = 1

'set field lengths according to an average length
For iLoop = 0 To m_rsdata.Fields.Count - 1
a_iFieldLength(iLoop) = a_iFieldLength(iLoop) / iRecordCount

If a_iFieldLength(iLoop) < Len(m_rsdata.Fields(iLoop).Name) Then
a_iFieldLength(iLoop) = Len(m_rsdata.Fields(iLoop).Name)
End If

a_iFieldLength(iLoop) = sForm.TextWidth(String(a_iFieldLength(iLoop), &quot;M&quot)
Next iLoop


For Each loopHeader In sForm.ctlListView.ColumnHeaders
loopHeader.Width = a_iFieldLength(CInt(Mid$(loopHeader.Key, COLUMN_LEN + 1)))
Next loopHeader

sForm.ctlListView.SortOrder = lvwAscending
sForm.ctlListView.SortKey = 0
Exit Sub

RefreshDataErr:
DoError &quot;Unable To Refresh ListView&quot;
End Sub



Then each time you want to refresh the listbox you can

Private Sub btnRefresh_Click()
  RefreshData Me
End Sub

don't forget to add in the beginning of your form


Public m_strTableName As String
Public m_strViewQuery As String
Public m_strWindowTitle As String
Public m_strMenuItem As String


Ciao >:O>

 

[KGB007]

Thanks Phathi

I will do as you suggested.


KGB007