Need help in running this vba code

[shopaholic]

Hi,
I'm very very new to access vba.
Here's what I did(I'll explain with an example) -
(NOTE: If status is inactive then column 32, 33 should be "No" and red. If status is "Active" then only next strings should be searched in column 13)

Example -

columns are - Status(4th column), Group(13th column), Part of Scorpio group(32nd column), Part of Virtual group(33rd column)

If Inactive -
Then column32 and 33 should be "No" and red.

If active -
Then in "Group" column search for
1) Scorpio or
2) Virtual or
3) Both

4th column is status column. So, I wanted t search "Active" in this column. Say, "Active" is found in cell(6,4). Now, say, 13th column is "Group" column. So, I want to search "Scorpio" in this column. But condition is, it should search in only that row which had "Active" string.

So, the point is user should be active. Otherwise column 32 and 33 should be red.

Now, say, "Scorpio" is found in cell(5,13). So, "Part of Scorpio Group" and "Part of Virtual group" should be marked as "No" and color changed to red because the member is inactive. If, the member is "Active" and "scorpio" is found in cell(6,13) then "Part of Scorpio Group" should be marked as "Yes" color changed to green and "Part of Virtual group" should be marked as "No" and color changed to red.

Say if active, in 13th column(Group) a member is part of virtual group then "Part of Scorpio Group" should be marked as "No" color changed to red and "Part of Virtual group" should be marked as "Yes" and color changed to green. If inactive then both should be "No" and red.

Also, if active, a member is part of scorpio and virtual both groups (string will be "Both") "Part of Scorpio Group" should be marked as "Yes" color changed to green and "Part of Virtual group" should be marked as "Yes" and color changed to green. If inactive then both should be "No" and red.

I wrote a vba code in excel and it was working as I intended.

Now, I HAVE to move the database into access(imported the excel sheet successfully) and write the same vba code for access.

I can post the excel vba code if anybody wants it. And, with the help of google i tried to convert it into access vba code. Here's what I ended up with. And, it doesnt work.

Option Compare Database
Option Explicit

Sub MyFirstMacro()


   ' Dim curDatabase As Object
    'Dim table_name As Object


   ' Set curDatabase = CurrentDb
    
  '  Set table_name = curDatabase.TableDefs("Members")
    

Dim db As Database
Dim rst As DAO.Recordset
Dim table_name As String

Set db = CurrentDb()




Dim FieldName1 As String, FieldName2 As String, FieldName3 As String, FieldName4 As String
    FieldName1 = "Status"
    FieldName2 = "Group"
    FieldName3 = "Part of Scorpio group"
    FieldName4 = "Part of Virtual group"

table_name = "SELECT Members FROM Members"
Set rst = db.OpenRecordset(table_name)


'Set table_name = "Members"

 
    Do Until rst.EOF
   

   
    If rst!Status = "Active" Then
       
        If rst!Group = "Scorpio" Then
        'MsgBox "Found"
        
        rst.Edit
        rst!FieldName3.Value = "Yes"
        rst!FieldName3.Interior.Color = RGB(50, 205, 50)
        rst!FieldName4.Value = "No"
        rst!FieldName4.Interior.Color = RGB(255, 0, 0)
        
        'table_name.Fields(FieldName3).Value = "Yes"
        'table_name(FieldName3).Interior.Color = RGB(50, 205, 50)
        'table_name(FieldName4).Value = "No"
        'table_name(FieldName4).Interior.Color = RGB(255, 0, 0)
        
        ElseIf rst!Group = "Virtual" Then
        
            rst.Edit
            rst!FieldName3.Value = "Yes"
            rst!FieldName3.Interior.Color = RGB(50, 205, 50)
            rst!FieldName4.Value = "No"
            rst!FieldName4.Interior.Color = RGB(255, 0, 0)
        
            'table_name.Fields(FieldName3).Value = "No"
            'table_name.Fields(FieldName3).Interior.Color = RGB(0, 0, 255)
            'table_name.Fields(FieldName4).Value = "Yes"
            'table_name.Fields(FieldName4).Interior.Color = RGB(50, 205, 50)
          
        
        ElseIf rst!Group = "Both" Then
        
        
            rst.Edit
            rst!FieldName3.Value = "Yes"
            rst!FieldName3.Interior.Color = RGB(50, 205, 50)
            rst!FieldName4.Value = "Yes"
            rst!FieldName4.Interior.Color = RGB(50, 205, 50)
            
            
           ' table_name.Fields(FieldName3).Value = "Yes"
            'table_name.Fields(FieldName3).Interior.Color = RGB(50, 205, 50)
            'table_name.Fields(FieldName4).Value = "Yes"
            'table_name.Fields(FieldName4).Interior.Color = RGB(50, 205, 50)
           
       
        End If
    Else
     
   rst.Edit
   rst!FieldName3.Value = "Yes"
   rst!FieldName3.Interior.Color = RGB(255, 0, 0)
   rst!FieldName4.Value = "Yes"
   rst!FieldName4.Interior.Color = RGB(255, 0, 0)
     
     
     
   End If
   rst.MoveNext
 Loop
    

End Sub

This is what I tried. I'm stuck in first few lines couldn't get to debug the entire code. So, in short, i feel what I ended up writing in access vba is not useful at all.

Please help me in correcting this code. If not then please suggest me another way to generate this report of who is part of which group. I hope I explained everything clearly.
PS: My example may look stupid because this is just intial step. After getting info about who is part of which group I have to add few more steps which i'll do once i learn how to write this code in access vba.

Thanks in advance.

 

[shopaholic]

PS: If anybody wants to suggest any book to learn the basics of access vba then I'll be more then grateful. I failed to learn it through google.

 

[Drunkenneo]

I see in the Code the Rst.Edit is there, but no Update is given at the end.