Create continuos Dialogs

[daniel]

Hello,

I'm trying to do a Dialog box that can be used for data entry. This form should have about 9 columns and over 75 rows, but I want the dialog box to show 10 rows at time and when those are filled, there would be a button that would create basically another dialog box for rows 11-20 and so on.

Then when all data is entered, I want the macro to sort these rows from oldest to newest date. After the sorting has been done, I need for the macro to send the first 75 rows and columns to another screen, which would require evey field to have it's own name. The records over 75, I need for the macro to write a text file or perhaps create an access table where they will be stored for future use.

In this dialog, I need for the macro to search for records 76-150 and send them to another screen at a later time.

Is this possible to do? Thanks for you help.

-daniel

[Bob Hansen]

The quick answer is probably YES, this could be done with Macro Scheduler, but it may not be pretty with Dialogs.

From your description, you might want to consider using Macro Scheduler to open up a copy of Access or OpenOffice.org Calc and perhaps creating some macros in those programs that are called by Macro Scheduler.

[daniel]

Thanks for your advice.

After posting the message I thought of another way of doing this, but I still need some help. I thought that maybe if I create a form in Access where the user can enter the data, the I would create and .MDE (Access stand-alone executable, but I'm sure you knew) file and link the master table so the user only has access to the form. In this form, around 1000 records would be entered daily and about 75 would be sent to a Telnet screen using Send>%variable%.....What I need help with is the code that tells the macro to look up the Access table and get the desired field and paste it in the screen.

I don't know which command from Macro Scheduler I could use to get this records. I also need a loop that gets first 75 rows from the Access table and marks on a field in the table that they have been used, so to speak.

What would you advice for this....

-daniel

[Bob Hansen]

Yes, I am familiar with MDE, but it is good practice on these forums to explain as much as possible. No one knows everythng, and we can always be learning from one another.

Separate from Macro Scheduler, you should porbably use an Update Query in Access to add the fields, and limit the records to the first 75 records.

Not sure what you want to telnet, but suggest you complete work in Access first, then we can help to export/make an image, etc, and move to desired destination. Desing your Acdess Tables, Queries, and Forms. Make your UpdateQuery and then we can handle the results of that query.

[daniel]

Thanks for you help again.

What do I need the Access qry to do in order for me to get all those fields from the query and send them to another screen as Send/Character Text> comand?

[Bob Hansen]

Although this is not an Access forum, here are some preliminary guidelines.

To ID and mark the records to be sent out:
1. You need one numeric field in each record that can be used as a "counter" or an "index" to help you locate the 75 records that you want. Should be Mandatory, Unique with no duplicates.

2. You need one Boolean field in each record that can hold the value that indicates that the record has been "read" or "transferred". For here, call it "Read". Mandatory=yes, Default = NO

3. Go to Query Design. Select the table/query that contains the records.
Make the query type an Update Query.
Select the "Read" field. to be visible.
Enter the value "YES" into the row for the new value under that field.
Enter . It you will be, then you will want to use the final Access Query results sent to a text file that can use Separate> in Macro Scheduler with the Send> command.
=========================================
Let me suggest that you complete the section above, making the final export query first, so that you can finally run a query of the 75 records you want with the READ field already updated. When that is done, we can come back to use Macro Scheduler to do the transfer to your destination in the format you want.

[daniel]

Ok... So I got Access to create my text files with only 75 records that I need. Then, I have to so that it saves this files as a text file, tab delimited. Let's say one of the files is named 0105758a.txt and the other is named 0105758b.txt and they are located is \\dab1\projects\files.

The files are text files are tab delimited and the first line contains headers. What I need the macro to do now is go to the first line and get the first first and name it the name in the header and add a 1 for the first line. For example, if the first field on that line is a date and the header says DATE, I would like for that variable to be named DATE1. If the second field is named LASTNAME, I would like the last name on the first line to be LASTNAME1 all the way to line 76. There will 76 lines in the text file because the first one has the header.

Now I need to use send to send this fields to the screen.

It would go something like this;

SetFocus>WindowName
Press HOME
Press TAB
Send>DATE1 or %DATE1%
Press TAB
Send>LASTNAME1 or %LASTNAME%
Press ENTER

I would need the commands above to repeat until we get to DATE75 and LASTNAME75.

Does this make any sense to you?

With the text file names given above and the locations of the file, do you think I can create a macro that prompts the user for a varible, which would be the name of the text file (0105758a) then it would run the GetFileList> command and the Separate> to get each variable>

Thanks again for all your help. I'm not a programmer, obviously, and perhaps this is well over my head, but hopefully I can get this done.

[Bob Hansen]

OK,getting closer now.....
no time for scrpt now but here is an outline:
==========================================

Input> To select file name (use browse button with a default value?)
ReadLn>FileName,Line1,result

//Collect Line 1 values for use as Labels.
Create Loop for number of fields on line (9?)
Separate>Line1 and put each value into variable like Label1, Label2,....Label9
End loop after doing the nine fields.

//Send values of lines 2-76
Now make another loop for count of 75. Start loop counter at 2 vs. 1 and continue until = 76.

Read Line>FileName, Line 2,result
Separate>Line2 into variables like Value1, Value2....Value9
Use the script you have shown in your posting to SetFocus, Tab to position and Send>%Label1%%Value1%, TAB, Send>%Label2%%Value2% for all ninve values.
Now increase loop counter to handle Line3, and continue until all 75 lines are done.
===================================

(I would probably use a ";" vs. a tab for a delimiter in the file from Access. It depends on content, but I don't like invisible characters, and using a comma in Separate> has required some special handling.