How to automate date change in Access macro

[dehere]

Maybe one of you Access geniuses can help me with this. I have this macro created whereby I automate the import process of the files which I have exported from HTR. It works great but I'm lazy and want to figure out how to automate the process further.

Here's the deal. I am importing four text files from HTR (HX3, HX4, HX5 and HX7). However, to do so I need to change the date of the "all" file to reflect the date I want imported for each of these four text files and the only way I can do this at this point is to actually open the macro, go to the appropriate "transfer text" entry and manually change the name of the text file to reflect the selected date. I have included a jpeg with this post to show you what I mean.

Is there a way that I can automate this process so that the selected date can be streamlined through a form or other tool?

I hope this question made sense. Any help would be appreciated.

[Rick]

NO.

For me the easy way is to have a separate directory to download and export from. That way I am always exporting to a ALL_HX?.txt file and the macro is always pointing to it.

Another way it to always move your ALL_HX?.txt files to a certain directory that is listed in the macro. If you always move your files you are always overwriting the older versions.

That works for me. You have to figure out what works for you.

[dehere]

The suggestion about creating a separate directory for the export files is good, but that raises two questions.

1) How do I redirect the export to a different directory from the one I am in? The export function in HTR does not seem to have an option to export to a specific directory.

2) How do I export a specific day's races to an All_HX* file as opposed to one which adds the date of the specified day's races? I can routinely export to an all file every day but because I only move files out of the main HTR folder every month that export (and the related import) will become unwieldy by the end of each month.

[Rick]

1) You can use the HDW download cart. It is easy to change directories and/or drives while using it.

2) Depending on how you use the racefiles after you have them downloaded, you could do something like setting up a directory for each day of the week. My feeling is that C:\htr is the worst place to download files. You can download todays races to C:\htr\saturday and once you have all the results and have done all your exporting you can move them to a monthly directory.

I keep racefiles with results and charts in directories by month and year. That works for me but will not fit everyones way of using HTR2. It is still a good way to organize your files to burn to CDs. It is much easier to keep old race files in extracted form than trying to unpack them from c:\htrdata\. I know cause I tried doing it that way for years. But that was before the cheap CD burners etc.

[hurrikane]

Dehere,

with just a tiny bit of code you could do this on a button on a form. run the macro and assign the table name at that time based on the date(today)

have you done anything with forms in the past? If so I could post the code up here and you could just paste it in....and then we could watch it not work for a little while while we figure out what I did wrong. :D

[dehere]

Hurrikane,

The short answer is "yes" I have worked some with forms. As you might be able to see from the jpeg, I have incorporated a form as part of this macro. The form permits me to enter the race date and track which I want to use to create my report. That is the last thing that happens in the macro (it opens the form so that I can enter the date and the track).

[edw]

I'm probably the last guy qualified to post a reply here but I get so few ideas that may help someone on this board I thought I'd suggest it if for no other reason than to, perhaps, stimulate other thoughts.

I've not yet used Access or written code in VB but if the goal is to get the use of an automatic date could you do the following:

1. Create a one field Access database to hold a date (or it could be a database with more than one field to hold other information that has to be referenced on occasion). "Seed" it with the date corresponding to the next data you want to load.

2. Have the macro that loads the data have code early on that grabs the date from the database so it knows the date you want it to use.

3. Every time you run the macro have one of the last statements of the macro replace the date in the database field with the previous date incremented by 1 day (date + 1) so that it is always ready with the correct date for the next day's data.

This may not be the most elegant solution but I think it may work.

[dehere]

Thanks for the suggestion but I'm not sure if it will do the trick. I don't print out reports daily so an automatic date will probably not be what I need. If I always printed out data for the next day then one might set up something that used today's date + 1 or, in the alternative, if I always printed out data for today's races, then simply grabbing today's date could work. However, that won't work either as sometimes I play with today's data and sometimes tomorrow's. But, what this macro does, in addition to other things, is deletes all records currently in my HX tables and substitutes that data for the day's races I am interested in. Each of these tables has the relevant date an en entry. Thus, if there were some way to grab that date that would do the trick.

At this point I have followed Rick's suggestion and have started downloading my race data into a "daily" folder and, then, after going through the extraction and export process, I simply move the date from that "daily" folder into my HTR folder where it becomes part of my current month's downloads. This way I simply point my macro to the "daily" folder and get what I need without having to enter a specific date manually into the macro each time.

Perhaps the HTR folder is not the best place to download things, but I find that it works to put my current month's data there and then, at the end of the month, move that data to a monthly folder and do an export of the entire month's data into HX_* files. I really like having the past several months data on my hard drive so that I can easily get charts and pp's for a specific race in the past when I need that as part of my handicapping process. Backing up on a CD is also good, but more for its utility as a backup than its easy accessibility on a day to day basis. But that's just my thoughts.

[Donnie]

Dehere--
have you tried inserting between each one of the import file commands in your macro a RunCommand (Rename) followed by a SendKeys command to send the new name of the file. I don't know if it will accept it, but the Now() line would append the system date/time to the new file name (I think). Something like Now()+HX3, or possibly Date()+HX3, may give you what you are looking for....you may have to look up the date commands specific for MSAccess.

[dehere]

Thanks, I'll give this a try and report back.

[MikeDee]

I stopped using the htr folder for data downloads a long time ago. I never liked putting data in a folder that contains program files.

I have a sub folder under the HTR folder I named today. When I run the HDW download wizard it sees any and all folders that one has as sub-folders of HTR and I can select the folder I want to download to.

At the end of the race day, or before I download a new day, I move the past days files to a monthly folder, which is also a sub folder of HTR. This way I can download results and charts directly into my monthly folder.

I changed the path on the file name line of my macro to look for the HX csv files in the today directory rather then the HTR directory.

Since the path is fixed in the macro if you waned you could make a copy of the macro rename it and change the path in the copy to a different folder. This way you could use one macro for a import from the today folder and a second macro for a import from the folder that holds past files that have results added.

Finally, although I have not done it, VBA code has "DOcmd" statements (do commands) that can put macro functions like the transfer text function into a VBA program. I know that you can use an input box to ask for user entered data like the "date" and then put this user response into the proper spot in a command string and run the transfer test function under program control.

If Donnies suggestion does not work, I can research the vba approach further and send you the code.

[hurrikane]

I tried this guys and couldn't get it to work. If you get it working let me know.

I'll try to post the code to do this on a button today.

[Rick]

Another sweet little trick is to edit the HTR2 icon properties.

You can change the Start in: to the directory you want HTR2 to default to, ie

"C:\HTR\today"

I just looked at mine and the quote marks are in there.

[Donnie]

sorry....off the top of my head that was the best I could come up with.

[hurrikane]

Henry,
this works. you have to put a button on a form. Also a text box named txtDayNo. this text box is to put in the number for the file. I tried it just pulling the day off of the computer but HTR exports by the day on the files not the day on the computer. So you could not do this with tomorrows files or files from last week. That is why I put in the textbox. IF you dont' want the text box and just import todays races with todays day number then comment out the rows that say for text box and uncomment (') the part that says for todays date.

This should work fine. but of course not the first time. It never works the first time.
if you need to change the path to your files.

Past this code into the click event for your button.



Dim stDocName1 As String
Dim stDocName2 As String
Dim stDocName3 As String
Dim stDocName4 As String

'if you are just using todays date
'strDocName1 = "C:\HTR\All" + Format(Date, "dd") + "_HX3.txt"
'strDocName2 = "C:\HTR\All" + Format(Date, "dd") + "_HX4.txt"
'strDocName3 = "C:\HTR\All" + Format(Date, "dd") + "_HX5.txt"
'strDocName4 = "C:\HTR\All" + Format(Date, "dd") + "_HX7.txt"

'if you want to specify the date number in a text box.
strDocName1 = "C:\HTR\All" + Me.txtDayNo.Value + "_HX3.txt"
strDocName2 = "C:\HTR\All" + Me.txtDayNo.Value + "_HX4.txt"
strDocName3 = "C:\HTR\All" + Me.txtDayNo.Value + "_HX5.txt"
strDocName4 = "C:\HTR\All" + Me.txtDayNo.Value + "_HX7.txt"

DoCmd.TransferText acImportDelim, "ALL_HX3 Import Specification", "All_HX3", strDocName1
DoCmd.TransferText acImportDelim, "ALL_HX4 Import Specification", "All_HX4", strDocName2
DoCmd.TransferText acImportDelim, "ALL_HX5 Import Specification", "All_HX5", strDocName3
DoCmd.TransferText acImportDelim, "ALL_HX7 Import Specification", "All_HX7", strDocName4

[hurrikane]

I forgot to tell you. YOu need to create the import spec files if you already haven't.

