Re: [MS_AccessPros] Report to show specific Client Name

 

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

End Sub

Or you will guide and suggest what to do next.

Regards,

Khalid

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

John,

Please give me some time. I have changed a lot of code while trying myself. Let me revert back.

Yes your suggestion now show in Dialog Box, ConsignmentNo and NameOfClient. This gives me hope that further we can get the required results.

I'll be back sooner.

Thanks for your answer and help.

Regards,

Khalid

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

Khalid-

Change the Row Source of cmbConsignmentNo to:

SELECT DISTINCT CollectionVoucher.ConsignmentNo, CollectionVoucher.NameOfClient

FROM CollectionVoucher

ORDER BY CollectionVoucher.ConsignmentNo DESC;

You can get to the new data by referencing Me.cmbConsignmentNo.Column(1)

Does that help?

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out 

Microsoft Office Access 2007 Inside Out 

http://www.viescas.com/

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

Hi All,

I have a report "Freight Invoice Client". The report is OK at the moment no errors. But my user needs some thing more on this report.

A Dialog Form "Dialog Freight Invoice Selective Consignment-Client" opens the report. The form has two Un bound Combo boxes:

"cmbConsignmentNo"-->Row Source

SELECT DISTINCT CollectionVoucher.ConsignmentNo

FROM CollectionVoucher

ORDER BY CollectionVoucher.ConsignmentNo DESC;

Having After Update Event:

Private Sub cmbConsignmentNo_AfterUpdate()

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"

 End Sub

Second un bound Combo box "cmbClientCIN"----> Row Source:

SELECT DISTINCT Clients.ClientCIN, Clients.ClientName

FROM Clients

ORDER BY Clients.ClientCIN;

The Report shows "ClientName" on its Report Header in Text box "CINandName" with Control Source:

="CIN: " & [ClientCIN] & ", Consignor: " & [ClientName] & ", Consignee: " & [CosigneeName]

My user needs that in data entry on Form "New Cargo Collection Input" for a particular "ConsignmentNo" what "ClientCIN" is selected, its "ClientName" should dispay on the report for specific "ConsignmentNo" even after wards "ClientName" is edited OR changed for specific "ClientCIN". My answer was simpy add new Client, but he has reservations for adding more and more Cients for some practical work reasons,while collecting cargo by his staff.

The only solution which i thought was to add a new field "NameOfClient" in table "CollectionVoucher" and on Before update event of Sub form "NewCargoCollectionInputsubform" add a line:

Me.NameOfClient = Me.ClientSelected

It did worked and NameOfClient does saves in table.

Secondly changing in reports Text box "CINandName" Control Source.

="CIN: " & [ClientCIN] & ", Consignor: " & [ClientName] & ", Consignee: " & [CosigneeName] 

To:

="CIN: " & [ClientCIN] & ", Consignor: " & [NameOfClient] & ", Consignee: " & [CosigneeName]

Now the  in Dialog Form "Dialog Freight Invoice Selective Consignment-Client" After Update Event of "cmbConsignmentNo" must be changed to get "NameofClient" from table "CollectionVoucher" which i am unable to set.

This is my approach. If some other better solution or possibilty is there please help and guide.

Regards,

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

---

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

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___