Using ' MoveLayout ' conditionally??? 1

[blakey2]

Hello All,

I have a table. I query the table using VBA embedded SQL. i store the resultant query in a query.

If I run my query, my results follow the form:

Item: Date:
Black Shoes 10/07/2004
Black Shoes 10/21/2004
Black Shoes 10/28/2004
Red Shoes 10/07/2004
Brown Shoes 10/21/2004

Now, I want to write a report which puts an X if there is an entry for that date.

ie.
________________________________________________________
Product Report: Shoes

Item: 10/07/2004 10/14/2004 10/21/2004 10/28/2004 11/05/2004
Black Shoes X X X
Brown Shoes X
Red Shoes X
---------------------------------------------------------

I thought I was close to having the report complete as it all works, except each Item creates a new row as it exists in the db. ie. Currently my report looks like:
________________________________________________________
Product Report: Shoes

Item: 10/07/2004 10/14/2004 10/21/2004 10/28/2004 11/05/2004
Black Shoes X
Black Shoes X
Black Shoes X
Brown Shoes X
Red Shoes X
---------------------------------------------------------

I have tried to use the 'MoveLayout = False' action/method/thing which works great if there is only one type of item in the db. But overwrites everything if there are multiple items.

So I tried to write some code to check for the condition that if the item was the same, then MoveLayout - False otherwise MoveLayout = True.

Sounds simple enough, but I can't get the condition to evaluate correctly:

Here is my code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim currValue
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryShoes")
    
    'rs.MoveFirst
    
    ' Intialise the current Value
    currValue = rs![cs.storecode]
    
    Do While Not rs.EOF And rs.BOF
        
        If rs![cs.storecode] = currValue Then
            Me.MoveLayout = False
        Else
            Me.MoveLayout = True
        End If
    
        rs.MoveNext
        currValue = rs![cs.storecode]
    Loop
    
End Sub

Essentially I opened the query as a recordset, as a recordset allows you to compare specific field values.

But the code does'nt work, it seems to be called for every record that is displayed, and does not enter the loop?

If anyone can help/offer alternate suggestions on how to do this, it is greatly appreciated.

Thanks - blakey2.

 

[hymn]

have you tried
Select distinct or distinct row

Hope this helps
Hymn

 

[dhookom]

This looks like a crosstab query report. Check faq703-5466 for a solution that groups columns by month. You should be able to modify this for weeks.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[blakey2]

Hi Duane n Hymn,

My columns are already set up as described in the crosstab FAQ. To display the 'X' I have a text field, which reads the date from the column header and using an 'iff' statement. When the record exists (iff = True) it places an 'X' in the text field.

Problem is that because I have multiple records of the same item in the source query (also db) under different dates, every record generates an extra line, with an 'X' or if date range is not valid, then an empty row.

Essentially I want to group all the results for a single item type, on the same line.

I read other posts, and looked at some source code provided by duane (from corporate technologies(??)) where the 'MoveLayout' method was used to overlay data.
This is what I tried to implement with my do loop - essentially to select distinct items from the db and set the MoveLayout = True/False.

I'll stress again that all works, except prints out a record of every item in the source query. I was thinking that i could build and event (write code) for the 'Detail' section of the report, which would compare the current and next items and set the MoveLayout condition accordingly.

If anyone can point out to me why the if condition of the loop never evaluates 'True', or perhaps a better method of achieving my desired outcome?

Thanks - blakey2

 

[dhookom]

I wrote the FAQ and the Corporate Technologies samples.

Please provide the SQL view of your query.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[blakey2]

Hey Duane,

I realised you wrote the Corp. Tech. sample, as I was directed to it from another thread, in which you provided the link.

