ACCESS FEED: Re: [MS_AccessPros] How to enter different product in the same carton

Khalid-

You need to fix your code:

Private Sub cmbConsignmentNo_AfterUpdate()

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

"FROM CollectionVoucher INNER JOIN Clients " & _

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

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

"' ORDER BY ClientCIN"

End Sub

You also need to fix the original Row Source of cmbConsignmentNo to match the code above without the WHERE clause.

If you run "qryGroupCartons" (you say you have now named it "qryGroupCartonsFreightInvoice") by itself, do you see one row per logical carton? It should be lumping together all the cartons that have a numeric suffix indicating multiple products in one carton.

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 Oct 30, 2014, at 6:00 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

All suggestions you gave did'nt worked....

Well John don't tap your head with your hands. You were perfectly right giving all suggestions, but you were not aware of the mistake/error which was hidden and sorry to say that i could not have trapped it out before and told you.

Last night having exploring the Dialog "Dialog Freight Invoice Selective Consignment-Client" I checked the After Update event of cmbConsignmentNo for the previous settings it was:

Private Sub cmbConsignmentNo_AfterUpdate()

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

"FROM CollectionVoucher " & _

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

"' ORDER BY ClientCIN"

End Sub

Ah....Here was our hidden mystery "ClientName", so now i removed ClientName and now i'm not been prompted for "ClientName".

Sorry once again for putting you in inconvenience.

Now on the second combo box "cmbClientCIN" i need to show ClientName against each ClientCIN, which is not showing now, i'm only getting ClientCIN.

Row Source of cmbClientCIN is:

SELECT DISTINCT CollectionVoucher.ClientCIN, Clients.ClientName

FROM Clients LEFT JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN

ORDER BY CollectionVoucher.ClientCIN;

Secondly Count of Cartons at ConsignmentNo footer on report is not giving correct count, The query you gave i just renamed as "qryGroupCartonsFrieghtInvoice"

TotalCartons:

=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ClientCIN = " & [ClientCIN] & " AND CartonNo = " & [CartonNo])

Regards,

Khalid

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

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

Khalid-

Are you opening the dialog from the Report's Open event, or are you opening the dialog and then the report?

Does this query run OK?
SELECT DISTINCT CollectionVoucher.ClientCIN, Clients.ClientName
FROM Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN
ORDER BY CollectionVoucher.ClientCIN;

As for the totals, you need a query similar to what you had originally.

qryGroupCartons:
SELECT CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, Sum(CollectionVoucher.WeightOfCarton) As CartonWeight, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1)) AS CartonSuffix
FROM CollectionVoucher
GROUP BY CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1));

Now in the footer, you can set controls to:

CartonCount:
=DCount("CartonNo", "qryGroupCartons", "ClientCIN = " & [ClientCIN] & " AND CartonNo = " & [CartonNo])

or

CartonWeight:
=DSum("CartonWeight", "qryGroupCartons", "ClientCIN = " & [ClientCIN] & " AND CartonNo = " & [CartonNo])

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 Oct 28, 2014, at 5:27 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Clients table has field "ClientName" having 83 records the ClientCIN yet not allotted have been assigned the ClientName = (Blank).

So the questions arises why it cannot find the field ClientName in Clients table?

Khalid Tanweer
Manager Marketing & Exports
Pak Caspian Trade Links
marketing@...
tanweerkhalid@...

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

Khalid-

I would guess it can't find a field called ClientName in the Clients table.

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 Oct 27, 2014, at 12:21 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
I tried this report with only this query "Freight Invoice Selective Consignment Client Query"

SELECT CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, Clients.ClientName, Consignee.ConsigneeName, CollectionVoucher.Destination, CollectionVoucher.ExportDocs, CollectionVoucher.DeliveryVr, CollectionVoucher.DeliveryVrDate, CollectionVoucher.CartonNo, CollectionVoucher.CartonSuffix, CollectionVoucher.ProductNameEnglish, CollectionVoucher.ProductQty, CollectionVoucher.WeightOfCarton, CollectionVoucher.Rate, CollectionVoucher.Amount
FROM Consignee INNER JOIN (Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN) ON (Consignee.ConsigneeID = CollectionVoucher.ConsigneeID) AND (Consignee.ConsigneeID = Clients.ConsigneeID)
WHERE (((CollectionVoucher.ConsignmentNo)=[Forms]![Dialog Freight Invoice Selective Consignment-Client]![cmbConsignmentNo]) AND ((CollectionVoucher.ClientCIN)=[Forms]![Dialog Freight Invoice Selective Consignment-Client]![cmbClientCIN]));

The report is giving me the desired results in each row. Except what i asked before why "Enter parameter values..." message box opens and asking for "ClientName" on the Form "Dialog Freight Invoice Selective Consignment-Client" second combo box "cmbClientCIN" Its Control Source is unbound and Row Source is:

SELECT DISTINCT CollectionVoucher.ClientCIN, Clients.ClientName
FROM Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN
ORDER BY CollectionVoucher.ClientCIN;

Also only ClientCIN displays in drop down list, ClientName does'nt shows.

Secondly, on the reports "ConsignmentNo" footer i need the total of cartons grouped on text box "TotalCartons" presently its Control Source is: ="Total Cartons:" & Count(*)

Khalid

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

Khalid-

First, you last query does not need to be a Totals query. The total weight is already calculated in the qryGroupCartonsFreightInvoice query.

You cannot display the discrete carton number suffixes because of your requirement to total the weight for the multiple carton entries when more than one product is in a carton. The rows showing up with a letter simply indicate duplicate CartonNo values that were moved from another assignment. The total count of cartons should be correct because of the grouping.

I suppose you need to display the individual rows to display the different products. To do that, you would have to use the raw CartonSuffix value and calculate your totals in a subtotal line on your report for each carton.

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 Oct 26, 2014, at 8:53 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

After a short break i am back. In the meanwhile i did worked on a report "Collection Voucher" almost similar to the previous report we were working "Consignment Delivery Note". The same way i made a query to "Pre-Group" the Cartons, and all did worked correctly. (Thanks again to you).

(Message over 64 KB, truncated)