While Loop?

[gabrielbatistuta]

Hi there,

I have a column called Product in my report. And I would like to add a data that will tell you the number of product changeovers. Could someone please provide me with some sample code.

It is probably a while loop that will go through all instances of product and will increase the counter when the two products being compared are different.

Thanks.

 

[TheAceMan1]

How are ya gabrielbatistuta . . .

Perhaps this:

[blue]Function ChgOvrs() As Long
   Dim rst As DAO.Recordset, OldTxt As String, cnt As Long
   
   Set rst = Me.RecordsetClone
   
   Do Until rst.EOF
      If OldTxt <> rst("[purple][b][i]FieldName[/i][/b][/purple]") Then
         cnt = cnt + 1
         OldTxt = rst("[purple][b][i]FieldName[/i][/b][/purple]")
      End If
         
      rst.MoveNext
   Loop
   
   ChgOvrs = cnt
   
End Function[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[TheAceMan1]

Woops! . . .

Corrected code:

[blue]Function ChgOvrs() As Long
   Dim rst As DAO.Recordset, OldTxt As String, cnt As Long
   
   Set rst = Me.RecordsetClone
   
   Do Until rst.EOF
      If OldTxt <> rst("[purple][b][i]FieldName[/i][/b][/purple]") Then cnt = cnt + 1
      OldTxt = rst("[purple][b][i]FieldName[/i][/b][/purple]")
      rst.MoveNext
   Loop
   
   ChgOvrs = cnt
   
End Function[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[gabrielbatistuta]

Thank you Aceman.

So you would paste this code in the report vba and set a textbox called "product changovers" to = ChgOvrs()

 

[gabrielbatistuta]

Can you please tell how you would paste the code?

Thanks.

 

[TheAceMan1]

[blue]So you would paste this code in the report vba and set a textbox called "product changovers" to = ChgOvrs()[/blue]

[blue]Exactly![/blue] However testing of the code revealed a few more changes. Here's the final code (The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK):

[blue]Function ChgOvrs() As Long
   Dim db As DAO.Database, rst As DAO.Recordset, OldTxt As String, cnt As Long
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset(Me.RecordSource, dbReadOnly)
   
   Do Until rst.EOF
      If OldTxt <> rst("[purple][b][i]FieldName[/i][/b][/purple]") Then cnt = cnt + 1
      OldTxt = Nz(rst("[purple][b][i]FieldName[/i][/b][/purple]"), "")
      rst.MoveNext
   Loop
   
   ChgOvrs = cnt
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[gabrielbatistuta]

Are you sure this is correct? Because in the report for Product ChangeOver = #ERROR

field name = Product

 

[maxhugen]

I'm not 100% sure of what you're doing, but it sounds like all you need is a count of UNIQUE products?

If that's correct, you could try a variation of TheAceMan1's suggestion, using a DISTINCT query.

If you haven't already, save the report's query, let's call it qryMyReport for this example. Presumably, the query is something like 'select Product, field2 ... fieldn from myTable'.

Function ChgOvrs() As Long
   Dim db As DAO.Database, rst As DAO.Recordset, cnt&, sSQL$
   
   Set db = CurrentDb
   sSQL = "SELECT [b]DISTINCT[/b] Product from qryMyReport"

   Set rst = db.OpenRecordset(Me.RecordSource, dbReadOnly)
      If not rst.EOF then rst.MoveLast: cnt=rst.RecordCount
   Set rst = Nothing
   Set db = Nothing
  
   ChgOvrs = cnt

End Function

HTH

Max Hugen

http://www.hugen.com.au

Australia

 

[TheAceMan1]

gabrielbatistuta . . .

Did you change [purple]FieldName[/purple] to the name of the control you want to track?

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[TheAceMan1]

How are ya maxhugen . . .

[blue]I would like to add a data that will tell you the number of [purple]product changeovers[/purple].[/blue]

The [blue]Distinct[/blue] predicate will cover this as long as group changes occur only once. My read is they wont (groups occuring more that once), hence the recordset in my code.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[gabrielbatistuta]

Yes I did. As I mentioned earlier, I am looking for changeover and NOT unique products. How many times does a product change from one to another?

 

[maxhugen]

Oh, right. In that case I'd look at using the report detail section's OnFormat event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static cnt&
Static LastProduct$

    if Product <> LastProduct then cnt=cnt+1
    LastProduct=Product
    ProductChangovers=cnt

End Sub

The Static declaration will preserve values between calls to the sub.

Cheers

Max Hugen

http://www.hugen.com.au

Australia

 

[maxhugen]

Oops, need to check the FormatCount though, as the report may need to loop through formatting more than once...

    if [b]FormatCount=1[/b] and Product <> LastProduct then cnt=cnt+1

Max Hugen

http://www.hugen.com.au

Australia