Re: [MS_AccessPros] Form Collection Voucher Recordset not updateable

 

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

John,

I have been waiting for your reply. Now after 3-4 days i have to enter data for new Consignment but i am stuck on the form as i cannot move to Detail section of the form to enter data.

In the mean while waiting for your reply i have been trying and looking for any possible way to overcome this new issue. One thing i may mention that this is causing now when we made Pkey of tbl CollectionVoucher:

ConsignmentNo

ClientCIN

CartonNo

CartonSuffix

Before this i was making data entry on this form.

Now i tried this on the Control cmbSetRate

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      <====  Stopped this line of code

    DoCmd.GoToRecord , , acLast

    CartonNo.SetFocus

End Sub

Then i move to Detail Section of the Form on the Control CartonNo, but unable to move to new record for data entry, on the Navigation Buttons New Record icon is disabled.

-------------------------

Second situation is if i change the line DoCmd GoToRecord, , acNewRec as below

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

I get an error message 'You can't go to the specified record.

---------------------------

Third situation is that if i remove After Update event of cmbSetRate i am floating on the (Sub Form's) Form Header form Tab Index 0 to 6.

Please help.

Khalid

On Monday, November 3, 2014 2:41 PM, "Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

John,

Rate is in the Detail section of  Sub Form and cmbRate is on Form Header of Sub Form.

Khalid

On Monday, November 3, 2014 2:06 PM, "John Viescas JohnV@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

Khalid-

Where is Rate defined?  It's not in the Record Source.  The Record Source appears to be updatable.

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 3, 2014, at 9:28 AM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Record Source of Form is query:

SELECT [Consignment Number].ConsignmentNo, [Consignment Number].ExportDocs

FROM [Consignment Number]

ORDER BY [Consignment Number].ConsignmentNo;

Khalid

On Monday, November 3, 2014 1:07 PM, "John Viescas JohnV@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

Khalid-

(Message over 64 KB, truncated)

__._,_.___

---

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 (16)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___