Sabtu, 19 Desember 2015

Re: [MS_AccessPros] Continuous form -- row colors

 

John -- That appears to work. Each transaction (purchase order) has a sequential number associated with its items. I should be able to use Conditional Formatting per your earlier suggestion to set backcolor of the rows.

The segment of SQL that provides this function is evidently "(SELECT COUNT(*) FROM qryTransIDDistinct WHERE qryTransIDDistinct.tID <= [tblItem].[iTransID]) AS tIDSeq". Would you be so kind as to give me a brief explanation of this snippet of SQL? I see the results, but the SQL statement is foggy (to say the least).

Thank you so much for seeing me through this.

Steve

On 12/19/2015 1:36 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
 

Steve-


Not sure what it's picking up for the tID value.  Try this and tell me what you see in the iTransID field.

SELECT tblItem.iTransID, qryTransVendor.tDate, tbItem.iTransID, tbItem.iLineNr, qryTransVendor.vName, tbItem.iDescription, tbItem.iQty, tbItem.iCost, Format([iqty]*[icost],"Currency") AS LineTotal, tbItem.iDateReceived, tbPurchFor1.pf1Description, qryTransVendor.tID, (SELECT COUNT(*) FROM qryTransIDDistinct WHERE qryTransIDDistinct.tID <= [tblItem].[iTransID]) AS tIDSeq FROM qryTransVendor RIGHT JOIN (tbPurchFor1 RIGHT JOIN tbItem ON tbPurchFor1.pf1ID = tbItem.iPurchFor1id) ON qryTransVendor.tID = tbItem.iTransID ORDER BY qryTransVendor.tDate, tbItem.iTransID, tbItem.iLineNr;

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 
(Paris, France)



On Dec 19, 2015, at 7:09 PM, Steve thaw5@suddenlink.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thanks John. The query runs now, but the tIDSeq field is 1010 in all of the rows. That field should be the sequence number you mentioned in an earlier note. And 1010 is the number of transactions (purchase orders) in the database. Where to go from here?

Steve

On 12/19/2015 1:00 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
 

Oh, you left out the field name:


SELECT qryTransVendor.tDate, tbItem.iTransID, tbItem.iLineNr, qryTransVendor.vName, tbItem.iDescription, tbItem.iQty, tbItem.iCost, Format([iqty]*[icost],"Currency") AS LineTotal, tbItem.iDateReceived, tbPurchFor1.pf1Description, qryTransVendor.tID, (SELECT COUNT(*) FROM qryTransIDDistinct WHERE qryTransIDDistinct.tID <= [tID]) AS tIDSeq FROM qryTransVendor RIGHT JOIN (tbPurchFor1 RIGHT JOIN tbItem ON tbPurchFor1.pf1ID = tbItem.iPurchFor1id) ON qryTransVendor.tID = tbItem.iTransID ORDER BY qryTransVendor.tDate, tbItem.iTransID, tbItem.iLineNr;

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 
(Paris, France)



On Dec 19, 2015, at 6:56 PM, Steve thaw5@suddenlink.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

It's asking that a parameter be entered for qryTransIDDistinct.

On 12/19/2015 12:14 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
 

Steve-


I don't see anything particularly wrong with that.  What is the exact parameter prompt?  You said it's prompting for qryPODistinct, but you clearly are using a different name.

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 
(Paris, France)



On Dec 19, 2015, at 5:21 PM, Steve thaw5@suddenlink.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

For purposes of this database, purchase orders are referred to as transactions. Hence, Trans means transaction, tID is the unique number for a transaction, iCost is the cost of an item associated with a transaction.

The first query is this: SELECT DISTINCT tID FROM tbTrans;

The form's record source is qryItemDisplay. Here's its SQL: SELECT qryTransVendor.tDate, tbItem.iTransID, tbItem.iLineNr, qryTransVendor.vName, tbItem.iDescription, tbItem.iQty, tbItem.iCost, Format([iqty]*[icost],"Currency") AS LineTotal, tbItem.iDateReceived, tbPurchFor1.pf1Description, qryTransVendor.tID, (SELECT COUNT(*) FROM qryTransIDDistinct WHERE qryTransIDDistinct <= [tID]) AS tIDSeq FROM qryTransVendor RIGHT JOIN (tbPurchFor1 RIGHT JOIN tbItem ON tbPurchFor1.pf1ID = tbItem.iPurchFor1id) ON qryTransVendor.tID = tbItem.iTransID ORDER BY qryTransVendor.tDate, tbItem.iTransID, tbItem.iLineNr;


