code not working when Database is open

[cneill]

Hi Everyone,

I have a strange one for you.
When I use the shift key to get into the database design, if I then open the form and press the reports button on the form the following code works fine

Dim RDate As String
Dim House As String
Dim strFile As String
Dim strFile1 As String
RDate = Format(Now(), "dd-mm-yy -HH-NN-SS")
House = Forms![FrmSchemes]![HousesAttached].Column(1)
strFile = ("Inventory Report for " & House & " - Created " & "" & RDate & "" & ".DOC")
strFile1 = ("Information Pack Report for " & House & " - Created " & "" & RDate & "" & ".DOC")
DoCmd.OutputTo acOutputReport, "RptHouseInventory", acFormatRTF, strFile, True
DoCmd.OutputTo acOutputReport, "RptHouseInformationPack", acFormatRTF, strFile1, True

if I now close the database and open the database without using the shift key and press the report button the code does not work.
I am not sure if I am giving you enough information to help you, as I am at a loss as to why this is not working.
Any thoughts

Thanks
CNEILL

 

[genomon]

Is this by any chance and .mde or accde project?

Gluais faicilleach le cupan làn

 

[cneill]

it is just an MDB project

thanks

 

[TheAceMan1]

How are ya cneill . . .

cneill[blue]if I now close the database and open the database without using the shift key and press the report button the code does not work.[/blue]

Does the code stop on a specific line or what?

Is an error number provided?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[cneill]

Hi ya TheAceMan1 I am well

I have done some more work on this problem, still have not solved it in the order that I would like, but I have managed to get it working now, but don't really know why it works at Point "A" but not at Point "B" may be you can see where I am going wrong?

Below is the full code

Private Sub NewTenancyDocuments_Click()

"A"

'Main Parameters
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim doc As Word.Document
Dim MyWord As Word.Application
Dim WordObj As Word.Application
Dim WordDoc As Word.Document
Dim WordRange As Word.Range

'Prefixes H=House, T=Tenant, Ty=Tenancy, D=Deposit, A=Alternative

'Documentation Declaration Prefix with DD
Dim DDHAddress As String
Dim DDTName As String
Dim DDTName1 As String
Dim DDTyStartDate As String

'Tenancy Agreement Prefix with AST
Dim ASTDeposit As String
Dim ASTLLAddress As String
Dim ASTLLAddress2 As String
Dim ASTHAddress As String
Dim ASTLLord As String
Dim ASTLLord2 As String
Dim ASTTName As String
Dim ASTMRent As String
Dim ASTMRent2 As String
Dim ASTHType As String
Dim ASTDeposit1 As String
Dim ASTDeposit2 As String
Dim ASTDName As String
Dim ASTTyStartDate As String
Dim ASTTyStartDate2 As String
Dim ASTRDueDate As String
Dim ASTDAddress As String
Dim ASTAAddress As String

'Section 21
Dim S21HAddress As String
Dim S21HAddress2 As String
Dim S21TName As String
Dim S21TelNo As String
Dim S21LLord As String
Dim S21LLord2 As String
Dim S21LLAddress As String
Dim S21LLAddress2 As String
Dim S21TStartDate As String

'Housing Benefit Prifix with HB
Dim HBHAddress As String
Dim HBHAddress2 As String
Dim HBTName As String
Dim HBTName2 As String
Dim HBMRent As String
Dim HBMRent2 As String
Dim HBDeposit As String
Dim HBRDDate As String
Dim HBDPName As String
Dim HBDPName2 As String
Dim HBDPAddress As String
Dim HBDPWebAddress As String
Dim HBDPTelNo As String
Dim HBLLord As String
Dim HBTyStartDate As String
Dim HBTyStartDate2 As String
Dim HBLLAddress As String

'Guarantor Prifix with G
Dim GLLAddress As String
Dim GHAddress As String
Dim GLLord As String
Dim GTName As String
Dim GTName2 As String
Dim GTelNo As String
Dim GMRent As String
Dim GTyStartDate As String

Set WordObj = CreateObject("Word.Application")
Set WordDoc = WordObj.Documents.Open _
("C:\Documents and Settings\Compaq_Owner\My Documents\Dee\Houses\SET UP DOCS\NewTenancyDocuments.docx")
WordObj.Visible = True
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set qdf = db.QueryDefs("QryNewTenantDocuments")
For Each prm In qdf.Parameters
prm.value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset
rst.MoveFirst

