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

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]
> >
>