Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-18-2018, 09:25 PM   #16
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Also there is a field in the Form that is called me.txtReportType where the name of the report is populated.

MITSupport is offline   Reply With Quote
Old 02-18-2018, 09:29 PM   #17
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

There is a field in the query that has the report type data in it. Also in the original code there was reference to the me.txtReportType as this is on the form that is used to print the different reports from the data in the recordset. I hope that this explains where the data is coming from
MITSupport is offline   Reply With Quote
Old 02-18-2018, 11:13 PM   #18
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Hi again Mark

I am at a loss as to where and how to create the Subroutine you mentioned.

I used the code that you had in place with the message box
Code:
 Public Sub btnPrint_Click()
    MsgBox "are you sure you want to print to MS Word", vbOKOnly
    ExportToWord.ExportToWord
    CallASubRoutine Me.txtReportType
End Sub
When I click on the button the msgbox does not appear and I am not sure if what i did was correct at all. If i put the full name into the code it errors out with an expected = error and that is confusing me.

Thanks for all your help with this Mark. I really do appreciate it.

TIA

Allan

MITSupport is offline   Reply With Quote
Old 02-19-2018, 11:35 AM   #19
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Who wrote all that code in the database you posted? Not you?
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 02-19-2018, 02:33 PM   #20
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Yes mark i wrote most of the code and googled other snippets of code. I am at a loss because this has gone deeper than I ever thought it would.
MITSupport is offline   Reply With Quote
Old 02-19-2018, 04:19 PM   #21
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
If the button click is not firing at all, then open the form in design view and look at the property sheet of the button on the 'Event' tab. Look for the On Click property, and make sure it is set to "[Event Procedure]" (without the quotes). That tells Access that button is supposed to raise events.

After that, check the routine name that is supposed to handle that click event. It should have a signature like this...
Code:
Private Sub ButtonName_Click()
...and if you put a message box in that event and click the button, you should see the MsgBox as evidence that the click is actually handled in code...
Code:
Private Sub cmdPrint_Click()
    MsgBox "cmdPrint_Click executed successfully"
End Sub
Once that is confirmed, try to call the routine ExportToExcel. To do so you need to pass it a parameter, presumably txtReportType, a value in a textbox. I have not confirmed that this is true, but that was the name of the object in the code in your database.
Code:
Private Sub cmdPrint_Click()
    ExportToExcel.ExportToExcel Me.txtReportType
End Sub
Does that make sense? Do you see how each of those steps is essential in the chain of things that happen following the button click?

hope that helps,
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 02-19-2018, 06:11 PM   #22
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Hi Mark

Yes that makes perfect sense. I have done the steps you mentioned above and yes the button works but when i change the code to call the module and routine it comes up with " Compile Error: Method or data member not found"

Here is the code that i am using and it is looking at the module and the sub with in the module.
Code:
 Private Sub btnPrint_Click()
    ExportToWord.ExportToWord Me.txtReportType
End Sub
Is there something i am doing wrong? I have gone through the code and rename the sub as a different name and it still comes up with the same error. So i changed it back and tried to run it again and still the same error.

Thanks for your help Mark. I greatly appreciate it.

Allan

MITSupport is offline   Reply With Quote
Old 02-19-2018, 06:30 PM   #23
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Hi again Mark

After some playing around with the button function and the name of the code behind it i have got it to work so far through.

There is now an error saying "Compile Error: Wrong Number of arguments or invalid property assignment"

The section of code that it has highlighted is

Code:
 wDoc.Bookmarks("CableNumber").Range.Text = Nz(!CableType, !Prefix, !Type, "")
The Nz was highlighted.

So i am guessing that the amount of data i need it put in is too much or i have coded it wrong with the selection.

Thanks for all your help Mark

Allan
MITSupport is offline   Reply With Quote
Old 02-19-2018, 06:32 PM   #24
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Here is the full code for the button:

Code:
Private Sub btnPrint_Click()
    PrintReport Me.txtReportType
