Coding Help please

[MarkK]

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...

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...

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.

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

 

[MITSupport]

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.

 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]

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

 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]

Here is the full code for the button:

Private Sub btnPrint_Click()
    PrintReport Me.txtReportType
End Sub

And the Routine

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

 

[BigHappyDaddy]

You have a syntax error in the Nz function.

 wDoc.Bookmarks("CableNumber").Range.Text = Nz([COLOR="Red"]!CableType, !Prefix, !Type, ""[/COLOR])

You are trying to pass in 4 arguments, but it only takes 2.

 

[MarkK]

Agree with BigHappy. Look at the usage in other places you use Nz().
And good job solving all those other bits.
Mark

 

[MITSupport]

Ok thanks guys

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

Thanks Mark and BigDaddy

 

[MITSupport]

Hey Mark on further debug of the code the error changes at the

 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]

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

 

[BigHappyDaddy]

insert this line immediately before the line that errors:

msgbox "Directory:" & fn

 

[MITSupport]

Hi BigHappyDaddy

This is where I inserted the line

   Set wApp = New Word.Application
    MsgBox "Directory:" & fn
    Set wDoc = wApp.Documents.Open(fn)

It still errors out on the line following saying invalid Directory.

Thanks for your help

Allan

 

[BigHappyDaddy]

But what was printed out on the message box? It should be the file path of the file, but I suspect it is not.

Sent from my SM-G900P using Tapatalk

 

[MITSupport]

Yes mate it did come up with the directory

 

[BigHappyDaddy]

Directory only, or did it include the file name?

 

[MITSupport]

It posted the directory only.

I have been trying to use the environ statement as well:

Original Code:

 fn = "C:\QA\Templates" & fn
 Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open(fn)

Test EnvironCode:

  fn = Environ("SystemDrive") & "QA\Templates" & fn
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open(fn)

Even with this code structure the database still comes up with:
Run-time error'5455':
The directory name isn't valid.
(C:\QA\Templates\)

and then on debug it highlights the

 Set wDoc = wApp.Documents.Open(fn)

Am i doing something wrong? I really cant find an answer for this conundrum and it is driving me crazy

Thanks for the help guys i really appreciate it

Allan

 

[BigHappyDaddy]

What exactly is in fn at the open command?

Sent from my SM-G900P using Tapatalk

 

[MITSupport]

It should be the complete file location:

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 = Environ("SystemDrive") & "QA\Templates" & fn
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open(fn)

So it should be opening "C:\QA\Templates\ and any of the files that were mentioned in the Case argument

I hope that this makes sense to you.

Thanks again

Allan

 

[MarkK]

No. Evaluate this...

? Environ("SystemDrive") & "QA\Templates"
 C:QA\Templates

That is not a valid folder spec.

    fn = Environ("SystemDrive") & "[B][COLOR="Red"]\[/COLOR][/B]QA\Templates[B][COLOR="Red"]\[/COLOR][/B]" & fn

See in red...?
This is what BigHappy has been saying with the MsgBox for the last few posts, check the value of fn. Is it a valid directory?

msgbox "Directory:" & fn

Test that value. Is it a valid path?
Mark

 

[MITSupport]

Hi Guys

I have put the msgbox code back into the routine above the line that was erroring out on:

fn = Environ("SystemDrive") & "\QA\Templates\"
    
    Set wApp = New Word.Application
    MsgBox "Directory:" & fn
    Set wDoc = wApp.Documents.Open(fn)

When it runs it pops up a message box with the just the directory name:

Directory:C:\QA\Templates\

The directory exists and it doesn't show the file that it is supposed to be using just the directory.

I thought the fn from the CASE argument was also supposed to be populated as well. I have a feeling that i am wrong with that idea.

Does this make sense to you because it doesn't make sense to me

Allan

 

[MITSupport]

Hi Guys

This is a snip from the the code that is msgbox section.

I am so sorry for being so dumb in vba (it has been a very long time since i have done anything with access vba)

Thanks for all your help. I really appreciate it

Allan