putting database name on reports (1 Viewer)

[Pvarga]

I have 6 users that I maintain DB's for. If I want to change a report I have to copy it into each and change the name of that location each time. Is there a way to use a command like you do with dates to have the file name in the header automatically?

Thanks

 

[ChrisO]

Option Compare Text
Option Explicit


[color=green]'   With the Text Box Control Source set as: -
'
'   [color=blue]=GetCurrentDBName()[/color]
'[/color]
Private Function GetCurrentDBName() As String

    [color=green]' This will return the full Path and Name.[/color]
    GetCurrentDBName = CurrentDb.Name
    
    [color=green]' This will return just the Name of the file.[/color]
    GetCurrentDBName = Dir(CurrentDb.Name)
    
    [color=green]' This will strip the file extension.[/color]
    GetCurrentDBName = Left$(Dir(CurrentDb.Name), Len(Dir(CurrentDb.Name)) - 4)

End Function

Hope that helps.

Regards
Chris

 

[Pvarga]

It sounds likt it will but I must be missing something. I get an error saying Access does not recognize =GetCurrentDBName()

Thanks for your time

 

[Calvin]

Try inserting ChrisO's code into a public module and change the declaration "Private Function" to "Public Function" and you should be able to use it for other things in addition to just the one report.

 

[ChrisO]

The only reason I can see that it might have failed is that the Function is in a Global Module.
Changing Private to Public should fix it or place the Function, as is, in the Class Module behind the Report.

However, Calvin is correct.
It would be more useful in a Global/Public Module so we could add a little functionality and call it from anywhere.

Try this in a Global Module. You may wish to add anything else you see fit.

Option Compare Text
Option Explicit


Sub Test()

    MsgBox GetCurrentDB("Path")
    MsgBox GetCurrentDB("Name")
    MsgBox GetCurrentDB("FullName")
    MsgBox GetCurrentDB("PathAndName")
    MsgBox GetCurrentDB("Rubbish")

End Sub


Public Function GetCurrentDB(ByVal strArgument As String) As String

    Select Case strArgument
           
           Case "PathAndName"
                GetCurrentDB = CurrentDb.Name
                
           Case "FullName"
                GetCurrentDB = Dir(CurrentDb.Name)
                
           Case "Name"
                GetCurrentDB = Left$(Dir(CurrentDb.Name), Len(Dir(CurrentDb.Name)) - 4)
                
           Case "Path"
                GetCurrentDB = Left$(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
                
           Case Else
                GetCurrentDB = "Invalid argument to Function GetCurrentDB(" & Chr$(34) & strArgument & Chr$(34) & ")"

    End Select
    
End Function

Hope that is more useful.

Regards
Chris

 

[Pvarga]

Thanks for the help but unfortunately you are all WAY over my head. I have never used a modual. I was thinking I could put this in box on the report so that I do not have to update 4 times for 6 databases. What exactly would I do ceate a module and then just put the name of it in the box on my report.

Sorry to be so slow.

 

[Calvin]

From the database window, the box that lets you select and open Tables, Queries, Forms, Reports, Macros and Modules, Select Modules and select the New button, it will open a new window where you enter VBA code that is blank except for one or two lines of code at the top.
Copy and past ChrisO's last code sample into the module and save it, the name of the module you save it as is not really important at this time, so anything will do.

Now on the Report, select the textbox that you want to display the database name, open the properties window and in the Control Source property, enter the following:

=GetCurrentDB("FullName")

now save and open the report and the report should now display the database name in the text box.

 

[ChrisO]

Have a play with the attachment and see how it works.
Any questions… just fire away.

Regards
Chris

 

[Pvarga]

Much thanks to you both. I will try it when I get to work in the morning and let you know.

Since you were so kind to answer this may I ask you about another post that has had no replies. I want to protect my coding on queries and reports when I put my combine DB on the server.

Can I do this without having to give others passwords?

Sorry to double ask but I am off to bed and the other had no replies.

Thanks for your kindness.

I keep wanting to study and learn more but the need to do something comes before I get a chance.

 

[Calvin]

Yes, it's possible but enter the link for that thread and I will post my reply there as not to confuse this thread with a second issue.

 

[Pvarga]

Thanks so mush.

This is exactly what I need.

I will now read the security stuff.

Have a great day!