Flag A Data Block

[JohnBoy]

A while back RuralGuy very kindly helped me out with a coding problem regarding setting flags, since then I have manged to sort other small issues out using his methods but have come up against one I need a bit of help with.

I have a data table that contains the following:
CustID...............GrpNo:...........Date........ ........Flag
A123456...............A123............01/04/2010.......Y....
A123456...............A123............01/04/2010.............

A456789...............R123............01/04/2010.............
A456789...............A123............01/04/2010........Y....
A456789...............A123............01/04/2010..............



What happens is the code runs a query that goes through the CUSTID and sets a flag for the first code it finds in the GrpNo: field for that customer that is NOT an R code, this all works fine, however I have come across some customers whose entries are all R Codes i.e

CustID...............GrpNo:...........Date........ ........Flag
A123456...............A123............01/04/2010.......Y....
A123456...............A123............01/04/2010.............

A456789...............R123............01/04/2010.............
A456789...............R123............01/04/2010.............
A456789...............R123............01/04/2010..............
A456789...............R123............01/04/2010.............
A456789...............R123............01/04/2010.............
A456789...............R123............01/04/2010..............

A654852...............C765............02/04/2010........Y....
A654852...............C765............02/04/2010..............

What I need to do is flag the first R code in a customers group of entries where they are all R codes,

Can anyone help

JB

[llkhoutx]

Create a query to count the GrpNo records for each customer, where GrpNo is > 1.

Join that query with your table/query of all records.

Cycle through that query's records, flagging each record when CustID and GrpNo changes.

[JohnBoy]

Hi thanks for your reply, I had thought about using a query to pull just the blocks that were all R codes and then just updating the first record for each block, however I am sort of stuck because any customer could have a R code within their block, (sorry the above data did not show this) so it's how do I differentiate between a record that is an R code within an already flagged customer block i.e

CustID...............GrpNo:...........Date........ ........Flag
A123456...............A123............01/04/2010.......Y....
A123456...............R123............01/04/2010.............
A123456...............A123............01/04/2010............


A123567...............R123............01/04/2010.............
A123567...............B123............01/04/2010........Y...
A123567...............A123............01/04/2010............

Which is fine

And a block that is made up of purely R Codes like so..

A456789...............R123............01/04/2010.............
A456789...............R123............01/04/2010.............
A456789...............R123............01/04/2010..............
A456789...............R123............01/04/2010.............
A456789...............R123............01/04/2010.............
A456789...............R123............01/04/2010..............

I’m sort of loosing the will to live with this one and don’t know if it can even be done

What are your thoughts...??

[llkhoutx]

If a customer already has a R code, do you skip that customer or do you flag another record? It doesn't hurt to flag an record twice.

Do you know how to create a dynaset and then cycle through that dynaset's records? That's a good way to flag your records.

[JohnBoy]

Hi, Yes what happens is a block of code runs a query that stores the customer number, it starts off as Skipping = False and checks the first record grpNo to see if it's an R code, if not then it Flags this record and skips to the next one, if the custid in memory is the same as the one in record it skips this until it comes to one that is not and starts the whole process over again,

Conversely if the first record is an R code it again moves to the next one and tries the test again, if the stored record is the same as the one it’s checking then if it’s not a R code it flags it and so on and so forth...

I also run another sub routine that checks to see if the record is a single entry and if the GrpNo is an R code it Flags it to...

Any records that are single entries and not R codes are captured in the first run..

These are all done using a dynaset created via the query..

I only want to flag one record in each customer set of records as the flag is used for reporting etc..

Do you think this is even possible... i.e. the check a block and if they are all R codes flag the first entry..??

[llkhoutx]

Yes, it can be done, cycle through the dynaset, record by record, backing up a record and flagging it, as record.

[JohnBoy]

Err sorry you have lost me now I am no great shakes at VBA, this is the Code I was helped with and what sets the initial blocks, another routine based on this sets the single R codes...

Option Compare Database

Public Function FlagRCodes()
On Error GoTo Err_TagRecord
Dim MyRs As DAO.Recordset
Dim MyDb As DAO.Database
Dim CurID As String
Dim GrpNo As String
Dim Skipping As Boolean
Skipping = False

Set MyDb = CurrentDb()
Set MyRs = MyDb.OpenRecordset("Qryud_Set_R_Codes", dbOpenDynaset)

With MyRs
CurID = !CustID
Do While Not .EOF
If Not Skipping Then
If Not !GrpNo Like "R*" Then
.Edit
!Flag = "Y"
.Update
Skipping = True
.MoveNext
Else
If !CustID <> CurID Then
CurID = !CustID
Skipping = False
Else
.MoveNext
End If
End If
Else
If !CustID <> CurID Then
CurID = !CustID
Skipping = False
Else
.MoveNext
End If

End If
Loop
End With
Exit_TagRecord:
On Error GoTo 0
MyRs.Close
Set MyRs = Nothing
Set MyDb = Nothing
Exit Function
Err_TagRecord:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.DESCRIPTION
Resume Exit_TagRecord

'MsgBox ("Flags Set")
End Function

Any help would be greatfully as I really am a novice with this stuff...

Regards

JB

[llkhoutx]

A cursary examination of you code looks good. Try single stepping though it (with the debugger) to verify that it's producing the correct result.