Prevent opening Database with Shift key 6

[Ajwebb]

Hi all,

When you open a database you can skip opening forms by holding down the shift key.

Is there anyway to prevent this?

Thanks in Advance

Ant

 

[KenReay]

Hi

You can disable it in code by setting the database property:


If ChangeProperty( "AllowBypassKey", dbBoolean, False) Then
...etc


Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
Dim dbs As DAO.Database, prp As Property
Const conPropNotFoundError = 3270
'
Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True

Change_Bye:
Exit Function

Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Ajwebb]

Hi Ken,

Thanks for the reply, where should i put the code?

Ant

 

[KenReay]

Hi

In a module which is executed at start up, or in (say) the on open event of the switchbord form, indeed anywhere where you can be sure it will be executed when the db opens

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Dalain]

Yes there is, create a Global Modual called StartFunctions and enter the following

Option Compare Database
Option Explicit

Function SetStartupProperties()
ChangeProperty "AllowBypassKey", dbBoolean, False
End Function

Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
Dim dbs As Database, prp As Property
Const conPropNotFoundError = 3270

Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True

Change_Bye:
Exit Function

Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function


To remove the key, on the first form that opens in your database, (your Starup Form) in the On Load Event enter

Private Sub Form_Load()
Call SetStartupProperties
End Sub

Be carfull, you should also have a way to add the shift key, in case you want to do some more work on your database.

One way of doing this is creating a hidden button that only you know where it is the when you double click it an unbounded field becomes visible(Named KeyCode).

on the Keycode lost focus event enter the following

Private Sub KeyCode_LostFocus()
If Me.KeyCode = "Your Code Here" Then
ChangeProperty "AllowBypassKey", dbBoolean, True
DoCmd.Beep
End If
End Sub

You then enter you code in this field, which will reactivate the shift key. The god thing about this I the user does find the hidden bouton, they still don't know the keycode.

P.S. Backup Your Database before playing with this code.

Hope this helps

Dalain

 

[Ajwebb]

Hi Ken,

Thanks again for replying but as i am not that used to code, what would i put after:
If ChangeProperty( "AllowBypassKey", dbBoolean, False) Then
...etc

Thanks

Ant

 

[KenReay]

Hi

well you don't HAVE to put anything:

If ChangeProperty( "AllowBypassKey", dbBoolean, False) Then
End If

would do

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Ajwebb]

Hi Ken and Dalain, on both codes it comes up with the error message "Compile Error - Variable not Defined".

What could this be.

Regards

Ant

 

[Dalain]

What Variable is highlighted when you get the message?
Check your library referances. This code is using the DAO Library.

Regards
Dalain

 

[KenReay]

Hi

As Dalain says, which line / variable is highlighted

To check Object Libarary references, open any code module in design view, choose Tools\References, ensure theer is a reference to the Microsoft DAO 3.6 Library, note depending on your version of Access/Jet, the version number (3.6) may vary

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Ajwebb]

Hi Guys,

There are a couple of things highlighted and the references on the tools menu is greyed out.

The code is as follows and i have marked what it doesn't like with a !* on each side:-

Function SetStartupProperties()
changeproperty "AllowBypassKey", !*dbBoolean!*, False
End Function

Function changeproperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
Dim !*dbs As Database!*, !*prp As Property!*
Const conPropNotFoundError = 3270

Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
changeproperty = True

Change_Bye:
Exit Function

Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
changeproperty = False
Resume Change_Bye
End If
End Function

 

[Ajwebb]

Hi Guys,

Eventually able to reference the DAO 3.6 Library but now i have an error on a diffent line saying "Data Type Conversion Error" on line

Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)

Regards

Anthony

 

[Dalain]

Hi,

Well for the references on the tools menu being greyed out, is because you were in a Code Break mode. This is when you have the Yellow Highligted line indicating where the code has stoped and the Blue highlighted words indicating which part of the code Access is having problems with.


