Need help in debugging 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. :D


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.

 

[SydneyGeek]

Hi shopaholic,

It's not clear whether you are trying to do the colouring in a table (which generally will not work) or in a form / report (which will).

In forms (generally used for data entry) or reports (used for printing) you can use conditional formatting, and / or events associated with controls to do the formatting for you.
I'd recommend using a report (build using the wizard, it gets most of the hard work done) and using the Detail_Format event to trigger the formatting.

The Detail_Format code could look something like:

Private Sub Detail_Format()
    Select Case Me!Status
        Case "Active"
            If Me!Group = "Scorpio" Then
                Me!Part_of_scorpio_group.Value = "Yes"
                Me!Part_of_scorpio_group.BackColor = RGB(50, 205, 50)
                Me!Part_of_virtual_group.Value = "No"
                Me!Part_of_virtual_group.BackColor = RGB(255, 0, 0)
            ElseIf Me!Group = "Virtual" Then
                Me!Part_of_scorpio_group.Value = "No"
                Me!Part_of_scorpio_group.BackColor = RGB(255, 0, 0)
                Me!Part_of_virtual_group.Value = "Yes"
                Me!Part_of_virtual_group.BackColor = RGB(50, 205, 50)
            ElseIf Me!Group = "Both" Then
                Me!Part_of_scorpio_group.Value = "Yes"
                Me!Part_of_scorpio_group.BackColor = RGB(50, 205, 50)
                Me!Part_of_virtual_group.Value = "Yes"
                Me!Part_of_virtual_group.BackColor = RGB(50, 205, 50)
            Else
                'any other options here
            End If
        Case Else
            Me!Part_of_scorpio_group.Value = "No"
            Me!Part_of_scorpio_group.BackColor = RGB(255, 0, 0)
            Me!Part_of_virtual_group.Value = "No"
            Me!Part_of_virtual_group.BackColor = RGB(255, 0, 0)
    End Select
End Sub

VBA doesn't change as you move from Excel to Access, but the object model does. That's the hardest part of moving from one application to another.

Denis

 

[shopaholic]

Hi Denis, I created a report using report wizard. And pasted the code given by you in a module. When I'm trying to debug it, it is saying - invalid use of me keyword. What am I doing wrong?

When this didnt work, I created a textbox on the report and in its control source property I selected the name of my vba i.e. Detail_Format. Got same error - invalid use of me keyword.

Thanks for the code. I understood what was wrong with my code..

 

[SydneyGeek]

In the report design click on the Detail section (actually, the divider for the Detail section) and then go to the Properties.
In the Events tab, find On Format. Double-click the blank line so you see [Event Procedure], then click the ... button. That will put you in the Detail_Format event; paste the code there.

The Me keyword is a shortcut that refers to the form or report that holds the code. If you put Me in a standard module, Access throws an error.
Hope that clarifies the set-up...

Denis

 

[shopaholic]

In the report design click on the Detail section (actually, the divider for the Detail section) and then go to the Properties.
In the Events tab, find On Format. Double-click the blank line so you see [Event Procedure], then click the ... button. That will put you in the Detail_Format event; paste the code there.

The Me keyword is a shortcut that refers to the form or report that holds the code. If you put Me in a standard module, Access throws an error.
Hope that clarifies the set-up...

Denis

I followed the steps suggested by you then changed view of report to print view. My code started debugging. But, I'm getting error in highlighted line -

Private Sub Detail_Format()
    Select Case Me!Status
        Case "Active"
            If Me!Group = "Scorpio" Then
            [COLOR=#ff0000]Me!Part_of_scorpio_group.Value = "Yes"
[/COLOR]

error is - runtime error 2448. You cannot assign value to this object.

 

[SydneyGeek]

Thinking about it, you can't change data at formatting time. You'll need to have a query that does the data changes, then use the report code for the formatting.

Denis

 

[shopaholic]

Thinking about it, you can't change data at formatting time. You'll need to have a query that does the data changes, then use the report code for the formatting.

Denis

I commented the lines where i was trying to change the value. But it should now at least change the color. I'm getting this error - Run-time error 438: Object doesn't support this property or method

Private Sub Detail_Format()
    Select Case Me!Status
        Case "Active"
            If Me!Group = "Scorpio" Then
             '   Me!Part_of_scorpio_group.Value = "Yes"
               [COLOR=#ff0000]Me!Part_of_scorpio_group.BackColor = RGB(50, 205, 50)[/COLOR]

PS: I google about this error. I'm using access 2007 and i imported excel sheet data into access. Excel from which I imported data is also 2007.
Also, I'm not using any control/command button.
I sinply importe dthe excel sheet. then using report wizard i created a report. As explained by you, I used the detail_format trigger.

 

[shopaholic]

Hi Denis,
I used update query to get the result I wanted. I wrote a few update queries in a module. Used DoCmd.RunSQL queryname(or query inside double quotes).

It works. Thanks for the help. Now, I'm trying to do change the backcolor in a report for Yes and No values.