Basic thinks

[groupy]

Hi,

I have some questions, it are basic thinks I think, but I don't know them, and cannot find the answer.

- How can I test of a table, report or form exist.
if ([formname].exist = true) then ...

- How can I get a list of all table, report, for example to use in a combo box.

- How can I disable for example 20 lines of code.

- Is it possible to change VBA code, with VBA code. For example to give the admin user to disable or enable a function, without going to the VBA code.

Thanks for you reactions.

 

[namliam]

some answers:
- Search
- Search
- Search
- Uhm, Search

All of these have been adressed and atleast some of which you should be able to find .... So try searching....

Regards

The Mailman

 

[groupy]

You're right, but it is not easy, so I put everythink I can find in this thread. When there question are left, I will ask again.

Answer on question, 'How can I get a list of all table, report, for example to use in a combo box':

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects;

With a where option you can filter the types.

 

[groupy]

Yes, I found a other answer, now on the question "How can I test of a table, report or form exist."

Answer:

If (IsNull(DLookup("MSysObjects.Name", "MSysObjects", "MSysObjects.Name = " _
& "'" & [name object] & "'")) = False) Then

Else
messageBox "[name object] don't exist!", "WARNING"
End If

 

[Mile-O]

- How can I test of a table, report or form exist.
if ([formname].exist = true) then ...

My preference would be to write a function that returns a boolean variable.

i.e. To test if a table exists (DAO method)

Public Function IsTable(ByVal strTable As String) As Boolean

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If tdf.Name = strTable Then
            IsTable = True
            Exit Function
        End If
    Next

End Function

So, you can then use:

If IsTable("MyTableName") = True Then

- How can I get a list of all table, report, for example to use in a combo box.

If I need to do this I ensure that I have used a good naming convention (preferably the accepted one although I do, sometimes, create my own for the purposes of a specific database).

Then, going to Tools -> Options and checking Show System Objects I get the systems tables. There is one called MSysObjects that I use a query like this for:

SELECT Mid(Name, 3) AS TableName
FROM MSysObjects
WHERE Left(Name, 3) = "tbl"
ORDER BY Mid(Name, 3);

If you have used the tbl convention for tables, this query returns all tables.

- How can I disable for example 20 lines of code.

Either manually with an ' at the start of each line and I believe you can also use a _ at the end of a comment line to continue the comment

- Is it possible to change VBA code, with VBA code. For example to give the admin user to disable or enable a function, without going to the VBA code.

Yes. There's an example of changing VBA code on this thread.

 

[groupy]

Hi Mile-O-Phile,

Thanks for your answers (again).

But only my following question was not clear enough I think:
How can I disable for example 20 lines of code.

I mean, when I have writen a lots of good, but I want to disable big parts because I have error's, how can I do that.

sub example

code
code

//Disable this part to
code
code
code
code
// here

code

end sub

Thanks a lot for youre answer

 

[Mile-O]

Visual Basic, as far as I know, does not have a feature such as this.

 

[namliam]

Yes it does... kind off....

sub bla

code
if 1 = 2 then
code
code
endif

end sub

Regards

 

[The_Doc_Man]

While it is indeed possible to use MSysObjects to get what you want in terms of whether table X exists, the CLEANER solution is probably the function that iterates through the members of the tables collection seeking that table whose name is X. If it finds it, the function returns TRUE. So I would advocate the solution offered by Mile-O-Phile.

This method is preferable because it doesn't depend on the underlying structure of MSysObjects, which (being an infrastructure item) is potentially changeable without much notice from version to version. However, the published COM interface involving enumerable collections is far less likely to change.

You would also do better to populate a new table by performing an enumeration of each collection's members and storing the name (and type) of each member. So in general, I would say that if you want to search for a named object, write something that will traverse the collection that putatively contains it, as a general approach that will always work better than anything that requires you to decompose a cryptic table.

Several ways exist to disable a block of code. The idea of placing the code inside an IF block for which the condition is FALSE is probably as good as any. If this is being done as a debugging issue, this is probably OK. But if there is ANY OTHER REASON for you to want to do this, you should design the code ahead of time to have a control key to enable or disable it. I.e. if you want the code to work for users but not for the Admins group, put it in an IF block from the start and test the group membership of your current user on entry to the block.

I have to warn you about self-modifying code, though. Yes, you can do it. Getting it to recompile correctly and be usable to all parties, though, might be a problem. The specific problem is that if the older version of the changed module is in use when you try this, your changer code will run into a lock and will fail miserably no matter how good it is. Also, I'm probably the most pretentious programmer you ever met, and this is something that even I would approach with extreme caution.

On the other hand, if you think about it, many of the control wizards (button wizard, for example) modify a form's module by inserting code somewhere in the body of that module. So yes, you can indeed write code to change code.