Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 8th, 2007, 04:44 PM
Authorized User
 
Join Date: Mar 2004
Location: Knoxville, TN, USA.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Rood67
Default Change An Objects Properties Via Sub Routine

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>
 
Old October 9th, 2007, 06:52 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 10th, 2007, 03:24 PM
Authorized User
 
Join Date: Mar 2004
Location: Knoxville, TN, USA.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Rood67
Default

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
 
Old October 11th, 2007, 07:01 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal
 
Old October 12th, 2007, 10:01 AM
Authorized User
 
Join Date: Mar 2004
Location: Knoxville, TN, USA.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Rood67
Default

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
 
Old October 12th, 2007, 10:07 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 12th, 2007, 12:14 PM
Authorized User
 
Join Date: Mar 2004
Location: Knoxville, TN, USA.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Rood67
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
function - change forms txtbox properties feets Access VBA 3 May 11th, 2007 10:05 AM
Change the properties of a textbox control? marksartwork ASP.NET 1.0 and 1.1 Basics 7 April 4th, 2006 04:55 PM
Picture Objects - how do I change the image on a r ERC Crystal Reports 13 March 19th, 2005 12:20 PM
How to change report's objects dynamically. Vadim Crystal Reports 2 May 17th, 2004 09:40 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.