Using VBA to wirte script.

[Robert]

This question is for an MS Access Guru/Wizard.
I have a command button on a form. When the button is pressed, I want
Access to write a script into a notepad file using the data from the current
records on the form. The scipt looks like this:

send <f2>
wait record
send <back-tab>
send "n<f2>"
wait record
send <enter>
wait record
send "2119<tab>"
send <tab>
send <tab>
send "<tab>"
send <tab>
send "n<tab>"
send <tab>
send "26301<tab>"

I am not sure what language this is, but I assume it is a very simple
script. Can this be done, Access Guru's? Thanks

 

[Douglas J Steele]

I'm unsure as to what you're really needing, since there doesn't appear to
be anything in that script that replies on the data on the form.

To write text to a file, you can use code like:

Dim intFile As Integer
Dim strFile As String

strFile = "C:\Script.txt"
intFile = FreeFile()

Open strFile For Output As #intFile

Print #intFile, "send <f2>"
Print #intFile, "wait record"
Print #intFile, "send <back-tab>"
Print #intFile, "send ""n<f2>"""
Print #intFile, "wait record"
Print #intFile, "send <enter>"
Print #intFile, "wait record"
Print #intFile, "send ""2119<tab>"""
Print #intFile, "send <tab>"
Print #intFile, "send <tab>"
Print #intFile, "send ""<tab>"""
Print #intFile, "send <tab>"
Print #intFile, "send ""n<tab>"""
Print #intFile, "send <tab>"
Print #intFile, "send ""26301<tab>"""

Close #intFile

 

[Dirk Goldgar]

This question is for an MS Access Guru/Wizard.
I have a command button on a form. When the button is pressed, I want
Access to write a script into a notepad file using the data from the
current records on the form. The scipt looks like this:

send <f2>
wait record
send <back-tab>
send "n<f2>"
wait record
send <enter>
wait record
send "2119<tab>"
send <tab>
send <tab>
send "<tab>"
send <tab>
send "n<tab>"
send <tab>
send "26301<tab>"

I am not sure what language this is, but I assume it is a very simple
script. Can this be done, Access Guru's? Thanks

It doesn't really need a guru or wizard, but it does need some
information as where the "data from the current records on the form"
comes into it. What you posted is all hard-coded, literal values.
Here's a simple Click event procedure that writes out exactly what you
posted into a file named "C:\Temp\Script.txt". Can you take it from
here? Warning -- this is untested "air code", but it should be pretty
close to correct.

'----- start of example code -----
Private Sub cmdYourButton_Click()

Dim intFile As Integer
Dim strFilePath As String

strFilePath = "C:\Temp\Script.txt"

' Get first free file number.
intFile = FreeFile()

Open strFilePath For Output As #intFile

Print #intFile, "send <f2>"
Print #intFile, "wait record"
Print #intFile, "send <back-tab>"
Print #intFile, "send ""n<f2>"""
Print #intFile, "wait record"
Print #intFile, "send <enter>"
Print #intFile, "wait record"
Print #intFile, "send ""2119<tab>"""
Print #intFile, "send <tab>"
Print #intFile, "send <tab>"
Print #intFile, "send ""<tab>"""
Print #intFile, "send <tab>"
Print #intFile, "send ""n<tab>"""
Print #intFile, "send <tab>"
Print #intFile, "send ""26301<tab>"""

Close #intFile

End Sub
'----- end of example code -----

 

[Dirk Goldgar]

I'm unsure as to what you're really needing, since there doesn't
appear to be anything in that script that replies on the data on the
form.

To write text to a file, you can use code like:

Dim intFile As Integer
Dim strFile As String

strFile = "C:\Script.txt"
intFile = FreeFile()

Open strFile For Output As #intFile

Print #intFile, "send <f2>"
Print #intFile, "wait record"
Print #intFile, "send <back-tab>"
Print #intFile, "send ""n<f2>"""
Print #intFile, "wait record"
Print #intFile, "send <enter>"
Print #intFile, "wait record"
Print #intFile, "send ""2119<tab>"""
Print #intFile, "send <tab>"
Print #intFile, "send <tab>"
Print #intFile, "send ""<tab>"""
Print #intFile, "send <tab>"
Print #intFile, "send ""n<tab>"""
Print #intFile, "send <tab>"
Print #intFile, "send ""26301<tab>"""

Close #intFile

Gee, Doug, yours looks a lot like mine! You must have copied me. Oh,
wait. Yours was posted before mine. I guess I must have copied you.

 

[Robert]

Pleas let me be more specific.

I use a form to input data into a query. This query links two different
tables and houses data in them. The data in the two tables share a
"requisition" number. There is one requisition number in table 1, linked to
many records in table 2. I want the command button to take the housed data
from these two tables and write to a txt file in the framework of the script
form as outlined previously. Please let me know if this helps. Thanks.

 

