PDA

View Full Version : Path File Access 75 error on Open strHTMLFile for Output As


miweiser
08-06-2011, 02:20 PM
I would appreciate any assistance by a quick audit of this code to determine why I am getting an error on:

Open strHTMLFile For Output As #1

Access 2007 ACCDB VBA Module

The purpose of this would be to cycle thru multiple records in a table and return an html file for each present.

Option Compare Database

Private Sub sCreateStaticHTMLFiles_Click()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim strHTMLFile As String
Dim s As String
Set Db = CurrentDb


Set rs = Db.OpenRecordset("SELECT * FROM XTab ORDER BY ID")
If rs.EOF Then
MsgBox "Empty Table"

s = Trim(rs!ID)

strHTMLFile = "E:\RMS\XTAB\" & Format(s, "000") & ".htm"
End If
s = Trim(rs!ID)
Open strHTMLFile For Output As #1
Print #1, "<html><head></head><body><TABLE BORDER=1>"
Print #1, "<TR bgcolor=E0E0E0 align=center><TD>Specialty</TD><TD>Business Unit</TD><TD>Cost Center</TD><TD>LOC_NAME</TD><TD>Status</TD><TD>Billing ID</TD><TD>Client_Name</TD><TD>ops_level_1</TD><TD>ops_level_2</TD><TD>ops_level_3</TD><TD>ops_level_4</TD><TD>ops_primary</TD><TD>client_level_4</TD><TD>client_primary</TD><TD>Total Of Balance</TD><TD>121 - 150</TD><TD>150+</TD><TD>61 - 90</TD><TD>91 - 120</TD></TR>"

If StrComp(s, Trim(rs!ID), vbTextCompare) <> 0 Then 'New file
If diff Then
Print #1, "</TABLE></body></html>"
Close #1
Open strHTMLFile For Output As #1
Print #1, "<html><head></head><body><TABLE BORDER=1>"
Print #1, "<TR bgcolor=E0E0E0 align=center><TD>Specialty</TD><TD>Business Unit</TD><TD>Cost Center</TD><TD>LOC_NAME</TD><TD>Status</TD><TD>Billing ID</TD><TD>Client_Name</TD><TD>ops_level_1</TD><TD>ops_level_2</TD><TD>ops_level_3</TD><TD>ops_level_4</TD><TD>ops_primary</TD><TD>client_level_4</TD><TD>client_primary</TD><TD>Total Of Balance</TD><TD>121 - 150</TD><TD>150+</TD><TD>61 - 90</TD><TD>91 - 120</TD></TR>"
Print #1, "<TR><TD>" & rs!Specialty & "</TD><TD>" & rs!Business; Unit & "</TD><TD>" & rs!Cost; Center & "</TD><TD>" & rs!LOC_NAME & "</TD><TD>" & rs!Status & "</TD><TD>" & rs!Billing; ID & "</TD><TD>" & rs!Client_Name & "</TD><TD>" & rs!ops_level_1 & "</TD><TD>" & rs!ops_level_2 & "</TD><TD>" & rs!ops_level_3 & "</TD><TD>" & rs!ops_level_4 & "</TD><TD>" & rs!ops_primary & "</TD><TD>" & rs!client_level_4 & "</TD><TD>" & rs!client_primary & "</TD><TD>" & rs!Total; Of; Balance & "</TD><TD>" & rs![121 - 150] & "</TD><TD>" & rs![150+] & "</TD><TD>" & rs![61 - 90] & "</TD><TD>" & rs![91 - 120] & "</TD></TR>"


rs.MoveNext
Do
Loop Until rs.EOF
Print #1, "</TABLE></body></html>"
Close #1
End If
End If
End Sub
HiTechCoach
08-08-2011, 10:45 AM
The first issue I see is this code:


If rs.EOF Then
MsgBox "Empty Table"

s = Trim(rs!ID)

strHTMLFile = "E:\RMS\XTAB\" & Format(s, "000") & ".htm"
End If
s = Trim(rs!ID)



You are olny setting strHTMLFile to have a path if on the last record or the table is empty.

This may be what you need:



