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
4 1321
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
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: laurenq uantrell |
last post by:
Is there any reason to have a row that is the PK/Identity and a row
that is datatype Timestamp in the same table?
Does this in any way help speeding up row updates?
Thanks,
lq
|
by: SJM |
last post by:
In the past I have in the occasions when I had to update the values in one
record from another record in the same table, I have used recordsets when in
Aceess. I done something similar in SQLServer...
|
by: khoegen |
last post by:
Folks,
I am in the process of designing a database, within which I have created a table called "tblDOCUMENTS". As the title suggests, this is a list of documents (records) of varying types. I...
|
by: polocar |
last post by:
Hi,
I'm writing a program in Visual C# 2005 Professional Edition.
This program connects to a SQL Server 2005 database called
"Generations" (in which there is only one table, called...
|
by: jl2886 |
last post by:
Can you reference a field from the table that a form is based on, if the field is not located on the form?
|
by: jmstur2 |
last post by:
How do I update a field in a record based on the value of another field in another record - in the same table?
Specifically, I want to update the value in the AMOUNT field of record1 with the...
|
by: Zimectrin |
last post by:
Found this code on this site. It seems to work except for one problem. Only the first five of 20 fields are copied. I have done my due diligence to find some reason for this behavior, but now am...
|
by: shirani79 |
last post by:
please simply let me know how could I copy last record to a same table.
|
by: veeru8989 |
last post by:
Hi,
i created form in ms access using form wizard.
and entered data in the fields and click on save button the record is inserted into table.its Logic is working fine and when i click on close Form...
|
by: TecnicoAlpha |
last post by:
Hello, this is my first time here at Bytes, and i'm a newbie to Access.
I'm really desperate.
Each time i enter a form, it saves a record automatically, wich is fine if it's a new record (Button...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| | |