The SQL view of my query ( used as report's 'Record Source') is:

SELECT CS.*, P.*
FROM tblStoresMasterList AS CS LEFT JOIN tblGarbageBagsArchive AS P ON CS.StoreCode=P.StoreCode
WHERE IIf(CS.GBGrade='A' And (P.Grade='C' Or P.Grade='B' Or P.Grade='A'),True,IIf(CS.GBGrade='B' And (P.Grade='B' Or P.Grade='C'),True,IIf(CS.GBGrade='C' And P.Grade='C',True,IIf(IsNull(CS.GBGrade),True,IIf(Not (P.Grade='C' Or P.Grade='B' Or P.Grade='A'),True,False))))) And P.PeriodEnding Between #31/10/2004# And #07/11/2005# And P.Item='LION BRAND BAGS GARBAGE BULK TWINGAR 50PACK'
ORDER BY CS.State, CS.StoreCode;

I realise this looks pretty messy, and contains nested 'iif' statements embedded into the code which are used to filter the records further- ( A result of a prev. post)

Can take it out yielding:

SELECT CS.*, P.*
FROM tblStoresMasterList AS CS LEFT JOIN tblGarbageBagsArchive AS P ON CS.StoreCode=P.StoreCode
WHERE P.PeriodEnding Between #31/10/2004# And #14/11/2004# And P.Item='LION BRAND BAGS GARBAGE BULK TWINGAR 50PACK'
ORDER BY CS.State, CS.StoreCode;

Which makes it a little more readable.

Quick explanation:
[]My db is not about shoes (used in prev example for simplification).
[] tblGarbageBagsArchive contains many different types of garbage bags, added into the archive on different dates.
[] I select a specific product and daterange and query the db. -- This returns a list of stores not selling the specific garbage bag.

Now my report looks like:
-----------------------------------------
Stores not selling: LION BRAND BAGS GARBAGE BULK TWINGAR 50PACK

StoreCode StoreName 31/10/2004 07/11/2004 14/11/2004
415 Mooralbark X
415 Mooralbark X
415 Mooralbark X
560 Westfield X
560 Westfield X
720 Newhaven X
etc.
--------------------------------------------

This is almost correct: but I want all of the same stores to have their data on the one line.

I thought I could check for when StoreCode (CS.StoreCode) was the same for two records running and if so MoveLayout = False (so it overwrites the same space) and if not then it must mean a new storecode/storename so it would need to reset MoveLayout = True so it could create a new record.

I could not get this to work, as the code in the detail section runs for each record, hence i always overwrote the old values when I initialised them (If that makes any sense). -- Thought about using a global variable, but unsure if they exist in VBA??

Thanks - blakey2.

 

[blakey2]

Thread about Global Variables and Forms can be found here:

Thread703-951134

-- Bout to play with this myself.

-blakey2

 

[dhookom]

I don't see anything in your SQL that looks like it would return dates as headings?

Regardless, could you group by Store and hide the detail section? You could then use expressions like:
=IIF(Count([yourdate])=0,"","X")

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[blakey2]

Hi Duane,

I think there is a slight misunderstanding, probably as I have structured my code badly(?).

I have a form, which has various drop down boxes etc to select products, date ranges etc. The on_click event calls a VBA module which generates the (stored) query (as above). The control returns to the on_click event of the form which opens the report.

My report uses the stored query as its 'Record Source'.

It looks a little like so (design view)

__________________________________________________________
Page Header
----------------------------------------------------------
|startDate| |dateAdd(...)|
Store Code Store Name (txtDate1) (txtDate2)
___________________________________________________________
Detail Section
-----------------------------------------------------------
____________ ____________ ______ ______
|CS.StoreCode| |CS.StoreCode| | val1 | | val2 |
------------ ------------ ----- -------


WHERE
Val1 =IIf([PeriodEnding]=
Reports!rptGBStoresByProduct!txtDate1,"X","")
Val2 = IIf([PeriodEnding]=
Reports!rptGBStoresByProduct!txtDate2,"X","")

_________________________________________________________

I did however solve my problem. Referring to the Global Variables thread (link in my previous post - above), I set a global to store the value of cs.storecode. As the report was populated (filled??) with records, the code in the 'detail' section of the report is executed once for each record. All I did was to check the new value against the old value stored in the global variable, set the moveLayout properties accordingly and then update the global value.

Code for the 'globalsModule' :

Option Compare Database

Option Explicit

Global currentValueGlobal As Integer
Global oldValueGlobal As Integer


Public Function getCurrentValue() As Integer
    
    getCurrentValue = currentValueGlobal
    
End Function

Public Function setCurrentValue(newValue As Integer)

    currentValueGlobal = newValue

End Function

Code of the 'Detail' section of the report.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim currValue As Integer
    
    
    Set db = CurrentDb
    
    ' Make a copy of the value in the global to aid comparison
    currValue = globalsModule.getCurrentValue
     
    If currValue = Reports!rptGBStoresByProduct![cs.storecode] Then
        MoveLayout = False
    Else
        MoveLayout = True
    End If
    
    
    'Now set the 'new' current value
    globalsModule.setCurrentValue (Reports!rptGBStoresByProduct![cs.storecode])
    
End Sub

Note for this to work the hide duplicates option must be selected.

Cheers, blakey2.

 

[blakey2]

Duane,
Pretty tired at the moment, hence overlooked your previous post as was fixing up 'dd/mm/yyyy' date probs. Re-read now. Looks like a much more elegant solution than mine and I don't see why it would not work. -- Will sleep now, fix up report in the morn'.

Purple star for you.

Cheers - blakey2.

 

[dhookom]

Actually, there are calendar reports at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4

that might fit your needs. These solutions position text boxes based on their values.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]