If not rs.EOF and not rs.BOF Then
s = Trim(rs!ID)
Else
MsgBox "Empty Table"

s = 0
' or should you exit?
End If

strHTMLFile = "E:\RMS\XTAB\" & Format(s, "000") & ".htm"






It would also help to see a sample of the data in the table.
miweiser
08-08-2011, 11:29 AM
Thank you, however I am still getting the Path File error on "Open strHTMLFile for Output As".

Fields:
Specialty Business Unit Cost Center LOC_NAME Status Billing ID Client_Name ops_level_1 ops_level_2 ops_level_3 ops_level_4 ops_primary client_level_4 client_primary Total Of Balance 121 - 150 150+ 61 - 90 91 - 120 ID

Data:
RAD RAD-W FEDERLAND DALLAS, TX Active 805PLUS RADIO ASSOC Pat Sajak Christopher Walkin Pat Sajak2 Emelda Marcos Emelda Marcos Rodney King Natalie Ambruglia -4989.04 -4989.04 175
HiTechCoach
08-09-2011, 07:55 AM
You have two line with Open strHTMLFile For Output As #1

Which is generating the error?


The following code is also an issue:

Close #1
Open strHTMLFile For Output As #1

In the above code you are closing teh output file and then reopening it again. You are not changing the value of strHTMLFile to a new path before opening the file again.

I would except to see something like this:

' close current file
Close #1

' change file name
s = Trim(rs!ID)
strHTMLFile = "E:\RMS\XTAB\" & Format(s, "000") & ".htm"
' open new file
Open strHTMLFile For Output As #1
HiTechCoach
08-09-2011, 07:56 AM
Also does the folder E:\RMS\XTAB\ already exist?
miweiser
08-09-2011, 10:30 AM
Yes, you are right and I do in fact have that already in my initial trial, however the error caused me to try a static location...the folders themselves do already exist.

My problem is that I do not understand what it is looking for...
HiTechCoach
08-09-2011, 02:11 PM
My problem is that I do not understand what it is looking for...

Huh? :dunno
HiTechCoach
08-09-2011, 02:11 PM
My problem is that I do not understand what it is looking for...
Huh? :dunno
miweiser
08-09-2011, 09:08 PM
I'm sorry - my code has been corrected as you advised however the error described in the original post still exists and is baffling me.

I do not understand what Path File should be represented or otherwise referenced.

Thanks so much for your time!
miweiser
08-10-2011, 01:44 PM
Well I have moved past the errors, however it only replaces itself and does not fully finish printing the code wrapped record.

Option Compare Database

Private Sub sCreateStaticHTMLFiles_Click()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim strHTMLFile As String
Dim s As String
Set Db = CurrentDb


Set rs = Db.OpenRecordset("SELECT * FROM XTab ORDER BY ID")



If Not rs.EOF And Not rs.BOF Then
s = Trim(rs!ID)
Else
MsgBox "Empty Table"

s = 0
' or should you exit?
End If

' close current file
Close #1

' change file name
s = Trim(rs!ID)
strHTMLFile = "E:\RMS\XTAB\" & Format(s, "000") & ".htm"
' open new file
Open strHTMLFile For Output As #1

Print #1, "<html><head></head><body><TABLE BORDER=1>"
Print #1, "<TR bgcolor=E0E0E0 align=center><TD>Specialty</TD><TD>Business Unit</TD><TD>Cost Center</TD><TD>LOC_NAME</TD><TD>Status</TD><TD>Billing ID</TD><TD>Client_Name</TD><TD>ops_level_1</TD><TD>ops_level_2</TD><TD>ops_level_3</TD><TD>ops_level_4</TD><TD>ops_primary</TD><TD>client_level_4</TD><TD>client_primary</TD><TD>Total Of Balance</TD><TD>121 - 150</TD><TD>150+</TD><TD>61 - 90</TD><TD>91 - 120</TD></TR>"

If StrComp(s, Trim(rs!ID), vbTextCompare) <> 0 Then 'New file
If diff Then
Print #1, "</TABLE></body></html>"


