Problems calling a function from a command button

[andy_25]

Hi,

I am trying to call the following function in a code module from a command button on a form:

Function PrintDoc()
   Dim WordObj As Object
   Set WordObj = CreateObject("Word.Application")
   WordObj.Documents.Open "C:\Project v0.1\PrintForm.docx"
   WordObj.PrintOut Background:=False
   WordObj.Quit
   Set WordObj = Nothing
End Function

I cannot work out how to run this by pressing a command button, nothing seems to work. I do not want to pass anything to it just run it as is.

I have looked around and it is suggested that just PrintDoc() should work but it is asking for a macro name :s

Any help please?

 

[SOS]

I have looked around and it is suggested that just PrintDoc() should work but it is asking for a macro name :s

Any help please?

If you have it in the event PROPERTY (and not the VBA window you need the equals sign):

=PrintDoc()

 

[andy_25]

If you have it in the event PROPERTY (and not the VBA window you need the equals sign):

=PrintDoc()

Private Sub Command233_Click()
=PrintDoc()
End Function

asks me for a macro name

 

[SOS]

Private Sub Command233_Click()
=PrintDoc()
End Function

asks me for a macro name

In the VBA window you do NOT use the = sign.

Did you put the PrintDoc code in a standard module? If not you will have to call it with the name of the form or report you put it on.

If you did put it in a standard module, hopefully you didn't name the module the same as the function (that will cause it to not work also).

 

[andy_25]

In the VBA window you do NOT use the = sign.

Did you put the PrintDoc code in a standard module? If not you will have to call it with the name of the form or report you put it on.

If you did put it in a standard module, hopefully you didn't name the module the same as the function (that will cause it to not work also).

The function is in a standard module named Module1

 

[MSAccessRookie]

The function is in a standard module named Module1

Is the Function defined as Public? Your original Definition does not define it as Public.

 

[SOS]

Is the Function defined as Public?

The function by default is public by means of using

Function PrintDoc()

It is only private if you add

Private Function PrintDoc()



I'm starting to wonder if it might be corruption. Try importing all into a brand new database shell and see if that fixes it.

 

[MSAccessRookie]

The function by default is public by means of using

Function PrintDoc()

It is only private if you add

Private Function PrintDoc()



I'm starting to wonder if it might be corruption. Try importing all into a brand new database shell and see if that fixes it.

Thanks for the clarification. I have always thought that it was the other way around.

 

[andy_25]

Can you not simply run a function? Do I have to declare it or something? In code I have seen they always seem to dim sone variables and use an IF statement before running a function.

 

[SOS]

Can you not simply run a function? Do I have to declare it or something? In code I have seen they always seem to dim sone variables and use an IF statement before running a function.

No, you should be able to. Can you post the db so we can see what might be the problem?

 

[andy_25]

No, you should be able to. Can you post the db so we can see what might be the problem?

I can't sorry due to the sensitivity of some of the data

 

[c_smithwick]

I think I understand the source of your confusion. Open the Properties window for your command button on your form, go to the Events tab and next to OnClick put =PrintDoc()

 

[MSAccessRookie]

Can you not simply run a function? Do I have to declare it or something? In code I have seen they always seem to dim sone variables and use an IF statement before running a function.

I think you should be able to. Let's assume that you have completed the Function Design and stored it properly. We can forget about any other VB code for a moment, and go back to the button on the form.

In the Properties sheet behind the form, look at the line that has the On Click Property. It proably has the phrase "[Event Procedure]". Change that line to say:

=PrintDoc()
​

If it turns out that this works for you, then you can remove the unused VB Code at a later time.

Note: c_smithwick and I said about the same thing, only I was just a little slower in posting. I believe that two votes makes it a good thing to try.

 

[andy_25]

Thats the problem I was putting it in the on click event code. Thanks a lot

 

[SOS]

So, I get to experience that I gave the correct answer in my first post but it took a while for it to get through.

 

[MSAccessRookie]

I can't sorry due to the sensitivity of some of the data

You can if you are willing to take the time to create another copy of the database using the Table structures ONLY and no data, and then quickly enter some dummy data that looks like real data. No harm no foul (unless you have a unique design that you are attempting to protect). Seeing a problem usually makes it easier to resolve.

 

[MSAccessRookie]

It looks like you have resolved the issue at hand, and posting your database is no longer required. Remember the trick about creating an empty database if it turns out that you have any additional issues.

 

[SOS]

And Bob Larson has a tool on his website which will clear your database quickly and efficiently. Just go to his download page and look for the database reset tool.

 

[andy_25]

So, I get to experience that I gave the correct answer in my first post but it took a while for it to get through.

Ooops apologies SOS. You even put it in capitals for me. I blame a long day