'Documentation Declaration Prefix with DD
DDHAddress = rst!DDHouseAddress
DDTName = rst!DDTenantName
DDTName1 = rst!DDTenantName1
DDTyStartDate = rst!DDTenancyStartDate
WordDoc.Bookmarks("DDHouseAddress").Range.Text = DDHAddress
WordDoc.Bookmarks("DDTenantName").Range.Text = DDTName
WordDoc.Bookmarks("DDTenantName1").Range.Text = DDTName1
WordDoc.Bookmarks("DDTenancyStartDate").Range.Text = DDTyStartDate

'Tenancy Agreement Prefix with AST
ASTLLAddress = rst!ASTFullLLAddress
ASTLLAddress2 = rst!ASTFullLLAddress2
ASTLLord = rst!ASTLandlord
ASTLLord2 = rst!ASTLandlord2
ASTHAddress = rst!ASTHouseAddress
ASTTName = rst!ASTTenantName
ASTMRent = rst!ASTMonthlyRent
ASTMRent2 = rst!ASTMonthlyRent2
ASTHType = rst!ASTHouseType
ASTDeposit = rst!ASTDeposit
ASTDeposit1 = rst!ASTDeposit1
ASTDeposit2 = rst!ASTDeposit2
ASTDName = rst!ASTDName
ASTRDueDate = rst!ASTRentDueDate
ASTTyStartDate = rst!ASTTenancyStartDate
ASTTyStartDate2 = rst!ASTTenancyStartDate2
ASTDAddress = rst!ASTDepositAddress
ASTAAddress = rst!ASTAlternatveAddress
WordDoc.Bookmarks("ASTDeposit").Range.Text = ASTDeposit
WordDoc.Bookmarks("ASTDeposit1").Range.Text = ASTDeposit1
WordDoc.Bookmarks("ASTDeposit2").Range.Text = ASTDeposit2
WordDoc.Bookmarks("ASTDName").Range.Text = ASTDName
WordDoc.Bookmarks("ASTFullLLAddress").Range.Text = ASTLLAddress
WordDoc.Bookmarks("ASTFullLLAddress2").Range.Text = ASTLLAddress2
WordDoc.Bookmarks("ASTLandlord").Range.Text = ASTLLord
WordDoc.Bookmarks("ASTLandlord2").Range.Text = ASTLLord2
WordDoc.Bookmarks("ASTHouseAddress").Range.Text = ASTHAddress
WordDoc.Bookmarks("ASTMonthlyRent").Range.Text = ASTMRent
WordDoc.Bookmarks("ASTMonthlyRent2").Range.Text = ASTMRent2
WordDoc.Bookmarks("ASTTenantName").Range.Text = ASTTName
WordDoc.Bookmarks("ASTHouseType").Range.Text = ASTHType
WordDoc.Bookmarks("ASTTenancyStartDate").Range.Text = ASTTyStartDate
WordDoc.Bookmarks("ASTTenancyStartDate2").Range.Text = ASTTyStartDate2
WordDoc.Bookmarks("ASTRentDueDate").Range.Text = ASTRDueDate
WordDoc.Bookmarks("ASTDepositAddress").Range.Text = ASTDAddress
WordDoc.Bookmarks("ASTAlternatveAddress").Range.Text = ASTAAddress

'Section 21 Prefix with S21
S21HAddress = rst!S21HouseAddress
S21HAddress2 = rst!S21HouseAddress2
S21TName = rst!S21TenantName
S21TelNo = rst!S21OfficeNumber
S21LLord = rst!S21Landlord
S21LLord2 = rst!S21Landlord2
S21LLAddress = rst!S21FullLLAddress
S21LLAddress2 = rst!S21FullLLAddress2
S21TStartDate = rst!S21TenancyStartDate
WordDoc.Bookmarks("S21HouseAddress").Range.Text = S21HAddress
WordDoc.Bookmarks("S21HouseAddress2").Range.Text = S21HAddress2
WordDoc.Bookmarks("S21TenantName").Range.Text = S21TName
WordDoc.Bookmarks("S21OfficeNumber").Range.Text = S21TelNo
WordDoc.Bookmarks("S21Landlord").Range.Text = S21LLord
WordDoc.Bookmarks("S21Landlord2").Range.Text = S21LLord2
WordDoc.Bookmarks("S21FullLLAddress").Range.Text = S21LLAddress
WordDoc.Bookmarks("S21FullLLAddress2").Range.Text = S21LLAddress2
WordDoc.Bookmarks("S21TenancyStartDate").Range.Text = S21TStartDate

