By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,462 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,462 IT Pros & Developers. It's quick & easy.

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

P: 5
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

Expand|Select|Wrap|Line Numbers
  1. Public Function PrintInvoice()
  2.  
  3. On Error GoTo Err_PrintInvoice
  4.  
  5.     Set gdb = CurrentDb()
  6.     Set grst = gdb.OpenRecordset("qryJob", dbOpenDynaset)
  7.  
  8.  
  9.  
  10.     CreateWordObj
  11.     gLocation = gDocumentLocation & "Document Templates\Invoice invoice.dot" '
  12.     PrintInit
  13.  
  14.  'Header Detail
  15.  
  16.     With gobjWord
  17.  
  18.         .Selection.GoTo wdGoToBookmark, Name:="CompanyName"
  19.         If Not IsNull(grst![Company Name]) Then
  20.         .Selection.TypeText grst![Company Name]
  21.         End If
  22.         .Selection.GoTo wdGoToBookmark, Name:="Address1"
  23.         If Not IsNull(grst![Address1]) Then
  24.         .Selection.TypeText grst![Address1]
  25.         End If
  26.         .Selection.GoTo wdGoToBookmark, Name:="Address2"
  27.         If Not IsNull(grst![Address2]) Then
  28.         .Selection.TypeText grst![Address2]
  29.         End If
  30.         .Selection.GoTo wdGoToBookmark, Name:="Town"
  31.         If Not IsNull(grst![Town]) Then
  32.         .Selection.TypeText grst![Town]
  33.         End If
  34.         .Selection.GoTo wdGoToBookmark, Name:="County"
  35.         If Not IsNull(grst![County]) Then
  36.         .Selection.TypeText grst![County]
  37.         End If
  38.         .Selection.GoTo wdGoToBookmark, Name:="PostCode"
  39.         If Not IsNull(grst![Postcode]) Then
  40.         .Selection.TypeText grst![Postcode]
  41.         End If
  42.         .Selection.GoTo wdGoToBookmark, Name:="CustRef"
  43.         If Not IsNull(grst![Customer Order Reference]) Then
  44.         .Selection.TypeText grst![Customer Order Reference]
  45.         End If
  46.         .Selection.GoTo wdGoToBookmark, Name:="OurRef"
  47.         If Not IsNull(grst![OrderID]) Then
  48.         .Selection.TypeText grst![OrderID]
  49.         End If
  50.         .Selection.GoTo wdGoToBookmark, Name:="DocDate"
  51.         .Selection.TypeText Date
  52.         .Selection.GoTo wdGoToBookmark, Name:="OrderId"
  53.         If Not IsNull(grst![InvNumb]) Then
  54.         .Selection.TypeText grst![InvNumb]
  55.         End If
  56.  
  57.  
  58. ' Line Detail
  59.         .Selection.GoTo wdGoToBookmark, Name:="JobTitle"
  60.         If Not IsNull(grst![Job Title]) Then
  61.         .Selection.TypeText grst![Job Title]
  62.         End If
  63.         .Selection.GoTo wdGoToBookmark, Name:="Qty"
  64.         If Not IsNull(grst![Quantity]) Then
  65.         .Selection.TypeText grst![Quantity]
  66.         End If
  67.         .Selection.GoTo wdGoToBookmark, Name:="OrigQuote"
  68.         If Not IsNull(grst![Origquote]) Then
  69.         .Selection.TypeText grst![Origquote]
  70.         End If
  71.         .Selection.GoTo wdGoToBookmark, Name:="AddInv1"
  72.         If Not IsNull(grst![AddInv1]) Then
  73.         .Selection.TypeText grst![AddInv1]
  74.         End If
  75.         .Selection.GoTo wdGoToBookmark, Name:="AddInv2"
  76.         If Not IsNull(grst![AddInv2]) Then
  77.         .Selection.TypeText grst![AddInv2]
  78.         End If
  79.         .Selection.GoTo wdGoToBookmark, Name:="For1"
  80.         If Not IsNull(grst![For1]) Then
  81.         .Selection.TypeText grst![For1]
  82.         End If
  83.         .Selection.GoTo wdGoToBookmark, Name:="For2"
  84.         If Not IsNull(grst![For2]) Then
  85.         .Selection.TypeText grst![For2]
  86.         End If
  87.         .Selection.GoTo wdGoToBookmark, Name:="InvTot1"
  88.         If Not IsNull(grst![InvTot1]) Then
  89.         .Selection.TypeText grst![InvTot1]
  90.         End If
  91.         .Selection.GoTo wdGoToBookmark, Name:="VAT"
  92.         If Not IsNull(grst![VAT]) Then
  93.         .Selection.TypeText grst![VAT]
  94.         End If
  95.         .Selection.GoTo wdGoToBookmark, Name:="InvTotNew"
  96.         If Not IsNull(grst![InvTotNew]) Then
  97.         .Selection.TypeText grst![InvTotNew]
  98.         End If
  99.  
  100.  
  101. 'Total detail
  102.  
  103.  
  104.         .Visible = True
  105.  
  106.     End With
  107.  
  108. '       gobjWord.ActiveDocument.PrintOut Background:=False
  109.         gobjWord.ActiveDocument.PrintPreview
  110. '       gobjWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
  111.  
  112.  
  113.     CloseWord
  114.  
  115. PrintInvoice_Exit:
  116.  
  117.     Exit Function
  118.  
  119. Err_PrintInvoice:
  120.     MsgBox Err.Number & "-" & Err.Description
  121.     Resume PrintInvoice_Exit
  122. End Function
Oh and the rest of the bookmarks work fine for all records. but the 3 mentioned only work on a certain record
Jun 5 '15 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
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
Jun 7 '15 #2

P: 5
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

Expand|Select|Wrap|Line Numbers
  1. .Selection.GoTo wdGoToBookmark, Name:="InvTot1"
  2.         If Not IsNull(grst![InvTot1]) Then
  3.         .Selection.TypeText grst![InvTot1]
  4.         End If
  5.         .Selection.GoTo wdGoToBookmark, Name:="VAT"
  6.         If Not IsNull(grst![VAT]) Then
  7.         .Selection.TypeText grst![VAT]
  8.         End If
  9.         .Selection.GoTo wdGoToBookmark, Name:="InvTotNew"
  10.         If Not IsNull(grst![InvTotNew]) Then
  11.         .Selection.TypeText grst![InvTotNew]
  12.         End If
  13.  
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.
Jun 8 '15 #3

Expert Mod 2.5K+
P: 2,545
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
Jun 8 '15 #4

NeoPa
Expert Mod 15k+
P: 31,768
You may find Debugging in VBA helpful.
Jun 9 '15 #5

Post your reply

Sign in to post your reply or Sign up for a free account.