VBA - Access2003 please review my code

[Programmer - wannaB]

First let me send my thanks out to Pieter who has helped me a great deal on
something else but that help has allowed me to get to this point, and
Wolfgang who directed me to an MSDN site where I found the rest of what I
needed for this one.
Thanks to you all..

I have written the code below to run as a toggle from a command button and
everything seems to work fine except displaying the changes to the properties
of the commandbutton. Specifically the transparent property. The button
should be transparent when the StartUp Options are set to secure the DB, and
visible when the DB is unsecured. I expect that it has something to do with
the ability or inability to save the form, and If I am correct how can I tell
it to save the form before closing?

I would appreciate any and all comments regarding the code that I’ve written
and how it could be done better.

Private Sub ToggleStartUpOptions_Click()
Dim dbs As Object
Const PROPERTY_NOT_FOUND As Integer = 3270
Const ITEM_NOT_IN_COLLECTION As Integer = 3265
Const TEXT_TYPE As Integer = 10 'Equivalent to DAO dbText data type.
Const BOOL_TYPE As Integer = 1 'Equivalent to DAO dbBoolean data type.
Const LONG_TYPE As Integer = 4 'Equivalent to DAO dbLong data type.
Set dbs = Application.CurrentDb

OpenStartUpProperties:
On Error GoTo ErrorHandler
If Me!ToggleStartUpOptions.Caption = "." Then
Me!ToggleStartUpOptions.Caption = "OFF"
Me!ToggleStartUpOptions.Transparent = False
dbs.Properties("AllowFullMenus") = True
MsgBox "AllowFullMenu setting is > " &
dbs.Properties("AllowFullMenus").Value
dbs.Properties("AllowShortcutMenus") = True
dbs.Properties("StartupShowDBWindow") = True
dbs.Properties("AllowSpecialKeys") = True
dbs.Properties("AllowBuiltInToolBars") = True
dbs.Properties("AllowToolbarChanges") = True
dbs.Properties("AllowBypassKey") = True
Else
Me!ToggleStartUpOptions.Caption = "."
Me!ToggleStartUpOptions.Transparent = True
dbs.Properties("AllowFullMenus") = False
MsgBox "AllowFullMenu setting is > " &
dbs.Properties("AllowFullMenus").Value
dbs.Properties("AllowShortcutMenus") = False
dbs.Properties("StartupShowDBWindow") = False
' dbs.Properties("AllowSpecialKeys") = False
dbs.Properties("AllowBuiltInToolbars") = False
dbs.Properties("AllowToolbarChanges") = False
dbs.Properties("AllowBypassKey") = False
End If

ExitLine:
On Error Resume Next
dbs.Close
Set dbs = Nothing
Exit Sub

ErrorHandler:
MsgBox "ERROR#>> " & Err.Number & " --DESCRIPTION>> " & Err.Description
Resume Next

End Sub

 

[SteveM]

You could try Me.Repaint at the end of your code to repaint the form...

Steve

 

[Programmer - wannaB]

Thanks Steve, but that doesn't seem to help. If I do not have that command
in there and I click the button over and over the button appears to change,
but leave the screen and come back and it does not correctly reflect the
startup conditions. With the button in place I never see the button change
and message is always False... seems like that me.repaint stops the changes.
I do appreciate your time, and efforts, thanks.

 

['69 Camaro]

Hi.

Specifically the transparent property. The button
should be transparent when the StartUp Options are set to secure the DB,
and
visible when the DB is unsecured.

Your code makes the button invisible and toggles it back to visible again
when Microsoft Office 2003 SP-1 is installed. I take it your button isn't
turning invisible on your form when you try it? Perhaps you've installed
the new Microsoft Office SP-3?

I would appreciate any and all comments regarding the code that I've
written
and how it could be done better.

Okay. I'll mostly ask for your reasoning on why you wrote the code you did.
You need to have good reasons for using non-standard programming practices,
instead of "I copied it from code I found on my Easter Egg hunts on the
Internet, so I have no idea what it's for."

Dim dbs As Object

Why are you using a generic object instead of the DAO database object? All
versions of Access can find the DAO library without stumbling on missing
references, even if it's installed in a non-standard directory.

Const PROPERTY_NOT_FOUND As Integer = 3270
Const ITEM_NOT_IN_COLLECTION As Integer = 3265
Const TEXT_TYPE As Integer = 10 'Equivalent to DAO dbText data type.
Const BOOL_TYPE As Integer = 1 'Equivalent to DAO dbBoolean data type.
Const LONG_TYPE As Integer = 4 'Equivalent to DAO dbLong data type.

Why are you declaring all of these local constants -- and then never using
them within the procedure?

OpenStartUpProperties:

Why are you placing this label in the code, and then never referencing it
when redirecting logic flow? (As a matter of fact, there is no redirecting
of logic flow in your procedure except for the On Error commands, so the
extra label is unnecessary.)

On Error Resume Next

If you didn't put a stumbling block in your code cleanup section, you
wouldn't need to tell Access to ignore it. And the use of "Resume Next"
almost always means the programmer is too lazy -- or lacks the skills -- to
fix his buggy code. Handle the errors your procedure encounters by fixing
the code instead of ignoring them, and you'll become a better programmer.

dbs.Close

Why are you closing an object you didn't open programmatically in your code?

ExitLine:

Again, why are you placing this label in the code, and then never
referencing it when redirecting logic flow?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Programmer - wannaB]

