UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Need Help Streaming A Text File From A Query Recordset    
 
   
madmix
post May 6 2012, 02:05 PM
Post #1

UtterAccess Addict
Posts: 259
Hi All,

I have written a function to stream text files to my hard drive. I also have a command button on my form that opens a query as a recordset and replaces some strings in the text stream with data from fields in my query before writing the stream to the hard drive. It is supposed to stream one file for each record in the recordset. I know the recordset is being openned and I know the function is working correctly because I was able to stream a "non replaced" text file but when I use the command button code I am getting an error 2465 "...can't find the field"|" referred to in your expression." Can someone please help me figure out what I am doing wrong here? I posted the command button code and I will be happy to post the function code if anyone wants me to.

Thanks in advance for any help.

Ken

Here is the command button code:

CODE
Private Sub cmdMakeXMLs_Click()
On Error GoTo Err_MakeXMLs_Click

Dim strXML As String
Dim fso As New Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim sFile As FileDialog ' Windows File browser
Dim Selection As String
Dim rs As DAO.Recordset
Dim xmlQuery As String

xmlQuery = "qryMetadata"

Set rs = CurrentDb.OpenRecordset(xmlQuery)

' Set variable
Set sFile = Application.FileDialog(msoFileDialogFolderPicker)

' Open and check for CANCEL
With sFile
    .AllowMultiSelect = False
    If .Show <> -1 Then
      Exit Sub
    End If
End With

' Store full path
Selection = sFile.SelectedItems(1)

'Pass to functions which build XML strings, and store the returned strings

With rs
      
Do While Not .EOF
        
strXML = HDxml()
strXML = Replace(strXML, "xmlCurrentDate", Format(Date, "yyyy - mm - dd"))
strXML = Replace(strXML, "xmlHD_AssetName", [HD_AssetName])
strXML = Replace(strXML, "xmlHD_PackageAsset", [HD_PackageAsset])
strXML = Replace(strXML, "xmlHD_MovieAsset", [HD_MovieAsset])
strXML = Replace(strXML, "xmlHD_PosterAsset", [HD_PosterAsset])
strXML = Replace(strXML, "xmlHD_TitleAsset", [HD_TitleAsset])
strXML = Replace(strXML, "xmlTitleSort", [TitleSort])
strXML = Replace(strXML, "xmlTitleBrief", [TitleBrief])
strXML = Replace(strXML, "xmlTitleDisplay", [TitleDisplay])
strXML = Replace(strXML, "xmlSummary", [Summary])
strXML = Replace(strXML, "xmlRating", [Rating])
strXML = Replace(strXML, "xmlCaptions", [Captions])
strXML = Replace(strXML, "xmlRunTime", [RunTime])
strXML = Replace(strXML, "xmlDisplayTime", [DisplayTime])
strXML = Replace(strXML, "xmlReleasedYear", [ReleasedYear])
strXML = Replace(strXML, "xmlCategory", [Category])
strXML = Replace(strXML, "xmlGenre", [Genre])
strXML = Replace(strXML, "xmlStartDate", [StartDate])
strXML = Replace(strXML, "xmlEndDate", [EndDate])

    'open the XML file with the name specified in the variable HD_AssetName.
    Set ts = fso.OpenTextFile(Selection & [HD_AssetName] & ".xml", ForWriting, True)

    'with the file open, write the xml text stream to it
    ts.Write strXML

    'close the text stream object
    ts.Close
    Set ts = Nothing
    
    'Verify that the file was written successfully and send an alert if it was not
        If Not fso.FileExists(Selection & [HD_AssetName] & ".xml") Then
            MsgBox "There was an error creating " & [HD_AssetName] & ".xml" & ".", vbOKOnly
        End If

.MoveNext
Loop

.Close
End With
        
' Release variable
Set sFile = Nothing

Exit_MakeXMLs_Click:
    Exit Sub
Err_MakeXMLs_Click:
    MsgBox "MakeXMLs_Click Error# " & Err.Number & " - " & Err.Description
End Sub
Go to the top of the page
 
+
bulsatar
post May 6 2012, 02:35 PM
Post #2

UtterAccess Ruler
Posts: 1,175
From: Indiana, USA
The thing that I notice is that the calling of the field is incorrect (if I read your code correctly). You have with rs but no ![field name]. If this is a field name of rs:

strXML = Replace(strXML, "xmlHD_AssetName", [HD_AssetName])
strXML = Replace(strXML, "xmlHD_PackageAsset", [HD_PackageAsset])

Then it should be this (notice the exclimations)

strXML = Replace(strXML, "xmlHD_AssetName", ![HD_AssetName])
strXML = Replace(strXML, "xmlHD_PackageAsset", ![HD_PackageAsset])

As long as your function doesn't call a field, then it should only be in this area that the problems arises. Can you verify by telling us what line it conks out at?
Go to the top of the page
 
+
madmix
post May 6 2012, 04:37 PM
Post #3

