[MS_AccessPros] Re: Report to show specific Client Name

 

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

 

 

__._,_.___

---

Posted by: JohnV@msn.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

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

.

__,_._,___