ACCESS FEED: Re: [MS_AccessPros] Report based on Crosstab query

Khalid-

See answers below.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 28, 2015, at 10:23 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

This is very informative and opening new doors of knowledge to me.

Still i have to get more information from me as i am doing these actions for the first time and not convenient with them.

You wrote:

Two things you can do:

1) In Tools / Startup, clear the check box "Use Access Special Keys". This prevents the user from pressing F11 to get the database window.

2) Define the AllowBypassKey property and then set it in your MDE. If you do that, then your boss cannot press Shift when he opens the file to bypass your Startup properties. Put this code in a Standard module:

For action No.1, this i have to do in the copy of .mdb which i am giving to my boss ?

==> I thought you were giving him an mde. I would do it in your mdb just before you create the mde, then change it back after creation. If you forget, you can always hold down the Shift key when you open your database to bypass Startup.

For action No.2, Shall this code to put in a Standard module in my .mdb OR in the copy to give to my boss?

==> Put the code in your mdb.

You wrote:

Note that you have to fix the reference to "C:\MyDatabases\PCTL.mde" to use the path where you store your mde after you create it.

I save my .mde in "D:\PCTL\PCTL.mde", so in the code "C:\MyDatabases\PCTL.mde" should remain same or it is to be modified as "D:\PCTL\PCTL.mde".

==> Yes.

You wrote:

Compile and save the above code in PCTL.mdb. Go to the Immediate Window (CTRL+G) and type:

ClearBypassProperty

… and press Enter. That will define the property in your database, but set it to True to allow you to continue to use the Shift key.

How do i set it to True again?

==> It gets defined and set to True when you run ClearBypassProperty in your MDB. (This defines the property but still allows you to use Shift when you open your database if necessary.) You want it set to False only in the MDE. That's why after you create the MDE you must run the SetBypassPropertyMDE code.

After doing all steps you mentioned i think there would be no need for the following code in my form MainMenu

Private Sub Form_Load()

Dim CheatDate As Date

Dim Dte As Date

Dte = Now()

'If Dte >= (#6/30/2011#) Then

If Year(Dte) >= 2016 Then

CheatDate = Dte

DoCmd.OpenForm "CheckDate", , , , acFormEdit

Forms!CheckDate!CheckDate = CheatDate

MsgBox "An unexpected error has occoured..." _

, vbCritical, "Microsoft Access"

DoCmd.Quit

End If

DoCmd.OpenForm "CheckDate"

If Forms!CheckDate!CheckDate > Dte Then

MsgBox "An unexpected error has occoured..." _

, vbCritical, "Microsoft Access"

DoCmd.Quit

End If

DoCmd.Close

End Sub

==> It's not clear what that code is doing. It doesn't matter whether you keep it or not.

Waiting for your kind guidance to proceed further and sorry for bothering you again & again.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

By the way, NEVER set AllowBypassKey to False in your mdb - you will lock yourself out!

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 28, 2015, at 8:30 AM, John Viescas JohnV@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Khalid-

If your boss is smart enough to get the database window open, then he's probably smart enough to troll the web and find your postings here!

When you give your boss an MDE file, he can see your tables, queries, forms, and reports, but he can't get to any VBA code. When you create an mde file, all VBA code gets removed.

Two things you can do:

1) In Tools / Startup, clear the check box "Use Access Special Keys". This prevents the user from pressing F11 to get the database window.

2) Define the AllowBypassKey property and then set it in your MDE. If you do that, then your boss cannot press Shift when he opens the file to bypass your Startup properties. Put this code in a Standard module:

Sub ClearBypassProperty()
Const DB_Boolean As Long = 1
ChangeProperty "AllowBypassKey", DB_Boolean, True
End Sub

