Emulate Excel VBA in Access

[JohnBoy]

How can I compare values in a column as I would in Excel... Stumped...



In the continuing saga of the DB I have inherited,
The Table contains customer details, the CustID to be exact, I need to flag the main product group for each customer, at present I do this by downloading the table into excel and then running code in the back that I have written to check the CustID No against some other values and if true then allocate a flag in a column at the end, sample as follows:-

Range (C”2”).Select
Do Until Activecell.value = “”
If Not Activecell = Activecell.offset (-1, 0) then
If Not Activecell = Activecell.offset (1, 0) then
If Not Activecell.offset(0, 6) Like “R*” then
Activecell.offset((0, 7) = “Y”
End If
End If
End If
Activecell.Offset(1, 0) select
Loop

There is a bit more to it than this but this is just to give an idea, what I am doing is selecting the cell, checking to make sure it is not the same as the one above or below it, then if this true then checking to make sure the value in column 6 does not begin with an R code, if all is true I allocate a Y flag to Column 7 ,

I am really stuck trying to re-create this in Access.. can anyone help/point me in the right direction

Cheers in advance John Boy

[RuralGuy]

*Above* and *below* are on separate records in Access and not particularly easy to get to. You could be looking at a normalization issue where *spreadsheet* was committed on an Access table. Maybe if you described what was in those fields and possible solution can be suggested.

[JohnBoy]

Unfortunately the DB was dropped on me and contains to many records to start trying to pull apart and rebuild, however yes sorry for not including the field contents etc, basically what I have in 1 Column are CustID Numbers, this number can repeat numerous times within the column, in another Column I have a Product Group Ref Number

So CustID could be A1234
Product Group Ref could be B456 etc etc,

What I do in excel is the whole checking thing for each customer ID to pick only the customer ID that has a Main product Ref Number in that column, (I don’t want to flag sub product categories like R*) the code in the post is the first lines of a lot more that evaluate various conditions and Allocate or not dependant on the result..

Hope this is a bit more clear...

Regards

JB

[RuralGuy]

Based on that last description, I don't see where you need to be concerned about the *previous* or *next* record. Just get all of the records whos [Product Group Ref] field does *not* begin with an "R", right? Am I missing something here?

[JohnBoy]

Hi Thanks for your help so far and I am with you on your last reply,

Its my fault as I havent been as clear as I should, the process in excel is thus: I load the spreadsheet with the table data, I then have code which sorts it by CustID, PurchNo, and ProdRef as below,


CustID;....PurchNo;..... ProdRef;..... Flag;
A123,...........1,............R123B2,
A123,.......... 2,............B345X1,
A123,...........3,............A123,............Y,
A123,...........4,............B123,
A123,...........5,............C123,

I then have it look down the CustID row and on this instance I would skip the first 2 rows as they are sub codes, when it hits the first Main Code row for that Cust I have excel allocate a Y flag as shown, it then ignores the rest of the entries and looks for the next CustID and repeats,

I then reload into access and run queries which use the Y flag to extract those Main Product Group records only etc...

Not even sure it can be done ???? sorry for the confusion

Cheers

JB

[RuralGuy]

I don't think your example displayed as you wanted, try editing it and separate the *columns* (or fields) with commas instead.

[JohnBoy]

That should be a bit more clear now thanks...

[RuralGuy]

Why did you skip the 2nd row? The ProdRef does not start with an "R".

[JohnBoy]

Not all sub groups start with an R code, I just used this as one instance, but the sub groups do have extra numbers and letters in them to id them...

[RuralGuy]

I'm pretty sure you can do what you need by walking the Recordset but would need the complete SubGroup description to assist further other than creating a generic "Walk the Recordset" procedure.

[JohnBoy]

Many thanks for your patients so far, the sub group is a fair size hence my use of initially just the R codes to keep it simple, if I can get my head around how to do it with one sub code I should be able to work through the rest and sort it so a Generic approach would be greatly received to get me started..

Thanks again ................ JB

[RuralGuy]

This is AIRCODE and untested but the procedure will look something like:

Private Sub TagRecord()
   On Error GoTo Err_TagRecord
   '-- This procedure expects the records to be ordered by [CustID], [PurchNo], [ProdRef] in the query
   '-- It further expect there will *always* be a record that satisfies the selection
   '-- This uses DAO and requires a reference to that Object Library
   Dim MyRs As DAO.Recordset
   Dim MyDb As DAO.Database
   Dim CurID As String
   Dim Skipping As Boolean       '-- True if moving down the recordset to the next CustID
   Skipping = False              '-- Initial value
   Set MyDb = CurrentDb()
   Set MyRs = MyDb.OpenRecordset("YourQueryName", dbOpenDynaset)
   With MyRs
      CurID = !CustID          '-- Initial CustID
      Do While Not .EOF
         If Not Skipping Then
            If Not SubGroup(!ProdRef) Then
               .Edit
               !Flag = True
               .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 Sub
Err_TagRecord:
   MsgBox "Error No:    " & Err.Number & vbCr & _
          "Description: " & Err.Description
   Resume Exit_TagRecord
End Sub
Function SubGroup(InValue As String) As Boolean
'-- Do whatever you need to do to determine if the [ProdRef]
'-- being passes is a Sub or not and return SubGroup = True
'-- if this record is a SubGroup
   If Left(InValue, 1) = "R" Then
      SubGroup = True
   Else
      SubGroup = False
   End If
End Function

[JohnBoy]

Many thanks for this, I see what your doing and will let you know how I get on...

Once again Many thanks JB

[JohnBoy]

Hi RG
I made 1 or 2 minor adjustments to cover the sub sets but it works an absolute treat and does the same job as the spreadsheet code ....

Once again thanx very much for all of your help... JB

[RuralGuy]

You're very welcome JB. Glad it worked out for you.