Requerying Problem

G

Guest

Hi Guys,

I created a botton that enrols a student into a class. when I pressed the
botton, it works properly in data insert and delete level.

But Requery statement at the end of the code is not working properly.
Therefore, I need to manually requery the subform (manually requerying also
need to wait about 2-3 seconds to reflect the changes after the button is
pressed).

Could someone help me solve this problem?

Thanks in Advance.


Private Sub btnEnrolStudent_Click()
On Error GoTo Err_btnEnrolStudent_Click

If IsNull(Forms!frmStudent.Form.sid) Then
MsgBox "Enter Customer Information before Enrolling into Class."
Else
If MsgBox("Enrol Student?", vbQuestion + vbYesNo, "Confirm") = vbYes
Then
Dim intSID As Integer, intCID As Integer, strsql As String
intSID = Forms!frmStudent.Form.sid

If Me.TabCtlClass.Value = 0 Then

intCID = Forms!frmClass!fsubClassList.Form.cid

strsql = "INSERT INTO ENROL (sid, cid) " & "Values (" &
intSID & ", '" & intCID & "' )"
CurrentDb().Execute strsql, dbFailOnError

ElseIf Me.TabCtlClass.Value = 1 Then
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rst1 As New ADODB.Recordset
Dim strg As String
Dim ttt As New ADODB.Command

strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\database\Korean Swimming.mdb;Persist Security Info=False"
conn.Open strg
rst.Open "SELECT C.cid FROM ENROL E, CLASS C WHERE E.cid =
C.cid AND E.sid = " & intSID & " AND C.ctype = 2", conn

If Not (rst.BOF Or rst.EOF) Then
intCID = rst.Fields("cid")
Else
strsql = "INSERT INTO CLASS (ctype) VALUES (2)"
ttt.ActiveConnection = conn
ttt.CommandText = strsql
ttt.ActiveConnection.BeginTrans
ttt.Execute
ttt.ActiveConnection.CommitTrans

rst1.Open "SELECT cid FROM CLASS WHERE ctype = 2 AND
is_on = Yes AND NOT EXISTS (select cid from ENROL where ENROL.cid =
CLASS.cid)", conn
If Not (rst1.BOF Or rst1.EOF) Then
intCID = rst1.Fields("cid")
End If
End If
strsql = "INSERT INTO ENROL (sid, cid) VALUES (" & intSID &
"," & intCID & ");"
CurrentDb().Execute strsql, dbFailOnError

Set rst = Nothing
Set rst1 = Nothing
Set conn = Nothing
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmStudent"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!frmStudent!fsubEnrolmentList.Requery
<---------------------(here)

End If
End If

Exit_btnEnrolStudent_Click:
Exit Sub

Err_btnEnrolStudent_Click:
MsgBox Err.Description
Resume Exit_btnEnrolStudent_Click

End Sub
 
G

Guest

Thank you Alex. Your reply is very helpful for Access novice like me.

It seems there is still something wrong in the code. I suspect the ADO
implementation because there is a delay in data manipulation so it's not
directly reflected and takes some time to get correct result using manual
process.

I am wondering if you could check the ADO uses in the code.

And could let me know how to activate pre-opened form not using
"DoCmd.OpenForm"?
("Forms!frmStudent!fsubEnrolmentList.ActiveControl",
"Forms!frmStudent!fsubEnrolmentList.SetFocus") <-- not working

Regards,


Daniel Yang
 
A

Alex Dybenko

Don't know why you using ADO and DAO same time, I would suggest to use DAO
only, like:

CurrentDb().Execute strsql, dbFailOnError

then, before opening the form, check if data is actually inserted - just
open table and look at last row

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 
G

Guest

Didn't know I was using both objects.
Could you check this segment please? Marked code (<------here) doesn't works