go through the usual manual steps to import the files. When you get to the last screen click the Advanced > SaveAs buttons.

Specify the name of the import spec file. I named the the prompted name but took out the reference to the dau no. What ever you name them make sure the code matches the names. These files tell it things like use first row for column name, and skip column info etc.

This is not the prettiest way but just fast and dirty.

If you get the other piece working let me know.

[dehere]

Thanks all for the help. I will hopefully find the time to play with it tonight but I have picked up bunches of other thoughts from this thread that may eliminate the need to add the date to the macro. Specifically, by having a "daily" or "today" folder that the day's races go into from the htr download before they are moved to the monthly folder seems like an easy solution. By doing so I eliminate the need for a date on the import entirely as there is only one day's races in that folder. I particularly like Rick's suggestion about changing the HTR icon to make the direct download to the "daily" folder. All these little baby steps are hopefully leading in the right direction. Thanks again.

[hurrikane]

I think that is the easiest way to too dehere. I dont' do that because I am moving from computer to computer all the time..it is easier for me to code it in.

[MikeDee]

When you change the start in directory, HTR2 will make a ini file and put it in the folder. It needs this ( I think) to keep a record of the time zone you selected.

I changed the properities of this file to archive. This way when I grab all the files in the folder "today" to move them to my "monthly" it won't let me move this file.

It is no biggie if you don't care about the time zone whenever the ini file is mising HTR2 just makes another one

[dehere]

For what its worth, I created two icons and placed both in my quick launch taskbar. Both icons point to the same program in the HTR folder, but one has a start location set at my monthly file and the other has a start location in a daily file. This way my downloads are set to go in the correct location. I can simply use one icon and download racefile and trainer file data in my daily folder, use the export feature and then delete all "visible" files when done. (It would be nice if there was a simple macro to automate that process.) Then I can use the other icon and download all four files for the previous day into my monthly folder each day. Nothing sophisticated but I think it will do what I want.

[MikeDee]

You might want to consider 2 transfer text macros in your access db as well one to look for your ".txt" export files in monthly and the other to look in today.

[dehere]

Okay, in principle things are working well with the new way of dealing with daily races. The problem is that, for some reason, I cannot get the auto-scratch feature or the odds feature that is part of ATR to work. I've changed the directory to the correct folder and I've successfully opened a scratch file from my "P" account, but when I go to the scratch screen there is not ATR Auto-Scratch button and when using the Odds button, all I get is an error message saying that Odds are not available for that race.

Any thoughts anyone?

[Rick]

Just what directory do you have ATRpro pointed at?

[dehere]

Well, I've tried it pointed to c:\HTR, c:\HTR\2005-03 (which is my current monthly folder where this month's races are located) and c:\HTR\daily (which is where my daily races are located which I export to access for my daily reports). I've downloaded today's races in each of these folders just to see what the problem might be. In no case does either the odds button or the auto scratch figure seem to work.

[Rick]

This is what worked for me just now.

I have htr2 opening in C:\htr\today

I have ATRpro set to C:\htr

It would not work when I changed ATRpro to C:\htr\today

[Rick]

Dumb question.

Did you click on the For All button under Scratches in ATRpro?

[dehere]

Not a dumb question - i'm sure I'm doing something dumb but that ain't it. I have a list of all scratches for all tracks open on my screen - they show for all tracks and it ain't working.

Here's the steps I followed.

1) Close HTR
2) Open ATR and make sure it is set up with c:\HTR as the directory. Click Go button.
3) Select "New R List" for current tracks. Enter password stuff in Philly Park site and minimize that screen.
4) Get scratches for all tracks and leave that screen open.
5) Select button for "get data on all tracks but display only Turfway"
6) Minimize ATR screen
7) Open HTR pointed to my daily folder and try scratch screen - no auto scratch button
8) Try Odds button - nothing

Then, to be sure, I also tried my monthly folder and just the HTR folder - nothing in any of these either. Then, to be sure I tried the regular HTR program (Ken had sent me a slightly modified version) and that didn't work either. I checked to be sure that I had the right version of HTR and it is the March 7, 2005 version and the ATR is version 3.63 (February 25, 2005).

I'm stumped.

[Rick]

OK.

One more dumb question.

Open ATRpro, click on GO, click on the third radio button next to options. Make sure item 20 has HTR checked.

[dehere]

You Da Man!!!!! Thanks a bunch - things are cool beans now. Now its off to the races.