[RESOLVED] Where is common dialog?

[OllieVB]

I've just copied my Database to my laptop which is running the exact same version of MS Access (XP) and the common dialog control is missing.
Also, some other parts of my code aren't working. I'm getting errors with some declarations.
Hmmm. Frustrating...

[RobDog888]

You probably manually added the cdl to your toolbox. So that means you need to do the same for the copy on your other system. When you manually add references and controols you need to also include them with the database where ever your deploy it.

[OllieVB]

Thanks for that info. Just one problem... On the laptop, the ActiveX controls list doesn't include the common dialog control, even though it's an identical Office install. When I used it on my desktop PC, it was already in the list and ready to use. How do I find the control so I can copy it across with the database and can it be somehow embedded?
(I do not have the ability to 'pack and go' my database in runtime format and have to have Access installed on whichever PC is going to run it at the moment)

[RobDog888]

The commondialog control is included with VB 6 so does your development system have VB installed on it? That could be why its on one and not the other. You can download it from the VB6 runtime redistributables but then it will install all the runtimes, etc.

You can create a dialog of your own using VBA. .GetOpenFilename .GetSaveAsFilename

[dannymking]

Office XP also comes with built in fileopen commands, but you will need the reference to MS Office itself for it to work correctly..

FileDialog Property
Returns a FileDialog object which represents a single instance of a file dialog box.

expression.FileDialog(dialogType)

expression Required. An expression that returns one of the objects in the Applies To list.

dialogType Required MsoFileDialogType. The type of file dialog box.

MsoFileDialogType can be one of these MsoFileDialogType constants.

• msoFileDialogFilePicker

• msoFileDialogFolderPicker

• msoFileDialogOpen

• msoFileDialogSaveAs

Example
This example displays the Save As dialog box.

Dim dlgSaveAs As FileDialog
 
Set dlgSaveAs = Application.FileDialog( _
    FileDialogType:=msoFileDialogSaveAs)
 
dlgSaveAs.Show

This example displays the Open dialog box and allows a user to select multiple files to open.

Dim dlgOpen As FileDialog
 
Set dlgOpen = Application.FileDialog( _
    FileDialogType:=msoFileDialogOpen)
 
With dlgOpen
    .AllowMultiSelect = True
    .Show
End With

[OllieVB]

Thanks again guys for your help. Don't mean to drag this out, but can you specify a default filename for each of these?
... Sorted this bit now!

Also, is there a similar method for calling up the print dialog box?

I might be better off posting this in the database area but I thought I would give it a try here first as you've been so helpful!

[OllieVB]

I've done some digging in the MSDN library after these wise words but there' still something I just can't find. Here's my code using the FileDialog Object:

Private Sub Backup_Click()
 
        Dim fd As FileDialog
        Dim FileDest As Variant
     
        Set fd = Application.FileDialog(msoFileDialogSaveAs)
        FileDest = ""
        
        With fd
            .InitialFileName = "backup.mdb"
            .Title = "Backup records to..."
            .Show
            FileDest = .SelectedItems
        End With
 
        'Test for filename
        MsgBox "You selected: " & FileDest
 
End Sub

... but the .Selected Items is wrong. Can you tell me what is the correct way to get the full path and filename into the variant 'FileDest'?

[dannymking]

Quite simply add (1) at the end of SelectedItems

FileDest = .SelectedItems(1)

You can leave the .InitialFileName blank to force your users to specify a filename as well as the filepath.

[dannymking]

Incidently to display the Print Dialog so that your users can specify where to print to you can use..

DoCmd.RunCommand acCmdPrint

However it's not that simple.. It will depend on what you are trying to print.

I tend to use a sub procedure in a module that is called from anywhere within the database.. this then sets a flag for the report to specify whether or not to print or display..

Sub NewPrintingMethod(strReportName As String)
  'Procedure will attempt to open the requested report for printing, allowing the user the option of choosing their printer before printing the report
  On Error GoTo NewPrint_Err
  DoCmd.OpenReport strReportName, acViewPreview
  On Error GoTo 0
  If ReportPrinted = False Then
    If NoRepData = False Then
      If vbYes = MsgBox("You have not printed this report, do you want to try again?", vbCritical + vbYesNo, "REPORT NOT PRINTED") Then
        NewPrintingMethod strReportName
      End If
    Else
      NewPrintingMethod strReportName
    End If
  End If
  Exit Sub
  
NewPrint_Err:
  'report contained no data try again
  On Error GoTo 0
  NewPrintingMethod strReportName
  Exit Sub
End Sub

This is called with the name of the report to be printed.. the PrintingReport and NoRepData are two Public Defined Boolean Variables in the Same module. PritingReport is set to True and instead of opening the report for print view this sub is called like so..

PrintingReport = True
  NewPrintingMethod "ReportName"
  PrintingReport = False

Behind the report is the following code that handles whether or not the report is to be printed or to allow normal view..

Private Sub Report_Activate()
  NoRepData = Not HasData
  If NoRepData Then Exit Sub
  If Not PrintingReport Then
    DoCmd.Maximize
    Exit Sub
  End If
  On Error GoTo Err_Report_Activate
  DoCmd.RunCommand acCmdPrint
  ReportPrinted = True
  DoCmd.Close acReport, Me.Name
  Exit Sub
  
Err_Report_Activate:
  ReportPrinted = False
  DoCmd.Close acReport, Me.Name
  On Error GoTo 0
  Exit Sub
End Sub
Private Sub Report_NoData(Cancel As Integer)
  NoRepData = True
  Cancel = True
End Sub

The NoRepData is handled inside the Report_NoData Event like above.. this forces the printing method to try again.. To stop this from happening I normally add a check on the report's source data before calling this sub to ensure that there is some data in the table/query for the report..

Hope this helps.

[OllieVB]

Danny,

Two things...

Firstly, I'm afraid that putting (1) after the .SelectedItems didn't work. It just gave the same error!

Secondly, I've already got the code to print and preview the report. So I don't start throwing out loads of paper, does you single line of code make the printer dialog appear before printing?

Why does all this stuff have to be so complicated

[dannymking]

Hmmm

Should work with the (1).. if not try .SelectedItems.Items(1), or if using option base 0 then you will need to use (0)..

Or if in doubt just do

Dim i
'rest of your code
For i = 1 to .SelectedItems.Count
  Msgbox .SelectedItems.Item(i)
Next i

The single line of code will not work in access as you will need to open the report first.. You can place that single line into the report_Activate event, but it will display the print dialog even when opening the report in page preview mode..

[OllieVB]

Danny,

Finally got it working as you suggested. It was all down to one little word missing...

In your posting, you said I needed to put (1) after the .SelectedItems, but actually it needed .Item(1) after it! Still, I wouldn't have got there if it wasn't for your invaluable help so thanks very much for that.