Microsoft Access cannot find the object 'I1'

[Gismo]

Hi all,

Think I am missing something silly here

'DoCmd.OutputTo acReport, ("DAW Sheet - Comm Approve"), acFormatPDF, vReportPDF
DoCmd.OutputTo acReport, ("DAW Sheet - Comm Approve" & " - Registration -" & " " & Forms![CS Raised - Mail]![RegCBO]), acFormatPDF, vReportPDF

The first code works fine but when I add detail from the form, I get an error
Please could you advise

 

[theDBguy]

What is the value in your Combobox? Do you actually have a report with that long, concatenated name?

 

[June7]

There is no need for those parentheses.

Could reduce the concatenation.

However, as far as I can see, syntax should work. So what is value of combobox? Is this code behind the referenced form?

DoCmd.OutputTo acReport, "DAW Sheet - Comm Approve - Registration - " & Forms![CS Raised - Mail]![RegCBO], acFormatPDF, vReportPDF

 

[Gismo]

What is the value in your Combobox? Do you actually have a report with that long, concatenated name?

The value in the combo box is usually a standard format XXX-00 from the form "CS Raised - Mail"
Control is RegCBO

The report name is DAW Sheet - Comm Approve
Then I need to add the registration from the form

"DAW Sheet - Comm Approve - Registration XXX-00.PDF"

 

[Gismo]

There is no need for those parentheses.

Could reduce the concatenation.

However, as far as I can see, syntax should work. So what is value of combobox? Is this code behind the referenced form?

DoCmd.OutputTo acReport, "DAW Sheet - Comm Approve - Registration - " & Forms![CS Raised - Mail]![RegCBO], acFormatPDF, vReportPDF

There is no code behind the combobox

 

[Gasman]

So you are trying to output a report that does not exist????

 

[Gismo]

So you are trying to output a report that does not exist????

The report name is "DAW Sheet - Comm Approve"

When using the below code, it works well

DoCmd.OutputTo acReport, ("DAW Sheet - Comm Approve"), acFormatPDF, vReportPDF

 

[theDBguy]

The report name is "DAW Sheet - Comm Approve"

When using the below code, it works well

So, that's why you're getting the error. You are concatenating the combo in the wrong argument. Are you trying to assign a file name to the output using the value in the combo?

 

[Gismo]

So, that's why you're getting the error. You are concatenating the combo in the wrong argument. Are you trying to assign a file name to the output using the value in the combo?

The file name is exactly as it should be

Not sure I understand what you mean by assigning a file name to the output using the combo value
is that not exactly what i need to do?

 

[June7]

Explore the arguments of OutputTo method. One of them is to allow specifying a name to assign file. Your code is concatenating the report name, not the destination file name. Since your code was not providing a file name, the wizard used the report name constructed, which is not a valid report name.

DoCmd.OutputTo acReport, "DAW Sheet - Comm Approve", , "DAW Sheet - Comm Approve - Registration - " & Forms![CS Raised - Mail]![RegCBO], acFormatPDF, vReportPDF

 

[theDBguy]

Not sure I understand what you mean by assigning a file name to the output using the combo value
is that not exactly what i need to do?

Yes, except you were doing it in the wrong place. Please follow @June7's example. Cheers!

 

[Gismo]

Explore the arguments of OutputTo method. One of them is to allow specifying a name to assign file. Your code is concatenating the report name, not the destination file name. Since your code was not providing a file name, the wizard used the report name constructed, which is not a valid report name.

DoCmd.OutputTo acReport, "DAW Sheet - Comm Approve", , "DAW Sheet - Comm Approve - Registration - " & Forms![CS Raised - Mail]![RegCBO], acFormatPDF, vReportPDF

Ah yes, I see my mistake
I need to specify the report name then give the file name with the detail from the form
Understood

But then an error "an expression you entered is the wrong data type for one of the arguments" pops up

 

[June7]

Sorry, acFormatPDF in wrong position. As I said, examine the arguments, heed the popup tips.