End Sub
And the Routine
Code:
Public Sub PrintReport(ReportType As String)

    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim fn As String
                          
    Select Case ReportType
        Case "Power"
            fn = "Core Power Cable.dotx"
        Case "Control"
            fn = "Core COntrol Cable.dotx"
        Case "Earth"
            fn = "Core Earth Test Sheet.dotx"
        Case "Motor"
            fn = "Core Test Motor Test Sheet.dotx"
        Case "Instrument"
            fn = "Core Instrument Test Sheet.dotx"
        Case "ITP"
            fn = "Core Earth Test Sheet.dotx"
        Case "Single Phase"
            fn = "Core Single Phase Power Cable.dotx"
        Case "Three Phase"
            fn = "Core Three Phase Power Cable.dotx"
        Case "IS"
            fn = "Core Intrinsically Safe Cable.dotx"
    End Select
    fn = "C:\QA\Templates\" & fn
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open(fn)
        
    With CurrentDb.OpenRecordset("qryReports")
        Do While Not .EOF
            wDoc.Bookmarks("Project").Range.Text = Nz(!Project, "")
            wDoc.Bookmarks("Location").Range.Text = Nz(!Location, "")
            wDoc.Bookmarks("CableNumber").Range.Text = Nz(!CableType, !Prefix, !Type, "")
            wDoc.Bookmarks("Origin").Range.Text = Nz(!OriginZone, "")
            wDoc.Bookmarks("Dest").Range.Text = Nz(!DestinationZone, "")
            wDoc.Bookmarks("Date").Range.Text = Nz(!DateChecked, "")
            wDoc.Bookmarks("CheckedBy").Range.Text = Nz(!CheckedBy, "")
            wDoc.Bookmarks("Comments").Range.Text = Nz(!Comments, "")
            wDoc.Bookmarks("Tool").Range.Text = Nz(!Certification, "")
        
            wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(!Project, ""))
            wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(!Location, ""))
            wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(!CableType, !Prefix, !Type, ""))
            wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(!OriginZone, ""))
            wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(!DestinationZone, ""))
            wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(!Date, ""))
            wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(!CheckedBy, ""))
            wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(!Comments, ""))
            wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(!Certification, ""))
            
            .MoveNext
        Loop
        .Close
    End With
    
    wDoc.Close True
    wApp.Quit
            
End Sub
MITSupport is offline   Reply With Quote
Old 02-19-2018, 06:56 PM   #25
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 205
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: Coding Help please

You have a syntax error in the Nz function.
Quote:
Code:
 wDoc.Bookmarks("CableNumber").Range.Text = Nz(!CableType, !Prefix, !Type, "")
You are trying to pass in 4 arguments, but it only takes 2.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
Old 02-19-2018, 08:01 PM   #26
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Agree with BigHappy. Look at the usage in other places you use Nz().
And good job solving all those other bits.
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 02-19-2018, 08:14 PM   #27
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Ok thanks guys

I will be playing with the coding this afternoon. will let yo9u know how i go

Thanks Mark and BigDaddy
MITSupport is offline   Reply With Quote
Old 02-19-2018, 08:23 PM   #28
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Hey Mark on further debug of the code the error changes at the
Code:
 Set wDoc = wApp.Documents.Open (fn)
The error comes up with a runtime error and it is saying that it is an invalid directory.

I am wondering that if in the previous code that tells the location or the filename of the templates are also called "fn" could that be creating a loop?
MITSupport is offline   Reply With Quote
Old 02-19-2018, 08:28 PM   #29
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

And thanks for the tip that it is only passing 2 arguments. I shortened the code to remove the !cabletype and the wildcard at the end of the of the lines

Thanks Guys
MITSupport is offline   Reply With Quote
Old 02-19-2018, 09:32 PM   #30
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 205
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: Coding Help please

insert this line immediately before the line that errors:
Code:
msgbox "Directory:" & fn

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Coding Prayder Modules & VBA 4 03-28-2014 03:23 PM
Need some help with coding Marinus Forms 10 03-22-2011 04:47 AM
Coding help please... WSC Modules & VBA 2 11-20-2006 10:53 AM
Which is a better way of coding? yhgtbfk General 6 10-18-2004 05:21 AM
VBA Coding StefanSch Forms 7 04-14-2003 08:52 AM




All times are GMT -8. The time now is 12:45 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World