How do you append to a file using TransferText option in Access 1

[keusch]

Problem:
I need to export data from Access that is created on the fly. This is to be written to a text file. I can create a file but I need to append successive data to the file as it runs through the loop.

Current Code:
The data set is generated with select criteria and creates a list.
For Each varItem In Forms![frm_date_year]![lstSelect].ItemsSelected
strRig = Forms![frm_date_year]![lstSelect].ItemData(varItem)
Forms![frm_date_year]![txtholditem] = strRig
DoCmd.OpenQuery "qry_export"
DoCmd.Close acQuery, "qry_export"
DoCmd.TransferText acExportFixed, "export WSN", "qry_export", "filename.txt", False, ""
Next


Can anyone help ?????
Thanks

 

[PHV]

Why not exporting a single query combining all the "Rig" you want to export ?
could you please post the SQL code of qry_export (in fact the WHERE clause should suffice)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[keusch]

The user has several options for selection criteria so the where is a little lenghthy (uneditied version)

WHERE (((DATA_WELL.WSN) Is Not Null) AND ((Year([Spud_Date_Proposed])) Is Not Null) AND ((DATA_RIG.Spud_Date_Proposed)>=[Forms]![frm_geo_spud_date_year]![txtStartDate] And (DATA_RIG.Spud_Date_Proposed)<=[Forms]![frm_geo_spud_date_year]![txtEndDate]) AND ((DATA_RIG.RIG)=[Forms]![frm_geo_spud_date_year]![txtholditem]) AND ((DATA_WELL.GEOLOGIST)=[Forms]![frm_geo_spud_date_year]![cbo_geologist]) AND ((DATA_WELL.SPUD_DATE) Is Null) AND ((DATA_WELL.WELL_TYPE)="W"))
It would probably be easier to creat a second qry picklist and then join but
I'll do that if there's no solution to the append.

As always, thank you for your help.

 

[PHV]

You may try the following.
1) qry_export:
WHERE DATA_WELL.WSN Is Not Null AND DATA_WELL.SPUD_DATE Is Null AND DATA_WELL.WELL_TYPE='W'
AND (DATA_RIG.Spud_Date_Proposed Between [Forms]![frm_geo_spud_date_year]![txtStartDate] And [Forms]![frm_geo_spud_date_year]![txtEndDate])
AND DATA_WELL.GEOLOGIST=[Forms]![frm_geo_spud_date_year]![cbo_geologist]
AND InStr([Forms]![frm_geo_spud_date_year]![txtholditem],',' & DATA_RIG.RIG & ',')>0

2) creating the file:
strRig = ""
For Each varItem In Forms![frm_date_year]![lstSelect].ItemsSelected
strRig = strRig & "," & Forms![frm_date_year]![lstSelect].ItemData(varItem) & ","
Next
Forms![frm_date_year]![txtholditem] = strRig
DoCmd.TransferText acExportFixed, "export WSN", "qry_export", "filename.txt", False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[keusch]

PH,
I'll give it a whirl - I see the obvious solution on the concatenation - Thanks - I will be back intouch

 

[PHV]

Amendment...
2) creating the file:
strRig = ","
For Each varItem In Forms![frm_date_year]![lstSelect].ItemsSelected
strRig = strRig & Forms![frm_date_year]![lstSelect].ItemData(varItem) & ","
Next
Forms![frm_date_year]![txtholditem] = strRig
DoCmd.TransferText acExportFixed, "export WSN", "qry_export", "filename.txt", False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[keusch]

PH,
Thank you so much. I'd just saw the amendment posted but had already worked through that process. I was amazed with the instr usage in the SQL but after much thought and many efforts trying figure out why I couldn't use the txtholditem (with many alterations) by itself in the query, I was very happy with your solution. Being a newbie to Access VBA, I have found wonderful answers here - thanks for your ongoing help!!!!
Thanks!!!