DoCmd.OutputTo acReport, "DAW Sheet - Comm Approve", acFormatPDF, "DAW Sheet - Comm Approve - Registration - " & Forms![CS Raised - Mail]![RegCBO]

I don't know what vReportPDF is for.

 

[Gismo]

Sorry, acFormatPDF in wrong position. As I said, examine the arguments, heed the popup tips.

DoCmd.OutputTo acReport, "DAW Sheet - Comm Approve", acFormatPDF, "DAW Sheet - Comm Approve - Registration - " & Forms![CS Raised - Mail]![RegCBO]

I don't know what vReportPDF is for.

I also tried the acFormatPDF in this positin, but then the "file save as" window does not pop up

DoCmd.OutputTo acReport, "DAW Sheet - Comm Approve", acFormatPDF, "DAW Sheet - Comm Approve - Registration - " & Forms![CS Raised - Mail]![RegCBO]

I looked at the the argument for OutputTo but seems to confuse me with my configuration required

The file is exported but does not provide a option to select the location

 

[June7]

It will save to a default location, probably user's Documents folder unless you provide a specific folder path as part of the destination file name. This path can be hard-coded string or can concatenate the db frontend location with CurrentProject.Path or to give user control over location, use FileSystemObject dialog to allow them to choose folder and use that choice in code.

I forgot about concatenating the .PDF suffix.

Example:

CurrentProject.Path & "\DAW Sheet - Comm Approve - Registration - " & Forms![CS Raised - Mail]![RegCBO] & ".pdf"

 

[Gismo]

It will save to a default location, probably user's Documents folder unless you provide a specific folder path as part of the destination file name. This path can be hard-coded string or can concatenate the db frontend location with CurrentProject.Path or to give user control over location, use FileSystemObject dialog to allow them to choose folder and use that choice in code.

I forgot about concatenating the .PDF suffix.

Example:

CurrentProject.Path & "\DAW Sheet - Comm Approve - Registration - " & Forms![CS Raised - Mail]![RegCBO] & ".pdf"

When I use the below code, I get the Output To popup window to save to a selected location. I would like to use the same principal.
The report name is autmatically populated
all I need is to add the registration from the combo box on the form to Read "DAW Sheet - Comm Approve - Registration XXX-OO

DoCmd.OutputTo acReport, ("DAW Sheet - Comm Approve"), acFormatPDF

 

[June7]

You get popup because the destination name argument is blank. If you want combobox value, can't use this dialog. Must provide file path/name argument.

As I said, if you want user to pick a location, use FileSystemObject FolderPicker. Here is one reference https://analystcave.com/vba-application-filedialog-select-file/

 

[Gismo]

You get popup because the destination name argument is blank. If you want combobox value, can't use this dialog. Must provide file path/name argument.

As I said, if you want user to pick a location, use FileSystemObject FolderPicker. Here is one reference https://analystcave.com/vba-application-filedialog-select-file/

I will have a look at your reference

Below is a code I use to transfer a spreadsheet using the file save as popup
works fine
dont know how to code it to export to pdf though

Dim StrFileName As String
Dim StrQryName As String
Dim StrSaveFile As String


StrFileName = strGetFileFolderName("Commercial Notification List" & " - Registration -" & " " & Forms![CS Raised - Mail]![RegCBO], 2, "Excel")
'Debug.Print StrFileName
StrQryName = "Commercial Notification List"


If Len(StrFileName & "") < 1 Then
StrFileName = Application.CurrentProject.Path & "\Commercial Notification List" & Format(Date, "yyyymmdd") & ".xls"
End If

DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrFileName, True, , True

 

[Gasman]

Ah yes, I see my mistake
I need to specify the report name then give the file name with the detail from the form
Understood

But then an error "an expression you entered is the wrong data type for one of the arguments" pops up

Why not use intellisense? , that is what it is there for?

 

[June7]

I will have a look at your reference

Below is a code I use to transfer a spreadsheet using the file save as popup
works fine
dont know how to code it to export to pdf though

strGetFileFolderName must be a VBA custom function that calls the FileSystemObject.
It would probably work the same way to build path for OutputTo.
Would have to see the code.