Conditional "For each..."

[aytee111]

I have a piece of code coming from a command button that opens another database, reads thru all its objects and does a whole bunch of other stuff. This code can be initiated either from a table (For each tdf in dbs.Tabledefs) or from a query (For each qdf in dbs.Querydefs). All the rest of the code is the same.

I am trying to avoid having to repeat the code - that is my last (and worst) option. But is there any other way to do it? I tried putting the FOR into an if statement but the compiler complained. I am not sure if I can call another procedure - what parameters do I send? At this point I have the database open, the recordsets all positioned and ready for action, etc.

Any ideas would be appreciated.

[ranman256]

depends on what you are doing...
the FOR EACH only works with objects (lists, collections)

FOR i = 1 to x works for variables.

[aytee111]

Not sure I understand your question - the FOR will either be for the list of tables or queries.

[ranman256]

what are you trying to do?
then i can answer.

[aytee111]

Read all the objects in an external table searching for use - example, opening all the forms for each table and checking to see where (or if) that table is used, same for query.

[aytee111]

Should have read "external database", sorry!

[ranman256]

you dont use FOR,
reading a table/query is a RECORDSET
then cycle thru the records

set rst = currentdb.openrecordset("select * from table")
with rst
while not .eof
  sItem = .fields("myFieldName")
   
   .movenext
wend
end with
set rst = nothing

[ItsMe]

I am not sure I understand. I suppose you could create a custom Class. Place your code in the Class and connect to the Class from an external Access file.

[aytee111]

This is the beginning part of the code. Where the "For Each tdf.." is, I want to change it optionally to "For Each qdf...."

I haven't used classes before! Will need to read up about them.

    ObjType(1) = "Query"
    ObjType(2) = "Form"
    ObjType(3) = "Report"
    ObjType(4) = "Macro"
    ObjType(5) = "Module"
    
    DoCmd.SetWarnings False
    Set app = New Access.Application
    app.OpenCurrentDatabase Me!DBName
    Set dbs = app.CurrentDb                                     'open other database
    Set rst = CurrentDb.OpenRecordset("WhereUsed_tbl")
    DoCmd.RunSQL "DELETE * FROM WhereUsed_tbl;"
    Me!TblTotal = dbs.TableDefs.Count                           'get count of tables
    Me!TblProc = 0
    
    For Each tdf In dbs.TableDefs
        UpdOpt = False
        If Not IsNull(Me!txtObjName) And Me!txtObjName <> "" Then         'if only one table selected
            TableN = Me!txtObjName
        Else
            TableN = tdf.Name
        End If
        Me!TblProc = Me!TblProc + 1
        
        For x = 1 To 5

[ItsMe]

It seems you wish to type less code by dynamically instantiating a tabledef object or a querydef object. I tried to imagine a couple ways and I am not seeing anything that will work. Bottom line, you will need to define the collection type somewhere.

Perhaps understanding what it is you wish to automate and what it is you wish to be dynamic will shed some light on the issue. Maybe you want to allow the User to select a collection (table or query) and then execute the appropriate code block.

[ranman256]

Attach the external table.

[aytee111]

It's a "where used" button, as in "where is this table (or query) referenced?". It reads thru all the objects in the external database - either using the Tabledefs list or Querydefs - and looks for a reference to that object.

(Working on some very old databases which have been maintained by the user community up to now, you can imagine the mess they are in)

[ItsMe]

If the objective is to understand the design and have a tool assist you in development, why not consider a third party tool?

http://www.mztools.com/

or

http://www.fmsinc.com/Products/access.asp

[aytee111]

Looks like I am re-inventing the wheel! Sadly those products are not free but in truth I am having fun, lol.

Thanks for your help.

[ItsMe]

Yeah, there is not anything wrong with customizing things for yourself.

What I see is you will have one procedure to process each of the different Object Types. They reside in different collections. So I am not seeing a way of using a Strongly typed VBA function to dynamically use a single function. This is how I understand your question. You want to combine them into a single function. I say just write different functions.

You could include the different function names in an Enum. Declare the enum in a Standard Module. A function in the same module could use the enum to process one of the previously described functions.