D
Darrell
I have a database for generating check requests which
consists of essentially three tables: tblCheckRequest,
tblCheckRequestDetail, and tblPayee. A main form, built
on tblCheckRequest contains a subform built on
tblCheckRequestDetail. The main form contains controls
which are populated from tblPayee. When the user clicks
the button cmdPrintCheckRequest, the following code runs.
Its purpose is to check to see if the Check Request has
been printed, and, if not, to: 1) do some minor
formatting adjustments; 2) increment a Check Request
counter and write the new number to tblCheckRequest; 3)
write the values from the controls populated from
tblPayee to tblCheckRequest, and 4)print 2 copies of the
Check Request. On clicking the button, the user has been
getting a Write Conflict message reading:
"This record has been changed by another user since you
started editing it. If you save the record, you will
overwrite the changes the other user made.
"Copying the changes to the clipboard will let you look
at the values the other user entered, and then paste your
changes back in if you decide to make changes."
The message box presents three options: "Save
Record", "Copy to Clipboard", and "Drop Changes."
Clicking on "Save Record" has resulted in printing the
Check Request, but failing to write all the fields to
tblCheckRequest (all the fields populated by tblPayee DO
get written).
The database is being used by only a single user, which
seems to leave no choice but to conclude that there is
something in the code causing a conflict.
I am using Access 2000, but originally developed the
database in Access 2003 and later imported in into an
empty Access 2000 database because I was getting erratic
behavior. This cleared up all of the issues, except this
Write Conflict issue.
The code for the button is below. If anyone can help, it
would be appreciated.
Private Sub cmdPrintCheckRequest_Click()
On Error GoTo mcrPrintOutRptCheckRequest_Err
If IsNull(Me!txtCRID) Then
Me.cboPayeeCode.SetFocus
Exit Sub ' no data to be printed
End If
'Check for EE check box and number.
If (Me.Employee = -1 And IsNull(Me.EENumberSSN) =
True) = True Then
MsgBox "Clear employee check box or enter number."
Forms!frmCheckRequest.EENumberSSN.SetFocus
Forms!frmCheckRequest.EENumberSSN.BackColor = RGB
(255, 255, 128)
Exit Sub
End If
Dim db As DAO.Database
Set db = CurrentDb()
'If this Check Request has already been printed, skip
the counter update,
'and address update.
If Me.chkPrinted = 0 Then
' first, update tblCounter so that it holds the
last 'correctly printed'
' check request number
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblCounter SET tblCounter.
[Counter] = [Counter] + 1;")
DoCmd.SetWarnings True
' Now, store that new value in tblCheckRequest in
field CheckRequestNumber.
Dim NewCounter As Long
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("tblCounter")
NewCounter = rs!Counter
Set rs = Nothing
Set rs = db.OpenRecordset("SELECT
CheckRequestNumber," & _
"PayeeName, Address1, Address2, City, State, Zip,
Printed FROM " & _
"tblCheckRequest WHERE CRID = " & Forms!
frmCheckRequest!txtCRID)
rs.Edit
rs!CheckRequestNumber = NewCounter
rs!PayeeName = PayeeName
rs!Address1 = Address1
rs!Address2 = Address2
rs!City = City
rs!State = State
rs!Zip = Zip
rs!Printed = -1
rs.Update
rs.Close
Me.Refresh
'DoCmd.GoToRecord , , acLast
End If
DoCmd.SetWarnings False
'Open check request in preview mode.
DoCmd.OpenReport "rptCheckRequest",
acViewPreview, , "tblCheckRequest!CRID = " & Me.txtCRID
'Print after previewing.
DoCmd.Echo False, "Check Request is printing."
DoCmd.PrintOut acPrintAll, , , acHigh, 2, True
DoCmd.Close acReport, "rptCheckRequest"
DoCmd.Echo True, "Check Request is finished printing."
DoCmd.GoToRecord , , acNewRec
DoCmd.SetWarnings True
' Return default for Division, cboCostCenter and
cboObjectCode to generic
' values of 0311, 3146-00 and 50440 respectively.
Forms!frmCheckRequest!subfrmCheckRequestDetail!
cboCostCenter.DefaultValue = Chr$(34) & "314600" &
Chr$(34)
Forms!frmCheckRequest!subfrmCheckRequestDetail!
cboObjectCode.DefaultValue = Chr$(34) & "50440" & Chr$(34)
Forms!frmCheckRequest!subfrmCheckRequestDetail!
cboDivisionCode.DefaultValue = Chr$(34) & "0311" &
Chr$(34)
mcrPrintOutRptCheckRequest_Exit:
Exit Sub
mcrPrintOutRptCheckRequest_Err:
MsgBox Error$
Resume mcrPrintOutRptCheckRequest_Exit
End Sub
Thanks.
Darrell
consists of essentially three tables: tblCheckRequest,
tblCheckRequestDetail, and tblPayee. A main form, built
on tblCheckRequest contains a subform built on
tblCheckRequestDetail. The main form contains controls
which are populated from tblPayee. When the user clicks
the button cmdPrintCheckRequest, the following code runs.
Its purpose is to check to see if the Check Request has
been printed, and, if not, to: 1) do some minor
formatting adjustments; 2) increment a Check Request
counter and write the new number to tblCheckRequest; 3)
write the values from the controls populated from
tblPayee to tblCheckRequest, and 4)print 2 copies of the
Check Request. On clicking the button, the user has been
getting a Write Conflict message reading:
"This record has been changed by another user since you
started editing it. If you save the record, you will
overwrite the changes the other user made.
"Copying the changes to the clipboard will let you look
at the values the other user entered, and then paste your
changes back in if you decide to make changes."
The message box presents three options: "Save
Record", "Copy to Clipboard", and "Drop Changes."
Clicking on "Save Record" has resulted in printing the
Check Request, but failing to write all the fields to
tblCheckRequest (all the fields populated by tblPayee DO
get written).
The database is being used by only a single user, which
seems to leave no choice but to conclude that there is
something in the code causing a conflict.
I am using Access 2000, but originally developed the
database in Access 2003 and later imported in into an
empty Access 2000 database because I was getting erratic
behavior. This cleared up all of the issues, except this
Write Conflict issue.
The code for the button is below. If anyone can help, it
would be appreciated.
Private Sub cmdPrintCheckRequest_Click()
On Error GoTo mcrPrintOutRptCheckRequest_Err
If IsNull(Me!txtCRID) Then
Me.cboPayeeCode.SetFocus
Exit Sub ' no data to be printed
End If
'Check for EE check box and number.
If (Me.Employee = -1 And IsNull(Me.EENumberSSN) =
True) = True Then
MsgBox "Clear employee check box or enter number."
Forms!frmCheckRequest.EENumberSSN.SetFocus
Forms!frmCheckRequest.EENumberSSN.BackColor = RGB
(255, 255, 128)
Exit Sub
End If
Dim db As DAO.Database
Set db = CurrentDb()
'If this Check Request has already been printed, skip
the counter update,
'and address update.
If Me.chkPrinted = 0 Then
' first, update tblCounter so that it holds the
last 'correctly printed'
' check request number
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblCounter SET tblCounter.
[Counter] = [Counter] + 1;")
DoCmd.SetWarnings True
' Now, store that new value in tblCheckRequest in
field CheckRequestNumber.
Dim NewCounter As Long
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("tblCounter")
NewCounter = rs!Counter
Set rs = Nothing
Set rs = db.OpenRecordset("SELECT
CheckRequestNumber," & _
"PayeeName, Address1, Address2, City, State, Zip,
Printed FROM " & _
"tblCheckRequest WHERE CRID = " & Forms!
frmCheckRequest!txtCRID)
rs.Edit
rs!CheckRequestNumber = NewCounter
rs!PayeeName = PayeeName
rs!Address1 = Address1
rs!Address2 = Address2
rs!City = City
rs!State = State
rs!Zip = Zip
rs!Printed = -1
rs.Update
rs.Close
Me.Refresh
'DoCmd.GoToRecord , , acLast
End If
DoCmd.SetWarnings False
'Open check request in preview mode.
DoCmd.OpenReport "rptCheckRequest",
acViewPreview, , "tblCheckRequest!CRID = " & Me.txtCRID
'Print after previewing.
DoCmd.Echo False, "Check Request is printing."
DoCmd.PrintOut acPrintAll, , , acHigh, 2, True
DoCmd.Close acReport, "rptCheckRequest"
DoCmd.Echo True, "Check Request is finished printing."
DoCmd.GoToRecord , , acNewRec
DoCmd.SetWarnings True
' Return default for Division, cboCostCenter and
cboObjectCode to generic
' values of 0311, 3146-00 and 50440 respectively.
Forms!frmCheckRequest!subfrmCheckRequestDetail!
cboCostCenter.DefaultValue = Chr$(34) & "314600" &
Chr$(34)
Forms!frmCheckRequest!subfrmCheckRequestDetail!
cboObjectCode.DefaultValue = Chr$(34) & "50440" & Chr$(34)
Forms!frmCheckRequest!subfrmCheckRequestDetail!
cboDivisionCode.DefaultValue = Chr$(34) & "0311" &
Chr$(34)
mcrPrintOutRptCheckRequest_Exit:
Exit Sub
mcrPrintOutRptCheckRequest_Err:
MsgBox Error$
Resume mcrPrintOutRptCheckRequest_Exit
End Sub
Thanks.
Darrell