Z
zywh
Guest
I wrote a VBA program using MS Access form.
In my form there are two grids and a button(called "send button").
In the upper grid the data from a table is shown using SQL Query.
A user can find a particular record in the upper grid and if he found he can press "send button" to send that record to lower grid and omit from upper grid.
To do so I wrote like this.. when user press send button I insert a new record to the table linked to lower grid using data from the table linked to upper grid.
And I also updated to the table linked to upper grid for ommiting sent record.
I use ado connection object for transaction automicity instead of "Docmd.RunSql" command.
Usually it works. But sometimes, when user press "send button", it does not send the record.
Can anyone help me on how can I solve this?
Here is my codes for "send button"
Private Sub Command5_Click()
On Error GoTo Err_Handler
'ado_send1 is already declared globally
ado_send1.BeginTrans
ado_send1.Execute "Insert into StudentNew" & _
"(VT_Code, SchoolCode,Grade,Name,fatherName,Sex,Age,Category,RegNo,FamilyRegNo,OldStuID) " & _
"SELECT VT_Code, SchoolCode,Grade, " & _
"Name, fatherName, Sex, Age, Category, " & _
"RegNo, FamilyRegNo,ID from MaungDawFDPStudent where id=" & OldStudents.Form.Recordset.Fields("ID").Value
ado_send1.Execute "Update MaungDawFDPStudent set sendstatus=yes where id=" & OldStudents.Form.Recordset.Fields("ID").Value
ado_send1.CommitTrans
Sleep 1000
OldStudents.Form.RecordSource = "SELECT ID, VT_Code, SchoolCode," & _
"Grade, Name, fatherName, Sex," & _
" Age, Category, RegNo, FamilyRegNo " & _
" FROM MaungDawFDPStudent where vt_code=" & cboVT_Code.Value & " and schoolcode='" & Trim(txtSchoolCode.Value) & "' and sendstatus=no order by ID"
Sleep 1000
OldStudents.Form.Recordset.Requery
OldStudents.Form.Refresh
OldStudents.Form.Repaint
StudentNewSSForm.SetFocus
StudentNewSSForm.Form.Recordset.Requery
StudentNewSSForm.Form.Refresh
StudentNewSSForm.Form.Repaint
OldStudents.SetFocus
Exit Sub
Err_Handler:
MsgBox Err.Description
End Sub
Thanks
zywh
In my form there are two grids and a button(called "send button").
In the upper grid the data from a table is shown using SQL Query.
A user can find a particular record in the upper grid and if he found he can press "send button" to send that record to lower grid and omit from upper grid.
To do so I wrote like this.. when user press send button I insert a new record to the table linked to lower grid using data from the table linked to upper grid.
And I also updated to the table linked to upper grid for ommiting sent record.
I use ado connection object for transaction automicity instead of "Docmd.RunSql" command.
Usually it works. But sometimes, when user press "send button", it does not send the record.
Can anyone help me on how can I solve this?
Here is my codes for "send button"
Private Sub Command5_Click()
On Error GoTo Err_Handler
'ado_send1 is already declared globally
ado_send1.BeginTrans
ado_send1.Execute "Insert into StudentNew" & _
"(VT_Code, SchoolCode,Grade,Name,fatherName,Sex,Age,Category,RegNo,FamilyRegNo,OldStuID) " & _
"SELECT VT_Code, SchoolCode,Grade, " & _
"Name, fatherName, Sex, Age, Category, " & _
"RegNo, FamilyRegNo,ID from MaungDawFDPStudent where id=" & OldStudents.Form.Recordset.Fields("ID").Value
ado_send1.Execute "Update MaungDawFDPStudent set sendstatus=yes where id=" & OldStudents.Form.Recordset.Fields("ID").Value
ado_send1.CommitTrans
Sleep 1000
OldStudents.Form.RecordSource = "SELECT ID, VT_Code, SchoolCode," & _
"Grade, Name, fatherName, Sex," & _
" Age, Category, RegNo, FamilyRegNo " & _
" FROM MaungDawFDPStudent where vt_code=" & cboVT_Code.Value & " and schoolcode='" & Trim(txtSchoolCode.Value) & "' and sendstatus=no order by ID"
Sleep 1000
OldStudents.Form.Recordset.Requery
OldStudents.Form.Refresh
OldStudents.Form.Repaint
StudentNewSSForm.SetFocus
StudentNewSSForm.Form.Recordset.Requery
StudentNewSSForm.Form.Refresh
StudentNewSSForm.Form.Repaint
OldStudents.SetFocus
Exit Sub
Err_Handler:
MsgBox Err.Description
End Sub
Thanks
zywh
Last edited: