I'm continuing to refine the code of a routine that runs several queries and uses colored labels as indicates of the overall process.

Look at the post by me to see the original problem I had and the solution. It is located at http://p2p.wrox.com/topic.asp?TOPIC_ID=63777

Now on to the new question...
Is it possible, to call a sub routine, that passes an objects name, and have the sub make changes to the properties of that object.

Example:
If chkCUPMMOF then
    Call MakeTable(me.lblCUPMMOF)
End If

The MakeTable sub would look something like
__________
Public Sub MakeTable(lbl As AccessObject, TN as String)
    lbl.Caption = "Running"
    lbl.BackColor = lngGreen

    StrSQL = "01a-qry_" & TN

    Call SQL(strSQL)

End Sub
__________

I've tried some variations on this, and I get a Type Mismatch error on most of my attempts. So do I have to use a different option than As AccessObject? Is this even possible?

Thanks in advance
Rood67

__________________
<b>Rood67</b>

It looks like you are oassing two variables here:

MakeTable(lbl As AccessObject, TN as String)

But you Call only passes one.

In any event, Access doesn't like it when you pass objects like this. Try this instead:

Public aoLabel As AccessObject
Public TN As String

aoLabel = me.lblCUPMMOF

etc.

Anyway, put everything in variables before you pass them around. That should help.


Call MakeTable(aoLabel, TN)

mmcdonal

Thanks M.McDonal,

Your excellent advise got me through the other problem I was having, so I'm confident this should work.

The overall goal here is to let the few conditional test set the main items that need to be updated, and as you pointed out, the string that I didn't type correctly when I made my original post.

I'll put this suggestion to practical application and post the results.

Rood67

I should talk about spelling mistakes, though. That post was riddled with them. =)

mmcdonal

Public Q, A, strSQL As String 'Q is for double Quote and A is for Ampersand
Public aoLabel As AccessObject 'Set up an object variable to pass to sub routine to use in setting properties
__________
    If Me.chkCUPMMOF Then
        aoLabel = Me.lblCUPMMOF
        strSQL = "01a-qry_MakeCUPMMOF"
        Call makeTable(aoLabel, strSQL)
    End If
__________
Public Sub makeTable(aoLabel as AccessObject, strSQL as String)
    'Test routine for the querry Make CUPMMOF

    aoLabel.Caption = "Test One"

    'Me.lblCUPMMOF.Caption = "Running" /using Test One instead of the normal Running to visually see change
    Me.lblCUPMMOF.BackColor = lngGreen
    Call SQL(strSQL)
    Stop
    Call updateTable
End Sub

Public Sub updateTable()
    Me.lblCUPMMOF.Caption = "Done"
    Me.lblCUPMMOF.BackColor = lngRed
    Call DC
End Sub
__________
Public Sub SQL(strSQL As String)
    Call DC
    DoCmd.SetWarnings False
    DoCmd.OpenQuery strSQL, acNormal, acEdit
    DoCmd.SetWarnings True
End Sub
====================
This is only snippets of the code, but all the parts that pertain to the problem. Set up the variables, call the initial routine and pass variables, call subsequent routines and pass variables.

I have tried several different things, but I constantly get Object variable or With block variable not set

Passing anything dealing with objects is new to me, so I'm not sure where the problem may be.

Rood67

Where is the Public Sub? If it is in a Module, then Me. will not work. This is where the code probably thinks you are using a With block. You need to change all the Me. references to:

[Forms]![frmYourFormName].[lblCUPMMOF].BackColor etc.

Did that help any?


mmcdonal

Hey Mac,

The public declarations are in the (General) (Declaration) section along with the Option Compare Database. I have hammered away at finding the solution, and found it in another forum
http://forums.devarticles.com/micros...set-10040.html
The answer is in the first reply there. I needed to Set aoLabel to be the label, not just a general assigning. Thus
       aoLabel = Me.lblCUPMMOF
should be
       Set aoLabel = Me.lblCUPMMOF
and it now works perfectly.

Thank you very much, again, for putting me on the right track to solving my problem.

Rood67