On 12/19/2015 11:01 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
 

Steve-


What is the SQL of the form's Record Source?

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 
(Paris, France)



On Dec 19, 2015, at 4:50 PM, Steve5 thaw5@suddenlink.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Yes and it appears to work as expected.

On 12/19/2015 10:47 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
 

Steve-


Did you create and save qryPODistinct?

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 
(Paris, France)



On Dec 19, 2015, at 4:36 PM, Steve5 thaw5@suddenlink.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I added the new field to the form's underlying query. When I attempt to view the modified query in Datasheet, it wants a parameter entered for qryPODistinct. I'm assuming it should not be asking for a parameter input. Advice?

Steve

On 12/19/2015 5:14 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
 

Steve-


Create a query like this:

qryPODistinct:
SELECT DISTINCT PONumber FROM PurchaseOrders;

Then add a column to the Record Source of your form:

POSeq: (SELECT COUNT(*) FROM qryPODistinct WHERE qryPODistinct <= [PONumber])

That should add a sequence number to each of the PO Numbers.  Use POSeq in Conditional Formatting to display white when the POSeq is odd, and Gray when it is even.

Expression: [POSeq] Mod 2 = 0

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 
(Paris, France)



On Dec 18, 2015, at 10:55 PM, Steve5 thaw5@suddenlink.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

No. The continuous form displays rows of the items associated with each of the many purchase orders. As I look at the screen, I'd like to see a band of gray rows for PO #1. And then a band of white rows for PO #2. And then a band of gray rows for PO #4 (PO #3 is missing). And so on, alternating bands of white and gray delineating the items associated with each PO. No clicking involved, just scrolling.

The objective is for the user to be able to scroll the screen and see the items associated with a given PO visually differentiated from the PO before and the PO after.

Thank you, Steve

On 12/18/2015 3:44 PM, wrmosca@comcast.net [MS_Access_Professionals] wrote:
 

Steve


Let me give an example and you tell me if I understand you right.

You click on a purchase order number.
All associated rows become gray
You click on another PO
The previous rows return to normal and the newly selected PO has its associated rows turn gray.

Is the above right?

-Bill


---In MS_Access_Professionals@yahoogroups.com, <thaw5@suddenlink.net> wrote :

Hi Bill,

My goal is to gray the rows associated with every other purchase order. While the recordset is sorted by purchase order ascending, there are randomly missing purchase order numbers. So, to answer your question, a change of purchase order number should gray the rows associated with that purchase order. And the next change of purchase order number should stop graying the rows associated with it.

Steve

On 12/18/2015 11:52 AM, wrmosca@comcast.net [MS_Access_Professionals] wrote:
 

Steve - What determines which row should be gray? Conditional Formatting would be the way to go for this as long as you have something that earmarks a record as needing to be gray.


Regards,
Bill Mosca, Founder - MS_Access_Professionals
Microsoft Office Access MVP
My nothing-to-do-with-Access blog

 


---In MS_Access_Professionals@yahoogroups.com, <thaw5@suddenlink.net> wrote :

I have a continuous form consisting of these fields: purchase order
number, item description, item quantity, item cost, date and other
fields. The underlying query is sorted by purchase order number
ascending. Each purchase order has a random number of items associated
with it. Some purchase order numbers are missing.

I'd like to set the background color to a light grey for every other
purchase order for all of the fields in the rows associated with that
purchase order. I have looped through a recordset and successfully
picked out the rows for which I want a light grey backcolor. I set the
FieldName.Backcolor property to the light grey color but I always end up
changing the backcolor of all of the rows. How do I restrict the
backcolor change to just the selected rows?

Steve














__._,_.___

Posted by: Steve <thaw5@suddenlink.net>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (22)

.

__,_._,___

Tidak ada komentar:

Posting Komentar