I made tons of changes to a database yesterday.
The user reviewed the changes and did not like them.
So, I am back to the drawing board.


There is a button on a form that uses the following code:

Private Sub dayxxxxxgeneratebols_Click()
On Error GoTo Err_cmddayxxxxxgenerateBOls_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmdayxxxxxgeneratebols"

' Me.CloseTicket = -1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Forms![frmdayxxxxxGenerateBOLs]!CustInvID.Value = Forms![frmUpdatePickTicket]!CustInvID
Forms![frmdayxxxxxGenerateBOLs]!FDWLotNo.Value = Forms![frmUpdatePickTicket]!FDWLotNo
Forms![frmdayxxxxxGenerateBOLs]!BOLNo.Value = Forms![frmUpdatePickTicket]!PickTicket
' Forms![frmGenerateBOL]!BOLDate.Value = Forms![frmUpdatePickTicket]!IssueDate
' Forms![frmGenerateBOL]!BOLDate.Value = Date
Forms![frmdayxxxxxGenerateBOLs]!ShipperCode.Value = Forms![frmUpdatePickTicket]!ShipperCode
Forms![frmdayxxxxxGenerateBOLs]!Shipper.Value = Forms![frmUpdatePickTicket]!Shipper
Forms![frmdayxxxxxGenerateBOLs]!ShipperAddress1.Value = Forms![frmUpdatePickTicket]!ShipperAddress1
Forms![frmdayxxxxxGenerateBOLs]!ShipperAddress2.Value = Forms![frmUpdatePickTicket]!ShipperAddress2
Forms![frmdayxxxxxGenerateBOLs]!ShipperCity.Value = Forms![frmUpdatePickTicket]!ShipperCity
Forms![frmdayxxxxxGenerateBOLs]!ShipperST.Value = Forms![frmUpdatePickTicket]!ShipperST
Forms![frmdayxxxxxGenerateBOLs]!ShipperZip.Value = Forms![frmUpdatePickTicket]!ShipperZip
Forms![frmdayxxxxxGenerateBOLs]!ShipperPhone.Value = Forms![frmUpdatePickTicket]!ShipperPhone
Forms![frmdayxxxxxGenerateBOLs]!ShipperContact.Value = Forms![frmUpdatePickTicket]!ShipperContact
Forms![frmdayxxxxxGenerateBOLs]!CustomerLotNo.Value = Forms![frmUpdatePickTicket]!CustomerLotNo
Forms![frmdayxxxxxGenerateBOLs]!CustomerOrderNo.Value = Forms![frmUpdatePickTicket]!CustomerOrderNo
Forms![frmdayxxxxxGenerateBOLs]!ReleaseNo.Value = Forms![frmUpdatePickTicket]!ReleaseNo
Forms![frmdayxxxxxGenerateBOLs]!Consignee.Value = Forms![frmUpdatePickTicket]!Consignee
Forms![frmdayxxxxxGenerateBOLs]!ConsigneeAddress1.Value = Forms![frmUpdatePickTicket]!ConsigneeAddress1
Forms![frmdayxxxxxGenerateBOLs]!ConsigneeAddress2.Value = Forms![frmUpdatePickTicket]!ConsigneeAddress2
Forms![frmdayxxxxxGenerateBOLs]!ConsigneeCity.Value = Forms![frmUpdatePickTicket]!ConsigneeCity
Forms![frmdayxxxxxGenerateBOLs]!ConsigneeST.Value = Forms![frmUpdatePickTicket]!ConsigneeST
Forms![frmdayxxxxxGenerateBOLs]!ConsigneeZip.Value = Forms![frmUpdatePickTicket]!ConsigneeZip
Forms![frmdayxxxxxGenerateBOLs]!ConsigneePhone.Value = Forms![frmUpdatePickTicket]!ConsigneePhone
Forms![frmdayxxxxxGenerateBOLs]!ConsigneeContact.Value = Forms![frmUpdatePickTicket]!ConsigneeContact
'
Forms![frmdayxxxxxGenerateBOLs]!ShipToCompany.Value = Forms![frmUpdatePickTicket]!ShipToCompany
Forms![frmdayxxxxxGenerateBOLs]!ShipToAddress1.Value = Forms![frmUpdatePickTicket]!ShipToAddress1
Forms![frmdayxxxxxGenerateBOLs]!ShipToAddress2.Value = Forms![frmUpdatePickTicket]!ShipToAddress2
Forms![frmdayxxxxxGenerateBOLs]!ShipToCity.Value = Forms![frmUpdatePickTicket]!ShipToCity
Forms![frmdayxxxxxGenerateBOLs]!ShipToST.Value = Forms![frmUpdatePickTicket]!ShipToST
Forms![frmdayxxxxxGenerateBOLs]!ShipToZip.Value = Forms![frmUpdatePickTicket]!ShipToZip
Forms![frmdayxxxxxGenerateBOLs]!ShipToPhone.Value = Forms![frmUpdatePickTicket]!ShipToPhone
Forms![frmdayxxxxxGenerateBOLs]!ShipToContact.Value = Forms![frmUpdatePickTicket]!ShipToContact

