Records in datagrid don't refresh after multiple

[khairuddina]

Hi. I’m new with VB6. I need your help to figure this one out.

I created a simple project utilizing ado datagrid, M.Access 97 Db, a text box, and a command button to do an update for multiple cells in datagrid. I was able to perform the update on several cells (action: clicking the command button). The records in the database were updated, however the datagrid did not refresh with the updated information. My codes are as follows (with comments).


Private Sub TxtLName_Change()
Dim mName As String

' User inputs a string value in the TxtLName text box
mName = Trim(TxtLName.Text)

' Datagrid shows only records matching the value in the textbox
If mName <> "" Then
Adodc1.Recordset.Filter = "miscdescription like '" & mName & "%'"
Else
Adodc1.Recordset.Filter = "miscdescription <> '%'"
End If
End Sub


Private Sub cmd1_Click()
Dim rsMisc As Recordset
Dim mName As String

Set rsMisc = New ADODB.Recordset
rsMisc.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\System2\Data\SysMisc.mdb;"
rsMisc.Open "Miscellaneous", , adOpenKeyset, adLockPessimistic, adCmdTable

' Take the value from the text box to filter records in data grid
mName = Trim(TxtLName.Text)
rsMisc.Find ("miscdescription like '" & mName & "%'")

' For the filtered records, flag Active field to -1, Dirty field to true and LastModified to current date.
Do Until rsMisc.EOF
If rsMisc.Fields("Active") = 0 Then
rsMisc.Fields("Active") = -1
End If
rsMisc.Fields("Dirty") = True
rsMisc.Fields("LastModified") = Now()
rsMisc.MoveNext
Loop

Datagrid1.Refresh ' show all records in data grid (refresh)
TxtLName.Text = "" 'reset the textbox to empty
TxtLName.SetFocus 'set focus to the text box.
rsMisc.Close

End Sub

[shishta]

khairuddina,
I have refreshed the records in the flexgrid after adding them to database. I am giving the code. It is a bit different but might help you. You may have to make some changes in this code according to your program.
Best Of luck.
Shishta


Private Sub cmdbrowse_Click()
rsmaster.Requery
rsmaster.find ("MasterName='" & Trim(lblmaster.Caption) & "'")
rsmaster.MoveNext
Dim i As Integer
Dim str_sel_field As String
Dim str_formatstring
Dim col_count As Integer
col_count = 0
str_sel_field = ""
For i = 0 To rsmaster(2) - 1
If str_sel_field = "" Then
str_sel_field = rsmaster(i + 3)
Else
str_sel_field = str_sel_field & "," & rsmaster(i + 3)
End If
Next
rsmaster.MovePrevious
str_formatstring = ""
For i = 0 To rsmaster(2) - 1
If str_formatstring = "" Then
str_formatstring = "|<" & rsmaster(i + 3)
col_count = col_count + 1
Else
str_formatstring = str_formatstring & " |< " & rsmaster(i + 3)
col_count = col_count + 1
End If
Next
str_formatstring = str_formatstring & " "
msh.Clear
msh.Visible = True
Dim str As String
Dim mshrow As Integer
mshrow = 1
str = ""
Dim rs1 As New ADODB.Recordset
If rs1.State = 1 Then rs1.Close
If Action = "Search" Then
If searchtype = "string" Then
rs1.Open "select " & str_sel_field & " from " & Trim(varmastername) & " where " & searchfieldname & "='" & Trim(lstsearch.Text) & "'", con, adOpenDynamic, adLockOptimistic
ElseIf searchtype = "number" Then
rs1.Open "select " & str_sel_field & " from " & Trim(varmastername) & " where " & searchfieldname & "=" & Trim(lstsearch.Text), con, adOpenDynamic, adLockOptimistic
'access
ElseIf searchtype = "date" Then
rs1.Open "select " & str_sel_field & " from " & Trim(varmastername) & " where " & searchfieldname & "=# " & Trim(lstsearch.Text) & "#, con, adOpenDynamic, adLockOptimistic"
'sql
'ElseIf searchtype = "date" Then
' rs1.Open "select " & str_sel_field & " from " & Trim(varmastername) & " where " & searchfieldname & "=' " & Trim(lstsearch.Text) & "', con, adOpenDynamic, adLockOptimistic"
End If
Else
rs1.Open "select * from " & Trim(varmastername), con, adOpenDynamic, adLockOptimistic
End If
If rs1.EOF = False Then
While rs1.EOF = False
For j = 0 To col_count - 1
If mshrow >= msh.Rows - 1 Then
msh.Rows = msh.Rows + 1
End If
msh.TextMatrix(mshrow, j + 1) = rs1(j) & ""
Next
mshrow = mshrow + 1
rs1.MoveNext
Wend
msh.FormatString = msh.FormatString
msh.Visible = True
msh.Rows = mshrow
Else
msh.FormatString = str_formatstring
mshtotrow = 1
msh.Visible = True
If mshrows < 15 Then
msh.Rows = 15
End If
End If
msh.ColWidth(1) = 10
End Sub



Private Sub cmdsave_Click()
On Error GoTo err
If con.State = 0 Then
Form_Load
End If


If rsmaster.State = 0 Then
rsmaster.Requery
End If
rsmaster.MoveFirst
rsmaster.find ("MasterName='" & Trim(lblmaster.Caption) & "'")
rsmaster.MoveNext: rsmaster.MoveNext: rsmaster.MoveNext


