Object Syntax

S

scott

I'm trying to dynamically change the string variable "objType", but I'm
getting an error on the "For Each acobjLoop" line below.

How can I change the CurrentProject.objType to either
"CurrentProject.AllForms" or "CurrentProject.AllReports" depending on my
CASE statement?

CODE *************

For i = 1 To 2
Select Case i
Case 1
objType = "AllForms"
Case 2
objType = "AllReports"
End Select

For Each acobjLoop In CurrentProject.objType
' Execute some code
Next i
 
J

Jeff Boyce

Scott

It looks like you are trying to use "objType" as a variable (you don't
mention what the error message is).

Another approach would be to evaluate your condition and set the
"CurrentProject.xxxx" line accordingly.

By the way, since a Select Case statement is usually used to evaluate and
"branch", using it as you have, embedded within a For...Next loop that
returns ALL possible values seems like a mis-use of the Case statements.
Can you explain more what you hope to accomplish?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

Klatuu

There are a couple of problems with your code. First, the CurrentProject
object can't use a variable like that. You would be better to establish two
separate object references. Second, the Select Case statement is usually
used for more than two choices based on the the value of one entity. I think
you would be better off to split it into two separate loops.

Dim frms As Object
Dim rpts As Object
Dim frm As Form
Dim rpt As Report

Set frms = CurrentProject.AllForms
Set rpts = CurrentProject.AllReports

For Each frm In frms
'Excecute some code
Next frm

For Each rpt In rpts
'Execute some code
Next rpt

Really much cleaner.
 
S

scott

I'm trying to loop through all objects and return the type, name and
modified date of the last modified object within a database. My code works
good if I hard-code "CurrentProject.AllForms" like below. I'm trying to
dynamically feed the object type to the "For Each ..." part of my code.

USAGE *****************************

Dim sObjectType As String, sObjectName As String, dtDateMax As Date
Call GetObjectMaxDate(sObjectType, sObjectName, dtDateMax)


FULL CODE *************************

Public Function GetObjectMaxDate(ByRef sObjectType As String, ByRef
sObjectName As String, ByRef dtDateMax As Date)
Dim acobjLoop As AccessObject, dtDateCurrent As Date, objType As String,
i As Integer

dtDateMax = "1/1/1900"

For i = 1 To 5
Select Case i
Case 1
objType = "AllForms"
sObjectType = "Form"
Case 2 '
objType = "AllReports"
sObjectType = "Report"
Case 3
objType = "AllModules"
sObjectType = "Module"
Case 4
objType = "AllTables"
sObjectType = "Table"
Case 5
objType = "AllQueries"
sObjectType = "Query"

End Select

For Each acobjLoop In CurrentProject.AllForms
With acobjLoop

dtDateCurrent = acobjLoop.DateModified

If dtDateCurrent > dtDateMax Then
dtDateMax = dtDateCurrent
sObjectName = acobjLoop.name
End If

End With
Next acobjLoop

Next i
Debug.Print "Type: " & sObjectType & " - " & sObjectName & ": " &
dtDateMax
End Function
 
K

Klatuu

You can't use variables to do what you are after. Here is a small example
that will give you a way to get what you are after:

Public Sub CheckObjects(intSelect As Integer)
Dim cpj As CurrentProject
Dim objs As Object
Dim obj As Object

Set cpj = CurrentProject
Select Case intSelect
Case 1
Set objs = cpj.AllForms
Case 2
Set objs = cpj.AllReports
Case 3
Set objs = cpj.AllModules
Case 4
Set objs = cpj.AllMacros
End Select

For Each obj In objs
Debug.Print obj.Name
Next obj

Set obj = Nothing
Set objs = Nothing
Set cpj = Nothing

End Sub
 
S

scott

thank you.


Klatuu said:
You can't use variables to do what you are after. Here is a small example
that will give you a way to get what you are after:

Public Sub CheckObjects(intSelect As Integer)
Dim cpj As CurrentProject
Dim objs As Object
Dim obj As Object

Set cpj = CurrentProject
Select Case intSelect
Case 1
Set objs = cpj.AllForms
Case 2
Set objs = cpj.AllReports
Case 3
Set objs = cpj.AllModules
Case 4
Set objs = cpj.AllMacros
End Select

For Each obj In objs
Debug.Print obj.Name
Next obj

Set obj = Nothing
Set objs = Nothing
Set cpj = Nothing

End Sub
Click to expand...
 
Top