'Housing Benefit Prifix with HB
HBHAddress = rst!HBHouseAddress
HBHAddress2 = rst!HBHouseAddress2
HBTName = rst!HBTenantName
HBTName2 = rst!HBTenantName2
HBMRent = rst!HBMonthlyRent
HBMRent2 = rst!HBMonthlyRent2
HBDeposit = rst!HBDeposit
HBRDDate = rst!HBDay
HBDPName = rst!HBDName
HBDPName2 = rst!HBDName2
HBDPAddress = rst!HBDAddress
HBDPWebAddress = rst!HBDWebAddress
HBDPTelNo = rst!HBDTelNo
HBLLord = rst!HBLandlord
HBTyStartDate = rst!HBTenancyStartDate
HBTyStartDate2 = rst!HBTenancyStartDate2
HBLLAddress = rst!HBFullLLAddress
WordDoc.Bookmarks("HBHouseAddress").Range.Text = HBHAddress
WordDoc.Bookmarks("HBHouseAddress2").Range.Text = HBHAddress2
WordDoc.Bookmarks("HBTenantName").Range.Text = HBTName
WordDoc.Bookmarks("HBTenantName2").Range.Text = HBTName2
WordDoc.Bookmarks("HBMonthlyRent").Range.Text = HBMRent
WordDoc.Bookmarks("HBMonthlyRent2").Range.Text = HBMRent2
WordDoc.Bookmarks("HBDeposit").Range.Text = HBDeposit
WordDoc.Bookmarks("HBDay").Range.Text = HBRDDate
WordDoc.Bookmarks("HBDName").Range.Text = HBDPName
WordDoc.Bookmarks("HBDName2").Range.Text = HBDPName2
WordDoc.Bookmarks("HBDAddress").Range.Text = HBDPAddress
WordDoc.Bookmarks("HBDWebAddress").Range.Text = HBDPWebAddress
WordDoc.Bookmarks("HBDTelNo").Range.Text = HBDPTelNo
WordDoc.Bookmarks("HBLandlord").Range.Text = HBLLord
WordDoc.Bookmarks("HBTenancyStartDate").Range.Text = HBTyStartDate
WordDoc.Bookmarks("HBTenancyStartDate2").Range.Text = HBTyStartDate2
WordDoc.Bookmarks("HBFullLLAddress").Range.Text = HBLLAddress

'Guarantor Prifix with G
GLLAddress = rst!GFullLLAddress
GLLord = rst!GLandlord
GHAddress = rst!GHouseAddress
GTName = rst!GTenantName
GTName2 = rst!GTenantName2
GMRent = rst!GMonthlyRent
GTelNo = rst!GOfficeNumber
GTyStartDate = rst!GTenancyStartDate

WordDoc.Bookmarks("GFullLLAddress").Range.Text = GLLAddress
WordDoc.Bookmarks("GLandlord").Range.Text = GLLord
WordDoc.Bookmarks("GHouseAddress").Range.Text = GHAddress
WordDoc.Bookmarks("GMonthlyRent").Range.Text = GMRent
WordDoc.Bookmarks("GTenantName").Range.Text = GTName
WordDoc.Bookmarks("GTenantName2").Range.Text = GTName2
WordDoc.Bookmarks("GOfficeNumber").Range.Text = GTelNo
WordDoc.Bookmarks("GTenancyStartDate").Range.Text = GTyStartDate

"B"

End Sub

Now if I insert the following Code at Point "A"

Dim RDate As String
Dim House As String
Dim strFile As String
Dim strFile1 As String
RDate = Format(Now(), "dd-mm-yy -HH-NN-SS")
House = Forms![FrmSchemes]![HousesAttached].Column(1)
strFile = ("Inventory Report for " & House & " - Created " & "" & RDate & "" & ".DOC")
strFile1 = ("Information Pack Report for " & House & " - Created " & "" & RDate & "" & ".DOC")
DoCmd.OutputTo acOutputReport, "RptHouseInventory", acFormatRTF, strFile, True
DoCmd.OutputTo acOutputReport, "RptHouseInformationPack", acFormatRTF, strFile1, True

It Works
but if I insert it at Point "B"
It does not work
There are no error messages, it does not stop on a specific line it just will not execute the code.


Any Thoughts

CNEILL

 

[TheAceMan1]

cneill . . .

Try setting a [blue]break point[/blue] & single stepping thru the code ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]