Coding Help please

Hi Guys

I have been playing and created a button with the following code:

Code: 
Sub RetriveFolder()
 
    Dim myfolder As String
    Dim Fldr As String
    
    myfolder = "C:\QA\Templates\"
    Fldr = Dir(myfolder, vbDirectory)
    
    If Len(Fldr) > 0 Then
      MsgBox (Fldr & " Already Exists")
    Else
      MkDir myfolder
      MsgBox ("Folder Created")
    End If
    
End Sub

When this runs it replys with "Already Exists"

now when we run the PrintReport routine it still errors out on the :

Code: 
 Set wDoc = wApp.Documents.Open(fn)

What am i doing wrong

Allan
 
If this is your code, you are missing a slash after "Templates". See below.

fn = Environ("SystemDrive") & "QA\Templates" & fn

Sent from my SM-T530NU using Tapatalk
 
Sorry, my post was way late!

Sent from my SM-T530NU using Tapatalk
 
Hi Guys

I have worked it out to a point. Here is the revised code that is working.

Code: 
Option Compare Database
Option Explicit

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 = "C:\QA\Templates\Core Power Cable.dotx"
        Case "Control"
            fn = "C:\QA\Templates\Core COntrol Cable.dotx"
        Case "Earth"
            fn = "C:\QA\Templates\Core Earth Test Sheet.dotx"
        Case "Motor"
            fn = "C:\QA\Templates\Core Test Motor Test Sheet.dotx"
        Case "Instrument"
            fn = "C:\QA\Templates\Core Instrument Test Sheet.dotx"
        Case "ITP"
            fn = "C:\QA\Templates\Core Earth Test Sheet.dotx"
        Case "Single Phase"
            fn = "C:\QA\Templates\Core Single Phase Power Cable.dotx"
        Case "Three Phase"
            fn = "C:\QA\Templates\Core Three Phase Power Cable.dotx"
        Case "IS"
            fn = "C:\QA\Templates\Core Intrinsically Safe Cable.dotx"
    End Select
        
    ' fn = Environ("SystemDrive") & "\QA\Templates\"
            
    Set wApp = New Word.Application
    ' MsgBox "Directory:" & fn
    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(!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(!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

Now the problem has arisen in regards to the bookmark fields.

When the code is run it goes through and errors out on the first line of the bookmark coding.

The error is:
Run-time error '91':
Object variable or With block variable not set

In this instance was i supposed to define the variables for the bookmarks?

Thanks for all your help guys

Allan
 
Post your code in its entirety. I am seeing a lot of inconsistencies in your code snippets. I understand what you want, but I am a little lost in exactly how you are trying to accomplish it. I am certain you have a small typo or omission somewhere in your code.
 
Sorry late again!

I don't get into VBA for Word very much, but I believe you are correct. I think you need to "add" a bookmark before you try to define its name/content.
 
Hi Big

The names for the bookmarks are already set to the names listed in the code.

This has gotten way bigger than i have ever intended it to be

Allan
 
I have just started looking at it, but already found something unexpected. When I manually open the form "Print Reports" and click the "Print" button, the value that gets passed to PrintReport is "3". A string, not a number. But your Select Case statement is based on text words like "Power", "Control", etc. So the variable fn is NEVER set because ReportType never equals any of your listed words.

You are returning a number from your Report Type combo box, but it gets converted to a string when you pass the contents of Me.txtReportType, because the sub-procedure PrintReport is expecting a string.

So either change the combo box to return the "Report Type" field, like your Select Case implies - OR -
Keep the combo box returning the field ID, but change the PrintReport parameter to a number and change the Select Case to check for the ReportType IDs.

Side note: Naming convention. Personally my form text boxes get named with the prefix "txt". My combo boxes get named with the prefix "cbo".
 
Hi BHD

How would i change the Combo box to a Text box and make sure that it only displays the Report Type field requested?

The form is based straight off a query called qryReports

Thanks for your help

Allan
 
Hi guys

Just an update:

I have got it working and running the code and saving the file. At the moment it is saving to the documents directory (I will need to change that to save in a different location)

The interesting thing is that it is not transfering the information to the document.

Any ideas?

Allan
 
I don't see it either. All the PrintReport procedure is doing is opening a file and setting bookmarks. I as assuming you are expecting the selected report to be written to the file? Do you have a separate procedure to do that?
 
Hi BHD

Sorry for the late reply.

After much testing i have it semi working with teh data being transferred across.

How do i get the reports to actually show the actual data of the cell instead of the ID?

For example The Project field is supposed to say Test Brisbane but instead it shows as the record ID instead of the actual project name.

Did i get the relationships all wrong when creating the database? I know that this is probably a question for a different forum but i thought i would ask you guys first.

TIA

Allan

PS i owe you guys a bottle or 2 for the help
 
First, I think you really mean "form" and not "report", but the concept is the same. I am not sure what report or form you are referring to, but here goes...

For the field in question (The Project or Project?), the control (combo Box or text box) is referencing the record ID. You want it to reference the field that contains the Project Name.

As for the relationships between tables... Let's just say there is room for improvement.;)

The single biggest concern is your lack of data normalization. Look up "Database Normalization". Honestly, that should have happened before you even cracked open Access. It will be worth the effort to go back and apply those normalization rules to your tables. That alone will force you to fix a number of your relationship errors / issues.

It will make you feel like you are scrapping your work up to this point, but it will save you time later on.

Best of luck.
 
Hi BHD

I have had a look through all the information that you have pointed me to and i cant make heads or tails out of it. I must admit that this project has become far more involved than what was originally discussed with the client.

To that end I am getting stuck on the Print Report code again.

When it runs it uses the first report and then all the other report types are not being saved or displayed on the other templates.

So for example: For a "Report Type = Earth" it is saving on the Report Type Power Template.

So my question now is do i take the loop sequence out of the code and have the Print function only? If so how do i get the code to reset itself after each run?

Code: 
Public Sub PrintReport(ReportType As String)

    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim fn As String
    Dim Path As String
                
    Select Case ReportType
        Case "1"
            fn = "C:\QA\Templates\Core Power Cable.docx"
        Case "2"
            fn = "C:\QA\Templates\Core Control Cable.dotx"
        Case "3"
            fn = "C:\QA\Templates\Core Earth Test Sheet.dotx"
        Case "4"
            fn = "C:\QA\Templates\Core Test Motor Test Sheet.dotx"
        Case "5"
            fn = "C:\QA\Templates\Core Instrument Test Sheet.dotx"
        Case "6"
            fn = "C:\QA\Templates\Core Earth Test Sheet.dotx"
        Case "7"
            fn = "C:\QA\Templates\Core Single Phase Power Cable.dotx"
        Case "8"
            fn = "C:\QA\Templates\Core Three Phase Power Cable.dotx"
        Case "9"
            fn = "C:\QA\Templates\Core Intrinsically Safe Cable.dotx"
        Case Else
            MsgBox "Your Report is not identified:" & "Please select the right Report"
    End Select
        
    ' fn = Environ("SystemDrive") & "\QA\Templates\"
            
    Set wApp = New Word.Application
    ' MsgBox "Directory:" & fn
    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(!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, "")
            
            Path = "C:\QA\Project\Reports\"
            fn = (Nz(!Prefix, "")) & "-" & (Nz(!Type, "")) & "-" & (Nz(!Cores, "")) & ".doc"
            wDoc.SaveAs2 FileName:=Path & fn, FileFormat:=wdFormatDocument
                If Err.Number = 0 Then
                    MsgBox "The file was saved as " & Path & fn, , "Saved"
                Else
                    MsgBox "Error " & Err.Number & vbCr & Err.Description
                End If
        
            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(!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(!DateChecked, ""))
            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
            
    Set wDoc = Nothing
    Set wApp = Nothing
    
End Sub

I hope that what i have tried to explain is understandable to you

Thanks Guys

Allan
 
Ok, I *think* I know what is going on, but I need you to confirm something for me.

Make the following changes to your PrintReport code:
Code: 
    Dim fn As String
    Dim Path As String
 
[COLOR="Red"]msgbox "ReportType = " & ReportType[/COLOR]
               
    Select Case ReportType
        Case "1"

I believe there are some inconsistencies in between what gets selected, sent to the PrintReport procedure and what is being checked.

Let me know exactly what the message box displays.
 
Allan, do you know how to set a breakpoint in code, and break into the debugger? Do you know how to step thru code one line at a time, and check the values of your variables as each line of code is executed? Do you understand what the locals window is showing you in the IDE? Do you have the locals window open when you debug? What tricks do you know when it comes to debugging?

The reason I ask is that I think you are making mistakes that you should be catching, and maybe you don't know how, or that you can, step thru code one line at a time. Am I wrong?

Mark
 
Hi Guys

@Mark to be honest Mark no i dont I am only getting back into access since 97 and i have forgotten a whole heap of things and trying to relearn on the fly is no fun.

I will make the changes and test it :)
Thanks Guys

Allan
 
Hi BHD,

I have attached the snip of the message box output. I placed the code right where you showed me to place it.

It has picked the right Template for the Data but then it retains the same Template for the other "Reports" as well.

Hence the question about the loop.

Thanks for the help guys

Allan
 

Attachments

  • msgbox.PNG
    msgbox.PNG
    9.5 KB · Views: 213
You must log in or register to reply here.

Similar threads

Rene vK
Solved Show progress during export to Word
Replies
14
Views
1,653
isladogs
isladogs
D
Exporting from Access to Word
Replies
14
Views
1,612
The_Doc_Man
The_Doc_Man
L
Export records to word bookmarks
Replies
7
Views
1,641
pbaldy
pbaldy
A
Export data from Access to Word
Replies
5
Views
972
Mark_
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom