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

Selasa, 23 April 2013

[MS_AccessPros] Re: Write conflict: help with code

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