When you get the error (While in Code Break mode)place your curser over each variable for a few seconds (strPropName, varPropType, varPropValue).

Access will then display the current value of the variable. This should help to figure which one is causing the problem.

You should have the following values

strPropName = "AllowBypassKey" (With the " marks)
varPropType = 1
varPropValue = False

While we are at it,You did mention problems with the code words dbBoolean and prp As Property. Normally Access should never have a problem with these words, So you should double check your libary referance files. Here is a list of the recomended referances and the file Name of the referance. (Access 2000)


TITLE --> File Name
Visual Basic For Applications-->VBE6.DLL
Microsoft Access 9.0 Object Library-->MSACC9.OLB
Microsoft DAO 3.6 Object Library-->dao360.dll
OLE Automation-->stdole2.tlb
Microsoft Access Visual Basic For Applications Extensibility 5.3-->VBE6EXT.OLB
Microsoft ActiveX Data Objects 2.1 Library. -->msado21.tlb

Hope this Helps

Dalain

 

[Ajwebb]

Thanks for getting back to me.

strPropName = "AllowBypassKey" (With the " marks)
varPropType = 1
varPropValue = False

Are all the same as yours, however i held the mouse pointer above the prp bit in the following sentance and the value said "Nothing": Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)

Is that correct?

 

[Ajwebb]

The Error i now get is Run-time Error "13" Type Mismatch and when i go into debug it is the following line that is Yellow.

Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)

Ant

 

[Ajwebb]

Hi Dalain and Ken,

Sorted it.

Instead of Dim dbs as database, prp as properties, it now reads dim dbs as dao.database, prp as dao.properties and it works fine.

I found it on an article which said with access 2000 you sometimes need to put that.

Don't know as to why you need to put that in front as i am still really, really new to all of this.

Thank you both for your time and effort.

Much appreciated

Anthony

 

[Dalain]

Hi, Anthony

The value of prp at the time of the break (Highlighted Yellow)is "Nothing" because that line of code has not been processed. The other variables were defined earlier.

Also the Reference Library list I gave you, ensure that the DAO library Reference is moved to the third from the top of the list.

Whenever you use a keyword, access searched the Library References to see what to do with the Keyword. Access starts with the first one in the list, then moves to the next one. Until it is found.

The problem is that some times there are two(2) or more Library References that contain the same Keyword, but use a different syntax. I want able to Re-Create your error just by moving the DAO library reference up and down the list.

Also change the following from

Dim dbs As Database, prp As Property
to
Dim dbs As DAO.Database, prp As Property


Dalain

 

[Dalain]

Hi, Anthony

I guess you were posting at the same time.

A small note: The term DAO.Database tell access which Library Reference to use. So if there are two using the same Keyword, only the DAO is used

Regards

Dalain

 

[Ajwebb]

Thanks Dalain,

Its nice to know the reason as to why you are doing something rather than just doing it because it works.

Thanks Again

Anthony

 

[mondoray]

Ajwebb,

The method I use to disable/enable the shift key is via the use of two invisible command buttons placed somewhere that only you know on the startup form or switch board. I found this code on the net somwhere. Very simple but works fine.

The first command button has the following:

Public Sub Command?_Click()
'This code disables shift key'
Dim db As Database
Dim prp As Property
Set db = CurrentDb
Set prp = db.CreateProperty("allowbypasskey", dbBoolean, False)
db.Properties.Append prp
End Sub


The other command button has the following:

Public Sub Command?_Click()
'This code re-enables shift key'
Dim db As Database
Set db = CurrentDb
db.Properties.Delete "allowbypasskey"
db.Properties.Refresh
End Sub



This procedure works very well. To enable/disable the shift key first you start the application then click on the location of the appropriate command button, close the application then restart, ie hold down the shift key, etc.

Hope this helps.

PS You also need to Microsoft DAO 3.6 Object Library set in your code references.

Mondoray