Re: [MS_AccessPros] Form Collection Voucher Recordset not updateable

 

Khalid-

Do you still have:

Debug.Print stLinkCriteria

.. after the assignment statement?  If so, you should see the output in the Immediate Window after you run the code.

So, you're saying the only unique index is the Primary Key?  The code should work providing it is building the filter correctly.

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)

On Nov 7, 2014, at 10:37 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Apart from Unique indexes i mentioned before, there are following indexes too but not Unique

UnitOfCarton

UnitOfGrossWeight

UnitOfNetWeight

UnitOfQty

UnitOfValue

I may say sorry that i do not know how to see the Debug.Print info output, that you told me before by pressing Ctrl+G we can see immediate window on the bottom.

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Do you have any other indexes defined on CollectionVoucher?  Do you see the correct info in the Debug.Print output?

We are checking for any duplicate on the entered ConsignmentNo, ClientCIN, CartonNo, and CartonSuffix, so your code *should* detect any previous records saved with that same combination of values.  We aren't doing any checking for letters or numbers, so it should work no matter what you enter in CartonSuffix.

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)

On Nov 7, 2014, at 6:21 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

It is yet not working.

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Try this:

    stLinkCriteria = "[CartonSuffix]='" & [CartonSuffix] & "' and [CartonNo]=" & [CartonNo] & " and [ClientCIN]=" & [cmbClientCIN] & _

                     " and [ConsignmentNo]='" & [ConsignmentNo] & "'"

I think by including DeliveryVr, you are missing some actual duplicates.

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)

On Nov 6, 2014, at 1:30 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

As you suggested Primary Key for Collection Voucher is:

ConsignmentNo

ClientCIN

CartonNo

CartonSuffix

There is no other Unique index.

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

What is the Primary Key of the Collection Voucher table, and what other Unique indexes do you have defined?

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)

On Nov 6, 2014, at 12:55 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John, 

Sorry that i did'nt posted the code for Before Update event.

I think that it's in the line:  If DCount("*", "CollectionVoucher", stLinkCriteria) > 0 Then

As Default value of CartonSuffix is 0.

Private Sub CartonSuffix_BeforeUpdate(Cancel As Integer)

    Dim stLinkCriteria As String

    Dim rsc As DAO.Recordset

    stLinkCriteria = "[DeliveryVr]='" & [cmbDeliveryVr] & "' and [CartonSuffix]='" & [CartonSuffix] & "' and [CartonNo]=" & [CartonNo] & " and [ClientCIN]=" & [cmbClientCIN] & _

                     " and [ConsignmentNo]='" & [ConsignmentNo] & "'"

                    Debug.Print stLinkCriteria

    

   'Check CollectionVoucher table for duplicate CartonNo

    If DCount("*", "CollectionVoucher", stLinkCriteria) > 0 Then

        'Message box warning of duplication

            MsgBox "This Carton Number: " & [CartonNo] & " has already been allotted" & vbCrLf & _

               "in Consignment No.'" & [ConsignmentNo] & "'," & vbCrLf & _

               "vide Contract No.'" & [cmbDeliveryVr] & "' for Client CIN: " & [cmbClientCIN] & "." _

              , vbInformation, "PCTL - Duplicate Entry"

            'Undo duplicate entry

            Me.Undo

   

            'Go to record of original CartonNo

            Set rsc = Me.RecordsetClone

            rsc.FindFirst stLinkCriteria

            Me.Bookmark = rsc.Bookmark

            Set rsc = Nothing

   End If

End Sub

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Please post the code for the Before Update event of CartonSuffix.

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)

On Nov 6, 2014, at 11:26 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Thanks a lot for your detailed and eye opening answer. I will check deeply my relationships with the logic you described. I must say you are a genius.

I removed the field ConsigneeID from CollectionVoucher and also removed relationship of ConsigneeID with CollectionVoucher.

Now it worked, either i make the line with  DoCmd.GoToRecord , , acNewRec OR last

Private Sub cmbSetRate_AfterUpdate()

    If Not IsNull(Me.cmbSetRate) Then

        ' Apply a filter

        Me.Filter = "Rate = " & Me.cmbSetRate

        Me.FilterOn = True

    End If

    Me.Rate = cmbRate

    DoCmd.GoToRecord , , acNewRec

    CartonNo.SetFocus

End Sub

One another thing i observed, please look into it. On subform's control CartonSuffix on its Before Update event, if CartonSuffix is an alphabat the event displays message box for duplicate Carton, but if CartonNo is same and CartonSuffix has its default value 0, it does'nt displays the message box for duplicate carton, instead before moving to new record Access's own message displays as:

The changes you requested to the table were not successful because they would create duplicate values in the index, Primary key, or relationship.   ..........................

I once again thank you for your help & guidance.

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Let me see if I can decipher the relationships from your JOIN statement.

       Consignment Number

              |

              |    Products

              |        |

              v        v

Clients -> CollectionVoucher

     ^                 ^

     |                 |

     +--------  Consignee

A query is updatable only when you follow a chain of one to many relationships down from one to many.  You have Consignee related to both Clients and CollectionVoucher and then Clients also related to CollectionVoucher.  Access can't figure out the unique row in CollectionVoucher to be updated, so it marks the query not updatable.

It does not make sense that Consignee is related to both Clients and CollectionVoucher when there is also a relationship between Clients and CollectionVoucher directly.  The ConsigneeID should be in Clients or CollectionVoucher, but not both.

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)

On Nov 6, 2014, at 8:50 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Record Source of subform is:

SELECT CollectionVoucher.ConsignmentNo, [Consignment Number].ExportDocs, CollectionVoucher.Route, CollectionVoucher.ClientCIN, Clients.ClientName, Consignee.ConsigneeID, Consignee.ConsigneeName, CollectionVoucher.Destination, CollectionVoucher.DeliveryVr, CollectionVoucher.DeliveryVrDate, CollectionVoucher.CartonNo, CollectionVoucher.CartonSuffix, CollectionVoucher.TripNo, CollectionVoucher.UnitOfCarton, CollectionVoucher.ProductNameEnglish, Products.ProductNameRussian, Products.HSCode, CollectionVoucher.BrandName, CollectionVoucher.ProductQty, CollectionVoucher.UnitOfQty, CollectionVoucher.WeightOfCarton, CollectionVoucher.UnitOfGrossWeight, CollectionVoucher.[Net Weight], CollectionVoucher.UnitOfNetWeight, CollectionVoucher.UnitOfValue, CollectionVoucher.Rate, CollectionVoucher.Amount

FROM Products INNER JOIN ([Consignment Number] INNER JOIN (Consignee INNER JOIN (Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN) ON (Consignee.ConsigneeID = CollectionVoucher.ConsigneeID) AND (Consignee.ConsigneeID = Clients.ConsigneeID)) ON [Consignment Number].ConsignmentNo = CollectionVoucher.ConsignmentNo) ON Products.ProductNameEnglish = CollectionVoucher.ProductNameEnglish;

P.S: I am not getting all replies on my yahoo mail, that is why i could not see your answer.

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

I answered several days ago!  You gave me the Record Source of your outer form but then told me that Rate is on a subform.  That means the Record Source of the subform is not updatable.  What is the SQL?

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)

On Nov 5, 2014, at 6:22 AM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (20)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___