Getting a specified Client Name on Report

[Khalid Tanweer]

Hi All, its my first post on this forum. Please excuse if my question is not properly described.


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

[Bulzie]

Holy WALL of TEXT Batman!!!

Maybe give some data examples of what you have on the form, in the tables/reports and what you need changed or added. Also might be good to attach a copy of the database so the fine folks on here can help troubleshoot.

[Micron]

Yeah, that's a lot of strain on the eyes. Please use code tags and indentation/separation.
If all you want to do is pass one value from a form to any other form or report when it's opened, use the OpenArgs parameter of the DoCmd.OpenForm method. When the form/report opens, pass the argument to the control.
https://msdn.microsoft.com/en-us/vba...roperty-access

[ssanfu]

What is the SQL for the Report?
Why can't you just add "NameofClient" to the report record source???

Then edit the "CINandName" control on the report.



----------------------------------------------------------------
I am not a fan of the way your cascading combo boxes are are set up.

If you try this, do so on a COPY of your dB...... (just in case --- you have been warned!)

Unbound "cmbConsignmentNo"-->Row Source

SELECT DISTINCT CollectionVoucher.ConsignmentNo
FROM CollectionVoucher
ORDER BY CollectionVoucher.ConsignmentNo DESC;

Having After Update Event:

Private Sub cmbConsignmentNo_AfterUpdate()
   Me.cmbClientCIN = vbnullstring
   Me.cmbClientCIN.Requery 
End Sub

Unbound "cmbClientCIN"----> Row Source:

SELECT DISTINCT Clients.ClientCIN, Clients.ClientName, Consignee.ConsigneeName, CollectionVoucher.NameofClient 
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