Report selection by customer (1 Viewer)

[hfsitumo2001]

Hello,

I have already report selection by date for certain item supplies and then I will select by certain custumer/supplier, can I add the selection in this column of query? this: [Forms]![frmReportselection_ReceiptbyItem]![Combo_User]

Thank you

 

[The_Doc_Man]

You can add that if there is a way to narrow down the specific customer.

I noted in the image that "Sales Order" has "Customer ID" in the mix. As long as you do the implied JOIN correctly, there is no reason for you to not make such an inclusion.

Having said that, you would also be best advised to assure that a formal relationship is established to allow customer ID to automatically feed back to this query if you intend to use wizards to build reports or queries to exploit this relationship.

 

[hfsitumo2001]

Having said that, you would also be best advised to assure that a formal relationship is established to allow customer ID to automatically feed back to this query if you intend to use wizards to build reports or queries to exploit this relationship.

Hi Sir...what is usually the problem if my combo does not extract the customer that I want to select like shown on the picture below.

 

[hfsitumo2001]

Hi Sir...what is usually the problem if my combo does not extract the customer that I want to select like shown on the picture below.

View attachment 104639

 

[Gasman]

Should not be a period between distinct and customers

 

[hfsitumo2001]

Should not be a period between distinct and customers

Thank you Gasman, I am now at home, I will test it in my work location. Upto now, I have not been successful to make 2 kinds of link path, 1 in the office and one at home. Do you have any idea?

Thank you

Frank

 

[hfsitumo2001]

Should not be a period between distinct and customers

Hi Gasman, I do not understand why my combo can not extract customer to be put in the selection form, because my query will take it to filter the report to be printed, but below is the error asking to fill in the customer.

Thank you for enlightening me

Frank

 

[Gasman]

Show your sql code within code tags.
Re paths, keep them the same is the easiest option.

 

[Pat Hartman]

I have not been successful to make 2 kinds of link path, 1 in the office and one at home. Do you have any idea?

That's a separate problem. Just use the linked tables manager. When you move the FE back to the office, do it again. You could automate it but I would not do that because it prevents you from ever linking to the production BE when the FE is not in the directory where the production FE normally resides and that can impede your ability to test.

 

[hfsitumo2001]

Show your sql code within code tags.
Re paths, keep them the same is the easiest option.

SELECT SalesOrder.CustomerID, Customers.Customer, SalesOrder.SlsControlNo, SalesOrder.OrderDate, SalesOrder.IssuedDate, SalesOrderDetails.ItemCode, SalesOrderDetails.Notes, Inventory.Description, SalesOrderDetails.UnitPrice, Inventory.UOM, SalesOrderDetails.Quantity, [SalesOrderDetails].[UnitPrice]*[Quantity] AS NetAmount
FROM Customers INNER JOIN (Inventory INNER JOIN (SalesOrder INNER JOIN SalesOrderDetails ON SalesOrder.[ID] = SalesOrderDetails.[SalsOrderID]) ON Inventory.[ItemCode] = SalesOrderDetails.[ItemCode]) ON Customers.CustomerID = SalesOrder.CustomerID
WHERE (((Customers.Customer)=[Forms]![frmReportselection_usagebyitem]![Combo32]) AND ((SalesOrder.IssuedDate) Between [Forms]![frmReportselection_usagebyitem]![txtstDate] And [forms]![frmReportselection_usagebyitem]![txtEndate]) AND ((SalesOrderDetails.ItemCode)=[Forms]![frmReportselection_usagebyitem]![Combo_User]));

That is my sql, but when I add to filter by Customer/user, the report shows nothing. Can you help me what is the problem?

Thank you,

Frank

 

[Gasman]

Build the sql criteria, one at a time.
Test the query output before going anywhere near a report.
Your combo is likely the customerid and not customer name. Check your actual values.

 

[hfsitumo2001]

Your combo is likely the customerid and not customer name. Check your actual values.

No Gasman, it is customers, that is why it shows already in the combo32

Frak

 

[Pat Hartman]

1. Do yourself a favor and give controls meaningful names. No one knows what combo32 means and after a while, even you won't remember.
2. Do you understand how combos work? What you see may not be the bound column and that is actually the most common scenario. Is Customer, the name of the customer or the ID? In combo32, are you selecting the name or the ID?

 

[hfsitumo2001]

1. Do yourself a favor and give controls meaningful names. No one knows what combo32 means and after a while, even you won't remember.

2. Do you understand how combos work? What you see may not be the bound column and that is actually the most common scenario. Is Customer, the name of the customer or the ID? In combo32, are you selecting the name or the ID?

The way I created the new combo is by copying the running one, such as item description selection, so I think it should work. And the format of the column is the same, and the coulumn count, and the bound column . column count is 2, because customer name is the 2nd column in the table.

