Re: [MS_AccessPros] Report to show specific Client Name

 

Khalid-

That doesn't make sense.  What is the Primary Key of the underlying table?  If ConsignmentNo / CartonNo aren't the Primary Key, do you at least have a Unique index defined?  There should not be a duplicate carton number in a given consignment.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Aug 29, 2017, at 9:54 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

This was old criteria for the previous settings when we have not added field "NameOfClient" and were looking for duplicate CatonNo for ClientCIN and ConsignmentNo

Now i am saying that "ConsignmentNo" is same and "ClientCIN" is also same, but "ClientName" has been changed. In this scenario criteria should also be changed.

For e.g. if there is one entry in 

ConsignmentNo ClientCIN ClientName CartonNo

2017-B-01 29 ABC 1

We are entering data in same ConsignmentNo, same ClientCIN, but ClientName has been changed and his first entry is CartonNo 1

2017-B-01 29 XYZ 1

Now No message for duplicate CartonNo should pop up, but if CartonNo 1 is entered again for:

2017-B-01 29 XYZ 

We should get Msg for duplicate CartonNo

This was my question. Please look into it.

Regards,

Khalid

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

Khalid-

Your criteria are:

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

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

.. and then you look to see if there's a row that matches.  As long as you have entered a different (new) carton number for this row, there should be no match.  You're saying that this is a new carton on a given consignment for a client whose name has changed.  You should not duplicate a previous carton number.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Aug 29, 2017, at 9:23 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Yes of course, two Clients cannot have the same ClientCIN.

What we have worked up to now and is OK for report is as below:

There was a Client with "ClientCIN"= 29 having "ClientName" = "ABC"

Then

This ClientCIN 29, ClientName is changed to "XYZ"

And we need to enter new cargo collection entries for new/changed/re-named ClientName "XYZ" with the same ClientCIN 29, although there have been entries in this particular Consignment with ClientCIN 29 and ClientName = "ABC" for CartonNo 1 to 10, now we should be able to enter any CartonNo between 1 to 10 in the same ConsignmentNo for ClientCIN 29 and ClientName "XYZ"

Regards,

Khalid

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

How can two clients have the same ClientCIN?  That shouldn't be possible.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Aug 29, 2017, at 6:04 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Wow! that is marvelous.

It is DONE.

One thing i did, i removed "NameOfClient" from cmbConsignmentNo. If i put "NameOfCient" also, then list was showing all "NameOfClient" with same ConsignmentNo.

The report is now OK by removing "NameOfClient" from cmbConsignmentNo.

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

John, one more favor is required, in data entry of form "NewCargoCollectionInputsubform" for new "New/ changed ClientName it is displaying Msgbox for CartonNo previously entered for other ClientName having same ClientCIN. I have tried with different ways but could not mange it. Below is the code i had before.

Private Sub CartonNo_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 " Carton Number: " & [CartonNo] & "-" & [CartonSuffix] & " has already been punched vide Contract No. " & [cmbDeliveryVr] & "." & vbCrLf & _

            "for Consignor: " & [cmbClientCIN] & "-" & ClientSelected & ", Consingee: " & ClientConsignee & "." & vbCrLf & _

            "In Consignment No. " & [ConsignmentNo] & "." _

            , vbInformation, "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

Thank you very much for your support and help.

Best regards,

Khalid

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

Khalid-

I told you to ADD NameOfClient to that code. - The SELECT DISTINCT.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Aug 29, 2017, at 3:10 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

In your code i can not see "NameOfClient" anywhere ?

Any how applying your current code:

Say for Consignment No.2017-B-01, cmbConsignmentNo shows List for NameOfCient

ABC for CientCIN 29

I renamed this "ClientName" to XYZ and made data entry in form.

This NameOfClient is also displaying in List

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

Now in cmbClientCIN i cannot see NameOfClient "ABC"

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

If i selected "XYZ" from cmbClientCIN, the report shows NameOfClient = XYZ, but shows 30 records i.e:

29 records of ABC and 1 record of XYZ.

Regards,

Khalid

 

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

No.  I was talking about this bit of code:

Me.cmbClientCIN.RowSource = "SELECT DISTINCT Clients.ClientCIN, Clients.ClientName, Consignee.ConsigneeName " & _

"FROM Consignee INNER JOIN (Clients INNER JOIN CollectionVoucher  " & _

"ON Clients.ClientCIN = CollectionVoucher.ClientCIN) " & _

"ON Consignee.ConsigneeName = Clients.CosigneeName " & _

"WHERE CollectionVoucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _

 "' ORDER BY Clients.ClientCIN"

Elsewhere in code, you that will allow you to fetch the related NameOfClient from the combo box.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Aug 28, 2017, at 10:39 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Perhaps you are talking of data input in "NewCargoCollectionInputsubform" ?

Its Record Source is:

SELECT CollectionVoucher.ConsignmentNo, [Consignment Number].ExportDocs, CollectionVoucher.Route, CollectionVoucher.ClientCIN, Clients.ClientName, CollectionVoucher.NameOfClient, CollectionVoucher.Destination, CollectionVoucher.DeliveryVr, CollectionVoucher.DeliveryVrDate, CollectionVoucher.CartonNo, CollectionVoucher.CartonSuffix, CollectionVoucher.CartonQty, CollectionVoucher.TripNo, CollectionVoucher.UnitOfCarton, CollectionVoucher.ProductID, Products.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, CollectionVoucher.ID

FROM Products INNER JOIN ([Consignment Number] INNER JOIN (Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN) ON [Consignment Number].ConsignmentNo = CollectionVoucher.ConsignmentNo) ON Products.ProductID = CollectionVoucher.ProductID

ORDER BY CollectionVoucher.CartonNo, CollectionVoucher.ProductID;

I have put "NameOfClient" on its Detail Section set Send to back and Visible property to No. Should i make it visible bring to front and what more...... to do ?

Regards,

Khalid

 

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

Khalid-

Since you are including the CollectionVoucher table in that query, why not simply add the NameOfClient field to the list of columns?  I gather what you want is the client name that was associated at the time the CollectionVoucher was created, even if the client changes later.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Aug 28, 2017, at 10:11 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I think obviously the next step would be to change After Update Event of cmbConsignmentNo, which is at present:

Private Sub cmbConsignmentNo_AfterUpdate()

Me.cmbClientCIN.RowSource = "SELECT

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

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___