ACCESS FEED: RE: [MS_AccessPros] Re: Write conflict: help with code

Doyce-

Yup. The Me.Dirty = False needs to be before you run the update query.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of dnwinberry
Sent: Wednesday, April 24, 2013 2:13 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Write conflict: help with code

John,

I reread your post from yesterday and thought about it some more. I put If
me.dirty then me.dirty = false right at the beginning of the procedure and
that seems to have solved the problem.

Thanks again!

Doyce

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "dnwinberry"
<winberry.doyce@...> wrote:
>
> John,
>
> I've changed my code to this and everything seems to be working correctly,
however, I'm still getting the write conflict error. Please help.
>
> Private Sub Command6_Click()
> On Error GoTo Err_Command6_Click
>
> 'Check for Unit Number
> If IsNull(Me.List4) Then
> Call MsgBox("Please select a valid unit number before printing.",
vbExclamation, "No Unit Number Selected")
> Me.List4.SetFocus
> Exit Sub
> End If
>
>
> 'check for Delivery Location
> If IsNull(Me.cboDelLocation) Then
> Call MsgBox("Please select a valid delivery location.", vbExclamation, "No
Delivery Location Selected")
> Me.cboDelLocation.SetFocus
> Exit Sub
> End If
>
> 'check to see if ship docs have already been printed
> If Me.ShipDocsPrinted = -1 Then
> Select Case MsgBox("Shipping documents have already been printer for this
trailer. " _
> & vbCrLf & "Do you want to print them again?" _
> , vbYesNo Or vbQuestion Or vbDefaultButton2, Application.Name)
>
> Case vbYes
> GoTo PrintShipDoc
> Case vbNo
> Me.List4.SetFocus
> Exit Sub
> End Select
> End If
>
> PrintShipDoc:
> 'update Delivery Location field in primary Table
> Dim strSQL As String
> Dim strCity As String
> Dim strUnit As String
> strCity = Me.cboDelLocation
> strUnit = Me.List4
>
> strSQL = "UPDATE tblTruckloadPrimary SET tblTruckloadPrimary.ShippingID =
" & "'" & strCity & "'" & _
> " WHERE tblTruckloadPrimary.[New Unit #] = " & "'" & strUnit & "'"
> Debug.Print strSQL
> CurrentDb.Execute strSQL, dbFailOnError
> 'Update Print Flag
> Me.ShipDocsPrinted = -1
> If Me.Dirty Then Me.Dirty = False
>
>
> 'Print Shipping Document
> Dim stDocName As String
>
> stDocName = "rptShippingDoc"
> DoCmd.OpenReport stDocName, acNormal
>
> Me.txtComments = Null
> Me.Refresh
>
> Me.List4.SetFocus
>
> Exit_Command6_Click:
> Exit Sub
>
> Err_Command6_Click:
> MsgBox Err.Description
> Resume Exit_Command6_Click
>
> End Sub
>
> thanks,
> Doyce
>
>
> --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "dnwinberry"
<winberry.doyce@> wrote:
> >
> > Thanks John. Sounds so simple. Sometimes I overthink things. I'll try
your suggestions and see if I can make it work.
> >
> > Doyce
> >
> > --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
wrote:
> > >
> > > Doyce-
> > >
> > >
> > >
> > > I suspect the form is "dirty" (unsaved edit) when you run this, so the
> > > update gets a write conflict because the record is being edited. Why
not
> > > just set the flag on the form rather than run a separate query?
> > >
> > >
> > >
> > > In any case, to be sure the current record is saved, do this:
> > >
> > >
> > >
> > > If Me.Dirty Then Me.Dirty = False
> > >
> > >
> > >
> > > That will force a save.
> > >
> > >
> > >
> > > John Viescas, Author
> > >
> > > Microsoft Access 2010 Inside Out
> > >
> > > Microsoft Access 2007 Inside Out
> > >
> > > Microsoft Access 2003 Inside Out
> > >
> > > Building Microsoft Access Applications
> > >
> > > SQL Queries for Mere Mortals
> > >
> > > http://www.viescas.com/
> > >
> > > (Paris, France)
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of dnwinberry
> > > Sent: Tuesday, April 23, 2013 2:31 PM
> > > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: [MS_AccessPros] Re: Write conflict: help with code
> > >
> > >
> > >
> > >
> > >
> > > I missed a section copying the code. Here is the complete code:
> > >
> > > Private Sub Command6_Click()
> > > On Error GoTo Err_Command6_Click
> > >
> > > 'Check for Unit Number
> > > If IsNull(Me.List4) Then
> > > Call MsgBox("Please select a valid unit number before printing.",
> > > vbExclamation, "No Unit Number Selected")
> > > Me.List4.SetFocus
> > > Exit Sub
> > > End If
> > >
> > >
> > > 'check for Delivery Location
> > > If IsNull(Me.cboDelLocation) Then
> > > Call MsgBox("Please select a valid delivery location.", vbExclamation,
"No
> > > Delivery Location Selected")
> > > Me.cboDelLocation.SetFocus
> > > Exit Sub
> > > End If
> > >
> > > 'check to see if ship docs have already been printed
> > > If Me.ShipDocsPrinted = -1 Then
> > > Select Case MsgBox("Shipping documents have already been printer for
this
> > > trailer. " _
> > > & vbCrLf & "Do you want to print them again?" _
> > > , vbYesNo Or vbQuestion Or vbDefaultButton2, Application.Name)
> > >
> > > Case vbYes
> > > GoTo PrintShipDoc
> > > Case vbNo
> > > Me.List4.SetFocus
> > > Exit Sub
> > > End Select
> > > End If
> > >
> > > PrintShipDoc:
> > > 'update Delivery Location field in primary Table
> > > Dim strSQL As String
> > > Dim strCity As String
> > > Dim strUnit As String
> > > strCity = Me.cboDelLocation
> > > strUnit = Me.List4
> > >
> > > strSQL = "UPDATE tblTruckloadPrimary SET
tblTruckloadPrimary.ShippingID = "
> > > & "'" & strCity & "'" & _
> > > " WHERE tblTruckloadPrimary.[New Unit #] = " & "'" & strUnit & "'"
> > > Debug.Print strSQL
> > > CurrentDb.Execute strSQL, dbFailOnError
> > >
> > > 'Print Shipping Document
> > > Dim stDocName As String
> > >
> > > stDocName = "rptShippingDoc"
> > > DoCmd.OpenReport stDocName, acNormal
> > >
> > > 'Update Print Flag
> > > Dim stQryName As String
> > > stQryName = "qryUpdateNewShippingDocPrinted"
> > > DoCmd.OpenQuery stQryName, acViewNormal
> > >
> > > Me.txtComments = Null
> > > Me.Refresh
> > >
> > > Me.List4.SetFocus
> > >
> > > Exit_Command6_Click:
> > > Exit Sub
> > >
> > > Err_Command6_Click:
> > > MsgBox Err.Description
> > > Resume Exit_Command6_Click
> > >
> > > End Sub
> > >
> > > Doyce
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "dnwinberry"
> > > <winberry.doyce@> wrote:
> > > >
> > > > Hello friends,
> > > >
> > > > I need some help making this code work smoothly. Currently, it is
working
> > > but when the procedure runs it returns a write conflict error and I
have to
> > > choose to save record, copy to clipboard or drop changes and it is not
> > > clearing the text box for comments.
> > > >
> > > > Here is what I want the code to do:
> > > > Check for a unit number in a combo box named List4, if one is there
> > > > check for a delivery location in combo box, cboDelLocation, then
check to
> > > see if the shipping docs have already been printed by looking at a
yes/no
> > > control, ShipDocsPrinted. If no, print the shipping docs. If yes,
prompt the
> > > user to either print again or exit. Finally, I want the Shipping docs
print
> > > flag upated to yes after the shipping docs are printed. Here is the
code:
> > > >
> > > > Private Sub Command6_Click()
> > > > On Error GoTo Err_Command6_Click
> > > >
> > > > 'Check for Unit Number
> > > > If IsNull(Me.List4) Then
> > > > Call MsgBox("Please select a valid unit number before printing.",
> > > vbExclamation, "No Unit Number Selected")
> > > > Me.List4.SetFocus
> > > > Exit Sub
> > > > End If
> > > >
> > > >
> > > > 'check for Delivery Location
> > > > If IsNull(Me.cboDelLocation) Then
> > > > Call MsgBox("Please select a valid delivery location.",
vbExclamation, "No
> > > Delivery Location Selected")
> > > > Me.cboDelLocation.SetFocus
> > > > Exit Sub
> > > > End If
> > > >
> > > > 'check to see if ship docs have already been printed
> > > > If Me.ShipDocsPrinted = -1 Then
> > > > Select Case MsgBox("Shipping documents have already been printer for
this
> > > trailer. " _
> > > > & vbCrLf & "Do you want to print them again?" _
> > > > , vbYesNo Or vbQuestion Or vbDefaultButton2, Application.Name)
> > > >
> > > > Case vbYes
> > > > GoTo PrintShipDoc
> > > > Case vbNo
> > > > Me.List4.SetFocus
> > > > Exit Sub
> > > > End Select
> > > > End If
> > > >
> > > > PrintShipDoc:
> > > > 'Print Shipping Document
> > > > Dim stDocName As String
> > > >
> > > > stDocName = "rptShippingDoc"
> > > > DoCmd.OpenReport stDocName, acNormal
> > > >
> > > > 'Update Print Flag
> > > > Dim stQryName As String
> > > > stQryName = "qryUpdateNewShippingDocPrinted"
> > > > DoCmd.OpenQuery stQryName, acViewNormal
> > > >
> > > > Me.Refresh
> > > > Me.txtComments = Null
> > > > Me.List4.SetFocus
> > > >
> > > > Exit_Command6_Click:
> > > > Exit Sub
> > > >
> > > > Err_Command6_Click:
> > > > MsgBox Err.Description
> > > > Resume Exit_Command6_Click
> > > >
> > > > End Sub
> > > >
> > > > the SQL for "qryUpdateNewShippingDocPrinted" is:
> > > >
> > > > UPDATE tblTruckloadPrimary SET tblTruckloadPrimary.ShipDocsPrinted =
True
> > > > WHERE (((tblTruckloadPrimary.[New Unit
> > > #])=[Forms]![frmPrintShippingDoc]![List4]));
> > > >
> > > > Thanks in advance.
> > > >
> > > > Doyce
> > > >
> > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
>

[Non-text portions of this message have been removed]