UtterAccess Addict
Posts: 259
Hi bulsatar,

That was it. Thanks! I am still having a slight issue, though. I have a folder picker for the user to select what folder the files should go into. I just tested and made a folder called "Test" on my desktop and selected that as my output folder with the chooser. Unfortnately all of the files wound up on the desktop, outside of the folder, and with the word "test" before the filename. What could be wrong with the code. I think the issue may be with this line of code:

CODE
Set ts = fso.OpenTextFile(Selection & ![HD_AssetName] & ".xml", ForWriting, True)


I added a messagebox to verify that my Selection variable was populating properly and it is:

CODE
Selection = sFile.SelectedItems(1)
MsgBox Selection, vbOKOnly


I reall appreciate your help.

Thanks again,

Ken

This post has been edited by madmix: May 6 2012, 04:38 PM
Go to the top of the page
 
+
bulsatar
post May 6 2012, 04:58 PM
Post #4

UtterAccess Ruler
Posts: 1,175
From: Indiana, USA
A couple of things...might try and concact the string outside of the function. Also might want to check for the trailing "\" at the end of the path of Selection. Selection might also be a reserved word and throwing up something strange on occasion. If the selection is missing the trailing slash, then try this:

ThisSelection = sFile.SelectedItems(1)
FulltxtPath = ThisSelection & "\" & ![HD_AssetName] & ".xml"

Set ts = fso.OpenTextFile(FulltxtPath, ForWriting, True)

If the trailing slash is there during folder selection then drop the: & "\"

Hope that helps
Go to the top of the page
 
+
madmix
post May 6 2012, 05:02 PM
Post #5

UtterAccess Addict
Posts: 259
Yes it hit me a few seconds ago that this is what it was. I realized that the files were landing just one level short of the intended target and that the foldername was being appended to the filename. I will try it out in a few minutes and post back ASAP.

Thanks!!!

This post has been edited by madmix: May 6 2012, 05:03 PM
Go to the top of the page
 
+
madmix
post May 6 2012, 07:54 PM
Post #6

UtterAccess Addict
Posts: 259
That was it... I forgot the trailing slash. Thank you sooo much for all your help!!! I did run into another snag, though. I parameterized the query (added "Between [start] And [end]" criteria) and now I am gettin an error stating "too many paramters, expected (2)". I tried adding these to the parameter list in the query design but that didn't help. I am guessing there is something with the way the recordset is being handled in the code that is not liking the parameters. Is there a way to make this work? I thought about adding the SQL code to my subroutine but it is quite complex and I am not sure even how to begin.

I confess I am not so much of a hard core coder as a copy an paste guy. I can follow logic and gain an understanding to piece together what I need but I don't have a great deal of knowledge of VBA syntax and the various methods and such (IMG:style_emoticons/default/confused.gif) .


Best regards,

Ken

This post has been edited by madmix: May 6 2012, 08:26 PM
Go to the top of the page
 
+
fkegley
post May 7 2012, 09:12 AM
Post #7

UtterAccess VIP
Posts: 23,583
From: Mississippi
The code window does not always understand parameters being passed in from a form. That is the problem. There are two ways around it:

The simplest way is to use the Eval function around the parameter strings in the query, like this:

Eval('[Forms]![NameOfForm]![NameOfControl]')

Note the ' around the reference to the form control. Eval is expecting a text string, that it will then evaluate and return to the calling procedure, in this case your query.

The other way is way harder than this one.
Go to the top of the page
 
+
madmix
post May 7 2012, 11:00 AM
Post #8

UtterAccess Addict
Posts: 259
Hi Frank,

Thanks for your advice. I tried the eval function as you recommended but I received an error #3464 Data type mismatch in query expression. I tried changing the data format in my form fields to plain text but still the error was triggered. I also tried typing the dates as a string directly into the function but still got the error, which has me puzzled as I thought for sure that would work.

I am almost afraid to ask, but what's the other way?

(IMG:style_emoticons/default/wary.gif)

Thanks,

Ken

UPDATE:

I got it to work, thanks. I had neglected to place the actual word "Eval" before the parameter string for my [EndDate] field.

Thank you again!!!

Ken

This post has been edited by madmix: May 7 2012, 11:08 AM
Go to the top of the page
 
+
fkegley
post May 7 2012, 12:53 PM
Post #9

UtterAccess VIP
Posts: 23,583
From: Mississippi
You're welcome, Ken. I am glad I could help.

The other way is considerably harder believe me. I involves setting the parameters in the code window, like this:

Set qdef = db.Queries("NameOfQuery")
qdef.Parameters(0).[Forms!NameOfForm!NameOfControl] = [Forms]![NameOfForm]![NameOfControl]
etc. until all parameters defined.

I used to to it like the above, then I discovered the Eval function. Much easier, it goes and gets the value from the form control, then places that into your query.
Go to the top of the page
 
+
« Next Oldest · Access Records · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 20th May 2013 - 04:36 AM