Form problems - One record works, whilst another in the same table does not

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Ragonz355
    New Member
    • Jun 2015
    • 5

    Form problems - One record works, whilst another in the same table does not

    So have just been asked to take a look at an access database that has been dropped on someone who is not particularly access-literate.

    They asked me to add the subtotal values, VAT and Total into the word document that gets generated when they click the Crete invoice button. Now I worked on this last night and had it working, when that create invoice button was pressed a word document would open with all the details in it pulled out from access.

    However this morning they have come in and told me it doesnt work, I check and lo and behold it doesnt. All the changes I made are still there and it should work. I go to the record that I know I tested last night and it does work

    So now I am confused. The record that does work is in the same table as the ones that dont, and the form is the same in each case. What could possibly be the problem?

    A little more detail. I cant just go posting the whole thing as its confidential but basically there is a form what has order details on it along with customer details and pricing which is pulled out of a few tables

    So the field InvTot1 & VAT & InvTotNew are what should be being pulled into word, the VBA script behind it is

    Code:
    Public Function PrintInvoice()
    
    On Error GoTo Err_PrintInvoice
            
        Set gdb = CurrentDb()
        Set grst = gdb.OpenRecordset("qryJob", dbOpenDynaset)
        
    
    
        CreateWordObj
        gLocation = gDocumentLocation & "Document Templates\Invoice invoice.dot" '
        PrintInit
        
     'Header Detail
    
        With gobjWord
        
            .Selection.GoTo wdGoToBookmark, Name:="CompanyName"
            If Not IsNull(grst![Company Name]) Then
            .Selection.TypeText grst![Company Name]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="Address1"
            If Not IsNull(grst![Address1]) Then
            .Selection.TypeText grst![Address1]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="Address2"
            If Not IsNull(grst![Address2]) Then
            .Selection.TypeText grst![Address2]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="Town"
            If Not IsNull(grst![Town]) Then
            .Selection.TypeText grst![Town]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="County"
            If Not IsNull(grst![County]) Then
            .Selection.TypeText grst![County]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="PostCode"
            If Not IsNull(grst![Postcode]) Then
            .Selection.TypeText grst![Postcode]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="CustRef"
            If Not IsNull(grst![Customer Order Reference]) Then
            .Selection.TypeText grst![Customer Order Reference]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="OurRef"
            If Not IsNull(grst![OrderID]) Then
            .Selection.TypeText grst![OrderID]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="DocDate"
            .Selection.TypeText Date
            .Selection.GoTo wdGoToBookmark, Name:="OrderId"
            If Not IsNull(grst![InvNumb]) Then
            .Selection.TypeText grst![InvNumb]
            End If
            
            
    ' Line Detail
            .Selection.GoTo wdGoToBookmark, Name:="JobTitle"
            If Not IsNull(grst![Job Title]) Then
            .Selection.TypeText grst![Job Title]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="Qty"
            If Not IsNull(grst![Quantity]) Then
            .Selection.TypeText grst![Quantity]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="OrigQuote"
            If Not IsNull(grst![Origquote]) Then
            .Selection.TypeText grst![Origquote]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="AddInv1"
            If Not IsNull(grst![AddInv1]) Then
            .Selection.TypeText grst![AddInv1]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="AddInv2"
            If Not IsNull(grst![AddInv2]) Then
            .Selection.TypeText grst![AddInv2]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="For1"
            If Not IsNull(grst![For1]) Then
            .Selection.TypeText grst![For1]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="For2"
            If Not IsNull(grst![For2]) Then
            .Selection.TypeText grst![For2]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="InvTot1"
            If Not IsNull(grst![InvTot1]) Then
            .Selection.TypeText grst![InvTot1]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="VAT"
            If Not IsNull(grst![VAT]) Then
            .Selection.TypeText grst![VAT]
            End If
            .Selection.GoTo wdGoToBookmark, Name:="InvTotNew"
            If Not IsNull(grst![InvTotNew]) Then
            .Selection.TypeText grst![InvTotNew]
            End If
            
            
    'Total detail
    
                 
            .Visible = True
            
        End With
        
    '       gobjWord.ActiveDocument.PrintOut Background:=False
            gobjWord.ActiveDocument.PrintPreview
    '       gobjWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
        
    
        CloseWord
        
    PrintInvoice_Exit:
    
        Exit Function
        
    Err_PrintInvoice:
        MsgBox Err.Number & "-" & Err.Description
        Resume PrintInvoice_Exit
    End Function
    Oh and the rest of the bookmarks work fine for all records. but the 3 mentioned only work on a certain record
    Last edited by Stewart Ross; Jun 6 '15, 09:50 AM. Reason: Merged your two posts together
  • Stewart Ross
    Recognized Expert Moderator Specialist
    • Feb 2008
    • 2545

    #2
    The VBA code routine looks ok, and as it is working for one invoice I doubt it has anything to do with the bookmarks on your Word template. You need to eliminate the possibility that the recordsource query itself is returning null field values for your vat and totals fields.

    Have you checked the contents of qryJob when this occurs? If the relevant totals fields are null in the record concerned they will not be transferred to the Word document (you test for this in your code). If the fields are present, have you single-stepped through your code and checked and inspected the values of all variables as you do so?

    I am assuming that there is nothing silly going on, such as any of the three fields being treated as text somewhere (in which case you could not perform addition of the vat field for instance).

    -stewart
    Last edited by Stewart Ross; Jun 7 '15, 07:55 AM.

    Comment

    • Ragonz355
      New Member
      • Jun 2015
      • 5

      #3
      I'm afraid I have no idea how to do any of that :/ The only reason I have been given this is because I'm the "IT Guy"

      I was given a working database, added in the 3 bits below following the working format already present

      Code:
      .Selection.GoTo wdGoToBookmark, Name:="InvTot1"
              If Not IsNull(grst![InvTot1]) Then
              .Selection.TypeText grst![InvTot1]
              End If
              .Selection.GoTo wdGoToBookmark, Name:="VAT"
              If Not IsNull(grst![VAT]) Then
              .Selection.TypeText grst![VAT]
              End If
              .Selection.GoTo wdGoToBookmark, Name:="InvTotNew"
              If Not IsNull(grst![InvTotNew]) Then
              .Selection.TypeText grst![InvTotNew]
              End If
      Then added the bookmarks in the word template. one of the records works fine but the rest do not.

      Could you point me to where I can watch it run the module and then I can see if I can spot any errors.
      Last edited by Rabbit; Jun 8 '15, 09:29 PM. Reason: Please use [code] and [/code] tags when posting code or formatted data.

      Comment

      • Stewart Ross
        Recognized Expert Moderator Specialist
        • Feb 2008
        • 2545

        #4
        Firstly, just run qryJob on its own. A query of that name will be visible in the Query objects list when you open the database window (press F11 to make the database window visible if it has been hidden).

        qryJob is the recordsource for the recordset that is being looped through (see line 6 above where qryJob is opened by the OpenRecordset method). If you run the source query directly from the database window you should be able to see straight away if there is a valid value present for each of the totals that are not being transferred to Word at present.

        For the VBA function, set a breakpoint on any suitable line (line 6 in the VBA code from post # 1 above, say). You can then single-step the VBA editor through one line at a time (press F8 to step into each code line, shift/F8 to execute the line without stepping into any local procedure or function). Ensure that you have the Locals window visible so you can see the value of all local variables as you step through the code.

        Debugging by using the editor is a vital skill as otherwise you will have no idea what your code is actually doing.

        -Stewart
        Last edited by Stewart Ross; Jun 8 '15, 04:32 PM.

        Comment

        • NeoPa
          Recognized Expert Moderator MVP
          • Oct 2006
          • 32636

          #5
          You may find Debugging in VBA helpful.

          Comment

          Working...