Forms![frmdayxxxxxGenerateBOLs]!SpecialInstructions.Value = Forms![frmUpdatePickTicket]!SpecialInstructions
Forms![frmdayxxxxxGenerateBOLs]!DeliveryDate.Value = Forms![frmUpdatePickTicket]!DeliveryDate
Forms![frmdayxxxxxGenerateBOLs]!DeliveryTime.Value = Forms![frmUpdatePickTicket]!DeliveryTime
Forms![frmdayxxxxxGenerateBOLs]!Freight.Value = Forms![frmUpdatePickTicket]!Freight
Forms![frmdayxxxxxGenerateBOLs]!Carrier.Value = Forms![frmUpdatePickTicket]!Carrier
Forms![frmdayxxxxxGenerateBOLs]!Truck.Value = Forms![frmUpdatePickTicket]!Truck
Forms![frmdayxxxxxGenerateBOLs]!WeightTare.Value = Forms![frmUpdatePickTicket]!WeightTare
DoCmd.Close acForm, "frmUpdatePickTicket"
Exit_cmdfrmdayxxxxxgenerateBOls_Click:
Exit Sub

Err_cmddayxxxxxgenerateBOls_Click:
MsgBox Err.Description
Resume Exit_cmdfrmdayxxxxxgenerateBOls_Click


End Sub





When it opens frmdayxxxxxgeneratebols, What is telling it to generate a new record?

When the users complete the process in one sitting, there is no issue.

However, if for some reason they have to save their work and come back to it later, they start to get errors because it says it is trying to create a new record.
When they hit the button to go back to frmdayxxxxxgeneratebols, it works fine and the form opens.
When they try the buttons on frmdayxxxxxgeneratebols, each one tells them it cant complete the process because it would create a duplicate record.
Even the View and Print buttons give the duplicate record error.

I dont want to create a new record.
I just want to be able to open the form, make some changes to the data, and hit the buttons to complete the task.


Once on frmdayxxxxxgeneratebols, they have the following buttons to choose from:


Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
If UpdSaved = False Then
UpdSaved = True
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub



Private Sub cmdViewCurrentBOL_Click()
'View Current BOL
On Error GoTo Err_cmdViewCurrentBOL_Click

Dim strDocName As String

strDocName = "dayxxxxxBOLView"
' View bol report, using qryCurrentBOLFilter query to view current bol
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenReport strDocName, acPreview, "qryCurrentBOLFilterNew1"

Exit_cmdViewCurrentBOL_Click:
Exit Sub

Err_cmdViewCurrentBOL_Click:
' If action was cancelled by the user, don't display an error message.
Const conErrDoCmdCancelled = 2501
If (Err = conErrDoCmdCancelled) Then
Resume Exit_cmdViewCurrentBOL_Click
Else
MsgBox Err.Description
Resume Exit_cmdViewCurrentBOL_Click
End If

End Sub




Private Sub Command185_Click()
On Error GoTo Err_cmdPrintCurrentBOL_Click

Dim strDocName As String

strDocName = "Copy of CurrentBOLFilterNew1"
' Print bol report, using qrycurrentboldayxxxxx query to view current bol
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenReport strDocName, acViewPreview, "qrycurrentboldayxxxxx"
DoCmd.PrintOut
DoCmd.Close acReport, strDocName

Exit_cmdPrintCurrentBOL_Click:
Exit Sub

Err_cmdPrintCurrentBOL_Click:
' If action was cancelled by the user, don't display an error message.
Const conErrDoCmdCancelled = 2501
If (Err = conErrDoCmdCancelled) Then
Resume Exit_cmdPrintCurrentBOL_Click
Else
MsgBox Err.Description
Resume Exit_cmdPrintCurrentBOL_Click
End If

End Sub




Private Sub cmdUpdateInvTrans_Click()
On Error GoTo Err_cmdUpdateInvTrans_Click
Dim stDocName, stShipCo, stShipCity, stShipST As String

stDocName = "frmCustomerInventory2"

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblPickTicket SET CloseTicket = -1 WHERE tblPickTicket.PickTicket=" & Me.BOLNo & ";"
DoCmd.SetWarnings True

If IsNull(ShipToCity) And IsNull(ShipToST) Then
stShipCo = Nz(Consignee, "")
stShipCity = Nz(ConsigneeCity, "")
stShipST = Nz(ConsigneeST, "")
Else
stShipCo = Nz(ShipToCompany, "")
stShipCity = Nz(ShipToCity, "")
stShipST = Nz(ShipToST, "")
End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm stDocName, , , "[CustInvID] =" & Forms![frmdayxxxxxGenerateBOLs].CustInvID
Forms!frmCustomerInventory2.Page104.SetFocus
If Not Forms!frmCustomerInventory2.Form.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If

