sikan71
asked on
Next and Previous button on a form using dao database and recordset
Hi!
The form involves two tables tblHeader and tblData. There is a one-to-many relationship between tblHeader and tblData. I have declared a recordset for tblHeader. I need to be able to move thru the records during a session with the form. I have tried to use the move forwards and move previous buttons and codes provided by access....did not meet with any success. Can somebody guide me with the moving through the records during a session. My code for entering data is as follows:
Option Compare Database
Option Explicit
Private Sub cmdAddCert_Click()
On Error GoTo Err_cmdAddCert_Click
Dim dbs As DAO.Database
Dim rstHeader As DAO.Recordset
Dim intQANumber As Long
Set dbs = CurrentDb
Set rstHeader = dbs.OpenRecordset("tblHead
'Ensure required fields are populated
If IsNull(Me.cmbCssName) Then
MsgBox "Please Choose CSS Name"
Me.cmbCssName.SetFocus
Exit Sub
ElseIf IsNull(Me.txtCertNumber) Then
MsgBox "Please Enter Certificate Number"
Me.txtCertNumber.SetFocus
Exit Sub
ElseIf IsNull(Me.txtClientName) Then
MsgBox "Please Enter Client Name"
Me.txtClientName.SetFocus
Exit Sub
ElseIf IsNull(Me.txtCertHolder) Then
MsgBox "Please Enter Certificate Holder Name"
Me.txtCertHolder.SetFocus
Exit Sub
ElseIf IsNull(Me.cmbReasonForRewo
MsgBox "Please Choose Reason For Rework"
Me.cmbReasonForRework.SetF
Exit Sub
ElseIf IsNull(Me.txtRequestDate) Then
MsgBox "Please Enter the Date of Request"
Me.txtRequestDate.SetFocus
Exit Sub
ElseIf IsNull(Me.cmbAssessorName)
MsgBox "Please Choose Assessor Name"
Me.cmbAssessorName.SetFocu
Exit Sub
ElseIf IsNull(Me.txtIssueDate) Then
MsgBox "Please Enter the Issue Date"
Me.txtIssueDate.SetFocus
Exit Sub
ElseIf IsNull(Me.txtIssueDate) Then
MsgBox "Please Enter Review date or click in Box"
Me.txtReviewDate.SetFocus
Exit Sub
End If
'Insert data into Parent Table information (tblHeader)
With rstHeader
DoCmd.RunSQL "Insert into tblHeader (CSSName, ClientName, " _
& "CertHolder, CertNumber, ReviewDate, RequestDate, IssueDate, " _
& " AssessorName) " _
& " Values ( '" _
& Me.cmbCssName & "','" & Me.txtClientName & "' , '" & Me.txtCertHolder _
& "','" & Me.txtCertNumber & "',#" & Me.txtReviewDate & "#, #" & Me.txtRequestDate _
& "# , #" & Me.txtIssueDate & "# , '" & Me.cmbAssessorName & "');"
End With
'MsgBox rstHeader.FindFirst(
rstHeader.MoveLast
intQANumber = rstHeader.Fields(0).Value
'Insert Data into Child Table
DoCmd.SetWarnings False 'Prevent update query prompt
DoCmd.RunSQL "Insert into tblQAData ( QANumber, ItemsforReview, State, Grade, PrimarySourceForError, ControllableError, Comments )" _
& "Values (" & intQANumber & ", '" & Me.lblCOff.Caption & "', '" & Me.cmbCOffState & "', '" & Me.cmbCOffGrade & "', '" _
& Me.cmbCOffPrimarySourceErr
'DoCmd.GoToRecord , , acNewRec
'Forms!Form_frmCertDataEnt
rstHeader.Close
clearAll ("frmCertDataEntry")
Exit_cmdAddCert_Click:
Exit Sub
Err_cmdAddCert_Click:
MsgBox Err.Description
Resume Exit_cmdAddCert_Click
End Sub
Private Sub txtReviewDate_Click()
Dim dtInputDate As Date
dtInputDate = VBA.DateTime.Date
Me.txtReviewDate = dtInputDate
End Sub
Private Sub cmdPreviousCert_Click()
On Error GoTo Err_cmdPreviousCert_Click
DoCmd.GoToRecord , , acPrevious
Exit_cmdPreviousCert_Click
Exit Sub
Err_cmdPreviousCert_Click:
MsgBox Err.Description
Resume Exit_cmdPreviousCert_Click
End Sub
Please help....Thank you
ved
The form involves two tables tblHeader and tblData. There is a one-to-many relationship between tblHeader and tblData. I have declared a recordset for tblHeader. I need to be able to move thru the records during a session with the form. I have tried to use the move forwards and move previous buttons and codes provided by access....did not meet with any success. Can somebody guide me with the moving through the records during a session. My code for entering data is as follows:
Option Compare Database
Option Explicit
Private Sub cmdAddCert_Click()
On Error GoTo Err_cmdAddCert_Click
Dim dbs As DAO.Database
Dim rstHeader As DAO.Recordset
Dim intQANumber As Long
Set dbs = CurrentDb
Set rstHeader = dbs.OpenRecordset("tblHead
'Ensure required fields are populated
If IsNull(Me.cmbCssName) Then
MsgBox "Please Choose CSS Name"
Me.cmbCssName.SetFocus
Exit Sub
ElseIf IsNull(Me.txtCertNumber) Then
MsgBox "Please Enter Certificate Number"
Me.txtCertNumber.SetFocus
Exit Sub
ElseIf IsNull(Me.txtClientName) Then
MsgBox "Please Enter Client Name"
Me.txtClientName.SetFocus
Exit Sub
ElseIf IsNull(Me.txtCertHolder) Then
MsgBox "Please Enter Certificate Holder Name"
Me.txtCertHolder.SetFocus
Exit Sub
ElseIf IsNull(Me.cmbReasonForRewo
MsgBox "Please Choose Reason For Rework"
Me.cmbReasonForRework.SetF
Exit Sub
ElseIf IsNull(Me.txtRequestDate) Then
MsgBox "Please Enter the Date of Request"
Me.txtRequestDate.SetFocus
Exit Sub
ElseIf IsNull(Me.cmbAssessorName)
MsgBox "Please Choose Assessor Name"
Me.cmbAssessorName.SetFocu
Exit Sub
ElseIf IsNull(Me.txtIssueDate) Then
MsgBox "Please Enter the Issue Date"
Me.txtIssueDate.SetFocus
Exit Sub
ElseIf IsNull(Me.txtIssueDate) Then
MsgBox "Please Enter Review date or click in Box"
Me.txtReviewDate.SetFocus
Exit Sub
End If
'Insert data into Parent Table information (tblHeader)
With rstHeader
DoCmd.RunSQL "Insert into tblHeader (CSSName, ClientName, " _
& "CertHolder, CertNumber, ReviewDate, RequestDate, IssueDate, " _
& " AssessorName) " _
& " Values ( '" _
& Me.cmbCssName & "','" & Me.txtClientName & "' , '" & Me.txtCertHolder _
& "','" & Me.txtCertNumber & "',#" & Me.txtReviewDate & "#, #" & Me.txtRequestDate _
& "# , #" & Me.txtIssueDate & "# , '" & Me.cmbAssessorName & "');"
End With
'MsgBox rstHeader.FindFirst(
rstHeader.MoveLast
intQANumber = rstHeader.Fields(0).Value
'Insert Data into Child Table
DoCmd.SetWarnings False 'Prevent update query prompt
DoCmd.RunSQL "Insert into tblQAData ( QANumber, ItemsforReview, State, Grade, PrimarySourceForError, ControllableError, Comments )" _
& "Values (" & intQANumber & ", '" & Me.lblCOff.Caption & "', '" & Me.cmbCOffState & "', '" & Me.cmbCOffGrade & "', '" _
& Me.cmbCOffPrimarySourceErr
'DoCmd.GoToRecord , , acNewRec
'Forms!Form_frmCertDataEnt
rstHeader.Close
clearAll ("frmCertDataEntry")
Exit_cmdAddCert_Click:
Exit Sub
Err_cmdAddCert_Click:
MsgBox Err.Description
Resume Exit_cmdAddCert_Click
End Sub
Private Sub txtReviewDate_Click()
Dim dtInputDate As Date
dtInputDate = VBA.DateTime.Date
Me.txtReviewDate = dtInputDate
End Sub
Private Sub cmdPreviousCert_Click()
On Error GoTo Err_cmdPreviousCert_Click
DoCmd.GoToRecord , , acPrevious
Exit_cmdPreviousCert_Click
Exit Sub
Err_cmdPreviousCert_Click:
MsgBox Err.Description
Resume Exit_cmdPreviousCert_Click
End Sub
Please help....Thank you
ved
Do you want something like this :
rstHeader.MoveFirst
Do until rstHeader.EOF
'you action
rstHeader.MoverNext
Loop
Right?
Ben
rstHeader.MoveFirst
Do until rstHeader.EOF
'you action
rstHeader.MoverNext
Loop
Right?
Ben
It looks like you are handling all of the data interactions yourself with the form not being bound to the tables?
If this is correct then you can move the rstHeader variable into the declaration section of the form module, open the recordset in the Form_Open event and then use the .MoveFirst, .MoveNext, .MovePrevious, .MoveLast methods of the recordset object. Now ... If you are going to do that then you need to check that the .BOF propery is false before doing a .MoveFirst and that the .EOF property is false before the .MoveNext.
Unless you have a compelling reason to work with the data unbound I would suggest that you bind the tables to your forms ... tblHeader to the main form and tblData to a subform and let Access handle all of the code for the record navigation and it would also handle the actual insert for you so all that you would be left with is the record validation code that you can call in the BeforeUpdate event of the form.
Steve
If this is correct then you can move the rstHeader variable into the declaration section of the form module, open the recordset in the Form_Open event and then use the .MoveFirst, .MoveNext, .MovePrevious, .MoveLast methods of the recordset object. Now ... If you are going to do that then you need to check that the .BOF propery is false before doing a .MoveFirst and that the .EOF property is false before the .MoveNext.
Unless you have a compelling reason to work with the data unbound I would suggest that you bind the tables to your forms ... tblHeader to the main form and tblData to a subform and let Access handle all of the code for the record navigation and it would also handle the actual insert for you so all that you would be left with is the record validation code that you can call in the BeforeUpdate event of the form.
Steve
ASKER
Hi Guys,
Thanks for the guidance. In an attempt to have intelligent buttons in the form I inserted the following code:
Private Sub Form_Current()
Dim recClone As Recordset
'Make a clone of the recordset underlying the form so we can move around
'without affecting the forms recordset
Set recClone = Me.RecordsetClone() '****** error 7951
'If we are in a new record disable the <Next> Button and enable the rest
'of the buttons
If Me.NewRecord Then
cmdFirst.Enabled = True
cmdPrevious.Enabled = True
cmdNext.Enabled = False
cmdLast.Enabled = True
cmdAddCert.Enabled = True
Exit Sub
End If
'if we reach here, we know we are not in a new record so we can enable the <Add>
'button.
cmdAddCert.Enabled = Me.AllowAdditions
'run check for new records. if there are no records then disable al buttons except
'<Add> button
If recClone.RecordCount = 0 Then
cmdFirst.Enabled = False
cmdNext.Enabled = False
cmdPrevious.Enabled = False
cmdLast.Enabled = False
Else
'if there are records, enable <First> and <Last> button
cmdFirst.Enabled = True
cmdLast.Enabled = True
'synchronize the current pointer in the two recordset
recClone.Bookmark = Me.Bookmark
'check if we are on the first record, if yes then disable <Previous>
recClone.MovePrevious
cmdPrevious.Enabled = Not (recClone.BOF)
recClone.MoveNext
'check if we are on the last record, if yes then disable <Next>
recClone.MoveNext
cmdNext.Enabled = Not (recClone.EOF)
recClone.MovePrevious
End If
'close cloned recordset
recClone.Close
End Sub
The problem is that it gives me runtime error 7951 at:
Set recClone = Me.RecordSetClone()
7951 error: "You have entered an expression that has invalid reference to the RecordsetClone property.
This code worked in a my previous program that was bound to one table.
What am I doing wrong??
P.S: am still working on the Next and Previous button
Thanks for the guidance. In an attempt to have intelligent buttons in the form I inserted the following code:
Private Sub Form_Current()
Dim recClone As Recordset
'Make a clone of the recordset underlying the form so we can move around
'without affecting the forms recordset
Set recClone = Me.RecordsetClone() '****** error 7951
'If we are in a new record disable the <Next> Button and enable the rest
'of the buttons
If Me.NewRecord Then
cmdFirst.Enabled = True
cmdPrevious.Enabled = True
cmdNext.Enabled = False
cmdLast.Enabled = True
cmdAddCert.Enabled = True
Exit Sub
End If
'if we reach here, we know we are not in a new record so we can enable the <Add>
'button.
cmdAddCert.Enabled = Me.AllowAdditions
'run check for new records. if there are no records then disable al buttons except
'<Add> button
If recClone.RecordCount = 0 Then
cmdFirst.Enabled = False
cmdNext.Enabled = False
cmdPrevious.Enabled = False
cmdLast.Enabled = False
Else
'if there are records, enable <First> and <Last> button
cmdFirst.Enabled = True
cmdLast.Enabled = True
'synchronize the current pointer in the two recordset
recClone.Bookmark = Me.Bookmark
'check if we are on the first record, if yes then disable <Previous>
recClone.MovePrevious
cmdPrevious.Enabled = Not (recClone.BOF)
recClone.MoveNext
'check if we are on the last record, if yes then disable <Next>
recClone.MoveNext
cmdNext.Enabled = Not (recClone.EOF)
recClone.MovePrevious
End If
'close cloned recordset
recClone.Close
End Sub
The problem is that it gives me runtime error 7951 at:
Set recClone = Me.RecordSetClone()
7951 error: "You have entered an expression that has invalid reference to the RecordsetClone property.
This code worked in a my previous program that was bound to one table.
What am I doing wrong??
P.S: am still working on the Next and Previous button
ASKER
Hi Jbarnesweb,
I am having trouble with navigation, the reason I provided the code is because I m a newbie in programming and wanted to provide information on the code I am currently using for inserting records and navigating through the form.
The current behavior is Null i.e., using the next and previous provided by access does not do anything.
Thanks
I am having trouble with navigation, the reason I provided the code is because I m a newbie in programming and wanted to provide information on the code I am currently using for inserting records and navigating through the form.
The current behavior is Null i.e., using the next and previous provided by access does not do anything.
Thanks
ASKER
Benjamine Luk,
Yes, I'd like to try the code...can you elaborate.
Thanks
Yes, I'd like to try the code...can you elaborate.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also create an unbound textbox txtRecCount to display your record number
you need to have the Microsoft DAO Object Library referenced and disambiguate your recordset variable ...
Dim recClone As DAO.Recordset
Steve
Dim recClone As DAO.Recordset
Steve
You said you are having trouble navigating. Does the master form navigate correctly and not the detail form? You don't have code for handling a next button click posted.
Please describe the behavior you are experiencing.
Regards,
Jeff