garydp
05-12-2015, 09:11 AM
I have an Access database that i write to using VBA from an excel spreadsheet.
the spreadsheet has a listview called lstfail loaded with all of the data that is entered into the database.
when data is entered onto the spreadsheet the data is writen to the database without any problem. once the data is saved to the database the listview should update with the latest data from the database. This isnt happening, i am having to close excel and reopen to see the new data. i have even put a time delay in before reading back the data.
this is how im writing to the database
Set cn = New ADODB.Connection
dbPath = Application.ActiveWorkbook.Path & "\Touch Sheet.mdb"
cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & dbPath & ";"
Set rs = New ADODB.Recordset
sSQL = "SELECT * From [Failures]"
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!Date = Format(Now, "dd/mm/yyyy")
rs!Month = Format(Now, "mmmm")
rs!CompTime = ""
rs!Category = cbFCat.Value
rs!Type = cbFType.Value
rs!WO = cbFWO.Value
rs!Serial = txtSerial.Text
rs!PartNo = cbFPartNo.Value
rs!TBCode = Left(cbFTBCode.Value, 2)
rs!TBCodeDesc = Replace(cbFTBCode.Value, Left(cbFTBCode.Value, 4), "")
rs!Lost = "1"
rs!Reason = txtReason.Text
rs!Operator = cbFOperator.Value
rs!process = cbFProcess.Value
rs.Update
rs.Close
this is how loading the listview with the data after writing to the databse
Set cn = New ADODB.Connection
dbPath = Application.ActiveWorkbook.Path & "\Touch Sheet.mdb"
cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & dbPath & ";"
Set rs = New ADODB.Recordset
sSQL = "SELECT * From [Failures] ORDER BY [WO]"
rs.Open sSQL, cn, adOpenDynamic, adLockUnspecified
i = 1
Do Until rs.EOF
With frmTouchSheet.lstFail
.ListItems.Add , , ""
.ListItems.Item(i).ListSubItems.Add = rs!Date
.ListItems.Item(i).ListSubItems.Add = rs!Month
.ListItems.Item(i).ListSubItems.Add = rs!CompTime
.ListItems.Item(i).ListSubItems.Add = rs!Category
.ListItems.Item(i).ListSubItems.Add = rs!Type
.ListItems.Item(i).ListSubItems.Add = rs!WO
.ListItems.Item(i).ListSubItems.Add = rs!Serial
.ListItems.Item(i).ListSubItems.Add = rs!PartNo
.ListItems.Item(i).ListSubItems.Add = rs!TBCode
.ListItems.Item(i).ListSubItems.Add = rs!TBCodeDesc
.ListItems.Item(i).ListSubItems.Add = rs!Lost
.ListItems.Item(i).ListSubItems.Add = rs!Reason
.ListItems.Item(i).ListSubItems.Add = rs!Operator
.ForeColor = vbBlack
.SelectedItem.Selected = False
End With
i = i + 1
rs.MoveNext
Loop
when the first set of data is entered it is loaded into the listview straight away, form the second set onwards it doesnt load or it loads the first set again.
the spreadsheet has a listview called lstfail loaded with all of the data that is entered into the database.
when data is entered onto the spreadsheet the data is writen to the database without any problem. once the data is saved to the database the listview should update with the latest data from the database. This isnt happening, i am having to close excel and reopen to see the new data. i have even put a time delay in before reading back the data.
this is how im writing to the database
Set cn = New ADODB.Connection
dbPath = Application.ActiveWorkbook.Path & "\Touch Sheet.mdb"
cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & dbPath & ";"
Set rs = New ADODB.Recordset
sSQL = "SELECT * From [Failures]"
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!Date = Format(Now, "dd/mm/yyyy")
rs!Month = Format(Now, "mmmm")
rs!CompTime = ""
rs!Category = cbFCat.Value
rs!Type = cbFType.Value
rs!WO = cbFWO.Value
rs!Serial = txtSerial.Text
rs!PartNo = cbFPartNo.Value
rs!TBCode = Left(cbFTBCode.Value, 2)
rs!TBCodeDesc = Replace(cbFTBCode.Value, Left(cbFTBCode.Value, 4), "")
rs!Lost = "1"
rs!Reason = txtReason.Text
rs!Operator = cbFOperator.Value
rs!process = cbFProcess.Value
rs.Update
rs.Close
this is how loading the listview with the data after writing to the databse
Set cn = New ADODB.Connection
dbPath = Application.ActiveWorkbook.Path & "\Touch Sheet.mdb"
cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & dbPath & ";"
Set rs = New ADODB.Recordset
sSQL = "SELECT * From [Failures] ORDER BY [WO]"
rs.Open sSQL, cn, adOpenDynamic, adLockUnspecified
i = 1
Do Until rs.EOF
With frmTouchSheet.lstFail
.ListItems.Add , , ""
.ListItems.Item(i).ListSubItems.Add = rs!Date
.ListItems.Item(i).ListSubItems.Add = rs!Month
.ListItems.Item(i).ListSubItems.Add = rs!CompTime
.ListItems.Item(i).ListSubItems.Add = rs!Category
.ListItems.Item(i).ListSubItems.Add = rs!Type
.ListItems.Item(i).ListSubItems.Add = rs!WO
.ListItems.Item(i).ListSubItems.Add = rs!Serial
.ListItems.Item(i).ListSubItems.Add = rs!PartNo
.ListItems.Item(i).ListSubItems.Add = rs!TBCode
.ListItems.Item(i).ListSubItems.Add = rs!TBCodeDesc
.ListItems.Item(i).ListSubItems.Add = rs!Lost
.ListItems.Item(i).ListSubItems.Add = rs!Reason
.ListItems.Item(i).ListSubItems.Add = rs!Operator
.ForeColor = vbBlack
.SelectedItem.Selected = False
End With
i = i + 1
rs.MoveNext
Loop
when the first set of data is entered it is loaded into the listview straight away, form the second set onwards it doesnt load or it loads the first set again.