If Action <> "Import" Then
For i = 3 To CInt(rsmaster(2)) + 3
If rsmaster(i) = "Not Null" Then
If Trim(Text1(i - 3).Text) = "" Then
MsgBox Label1(i - 3).Caption & " Can't Be left Blank", vbInformation
Text1(i - 3).SetFocus
Exit Sub
End If
ElseIf rsmaster(i) = "Unique" Then
If Trim(Text1(i - 3).Text) = "" Then
MsgBox Label1(i - 3).Caption & " Can't Be left Blank", vbInformation
Text1(i - 3).SetFocus
Exit Sub
End If
rsmaster.MovePrevious: rsmaster.MovePrevious
Dim fieldname As String
fieldname = rsmaster(i)
rsmaster.MoveNext: rsmaster.MoveNext
If rsexp.State = 1 Then rsexp.Close
If Action = "Add" Then
rsexp.Open "select " & Trim(fieldname) & " from " & Trim(varmastername) & " Where " & Trim(fieldname) & " ='" & Trim(Text1(i - 3).Text) & "' ", con, adOpenDynamic, adLockOptimistic
If rsexp.EOF = False And rsexp.BOF = False Then
MsgBox "Duplicate " & Label1(i - 3).Caption, vbInformation
Text1(i - 3).SetFocus
Exit Sub
End If
ElseIf Action = "Mod" Then
rsexp.Open "select count(" & Trim(fieldname) & ") from " & Trim(varmastername) & " Where " & Trim(fieldname) & " ='" & Trim(Text1(i - 3).Text) & "' and " & var_keyfield & "<> " & Trim(Text1(0).Text) & "", con, adOpenDynamic, adLockOptimistic
If rsexp(0) = 1 Then
MsgBox "Duplicate " & Label1(i - 3).Caption, vbInformation
Text1(i - 3).SetFocus
Exit Sub
End If
End If
End If
Next
End If

rsmaster.MovePrevious
Dim rs1 As New ADODB.Recordset
If rs1.State = 1 Then rs1.Close
rs1.Open "select * from " & Trim(varmastername), con, adOpenDynamic, adLockOptimistic
If Action = "Add" Then

rs1.AddNew
For i = 0 To rsmaster(2) - 1
'MsgBox i
' MsgBox Label1(i).Caption & ": " & Text1(i).Text
rs1(i) = Text1(i).Text & ""
Next
If glousername <> "" Then
rs1!UserName = glousername
End If
rs1!logindatetime = DateTime.Now
rs1!t_type = "Add"
rs1.Update


rsmainmaster.Requery
rsmainmaster.MoveLast
totrec = totrec + 1
currpos = totrec
movefields
Dim rsnewexp As New ADODB.Recordset
If rsnewexp.State = 1 Then rsnewexp.Close

ElseIf Action = "Mod" Then
rsmaster.Requery
rsmaster.find ("MasterName='" & Trim(lblmaster.Caption) & "'")
Dim j As Integer
' rs1.CancelUpdate
j = rsmaster(2)
rsmaster.MoveNext: rsmaster.MoveNext: rsmaster.MoveNext
For i = 0 To j - 1
If Left(rsmaster(i + 3), 4) = "Auto" Or Left(rsmaster(i + 3), 2) = "ID" Then
rsmaster.MovePrevious: rsmaster.MovePrevious
fieldname = rsmaster(i + 3)
Exit For
End If
Next
rs1.find (var_keyfield & "=" & Trim(Text1(0).Text) & "")
j = i
Dim n As Integer
For n = 0 To rsmaster(2) - 1
If n = j Then
Else
'MsgBox Label1(n).Caption & " :" & n & ": " & Text1(n)
If n = 21 Or n = 3 Or n = 7 Then
If Text1(n).Text = "" Then
rs1(n) = Null
End If
Else
rs1(n) = Text1(n)
End If
End If
Next

If glousename <> "" Then
rs1!UserName = glousername
End If
rs1!logindatetime = DateTime.Now
rs1!t_type = "Modify"
rs1.Update

Call UnRecordLock("" & varmastername & "", "SerialNo", Text1(0).Text)

rsmainmaster.Requery
currpos = 1

ElseIf Action = "Import" Then
Dim mshrow As Integer
Dim str_not_in As String
If rs.State = 1 Then rs.Close
If rs.State = 1 Then rs.Close
rs.Open "select * from control", con, adOpenDynamic, adLockOptimistic

' rs.CancelUpdate
If rs.State = 1 Then rs.Close
rs.Open "select * from employeemaster", con, adOpenDynamic, adLockOptimistic

mshrow = 1
While mshrow < msh.Rows
rs.AddNew
For i = 1 To msh.Cols - 1
rs(i - 1) = msh.TextMatrix(mshrow, i)
Next
rs.Update
lblimport.Caption = mshrow & " Records Saved......"
lblimport.Refresh
mshrow = mshrow + 1
Wend
lblimport.Caption = "All Records Saved......"
' MsgBox mshrow

End If
Action = ""
Call saveclick
cmdbrowse_Click
Exit Sub
err:
MsgBox err.Number & err.Description
rs.CancelUpdate

End Sub