[Dirk Goldgar]

Pleas let me be more specific.

I use a form to input data into a query. This query links two
different tables and houses data in them. The data in the two tables
share a "requisition" number. There is one requisition number in
table 1, linked to many records in table 2. I want the command button
to take the housed data from these two tables and write to a txt file
in the framework of the script form as outlined previously. Please
let me know if this helps. Thanks.

I don't know about Doug, but I have no idea where in the framework of
the script the data is supposed to go. How about reposting the script
with the field names, enclosed in [], substituted in?

 

[Robert]

OK, let me see.

Let's say we have an input field on the form that the user enters a date into:
ex. 06/22/05

Then, there is a field on the form that the user enters their name:
ex. John Doe

Finally, there is a field on the form that the user enters a dollar amount:
ex. $5.00

Once the user has entered this information, they would then hit the "Write
Script" button:

The script behind this button should take information from these three
fields and write the code below into a .psl or notepad file:

send "062205"
wait record
send "JohnDoe"
wait record
send "5.00"
waitrecord

If I could get this part to work, i could probably figure out the rest. I
am just not sure how to write the code behind the command button that will
transfer the data.

I am very appreciative of your efforts thus far.


--
RSF

Pleas let me be more specific.

I use a form to input data into a query. This query links two
different tables and houses data in them. The data in the two tables
share a "requisition" number. There is one requisition number in
table 1, linked to many records in table 2. I want the command button
to take the housed data from these two tables and write to a txt file
in the framework of the script form as outlined previously. Please
let me know if this helps. Thanks.

I don't know about Doug, but I have no idea where in the framework of
the script the data is supposed to go. How about reposting the script
with the field names, enclosed in [], substituted in?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

OK, let me see.

Let's say we have an input field on the form that the user enters a
date into: ex. 06/22/05

Then, there is a field on the form that the user enters their name:
ex. John Doe

Finally, there is a field on the form that the user enters a dollar
amount: ex. $5.00

Once the user has entered this information, they would then hit the
"Write Script" button:

The script behind this button should take information from these three
fields and write the code below into a .psl or notepad file:

send "062205"
wait record
send "JohnDoe"
wait record
send "5.00"
waitrecord

If I could get this part to work, i could probably figure out the
rest. I
am just not sure how to write the code behind the command button that
will transfer the data.

I am very appreciative of your efforts thus far.

Assuming the code has already opened the output file and written any
preliminary lines, the lines you just posted could be written with code
that looks like this:

Print #intFile, "send """; Format(Me!DateField, "mmddyy"); """"
Print #intFile, "wait record"
Print #intFile, "send """; Me!NameField; """"
Print #intFile, "wait record"
Print #intFile, "send """; Format(Me!AmountField, "0.00"); """"

Of course, you'd have to replace "DateField", "NameField", and
"AmountField" in the above code with the names of fields on your form.
If those names contain spaces or other non-standard characters, you'll
have to enclose them in square brackets ([]).

Note that your post suggests that a name "John Doe" should have its
embedded space removed when it's written to the script (as "JohnDoe").
I didn't do that in the above code, because I wasn't sure you meant it.
If you did, you could write the relevant line as:

Print #intFile, "send """; Replace(Me!NameField, " ", ""); """"

 

[Robert]

Dirk, this is starting to work. I can't thank you enough. If I may, please,
another question:

What if I want to combine the date and cost into one send line, like this:

send "0622055.00"
wait record

Also, how do I differentiate between line items. This is the way I have it
set up.

There is a requisition (or oder) number.
Within this order number are line numbers one through X
Each line number holds a part number, quantity to be ordered, unit price,
and total price for the line item.

Now let's say that I am looking at requisition X on my form. It has two
line items:

I click the command button that generates the script.

Then I go to the next requisition, requisition Y. It has three line items:

I click the command button that generates the script for this requisition.

How do I know that the two .txt files created hold unique data based on the
requisition number? Thanks.


--
RSF

OK, let me see.

Let's say we have an input field on the form that the user enters a
date into: ex. 06/22/05

Then, there is a field on the form that the user enters their name:
ex. John Doe

Finally, there is a field on the form that the user enters a dollar
amount: ex. $5.00

Once the user has entered this information, they would then hit the
"Write Script" button:

The script behind this button should take information from these three
fields and write the code below into a .psl or notepad file:

send "062205"
wait record
send "JohnDoe"
wait record
send "5.00"
waitrecord

If I could get this part to work, i could probably figure out the
rest. I
am just not sure how to write the code behind the command button that
will transfer the data.

I am very appreciative of your efforts thus far.

Assuming the code has already opened the output file and written any
preliminary lines, the lines you just posted could be written with code
that looks like this:

