Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 17th, 2006, 09:56 AM
Authorized User
 
Join Date: Oct 2005
Location: South Ockendon, Essex, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default Application Title

I have an access '97 database at work which is split across several front ends and one main database. I have a table within the database which is used as an activity log. I have this working finr for nearly everything I want to log. However I'm trying to add a new item to the log. What I want to do is everytime someone logs on I want to record the name of the particular front end they are using. For example I have a front end called "SC Claims DB" and one called "Confirmations DB". These are also the Application Titles that have been set on the Start up options. What I need to do is find a way to return these values through a piece of VB code so it can be added to a string for the log.

I have found several references on how to change the Application Title with VBA but nothing on how to retrieve the application title. Can anyone suggest a method for me. Many thanks.

Fig
Reply With Quote
  #2 (permalink)  
Old August 17th, 2006, 01:58 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You could have the front end store just a text string as a Global containing the name. I don't know how you'd access the App Title though, so this would just be a hardcoded workaround assuming there's not an unwieldy number of front ends.

Reply With Quote
  #3 (permalink)  
Old August 17th, 2006, 02:19 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This code works to return the filename of the database including path:

'------------------
Dim sAppName As String

sAppName = Application.CurrentDb.Name

MsgBox sAppName
'-------------------

If you set the value for the variable and return that, you could break off the filename from the end of the string. Or you could just log what was returned.



mmcdonal
Reply With Quote
  #4 (permalink)  
Old August 17th, 2006, 02:22 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Ooops, found it messing around:

sAppName = Application.CurrentProject.Name

This returns the filename only.


mmcdonal
Reply With Quote
  #5 (permalink)  
Old August 17th, 2006, 06:18 PM
Authorized User
 
Join Date: Oct 2005
Location: South Ockendon, Essex, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

Thanks guys. Seems funny as the application title is stored as a property of the database that you cant dirrectly access it. However as I have the name of the application the same as the file name your solutions should work. Thanks again.


Fig
Reply With Quote
  #6 (permalink)  
Old August 18th, 2006, 03:53 AM
Authorized User
 
Join Date: Oct 2005
Location: South Ockendon, Essex, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

Grrrr. Just tried the solution you suggested and it does not seem to be supported in Access'97. Name works with the full file path but the CurrentProject doesnt.


Fig
Reply With Quote
  #7 (permalink)  
Old August 22nd, 2006, 05:57 AM
Friend of Wrox
 
Join Date: Dec 2005
Location: CHENNAI, TAMILNADU, India.
Posts: 233
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to anukagni Send a message via Yahoo to anukagni
Default

what this look like I cont understand what u the guys are talk pls explain me...

 How and where the VBA codes are using ..

Learn as you can..
------------------------
pap...
Reply With Quote
  #8 (permalink)  
Old August 22nd, 2006, 06:23 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Figgis, the quick and dirty way to do this to make sure it works is to create a hidden table with the values you need to push to your transaction log. Then when the database opens (main form on open event) open a hidden form based on the hidden table.

Let's say your table as a field AppName. Open a hidden form (Form1) that has AppName (one record in the table).

Now when you need to pass the app name from each front end to the log, take the name like this:

sAppName = [Forms]![Form1].[AppName]

You can of course store other values in the hidden table and take them from the form. Use the same table and hidden form and code in each front end, just change the values in the table. The table should have only one record.

Does this help?

Anukagni, use the code on any event where you need to take the app name using VBA. For example, if I wanted to store the app name in a table when a record was created in a form, I would use the Before Insert event of the PK field on the form (should be hidden) with this code (app name field is also on the form - hidden):

'-------------------------
Before Insert PK

Dim sAppName As String

sAppName = Application.CurrentProject.Name

Me.AppName = sAppName
'----------------------------

This would put the name of the app in each record as it is created using the form. There are other ways you can use this value. For example in access control (security). Wrap all your update or delete functions in a conditional and check the appname before each transaction. If the wrong app is being used, then prevent the transaction. Give a reader app to some users, and a data entry/management app to others who can update or delete records.

For example - prevent deletions:

'--------------------------
Button_OnClick to delete

Dim sAppName As String
Dim sAdminAppName As String

sAdminAppName = "Admin_Application.mdb"

sAppName = Application.CurrentProject.Name

If sAppName = sAdminAppName Then
   'Allow transaction
   Else
   MsgBox("You do not have the administrative rights to perform this action.")
End If
'----------------------

Something like that. Of course a better thing is to not allow these transactions if it is the wrong user. You might create a database with the Admin_App.mdb name, and then put code on each form to hide buttons if the app name were wrong:

