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

John,

OK, i will follow this method.

But what about getting CartonTotal on the report's ConsignmentNo Footer, which is not coming correct?

Khalid

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

Khalid-

For cases 1 and 3, use a letter suffix. For case 2, use a number. When a carton that is in case 1 or 3 has multiple products in one carton, use a letter AND a number. The default value for suffix should be blank or 0. If you use 0, start with 1 to indicate multiple products in a 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 31, 2014, at 10:16 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Yes! of course a good catch from Robert (Thanks Robert). It worked smoothly and now i can see ClientName on cmbClientCIN along with its relative ClientCIN.

Private Sub cmbConsignmentNo_AfterUpdate()
Me.cmbClientCIN.RowSource = "SELECT DISTINCT Clients.ClientCIN, Clients.ClientName " & _
"FROM CollectionVoucher INNER JOIN Clients " & _
"ON Clients.ClientCIN = CollectionVoucher.ClientCIN " & _
"WHERE CollectionVoucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _
"' ORDER BY Clients.ClientCIN"
End Sub

Regarding your question for the Total Cartons:
You wrote:
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.

Yes i can see one row per logical carton.

On Reports ConsignmentNo Footer Text Box TotalCartons Control Source is:
=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ClientCIN = " & [ClientCIN] & " AND CartonNo = " & [CartonNo])

qryGroupCartonsFrieghtInvoice:
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 i'm not getting correct TotalCartons

One thing which is still confusing me is how to enter CartonSuffix in Form CollectionVoucher.
Let me brief you the scenario of duplicate CartonNo

1- Same ConsignmentNo, same ClientCIN but having different DeliveryVr. Some CartonNo may be duplicate some may not.

2- Same ConsignmentNo, same ClientCIN but having multiple Products in certain Cartons.

3- Some Cartons are shifted from another Consignment some CartonNo may be duplicate, some may not be duplicate.

Khalid

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

Good catch, Robert!

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:23 PM, 'Robert Peterson' bob@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Should the last ClientCIN in the order by clause also have which table it is looking at.
Says ORDER BY ClientCIN
Should it be ORDER BY Clients.ClientCIN"

Bob Peterson
Alternate Finishing, Inc.
P: 978-567-9205 ext. 22
F: 978-567-8742
M: 978-333-0060
Our full range of services can be found at http://www.alternatefinishing.com/services.html .

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, October 30, 2014 5:58 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] How to enter different product in the same carton

Khalid-

Sorry about that - forgot that ClientCIN is in both tables. Do this:

Private Sub cmbConsignmentNo_AfterUpdate()
Me.cmbClientCIN.RowSource = "SELECT DISTINCT Clients.ClientCIN, Clients.ClientName " & _
"FROM CollectionVoucher INNER JOIN Clients " & _
"ON Clients.ClientCIN = Col lectionVoucher.ClientCIN " & _
"WHERE CollectionVoucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _
"' ORDER BY ClientCIN"
End Sub

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 10:30 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I fixed the code as you gave:
Private Sub cmbConsignmentNo_AfterUpdate()
Me.cmbClientCIN.RowSource = "SELECT DISTINCT ClientCIN, ClientName " & _
"FROM CollectionVoucher INNER JOIN Clients " & _
"ON Clients.ClientCIN = CollectionVoucher.ClientCIN " & _
"WHERE CollectionV oucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _
"' ORDER BY ClientCIN"
End Sub

I can't see WHERE clause in the original Row Source of cmbConsignmentNo, which as below:

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

Is it correct?

Now when i open the report, on the Dialog after selecting any ConsignmentNo when i click on the cmbClientCIN the following error displays:

The specified fiedl 'ClientCIN' could refer to more then one table listed in the FROM clause of your SQL statement

Therefore cannot open the report now.

We will discuss second part of your reply after fixing above issue.

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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, "") & _
&nbs p; "' 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@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogro ups.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_AfterUpd ate()
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 Insid e 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;