Print #intFile, "send """; Format(Me!DateField, "mmddyy"); """"
Print #intFile, "wait record"
Print #intFile, "send """; Me!NameField; """"
Print #intFile, "wait record"
Print #intFile, "send """; Format(Me!AmountField, "0.00"); """"

Of course, you'd have to replace "DateField", "NameField", and
"AmountField" in the above code with the names of fields on your form.
If those names contain spaces or other non-standard characters, you'll
have to enclose them in square brackets ([]).

Note that your post suggests that a name "John Doe" should have its
embedded space removed when it's written to the script (as "JohnDoe").
I didn't do that in the above code, because I wasn't sure you meant it.
If you did, you could write the relevant line as:

Print #intFile, "send """; Replace(Me!NameField, " ", ""); """"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk, this is starting to work. I can't thank you enough. If I may,
please, another question:

What if I want to combine the date and cost into one send line, like
this:

send "0622055.00"
wait record

Print #intFile, "send """; _
Format(Me!DateField, "mmddyy"); _
Format(Me!AmountField, "0.00"); _
""""
If your amount field needs to be a fixed with (possibly with leading
zeros), it's marginally more complicated

Also, how do I differentiate between line items. This is the way I
have it set up.

There is a requisition (or oder) number.
Within this order number are line numbers one through X
Each line number holds a part number, quantity to be ordered, unit
price, and total price for the line item.

Now let's say that I am looking at requisition X on my form. It has
two line items:

I click the command button that generates the script.

Then I go to the next requisition, requisition Y. It has three line
items:

I click the command button that generates the script for this
requisition.

How do I know that the two .txt files created hold unique data based
on the requisition number? Thanks.

I'm not sure what you're asking. I don't know how the multiple line
items come into play, nor how they are represented in your tables and on
your form. If you're supposed to have one output text file for each
distinct requisition number, you could embed the requisition number in
the name of the file when you create it. E.g.,

strFilePath = _
"C:\Temp\ScriptReqNo" & _
Me.RequisitionNumber & _
".txt"

 

[Robert]

Again, I understand how this works

Now, what if the first two fields (and there data) are on a main from, and
the other is on a sub-form?

 

[Dirk Goldgar]

Again, I understand how this works

Now, what if the first two fields (and there data) are on a main
from, and the other is on a sub-form?

What if you give me more complete information? Like what are the tables
involved, how are they related, what's are recordsource for the form and
the subform, and what do you actually want the output file to look like?

 

[T]

OK, let me see.

Let's say we have an input field on the form that the user enters a date
into:
ex. 06/22/05

Then, there is a field on the form that the user enters their name:
ex. John Doe

Finally, there is a field on the form that the user enters a dollar
amount:
ex. $5.00

Once the user has entered this information, they would then hit the "Write
Script" button:

The script behind this button should take information from these three
fields and write the code below into a .psl or notepad file:

send "062205"
wait record
send "JohnDoe"
wait record
send "5.00"
waitrecord

If I could get this part to work, i could probably figure out the rest. I
am just not sure how to write the code behind the command button that will
transfer the data.

I am very appreciative of your efforts thus far.


--
RSF

Pleas let me be more specific.

I use a form to input data into a query. This query links two
different tables and houses data in them. The data in the two tables
share a "requisition" number. There is one requisition number in
table 1, linked to many records in table 2. I want the command button
to take the housed data from these two tables and write to a txt file
in the framework of the script form as outlined previously. Please
let me know if this helps. Thanks.

I don't know about Doug, but I have no idea where in the framework of
the script the data is supposed to go. How about reposting the script
with the field names, enclosed in [], substituted in?

Let's say we have an input field on the form that the user enters a date
into:
ex. 06/22/05

Then, there is a field on the form that the user enters their name:
ex. John Doe

Finally, there is a field on the form that the user enters a dollar
amount:
ex. $5.00

Once the user has entered this information, they would then hit the "Write
Script" button:

The script behind this button should take information from these three
fields and write the code below into a .psl or notepad file:

send "062205"
wait record
send "JohnDoe"
wait record
send "5.00"
waitrecord

If I could get this part to work, i could probably figure out the rest. I
am just not sure how to write the code behind the command button that will
transfer the data.

I am very appreciative of your efforts thus far.


--
RSF

Pleas let me be more specific.

I use a form to input data into a query. This query links two
different tables and houses data in them. The data in the two tables
share a "requisition" number. There is one requisition number in
table 1, linked to many records in table 2. I want the command button
to take the housed data from these two tables and write to a txt file
in the framework of the script form as outlined previously. Please
let me know if this helps. Thanks.

I don't know about Doug, but I have no idea where in the framework of
the script the data is supposed to go. How about reposting the script
with the field names, enclosed in [], substituted in?

--
Dirk Goldgar, MS Access MVP
www.datagnSpainosticsostics.com

(please reply to the newsgroup)