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

[Ragonz355]

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

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

[Stewart Ross]

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

[Ragonz355]

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

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

[Stewart Ross]

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

[NeoPa]

You may find Debugging in VBA helpful.