Forms![frmCustomerInventory2]![frmInventoryTransactions2].Form!LoadDateFrom.Value = Forms![frmdayxxxxxGenerateBOLs]!BOLDate
Forms![frmCustomerInventory2]![frmInventoryTransactions2].Form!LadingNo.Value = Forms![frmdayxxxxxGenerateBOLs]!BOLNo
Forms![frmCustomerInventory2]![frmInventoryTransactions2].Form!ShippedTo.Value = stShipCo
Forms![frmCustomerInventory2]![frmInventoryTransactions2].Form!City.Value = stShipCity
Forms![frmCustomerInventory2]![frmInventoryTransactions2].Form!ST.Value = stShipST
Forms![frmCustomerInventory2]![frmInventoryTransactions2].Form!Transaction.Value = "SHIP"
Forms![frmCustomerInventory2]![frmInventoryTransactions2].Form!TonsQty = Forms![frmdayxxxxxGenerateBOLs]!txtQtyPickedTons * -1
Forms![frmCustomerInventory2]![frmInventoryTransactions2].Form!PiecesQty = Forms![frmdayxxxxxGenerateBOLs]!txtQtyPickedPieces * -1
Forms![frmCustomerInventory2]![frmInventoryTransactions2].Form!ReleaseNo = Forms![frmdayxxxxxGenerateBOLs]!ReleaseNo

DoEvents
Me.Recalc
Me.Requery


Exit_cmdUpdateInvTrans_Click:
Exit Sub

Err_cmdUpdateInvTrans_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateInvTrans_Click

End Sub





Private Sub Command136_Click()
'Print Inspection
On Error GoTo Err_cmdPrintPickTicket_Click

Dim strDocName As String

strDocName = "rptInspectionForm"
'Print current pick ticket, using qryCurrentPickTicketFilter query to view current ticket
DoCmd.OpenReport strDocName, acViewNormal, "qryCurrentPickTicketNew1"


Exit_cmdPrintPickTicket_Click:
Exit Sub

Err_cmdPrintPickTicket_Click:
' If action was cancelled by the user, don't display an error message.
Const conErrDoCmdCancelled = 2501
If (Err = conErrDoCmdCancelled) Then
Resume Exit_cmdPrintPickTicket_Click
Else
MsgBox Err.Description
Resume Exit_cmdPrintPickTicket_Click
End If
End Sub



There is also a section on this form to enter the weight of the truck.
If they run right through the process, no stopping, it works like a charm.
If they have to save and come back later, even entering this weight and hitting save causes the duplicate record error.


How can I get past the duplicate error I am seeing?
Here is the error I got just now when I told the form I wanted to cmdViewCurrentBOL

Click to view attachment

Hi,

I don't really get all that but just as a wild guess, see if the form's Data Entry property is set to "Yes." And if it is, try setting it to "No."

Just my 2 cents...

I changed it to No and it still did the same thing.

What in all of that first bit of code is telling it to create a new record?
I just want to create a new form that will "VIEW" the data, not generate a new record.
I say new form, because I want to create a new button to view the current BOL as a form.


That is, of course, if I can't figure out the original code.

Hi,


Sorry to hear that. So it was set to Yes. When you set it to No, did any of the other problems, like creating new records, go away at all? Or, did all of the problems stay?

Just my 2 cents...

They all stayed

What I don't get is I don't see anything in the first code that would tell it that it is creating a new record.
If I can have this same form, but not have it create a new record, that would be ideal.

I guess.

Hi,


Yeah, it's not the code. The Data Entry property is the one that says "Yes" create a new record.

Just my 2 cents...

Looking into that I found this code:

Private Sub Form_Dirty(Cancel As Integer)
UpdSaved = False
End Sub


What does it do?

It probably just marks a flag to indicate that the record has been updated and needs to be saved.

Turning the Data Entry off worked................AT FIRST

I went and showed it to the user and it didnt work like it did when I was working in it.
I created a new form for "Modifying the current BOL" and assigned a button on the main form.
It worked like a charm.

However, it started to act just like the other form.
I am getting duplicate record errors and I have the Data Entry property turned to off.


Where should I look now?


Also, the new form for some reason has default data on it.
I do not want any default data.
For example, if I open the form in form view, without the needed data to fill it in, it has assumed data on it instead of being blank.
How do I remove it?

Hi,


I'm really sorry to hear that you are having so much issues with this project but I can't even imagine where to tell you to look without seeing what you're seeing.

Is there any way you can share your screen with me?

Just my 2 cents...

Email sent