Dim rst As DAO.Recordset
Set rst = CurrentDb().OpenRecordset("SELECT C.cid FROM ENROL
E, CLASS C WHERE E.cid = C.cid AND E.sid = " & intSID & " AND C.ctype = 2")
If Not (rst.BOF Or rst.EOF) Then
intCID = rst.Fields("cid").Value <-------------(here)
Else

** The query returns only one single value

Regards,
 
G

Guest

Hello,

Thanks Alex.

From your debug code I've found the problem which (A) is not committed in
database and (B) need to reference the record (A) committed.

I used ADO Begintans to solve this issue (commit (A) before executing (B).

I've read Help document, but don't understand the way of implementing
begintrans in DAO. Could you give me some clue about using begintrans?

Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim strg As String

Set rst = CurrentDb().OpenRecordset("SELECT C.cid FROM ENROL E, CLASS C
WHERE E.cid = C.cid AND E.sid = " & intSID & " AND C.ctype = 2")
If Not (rst.BOF And rst.EOF) Then
intCID = rst.Fields("cid").Value
Else
strsql = "INSERT INTO CLASS (ctype) VALUES (2)" <--------(A)
CurrentDb().Execute strsql, dbFailOnError

Set rst1 = CurrentDb().OpenRecordset("SELECT cid FROM CLASS WHERE ctype
= 2 AND is_on = Yes AND NOT EXISTS (select cid from ENROL where ENROL.cid =
CLASS.cid)")
If Not (rst.BOF And rst.EOF) Then
intCID = rst1.Fields("cid") <-------(correct)
End If
End If
strsql = "INSERT INTO ENROL (sid, cid) VALUES (" & intSID & "," & intCID &
");" <-----(B)
CurrentDb().Execute strsql, dbFailOnError

Set rst = Nothing
Set rst1 = Nothing

Regards,
 
G

Guest

Hi Alex,
I've done it up. Please ignore the above thread.

Thank you for your help. I could finally finish up the coding. I don't think
I could do it this quick without your advice.

I find MS-Access is quite interesting and feeling to do more study as well
as MS SQL.

I put final code here in case someone may need to reference. If you see any
mistake or anything to improve please add comments since I may have some
misunderstanding or misuse of implementing DAO objects.

Best Regards,

Daniel S Yang

======================================================
Private Sub btnEnrolStudent_Click()
On Error GoTo Err_btnEnrolStudent_Click

If IsNull(Forms!frmStudent.Form.sid) Then
MsgBox "Enter Customer Information before Enrolling into Class."
Else
If MsgBox("Enrol Student?", vbQuestion + vbYesNo, "Confirm") = vbYes
Then
Dim intSID As Integer, intCID As Integer, strsql As String
Dim db As DAO.Database
Dim wrk As DAO.Workspace
Set wrk = Workspaces(0)
Set db = DBEngine.Workspaces(0).Databases(0)

intSID = Forms!frmStudent.Form.sid

If Me.TabCtlClass.Value = 0 Then
intCID = Forms!frmClass!fsubClassList.Form.cid
strsql = "INSERT INTO ENROL (sid, cid) " & "Values (" &
intSID & ", '" & intCID & "' )"
db.Execute strsql, dbFailOnError
ElseIf Me.TabCtlClass.Value = 1 Then
Dim rst As DAO.Recordset
Set rst = db.OpenRecordset("SELECT C.cid FROM ENROL E, CLASS
C WHERE E.cid = C.cid AND E.sid = " & intSID & " AND C.ctype = 2;")

If Not (rst.BOF And rst.EOF) Then
intCID = rst.Fields("cid").Value
Else

rst.Close
strsql = "INSERT INTO CLASS (ctype) VALUES (2)"
wrk.BeginTrans
db.Execute strsql, dbFailOnError
wrk.CommitTrans
Set rst = db.OpenRecordset("SELECT cid FROM CLASS WHERE
ctype = 2 AND is_on = Yes AND NOT EXISTS (select cid from ENROL where
ENROL.cid = CLASS.cid)")
If Not (rst.BOF And rst.EOF) Then
intCID = rst.Fields("cid")
End If
rst.Close
End If
strsql = "INSERT INTO ENROL (sid, cid) VALUES (" & intSID &
"," & intCID & ");"
CurrentDb().Execute strsql, dbFailOnError
Set rst = Nothing
End If

wrk.Close
db.Close
Set db = Nothing
Set wrk = Nothing
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmStudent"

DoCmd.OpenForm stDocName
Forms!frmStudent!fsubEnrolmentList.Form.Requery
End If
End If

Exit_btnEnrolStudent_Click:
Exit Sub

Err_btnEnrolStudent_Click:
MsgBox Err.Description
Resume Exit_btnEnrolStudent_Click
End Sub

======================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA coding Questions 2
Second Report not printing - whats wrong with the VBA I have?????? 1
Connection Problem (Oracle and Access join) 5
Importing a multi-sheet Excel spreadsheet into Access 9
PLEASE HELP: Design Students Screener in Access 1
Append loop working but causing duplicates - I'm lost 3
ADO Error in Access calling from Excel 6
Excel Export Filtered Form Data To Excel 0

Top