Hi Gunny.

Let’s start at the top; The code makes the button transparent, not invisible
I believe that if it were invisible, it would not be usable. And SP-1 has
nothing to do with any of this. The caption, and transparency properties
of the button are changed with each click of the button in order to signify
the conditions that the StartUp Options have been set to, and to effect the
toggle ability.

The reasoning for this code is to be able to toggle the StartUp Options,
including the ability to prevent the use of the shift key when the DB is
opened. Without this code how could the DB be locked down and still
have the ability for developers to make changes. The button dimensions are
slightly bigger then a . and is Transparent while the StartUp Options are set
to restrict structural changes. One needs to know where the hidden button
is in order to modify the structure of the database.
I do not know enough to fully understand the difference between a generic
object
and a DAO object, and this is a reference that I found that worked.
These constants were used in the first run through, and were needed in the
Error checking that was used to initially create the AllowByPassKey Property.

I created that label when I first started and later cleaned it up to use
the If Then Else... just failed to notice that it was no longer used

Granted I may be lazy, but in this case I would have to say the reason is that
I lack the Skills, and that is one reason why I post my creations and ask for
input, such as yours.

I thought that dbs.close was like closing the window that is used when you set
the options in the StartUp Options window.

Again another of those left over from trial and error programming until I got
what I was looking for.

Yes I did go on an Easter egg hunt in search of some code that would get me
started on the project at hand. Using bits and pieces from the site listed
below I came up with the code that seemed to do what I was looking for.
http://msdn2.microsoft.com/en-us/library/aa662934(office.11).aspx

Public Sub SetMDBAppTitle()
Dim dbs As Object
Dim prp As Object
Dim strTitle As String

Const PROPERTY_NOT_FOUND As Integer = 3270
Const TEXT_TYPE As Integer = 10
' Equivalent to DAO dbText data type.
Const BOOL_TYPE As Integer = 1
' Equivalent to DAO dbBoolean data type.
Const LONG_TYPE As Integer = 4
' Equivalent to DAO dbLong data type.

On Error GoTo SetMDBAppTitle_Err

Set dbs = Application.CurrentDb
strTitle = "Setting *.MDB Startup Options"

' Try to set the property. If it fails, the property does not exist.
dbs.Properties("AppTitle") = strTitle

' Refresh the title bar to reflect the change.
Application.RefreshTitleBar

ExitLine:
dbs.Close
Set dbs = Nothing
Set prp = Nothing
Exit Sub

SetMDBAppTitle_Err:
If Err.Number = PROPERTY_NOT_FOUND Then
' Create the new property.
Set prp = dbs.CreateProperty("AppTitle", TEXT_TYPE, strTitle)
dbs.Properties.Append prp
Resume Next
Else
Resume ExitLine
End If

End Sub

Public Sub RemoveMDBAppTitle()
Dim dbs As Object

Const ITEM_NOT_IN_COLLECTION As Integer = 3265

On Error GoTo RemoveMDBAppTitle_Err

Set dbs = Application.CurrentDb

' Remove the startup property.
dbs.Properties.Delete ("AppTitle")

'Refresh the title bar to reflect the change.
Application.RefreshTitleBar

ExitLine:
dbs.Close
Set dbs = Nothing
Exit Sub

RemoveMDBAppTitle_Err:
If Err.Number = ITEM_NOT_IN_COLLECTION Then
Resume ExitLine
Else
Resume Next
End If

End Sub

Thanks,
69TA
=========================================

 

[opeyemi]

my windows media player 11 is not showing pictures , i only hear the audio
but no video output.pls who can help me

 

[Douglas J. Steele]

Sorry, this newsgroup is for questions about Access, the database product
that's part of Office Professional.

You'll need to repost your question to a more appropriate newsgroup, such as
microsoft.public.windowsmedia.player