'-------------------------
Form On Current
If Application.CurrentProject.Name <> "Admin_App.mdb" Then
   Me.Button1.Visible = False
   Else
   Me.Button1.Visible = True
End If
'------------------------

This way you could send around a front end, and limit functionality merely by changing the name of the file. You have to plan this sort of coding though. I use it in place of Access security lots of times.

Does this help?


mmcdonal
Reply With Quote
  #9 (permalink)  
Old August 23rd, 2006, 04:01 AM
Authorized User
 
Join Date: Oct 2005
Location: South Ockendon, Essex, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

Hi,

I managed to figure a way of doing what I needed by using part of the suggestion above. I manage security on the database by having a menu form. All I do for the various front end versions is to delete the buttons from the menu form that I dont want them to have access to. As the front ends are located on departmental share drives other departments do not have access to them. There are only 1 or 2 people that have the full menu. I remove access to the tables and queries ect by taking them out on the startup options.

I have the various items that I want the logging for on a table with a yes no field. I then do a query within the code to check if logging is required and if it is I build a simple string to insert into the log table. To do what I originally started this topic on I have added the code to the "on open" event on a splash screen. The code is probably a bit long winded but it works which is all I wanted. I have put the entire code below:-

    Dim Conn As Database ' Variable to reference database
    Dim ssql As String, qdf As QueryDef ' Variables for creating containing SQL and Query instruction
    Dim RptName As String ' Variable for report name and reused for text string in SQL
    Dim ctrl As Recordset ' Variable used for holding record set for logging requirement
    Dim tst As Integer ' Variable used to test if logging is required
    Dim tmp As String ' Variable for creating SQL string for use with ctrl record set
    Dim Tmp2 As String ' Variable for creating query name for insert query for logging
    Dim Tmp3 As String ' Variable for SQL string for use with ctrl2
    Dim ctrl2 As Recordset ' Variable for holding record set to get version number
    Dim ver As String ' Variable for holding version number
    Dim Tmp4 As String ' Variable for holding full file path to front end version
    Dim ltr As Integer ' Variable for calculating position of first letter of name
    Dim Tmp5 As String ' Variable used for reducing file name down to end version name
    Dim ltr2 As Integer ' Variable used to find position of folder separators in file name.


    'Set initial variables

    RptName = "'logon'"
    Set Conn = CurrentDb
    Tmp4 = Conn.Name
    ltr2 = 7 ' Initially set to any possitive number or first test in loop fails.

    ' Reduce file path down to file name

    Do While ltr2 <> 0

        ltr2 = InStr(1, Tmp4, "\", vbTextCompare)
        ltr = (Len(Tmp4)) - ltr2
        Tmp5 = Right(Tmp4, ltr)
        Tmp4 = Tmp5

    Loop

    'Test if logging is require and if so create log entry

    tmp = "SELECT * FROM tblOBJ_Control WHERE (((tblOBJ_Control.Obj_Name)=" & RptName & "))"
    Set ctrl = Conn.OpenRecordset(tmp, dbOpenSnapshot)
    tst = ctrl.Fields("Log_Required")

    If tst = -1 Then

    Tmp3 = "SELECT * FROM Version"
    Set ctrl2 = Conn.OpenRecordset(Tmp3, dbOpenSnapshot)
    ver = ctrl2.Fields("Version_Number")

    Tmp2 = Environ("UserName") & "Inslog"
    RptName = "'" & "Logged on using " & " " & Tmp4 & " version " & ver & "'"
    Debug.Print RptName
    ssql = "INSERT INTO tblLog ( [Date], Activity, User, Description )SELECT Now() AS Expr1, 'Logon' AS Expr2, Environ('UserName') AS Expr3," & RptName & " AS Expr4;"
    Set qdf = Conn.CreateQueryDef(Tmp2, ssql)

    DoCmd.SetWarnings (False)
    qdf.Execute
    DoCmd.SetWarnings (True)
    DoCmd.DeleteObject acQuery, Tmp2
    End If
End Sub


As I say, probably not the tidiest piece of code but it does work and if it helps anyone else then thats great too.


Fig
Reply With Quote
  #10 (permalink)  
Old September 25th, 2006, 12:59 AM
JIX JIX is offline
Registered User
 
Join Date: Sep 2006
Location: Springfield, MO, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Use this to get to the application title:

CurrentProject.Properties("AppTitle")

Should work just fine!
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Get Order By Title ssomchai BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 April 3rd, 2008 09:13 PM
Is there a way to style the application title? Nadine Access 2 February 22nd, 2007 11:07 AM
Report Title shanib Crystal Reports 1 October 7th, 2005 02:10 PM
Name or Title charlie3 VBScript 2 July 8th, 2003 05:00 PM



All times are GMT -4. The time now is 06:34 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.