access and multiple record formats
- Thread starter monad
- Start date
1. In the VB code, get a recordset with the data required for the output.
2. Write set of functions (each function corresponds to one required output format), that accept as parameter recordset row, and will format it accroding to the different formats that you require.
Hope it helps..
2. Write set of functions (each function corresponds to one required output format), that accept as parameter recordset row, and will format it accroding to the different formats that you require.
Hope it helps..
- Local time
- Today, 08:12
- Joined
- Feb 28, 2001
- Messages
- 30,559
monad, you are doing something that isn't done so often that we would be likely to have a link to it.
What you want to do is not going to work from the normal "transfer text" methods because of the different formats. That is, DoCmd.TransferText doesn't give you a chance to do anything between records. It doesn't support a formatting "callback" event or method. If you choose to output the data using a report converted via export, again you have the same general problem.
What you want to do is write a VBA function. If you aren't familiar with these keywords and concepts, look up
OPEN, CLOSE verbs in VBA (open file, close file)
PRINT and PRINT USING verbs.
FORMAT function.
String concatenation.
Recordset operations
Select statements
The skeleton of what you want would resemble:
OPEN filename FOR OUTPUT AS FILE 1 {some options exist, read the help}
open a recordset to your data. Reset to the first record.
Loop-top:
determine which of the six formats you want to use.
Select fmt-number
case 1
build the string containing the line in format 1
case 2
build the string containing the line in format 2
etc.
end select
write the string you just build with a PRINT verb
step to the next record in the recordset.
if not at EOF yet, go back to the top of the loop and do this again.
CLOSE #1
close the recordset.
That's it, you are done.
What you want to do is not going to work from the normal "transfer text" methods because of the different formats. That is, DoCmd.TransferText doesn't give you a chance to do anything between records. It doesn't support a formatting "callback" event or method. If you choose to output the data using a report converted via export, again you have the same general problem.
What you want to do is write a VBA function. If you aren't familiar with these keywords and concepts, look up
OPEN, CLOSE verbs in VBA (open file, close file)
PRINT and PRINT USING verbs.
FORMAT function.
String concatenation.
Recordset operations
Select statements
The skeleton of what you want would resemble:
OPEN filename FOR OUTPUT AS FILE 1 {some options exist, read the help}
open a recordset to your data. Reset to the first record.
Loop-top:
determine which of the six formats you want to use.
Select fmt-number
case 1
build the string containing the line in format 1
case 2
build the string containing the line in format 2
etc.
end select
write the string you just build with a PRINT verb
step to the next record in the recordset.
if not at EOF yet, go back to the top of the loop and do this again.
CLOSE #1
close the recordset.
That's it, you are done.
