By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,935 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

VB in Module to access Query Results

P: n/a
Hi,

I'm using Access 97.

I can run a query that finds a number of records, I then need to
access these results to build a formatted text file containing all of
the results. There will be 25 fields per record and potentially any
number of records.

I would have liked to have just produced a report sent to file but
formatted as I need. I don't think I can do this with a report as the
formatting gets messed up, so I think I really need to use VB to take
the results of the query and output them into the text file.

Can someone please give me a start in building the VB to access the
query and loop through the records.

Regards,

Bernard D
Aug 22 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
If you can create a query that gives you the results you need, TransferText
would export it to a text file. No looping needed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bernard D" <bd****@castlecs.co.ukwrote in message
news:ot********************************@4ax.com...
Hi,

I'm using Access 97.

I can run a query that finds a number of records, I then need to
access these results to build a formatted text file containing all of
the results. There will be 25 fields per record and potentially any
number of records.

I would have liked to have just produced a report sent to file but
formatted as I need. I don't think I can do this with a report as the
formatting gets messed up, so I think I really need to use VB to take
the results of the query and output them into the text file.

Can someone please give me a start in building the VB to access the
query and loop through the records.

Regards,

Bernard D

Aug 22 '06 #2

P: n/a
On Tue, 22 Aug 2006 18:24:53 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:
>If you can create a query that gives you the results you need, TransferText
would export it to a text file. No looping needed.
Yes, but the output file needs to have a specific format - one field
per line with blank lines where appropriate.

Does TransferText do that?
Aug 22 '06 #3

P: n/a
"Bernard D" <bd****@castlecs.co.ukwrote in message
news:ot********************************@4ax.com...
Hi,

I'm using Access 97.

I can run a query that finds a number of records, I then need to
access these results to build a formatted text file containing all of
the results. There will be 25 fields per record and potentially any
number of records.

I would have liked to have just produced a report sent to file but
formatted as I need. I don't think I can do this with a report as the
formatting gets messed up, so I think I really need to use VB to take
the results of the query and output them into the text file.
Have you looked at using Mail Merge to output your data to Word?

Keith.
www.keithwilby.com
Aug 22 '06 #4

P: n/a
Okay, if you want to programmatically loop the records, exporting one field
per line, this is the basic idea:

Function ExportText() As Long
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Open "C:\MyFile.txt" For Output As #1
Set rs = DBEngine(0)(0).OpenRecordset("Query1")

Do While Not rs.EOF
For Each fld In rs.Fields
Print #1, fld.Name, fld.Value
Next
Print #1, "----------------"
rs.MoveNext
Loop

'Return the number of records exported.
ExportText = rs.RecordCount
rs.Close
Close #1
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bernard D" <bd****@castlecs.co.ukwrote in message
news:6m********************************@4ax.com...
On Tue, 22 Aug 2006 18:24:53 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:
>>If you can create a query that gives you the results you need,
TransferText
would export it to a text file. No looping needed.

Yes, but the output file needs to have a specific format - one field
per line with blank lines where appropriate.

Does TransferText do that?

Aug 22 '06 #5

P: n/a
On Tue, 22 Aug 2006 13:47:09 +0100, "Keith Wilby" <he**@there.com>
wrote:
>"Bernard D" <bd****@castlecs.co.ukwrote in message
news:ot********************************@4ax.com.. .
>Hi,

I'm using Access 97.

I can run a query that finds a number of records, I then need to
access these results to build a formatted text file containing all of
the results. There will be 25 fields per record and potentially any
number of records.

I would have liked to have just produced a report sent to file but
formatted as I need. I don't think I can do this with a report as the
formatting gets messed up, so I think I really need to use VB to take
the results of the query and output them into the text file.

Have you looked at using Mail Merge to output your data to Word?

Keith.
www.keithwilby.com
No. Mailmerge won't do.

This MUST be a text file, one field per line and 25 lines per record -
its for importing into a banking system, so it has to conform.

Thanks anyway,

Bernard D
Aug 22 '06 #6

P: n/a
On Tue, 22 Aug 2006 23:21:16 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:
>Okay, if you want to programmatically loop the records, exporting one field
per line, this is the basic idea:

Function ExportText() As Long
....
>End Function
Many thanks that looks like I might be able to use it.

Cheers,

Bernard D
Aug 22 '06 #7

P: n/a
"Bernard D" <bd****@castlecs.co.ukwrote in message
news:gb********************************@4ax.com...
On Tue, 22 Aug 2006 13:47:09 +0100, "Keith Wilby" <he**@there.com>
wrote:
>>>

Have you looked at using Mail Merge to output your data to Word?

Keith.
www.keithwilby.com
No. Mailmerge won't do.

This MUST be a text file, one field per line and 25 lines per record -
its for importing into a banking system, so it has to conform.
Fair enough, just a thought.

Keith.
Aug 23 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.