Declare Public

[Michael Abrams]

25 years doing this,........

Option Compare Database
Option Explicit

Public reportDir As String

Where do I find where Public is defined?

[HansV]

See Understanding scope and visibility and A matter of scope: visibility of code elements.

[Michael Abrams]

OK - I read both and still do not understand.
reportDir is used here

Code: Select all

strFileName = reportDir & "HMO ISSUES - LETTERS SENT & RESPONSES\SUPV ISSUES\OPEN ISSUES FOR BAY CLIENTS.xlsx"

I believe reportDir is a "path" to a server, but cannot see where it is defined.

This was created by the IT folks and handed to me. At some point I may need to change the server path.

[HansV]

To find where reportDir is declared, right-click in the word reportDir and select Definition from the context menu.
To find where reportDir is assigned and used, select reportDir, then press Ctrl+F to activate the Find dialog. The word reportDir should automatically be entered into the 'Find What' box.
Under Search, select the 'Current Project' option button, then click Find Next.

[Michael Abrams]

I may not be explaining this correctly, so please forgive me.
I am exporting a report to a specific server/drive.

Code: Select all

strFileName = reportDir & "HMO ISSUES - LETTERS SENT & RESPONSES\SUPV ISSUES\OPEN ISSUES FOR BAY CLIENTS.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OPEN ISSUES FOR BAY CLIENTS", strFileName, True

I need to know what directory reportDir refers to because it may change in the near future.

[Gasman]

Search for reportDir = in the whole project.
Could it come from a settings table?

[Michael Abrams]

Doing the search in the whole project just gives me the instances of reportDir in the code.

I'm probably going to get shamed for this, but what if I didn't mention this is a SQL front end?

[HansV]

That shouldn't matter - the VBA code lives in the frontend, not in the backend.
It's mysterious.

[Gasman]

Doing the search in the whole project just gives me the instances of reportDir in the code.

So one of those must have reportDir ?

If you walk the code what is it's value?

What is a SQL FE?

[HansV]

I assume that Michael meant that the Access database is a frontend for a SQL Server database.

[Michael Abrams]

Access front end to SQL backend.

I will try to walk the code.

(at least I used code tags when posting code snippets)

[Gasman]

Well if you cannot find it, upload the front end.
That cannot hold a proprietary data surely?

[Michael Abrams]

I tracked down the programmer. He's not a very patient person, but I too can be a PITA and I do not give up.

I will post when(if) I hear back from him.

Thanks Hans & Gasman for your continued support.