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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-22-2018, 08:10 PM   #46
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 BigHappyDaddy

Do you want th eentire DB?

Allan

MITSupport is offline   Reply With Quote
Old 02-22-2018, 08:11 PM   #47
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

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

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-22-2018, 08:14 PM   #48
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 BigHappyDaddy

Here is the full database

Allan
Attached Files
File Type: zip CoreQA - Copy.zip (560.5 KB, 26 views)

MITSupport is offline   Reply With Quote
Old 02-22-2018, 08:22 PM   #49
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 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
MITSupport is offline   Reply With Quote
Old 02-22-2018, 08:41 PM   #50
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

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

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-22-2018, 10:34 PM   #51
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 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
MITSupport is offline   Reply With Quote
Old 02-22-2018, 11:29 PM   #52
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 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

MITSupport is offline   Reply With Quote
Old 02-23-2018, 07:35 AM   #53
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

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?
__________________

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-26-2018, 10:18 PM   #54
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 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
MITSupport is offline   Reply With Quote
Old 02-27-2018, 08:29 AM   #55
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

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

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 03-01-2018, 03:58 PM   #56
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 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
MITSupport is offline   Reply With Quote
Old 03-01-2018, 04:10 PM   #57
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

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
 
msgbox "ReportType = " & ReportType
               
    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.
__________________

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 03-01-2018, 04:34 PM   #58
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,289 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
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
__________________
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 03-01-2018, 06:03 PM   #59
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 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
MITSupport is offline   Reply With Quote
Old 03-01-2018, 06:22 PM   #60
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 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
Attached Images
File Type: png msgbox.PNG (9.5 KB, 30 views)

MITSupport 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 04:31 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