Sub SetBypassPropertyMDE()
Dim db As DAO.Database
' Open the MDE file
' *** CHANGE the file location below to match
' the location of your MDE after you create it.
Set db = DBEngine(0).OpenDatabase("C:\MyDatabases\PCTL.mde")
db.Properties("AllowByPassKey") = False
db.Close
Set db = Nothing
End Sub

Function ChangeProperty(strPropName As String, _
varPropType As Variant, _
varPropValue As Variant) As Integer
Dim dbs As Object, prp As Variant
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

Note that you have to fix the reference to "C:\MyDatabases\PCTL.mde" to use the path where you store your mde after you create it.

Compile and save the above code in PCTL.mdb. Go to the Immediate Window (CTRL+G) and type:

ClearBypassProperty

… and press Enter. That will define the property in your database, but set it to True to allow you to continue to use the Shift key.

Then create your mde, go back to your mdb and to the Immediate Window, and type:

SetBypassPropertyMDE

… and press Enter. That will set the property to FALSE in the mde that you give your boss.

He won't be able to hold down SHIFT when opening the database, so your Startup properties are protected. With Use Access Special Keys cleared, he won't be able to use F11, either.

ztbl must remain in the front end because it's a "working" table that my code uses for each report. If you share that table and two people run the report at the same time, one will overwrite the other, and one of the users will get the wrong data. Designing a "working" table like this is quite common for complex reports, but it shouldn't be shared - each user must have his or her own copy. Notice that there are no relationships defined for this table.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 28, 2015, at 5:56 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
This indicates clearly to me that in future i don't have to give him back end but only the front end and .mde if i create a new one.

But here i have a concern, as once by mistake i hide my Database Window of front end and you told me the way how to re-open Database Window. Now if i am going to give him front end with Database Window hidden and by some means he knows how to open Database Window he will have the access to my all coding.

Honestly saying i don't want to give my coding to him because unfortunately we have different culture and approach. If he gets the code then at any time he may fire me and hire some other guy on low package. Is there any other security and safe side for me ? And hope you can understand my point and position.

Coming to your comment:
-> you have to give him a way in the app to open the form that drives the report.
I have a form Main Menu in my app which opens all forms and reports and ultimately returning to Main Menu.

One last question that why you insisted to put ztbl in front end, as my all other tables are in back end and linked in front end. Also the Relationships are defined in back end.

Regards,
Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

You're going to have to give him the front end to be able to use the new report, so yes, you should create a new .mde to give to him - that includes the ztbl for the report. If you hide the Database Window, you have to give him a way in the app to open the form that drives the report.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 27, 2015, at 1:36 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
As i mentioned you earlier after updation / new entries particularly in Collection table i give PCTL_be to my boss to view updated fresh reports, PCTL.mde file is already on his computer. If there is any change in program i create new .mde and overwrite on my boss's computer.

Now as you are insisting me to keep "That table must remain in the front end - it is not sharable." Would i have to give him front end. Also after making .mde from Tools->Start up-> Display Database Window i un-check this box.

Then i future when i give front end to my boss do i uncheck Display Database Window for his copy ?

OR there is some other way/rule?
Please guide this is a new situation for me.

Note: now the report is perfect and OK i have no problems.

Regards,
Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

That table must remain in the front end - it is not sharable.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 27, 2015, at 6:12 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Thank you for writing down the list. I had already imported these items in PCTL.mdb

One thing may be i am wrong, should the ztblKhalidReport be imported in PCTL_be and then from PCTL.mdb it should be linked?

Regards,
Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Here's the list again:

ztblKhalidReport
qryKhalid
qryKhalid_DH
frmRunCrosstab
frmRunCrosstabJV
rptConsignmentCrosstab
rptKhalidMT
srptConsignmentNumberHeadings
Module1

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 26, 2015, at 8:08 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
I did as you said.

(By the way where are the items listed "and import the 9 objects I listed below from the database you downloaded from our group.")

It's late night here i will do my necessary formatting tomorrow morning and let you know the progress.

In the mean while take care & thanks.

Regards,
Khalid