ACCESS FEED: [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, "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, "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, 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@yahoogroups.com] On Behalf Of dnwinberry
> > > Sent: Tuesday, April 23, 2013 2:31 PM
> > > To: MS_Access_Professionals@yahoogroups.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> , "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]
> > >
> >
>