Print #1, "<html><head></head><body><TABLE BORDER=1>"
Print #1, "<TR bgcolor=E0E0E0 align=center><TD>Specialty</TD><TD>Business Unit</TD><TD>Cost Center</TD><TD>LOC_NAME</TD><TD>Status</TD><TD>Billing ID</TD><TD>Client_Name</TD><TD>ops_level_1</TD><TD>ops_level_2</TD><TD>ops_level_3</TD><TD>ops_level_4</TD><TD>ops_primary</TD><TD>client_level_4</TD><TD>client_primary</TD><TD>Total Of Balance</TD><TD>121 - 150</TD><TD>150+</TD><TD>61 - 90</TD><TD>91 - 120</TD></TR>"
Print #1, "<TR><TD>" & rs!Specialty & "</TD><TD>" & rs!Business; Unit & "</TD><TD>" & rs!Cost; Center & "</TD><TD>" & rs!LOC_NAME & "</TD><TD>" & rs!Status & "</TD><TD>" & rs!Billing; ID & "</TD><TD>" & rs!Client_Name & "</TD><TD>" & rs!ops_level_1 & "</TD><TD>" & rs!ops_level_2 & "</TD><TD>" & rs!ops_level_3 & "</TD><TD>" & rs!ops_level_4 & "</TD><TD>" & rs!ops_primary & "</TD><TD>" & rs!client_level_4 & "</TD><TD>" & rs!client_primary & "</TD><TD>" & rs!Total; Of; Balance & "</TD><TD>" & rs![121 - 150] & "</TD><TD>" & rs![150+] & "</TD><TD>" & rs![61 - 90] & "</TD><TD>" & rs![91 - 120] & "</TD></TR>"

Print #1, "</TABLE></body></html>"

rs.MoveNext
Do
Loop Until rs.EOF
Close #1
End If
End If
End Sub
miweiser
08-10-2011, 06:55 PM
I worked it out now to fix the looping thru records:


Option Compare Database

Private Sub sCreateStaticHTMLFiles_Click()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim strHTMLFile As String
Dim s As String
Set Db = CurrentDb


Set rs = Db.OpenRecordset("SELECT * FROM XTab ORDER BY ID")



If Not rs.EOF And Not rs.BOF Then
s = Trim(rs!ID)
Else
MsgBox "Empty Table"

s = 0
' or should you exit?
End If

' close current file
Close #1

' change file name
s = Trim(rs!ID)
strHTMLFile = "E:\RMS\XTAB\" & Format(s, "000") & ".htm"
' open new file
Open strHTMLFile For Output As #1

Print #1, "<html><head></head><body><TABLE BORDER=1>"
Print #1, "<TR bgcolor=E0E0E0 align=center><TD>Specialty</TD><TD>Business Unit</TD><TD>Cost Center</TD><TD>LOC_NAME</TD><TD>Status</TD><TD>Billing ID</TD><TD>Client_Name</TD><TD>ops_level_1</TD><TD>ops_level_2</TD><TD>ops_level_3</TD><TD>ops_level_4</TD><TD>ops_primary</TD><TD>client_level_4</TD><TD>client_primary</TD><TD>Total Of Balance</TD><TD>121 - 150</TD><TD>150+</TD><TD>61 - 90</TD><TD>91 - 120</TD><TD>ID</TD></TR>"
Print #1, "</BR>"
Print #1, "<TR align=center><TD>" & rs!Specialty & "</TD><TD>" & rs![Business Unit] & "</TD><TD>" & rs![Cost Center] & "</TD><TD>" & rs!LOC_NAME & "</TD><TD>" & rs!Status & "</TD><TD>" & rs![Billing ID] & "</TD><TD>" & rs!Client_Name & "</TD><TD>" & rs!ops_level_1 & "</TD><TD>" & rs!ops_level_2 & "</TD><TD>" & rs!ops_level_3 & "</TD><TD>" & rs!ops_level_4 & "</TD><TD>" & rs!ops_primary & "</TD><TD>" & rs!client_level_4 & "</TD><TD>" & rs!client_primary & "</TD><TD>" & rs![Total Of Balance] & "</TD><TD>" & rs![121 - 150] & "</TD><TD>" & rs![150+] & "</TD><TD>" & rs![61 - 90] & "</TD><TD>" & rs![91 - 120] & "</TD><TD>" & rs!ID & "</TD></TR>"

