0

i am working with VB6. i used ADODC recordset. in my Form there is some text box and command bottom. it is about Student Data for example. it can view and add new record. i want a view after adding new record.so how can i refresh DataBase record from Access with closing a Form.


Coding


'Option Explicit

Private WithEvents Chan As ADODB.Recordset
Private Nyein As ADODB.Recordset
Private Conn As New ADODB.Connection
Private LinkAction As String
Private id As String


Private Sub cmdAdd_Click()
LockCmd False
If cmdAdd.Caption = "&New" Then
Call clear
txtID.SetFocus
lblRecord.Caption = "Adding New Record"
cmdAdd.Caption = "&Save"
Exit Sub
End If

If txtID.Text <> "" Then
If LinkAction = "Edit" Then
Data1.RecordSource = "select * from Personal where ID = '" & txtID & "'"
Data1.Refresh
If Data1.Recordset.RecordCount > 0 Then
Data1.Recordset.MoveFirst
Data1.Recordset.Edit
Data1.Recordset.Fields("ID") = txtID
Data1.Recordset.Fields("NAME") = txtName
Data1.Recordset.Fields("ADDR") = txtAdd
Data1.Recordset.Update
Call clear
MsgBox "Successfully Edit Data!", vbInformation + vbOKOnly, "Student Data"
Else
MsgBox "Record Not Found!", vbInformation + vbOKOnly, "Student Data"
End If
Else
' Data1.Recordset.AddNew
' Data1.Recordset.Fields("ID") = txtID
' Data1.Recordset.Fields("NAME") = txtName
' Data1.Recordset.Fields("ADDR") = txtAdd
' Data1.Recordset.Update
'Chan.LockType = adLockOptimistic
Nyein.AddNew
Nyein.Fields("ID") = txtID
Nyein.Fields("NAME") = txtName
Nyein.Fields("ADDR") = txtAdd
Nyein.Update
Call clear
txtID.SetFocus
MsgBox "Successfully Add New Data!", vbInformation + vbOKOnly, "Student Data"
End If
Else
MsgBox "Enter a data!", vbInformation + vbOKOnly, "Student Data"
End If
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub cmdDelete_Click()
id = InputBox("Enter a ID which you want to delete!", "Student Data")
Data1.RecordSource = "select * from Personal where ID = '" & id & "'"
Data1.Refresh

If Data1.Recordset.RecordCount > 0 Then
Data1.Recordset.MoveFirst
Data1.Recordset.Delete
MsgBox "Successfully Delete Data!", vbInformation + vbOKOnly, "Student Data"
Else
MsgBox "Record Not Found!", vbInformation + vbOKOnly, "Student Data"
End If
End Sub

Private Sub cmdEdit_Click()
LinkAction = "Edit"
cmdAdd.Caption = "&Update"
txtID.Enabled = False

id = InputBox("Enter a ID which you want to edit!", "Student Data")
Data1.RecordSource = "select * from Personal where ID = '" & id & "'"
Data1.Refresh

If Data1.Recordset.RecordCount > 0 Then
txtID.Text = Data1.Recordset.Fields("ID")
txtName.Text = Data1.Recordset.Fields("NAME")
txtAdd.Text = Data1.Recordset.Fields("ADDR")
End If
End Sub

Private Sub cmdNext_Click()
If Chan.AbsolutePosition < Chan.RecordCount Then Chan.MoveNext
Contorl_Command_Button
lblRecord.Caption = "Record " & Chan.AbsolutePosition & " Of " & Chan.RecordCount
End Sub

Private Sub cmdPrevious_Click()
If Chan.AbsolutePosition > 1 Then Chan.MovePrevious
Contorl_Command_Button
lblRecord.Caption = "Record " & Chan.AbsolutePosition & " Of " & Chan.RecordCount
End Sub

Private Sub cmdTop_Click()
Chan.MoveFirst
Contorl_Command_Button
lblRecord.Caption = "Record " & Chan.AbsolutePosition & " Of " & Chan.RecordCount
End Sub

Private Sub cmdBottom_Click()
Chan.MoveLast
Contorl_Command_Button
lblRecord.Caption = "Record " & Chan.AbsolutePosition & " Of " & Chan.RecordCount
End Sub

Private Sub Command1_Click()
LockCmd True
Nyein.Requery
Nyein.MoveFirst
txtID.Text = Nyein.Fields("ID")
txtName.Text = Nyein.Fields("NAME")
txtAdd.Text = Nyein.Fields("ADDR")
lblRecord.Caption = "Record " & Chan.AbsolutePosition & " Of " & Chan.RecordCount
End Sub

Private Sub Form_Load()
Data1.DatabaseName = App.Path & "\StudentData.mdb"
Data1.RecordSource = "Personal"
Data1.Visible = False

'Test
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & App.Path & "\StudentData.mdb;"

Set Nyein = New ADODB.Recordset
Nyein.ActiveConnection = Conn
Nyein.Source = "select * from Personal"
Nyein.CursorType = adOpenStatic
Nyein.LockType = adLockOptimistic
Nyein.Open

Set Chan = New ADODB.Recordset
Chan.ActiveConnection = Conn
Chan.Source = "select * from Personal"
Chan.CursorType = adOpenStatic
Chan.LockType = adLockOptimistic
Chan.CursorLocation = adUseClient
Chan.Open
If Chan.RecordCount < 1 Then
cmdTop.Enabled = False
cmdPrevious.Enabled = False
cmdNext.Enabled = False
cmdBottom.Enabled = False
End If
lblRecord.Caption = "Record " & Chan.AbsolutePosition & " Of " & Chan.RecordCount
End Sub

Private Sub clear()
txtID.Text = ""
txtName.Text = ""
txtAdd.Text = ""
End Sub

Private Sub Chan_Movecomplete(ByVal adReason As ADODB.EventReasonEnum, _
ByVal pError As ADODB.Error, _
adStatus As ADODB.EventStatusEnum, _
ByVal pRecordset As ADODB.Recordset)
If Chan.RecordCount < 1 Then
lblRecord.Caption = "No Record Exist."
Exit Sub
End If
Display_Data
End Sub

Private Sub Display_Data()
txtID.Text = Chan.Fields("ID")
txtName.Text = Chan.Fields("NAME")
txtAdd.Text = Chan.Fields("ADDR")
End Sub

Private Sub Contorl_Command_Button()
If Chan.AbsolutePosition = 1 Then
cmdTop.Enabled = False
cmdPrevious.Enabled = False
cmdNext.Enabled = True
cmdBottom.Enabled = True
ElseIf Chan.AbsolutePosition = Chan.RecordCount Then
cmdTop.Enabled = True
cmdPrevious.Enabled = True
cmdNext.Enabled = False
cmdBottom.Enabled = False
Else
cmdTop.Enabled = True
cmdPrevious.Enabled = True
cmdNext.Enabled = True
cmdBottom.Enabled = True
End If
End Sub

Private Sub LockCmd(Status As Boolean)
cmdTop.Enabled = Status
cmdNext.Enabled = Status
cmdPrevious.Enabled = Status
cmdBottom.Enabled = Status
End Sub

2
Contributors
1
Reply
6
Views
12 Years
Discussion Span
Last Post by Comatose
0

If you set the database objects that you use to reference your database to nothing, and then whenever you want to have them refresh, just recreate the objects, and pass it another query. That should effectively load the new information.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.