Ok I will try tomorrow, because I am not at home and when I tried to open, I still can not open, I will try it at work tomorrow.

Thank you.

Frank

 

[hfsitumo2001]

1. Do yourself a favor and give controls meaningful names. No one knows what combo32 means and after a while, even you won't remember.
2. Do you understand how combos work? What you see may not be the bound column and that is actually the most common scenario. Is Customer, the name of the customer or the ID? In combo32, are you selecting the name or the ID?

Hi Pat,

I can change the link using link manager at office and at home. As you can see attached even I have changed the name of the combo, but the report still show nothing. Regarding the combo, already shows the name of the customer.
I do not know where *I am wrong.

Thanks for any idea.

Frank

 

[Pat Hartman]

The tables in the picture are poorly organized and the customer table isn't even showing. I can see that CustomerID from the sales order seems to relate to some field in the missing table. If the field in the Customer table which is the PK is improperly named Customer instead of CustomerID, then your criteria will work but your combo should almost certainly be referencing the CustomerID field, NOT the Customer field.

You would need to show the relationship diagram with all the necessary tables and fields clearly visible and expanded. It also looks like you are not enforcing RI on all your relationships and you should be. And finally, naming primary keys "ID" just makes the relationships harder to understand.

Standards are your friend. A rational standard is to use ID as the suffix for your autonumbers. So, I can look at a table definition and see that SalesOrderID is an autonumber but ItemCode is not and in fact is probably text rather than numeric.

When I look at the Customer table, I might see three fields.
CustomerID = the autonumber PK which is used throughout the application and is used in ALL relationships
CustomerCD = the possibly text, human display field. This may be a custom field that you generate or it might be a numeric value that comes from a different application.
Customer or CustomerName = the Name of the customer.

 

[plog]

A filter on your DoCmd.OpenReport is such a better way to handle this.

DoCmd.OpenReport method (Access)

Office VBA reference topic

learn.microsoft.com

You probably are already using DoCmd.OpenReport in the code on your form's button to open the report--you just need to use it to its full potential. It allows you to include a criteria string when you use it and filter the report to just the records your users want.

Step 1 is make a backup of your database. Step 2 remove all the user supplied criteria from your query. Step 3 is use DoCmd.OpenReport on your form to open the desired report. Once all that works you can now build your filter string from what the user provides you.

' Psuedo-code warning

str_Filter="(1=1)"

if IsNull(Me.[Combo32])=False Then str_Filter = str_Filter & " AND ([Customer]='" & Me.[Combo32] & ")")
  ' if user supplied Customer uses that in report filter

DoCmd.OpenReport "YourReportNameHere", acViewNormal, str_Filter

 

[hfsitumo2001]

The tables in the picture are poorly organized and the customer table isn't even showing. I can see that CustomerID from the sales order seems to relate to some field in the missing table. If the field in the Customer table which is the PK is improperly named Customer instead of CustomerID, then your criteria will work but your combo should almost certainly be referencing the CustomerID field, NOT the Customer field.

You would need to show the relationship diagram with all the necessary tables and fields clearly visible and expanded. It also looks like you are not enforcing RI on all your relationships and you should be. And finally, naming primary keys "ID" just makes the relationships harder to understand.

Standards are your friend. A rational standard is to use ID as the suffix for your autonumbers. So, I can look at a table definition and see that SalesOrderID is an autonumber but ItemCode is not and in fact is probably text rather than numeric.

When I look at the Customer table, I might see three fields.
CustomerID = the autonumber PK which is used throughout the application and is used in ALL relationships
CustomerCD = the possibly text, human display field. This may be a custom field that you generate or it might be a numeric value that comes from a different application.
Customer or CustomerName = the Name of the customer.

It works for other button to open the reports, so maybe I will try to make a different report and different query and different button

Frank

 

[Pat Hartman]

Except that we have NO idea what your combo properties are nor the tables involved. Showing the relevant tables and fields in the picture you posted would have helped. Instead you keep telling us that it works somewhere else. CLEARLY there is something different about the second situation. This is your problem not a problem with Access and we can't help without information about the objects involved.

 

[hfsitumo2001]

Except that we have NO idea what your combo properties are nor the tables involved. Showing the relevant tables and fields in the picture you posted would have helped. Instead you keep telling us that it works somewhere else. CLEARLY there is something different about the second situation. This is your problem not a problem with Access and we can't help without information about the objects involved.

Thank you Pat and all of you guys, your comments trigger my brain to search the reason why it did't work, and now it works for me. The clue is that we should filter it from the field of the the table on the many side in the one to many relationship. So the customer name should be taken from the SalesOrder.

Again thanks to all of you.

Frank