Re: [MS_AccessPros] Form Collection Voucher Recordset not updateable

 

John,

Thank you very much.

This is you who could have got that this code should be on  subform's before update event. The code is now working fine. 

Proud to be member of this group with worthy MVP's like you.

Khalid

On Friday, November 7, 2014 4:48 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

Khalid-

Ah, when you don't change the value of CartonSuffix at all, this code never runs!  Duh.  This code should be in the Form_BeforeUpdate and needs to be modified like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim stLinkCriteria As String

    Dim rsc As DAO.Recordset

    ' Don't check if not on a new row

    If Not Me.NewRecord Then Exit Sub

      stLinkCriteria = "[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

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 12:13 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Yes it is still there, see that is it on the correct line:

Private Sub CartonSuffix_BeforeUpdate(Cancel As Integer)

    Dim stLinkCriteria As String

    Dim rsc As DAO.Recordset

      stLinkCriteria = "[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

Now i tried to enter CartonNo = 15 leaving CartonSuffix to its default value 0. On the next row i entered same CartonNo = 15 and left CartonSuffix again to its default value 0. Before jumping to next i again got the same error. When i changed CartonSuffix of the last row to 1 i moved to next row.

Then i opened the code and checked immediate window i get the following there:

[CartonSuffix]='1' and [CartonNo]=15 and [ClientCIN]=1 and [ConsignmentNo]='2014-A-07'

Khalid

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

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@... [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:

__._,_.___

---

Posted by: Khalid Tanweer <khalidtanweerburrah@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (23)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___