Print #1, "</TABLE></body></html>"


If StrComp(s, Trim(rs!ID), vbTextCompare) <> 0 Then 'New file
If diff Then



Print #1, "<html><head></head><body><TABLE BORDER=1>"
Print #1, "<TR bgcolor=E0E0E0 align=center><TD>Specialty</TD><TD>Business Unit</TD><TD>Cost Center</TD><TD>LOC_NAME</TD><TD>Status</TD><TD>Billing ID</TD><TD>Client_Name</TD><TD>ops_level_1</TD><TD>ops_level_2</TD><TD>ops_level_3</TD><TD>ops_level_4</TD><TD>ops_primary</TD><TD>client_level_4</TD><TD>client_primary</TD><TD>Total Of Balance</TD><TD>121 - 150</TD><TD>150+</TD><TD>61 - 90</TD><TD>91 - 120</TD></TR>"
Print #1, "</BR>"
Print #1, "<TR><TD>" & rs!Specialty & "</TD><TD>" & rs![Business Unit] & "</TD><TD>" & rs![Cost Center] & "</TD><TD>" & rs!LOC_NAME & "</TD><TD>" & rs!Status & "</TD><TD>" & rs![Billing ID] & "</TD><TD>" & rs!Client_Name & "</TD><TD>" & rs!ops_level_1 & "</TD><TD>" & rs!ops_level_2 & "</TD><TD>" & rs!ops_level_3 & "</TD><TD>" & rs!ops_level_4 & "</TD><TD>" & rs!ops_primary & "</TD><TD>" & rs!client_level_4 & "</TD><TD>" & rs!client_primary & "</TD><TD>" & rs![Total Of Balance] & "</TD><TD>" & rs![121 - 150] & "</TD><TD>" & rs![150+] & "</TD><TD>" & rs![61 - 90] & "</TD><TD>" & rs![91 - 120] & "</TD></TR>"

Print #1, "</TABLE></body></html>"




rs.MoveNext
Do
Loop Until rs.EOF
Close #1
End If
End If
End Sub
miweiser
08-11-2011, 09:10 AM
I am now trying to fine tune the LOOP so that it will move to the next record:


Option Compare Database

Private Sub sCreateStaticHTMLFiles_Click()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim strHTMLFile As String
Dim s As String
Set Db = CurrentDb


Set rs = Db.OpenRecordset("SELECT * FROM XTab ORDER BY ID")



If Not rs.EOF And Not rs.BOF Then
s = Trim(rs!ID)
Else
MsgBox "Empty Table"

s = 0
' or should you exit?
End If

' close current file
Close #1

' change file name
s = Trim(rs!ID)
strHTMLFile = "E:\RMS\XTAB\" & Format(s, "000") & ".htm"
' open new file
Open strHTMLFile For Output As #1

Print #1, "<html><head></head><body><TABLE BORDER=1>"
Print #1, "<TR bgcolor=E0E0E0 align=center><TD>Specialty</TD><TD>Business Unit</TD></TR>"
Print #1, "</BR>"
Print #1, "<TR align=center><TD>" & rs!Specialty & "</TD><TD>" & rs![Business Unit] & "</TD></TR>"

Print #1, "</TABLE></body></html>"


If StrComp(s, Trim(rs!ID), vbTextCompare) <> 0 Then 'New file
If diff Then



Print #1, "<html><head></head><body><TABLE BORDER=1>"
Print #1, "<TR bgcolor=E0E0E0 align=center><TD>Specialty</TD><TD>Business Unit</TD><TD>ID</TD></TR>"
Print #1, "</BR>"
Print #1, "<TR><TD>" & rs!Specialty & "</TD><TD>" & rs![Business Unit] & "</TD><TD>" & rs!ID & "</TD></TR>"

Print #1, "</TABLE></body></html>"




