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

 

Khalid-

After I sent my last response, I thought that might be the case.  Add a filter on ClientCIN like this:

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

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 2:53 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

The code you gave me it counts total cartons of a particular Consignment, but i need TotalCartons for specific Consignment and specific selected ClientCIN. 

I assume that ClientCIN should be in this code, i've tried myself but getting error.

ConsignmentNo is Text

ClientCIN is Numeric

Khalid

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

Khalid-

Ah!  The clue is you have now told met that this in the ConsignmentNo footer.  Change the Control Source to this:

=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ConsignmentNo = " & [ConsignmentNo])

I don't remember if ConsignmentNo is text or not.  If it is text, you'll have to add quotes like this:

=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ConsignmentNo = '" & [ConsignmentNo] & "'")

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 1:13 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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:

 

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

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___