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

Selasa, 23 April 2013

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

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]