rs.MoveNext
Do
Loop Until rs.EOF
Close #1
End If
End If
End Sub
hansup
08-11-2011, 09:17 AM
You can make it easier on us to audit your code by giving us shorter lines which don't require scrolling the browser window.

Consider this example:

Print #1, "<TR align=center><TD>" & rs!Specialty & "</TD><TD>" & _
rs![Business Unit] & "</TD><TD>" & rs![Cost Center] & "</TD><TD>" & _
rs!LOC_NAME & "</TD><TD>" & rs!Status & "</TD><TD>" & _
rs![Billing ID] & "</TD><TD>" & rs!Client_Name & "</TD><TD>" & _
rs!ops_level_1 & "</TD><TD>" & rs!ops_level_2 & "</TD><TD>" & _
rs!ops_level_3 & "</TD><TD>" & rs!ops_level_4 & "</TD><TD>" & _
rs!ops_primary & "</TD><TD>" & rs!client_level_4 & "</TD><TD>" & _
rs!client_primary & "</TD><TD>" & rs![Total Of Balance] & "</TD><TD>" & _
rs![121 - 150] & "</TD><TD>" & rs![150+] & "</TD><TD>" & _
rs![61 - 90] & "</TD><TD>" & rs![91 - 120] & "</TD><TD>" & _
rs!ID & "</TD></TR>"

Taking it one step further, do you really need to show us that full Print statement in order for us to understand the problem you're trying to solve?
miweiser
08-11-2011, 09:24 AM
You can make it easier on us to audit your code by giving us shorter lines which don't require scrolling the browser window.


Taking it one step further, do you really need to show us that full Print statement in order for us to understand the problem you're trying to solve?

No, I'm sorry - just trying to be thorough, since my inquiries seem vague and somewhat overlooked.
hansup
08-11-2011, 10:12 AM
In your earlier post you said "I am now trying to fine tune the LOOP so that it will move to the next record".

Your code includes ...

Do
Loop Until rs.EOF

... with nothing between those lines (nothing inside the loop). Is that the piece you're trying to fine tune?

I think your code sample would be clearer if you discard everything which is not directly relevant to the problem at hand. It will be clearer to us, and should be clearer to you.

Another important point is your code module needs Option Explicit in the Declarations section. You should never ever attempt to troubleshoot VBA code which doesn't include Option Explicit. Find the VBE option which says "require variable declaration" and check the box next to it. That way any new code module you create will automatically include Option Explicit. But you'll have to go into all your existing code modules and add it manually.
miweiser
08-11-2011, 10:53 AM
In your earlier post you said "I am now trying to fine tune the LOOP so that it will move to the next record".

Your code includes ...

Do
Loop Until rs.EOF

... with nothing between those lines (nothing inside the loop). Is that the piece you're trying to fine tune?

I think your code sample would be clearer if you discard everything which is not directly relevant to the problem at hand. It will be clearer to us, and should be clearer to you.


OK, thank you - Yes, I am not sure how to effectively use the "IF DIFF" or "Do Loop".

I have not been able to move past the first record.

If StrComp(s, Trim(rs!ID), vbTextCompare) <> 0 Then 'New file
If diff Then

Do
rs.MoveNext
Loop Until rs.EOF
Close #1
End If
miweiser
08-11-2011, 12:03 PM
I guess I figured out the DO LOOP:

Do
' close current file
Close #1

' change file name
s = Trim(rs!ID)
strHTMLFile = "E:\RMS\XTAB\" & Format(s, "000") & ".htm"
' open new file
Open strHTMLFile For Output As #1
If StrComp(s, Trim(rs!ID), vbTextCompare) <> 1 Then 'New file
' If diff Then

rs.MoveNext

Print #1, "<html><head></head><body><TABLE BORDER=1>"
Print #1, "<TR bgcolor=E0E0E0 align=center><TD>Specialty</TD><TD>Business Unit</TD><TD>ID</TD></TR>"
Print #1, "</BR>"
Print #1, "<TR><TD>" & rs!Specialty & "</TD><TD>" & rs![Business Unit] & "</TD><TD>" & rs!ID & "</TD></TR>"

Print #1, "</TABLE></body></html>"


End If
Loop Until rs.EOF
Close #1