Access Form Question

[dehere]

Hey, Access gurus, got a question for you smart guys. I'm trying to do a form that permits the input of data while handicapping so that I can incorporate handicapping thoughts in my final report. The reason for this, among others, is that I cannot sometimes read my writing after I jot down initial thoughts.

So, I set up my form so that it looks to an All_HX4 table for some basic information that is automatically populated in the form - things like Track, Race, PGM, PPO, EPR, VI and other race specific and horse specific data. This saves some time when filling in the form.

I have the form set up as a continuous form in a "tabular" format. That way the entire stream of races with an entry for each horse in each race is listed.

So here's the problem. I want to input some general thoughts about a race and then, thoughts about indvidual horses. There are a couple of problems I'm experiencing to get this to do what I want it to do.

1) The form, when opened, shows all races and all entrants. I would like to get some sort of separation between races - either by marks in the form or some other way.

2) I would like to have the data for the "race" show up separately for the data for each horse in the race. This way I could enter race comments separately from horse comments. You see, if I don't do it this way and I mistakenly enter race comments more than once (as the entry for race comments shows up on the same line as each horse in the race), it creates a duplicate race when I go to print out the report.

So, all this is very confusing to read I am sure but does anyone have any thoughts about how I can get this to do what I want it to do?

[AwolAtHTR]

---
2) I would like to have the data for the "race" show up separately for the data for each horse in the race.
--

you are describing a classic example for the need for another table.

consider a new RaceComment data table which has a primary key of (Track)-(Date)-(Race) and a comment (recommend 255 text NOT a memo) field.

then you change your Report to connect the Race detail with the RaceComment.
the report would break on the race number to give separation between races.
If you want to add Horse comments, then that means a new field in the HX4 (or whatever) table you are using for the detail.

I recommend you ignore the system issue about generating the RaceComment data.

that is, create the table, MANUALLY enter (using Table view) data to test your report.
change and debug your revised report.

IF the revised report processing works, THEN address the issue about generating the RaceComment data records by an update query or a VBA code or .... yes, there are many choices. Oh, expect this SIMPLE task to be a learning experience that be frustrating because it will be the first time you have done it. That is, keep focused on what you want done and take time for this other stuff later.

duane

[dehere]

Thanks for the suggestion Duane but I think I may not have been very clear in what I was asking. The thing is, I am not trying to create a Report but, rather, am trying to create a Form. Whereas it is possible to integrate several tables or queries in a Report that does not seem possible when using the Form creation process.

As I see the Form process, the Form can only be tied to one existing table or query and the Form that is created groups everything together with no separation by data type. Thus, while I could have race specific data show up in a header in a Report, there is nothing similar, at least that I can find, in a Form setup.

I hope that clarifies my needs a bit.

[DanG]

Henry…

I wish I could help as I do exactly what you’re talking about.

I attempted to set-up forms to input my trips, race comments, trainer comments from articles etc…I finally gave up and input directly in a table.

I know what you’re saying…I’ve seen real slick templates that help organize input with headers and all the rest, but it just took too long to set up and I never got back into it.

You are much more advanced with Access / Computers than me so my guess is you’ll design some clever forms.

How’s that for a post that offers no help whatsoever!!! :D

[AwolAtHTR]

well Henry, I use VB to do this kind of stuff

so, this was chance to TRY the Form wizard

I think the Wizard's Main/Subform is the structure that fits your description.

However, I could not get the LINK from Main to SubForm to work.
I tried a hunch to create an Alternate Key but that failed.

Donnie is on his way to Florida. I expect this will be trivial for him.

so, I give up for now and wait for Donnie's answer.

duane

[MikeDee]

I have done very little with forms, but I don't think there is a way to do what you want with the hx4 table or a hx4 based query.

HX4 is horse level table all the data is at the horse level so there is no way to have a field that is at the race level only.

It is easy to make a table with a table query that would hold race level data only from the hx4 table and a comment field. You could them use a form to enter your comments into a field in this table.

I do something very similar. I have a "today's races" table where I put in race level data. I have a query that I sort in on post time, track and race no. and I have a comment field where I enter comments about the race. I keep this query open all day and refer to it often as the day progresses.

[dehere]

Thanks guys. I'll just deal with what I can get out of this but a couple of things. Duane, I know nothing about VB so even looking at the letters scares me. For what its worth I'm posting a little screenshot of the form that I am starting with. Everything to the left of "Henry" is populated from the HX4 table. The three columns on the right are entries that I make. The issue that I was posting about arose with the last column. This is a general comment line for the race. I can't get the form to take only one entry in this column PER RACE. If I mistakenly enter something here next to two of the horses entered in the race, it will duplicate the race in my report printout.

Mike - the table that this form is using is an extension of the HX4 table. I have created a few more fields which are essentially horse rankings and comments as well as race comments. Then when I use a macro to dump whatever is in the HX4 file and grab new stuff for "today's races", the comment lines are blank so that, when I open the form, the data that I need to be able to identify a race and a horse is already there. I can then enter my rankings and comments and they become part of the HX4 table. Then, when my reports print it grabs that comment stuff along with the regular HX4 data.

So, I like the query idea you have with a comment line to keep track of trip notes and such. I think I can do the same thing with this form approach and then run a macro at the end of the day to dump the day's comments into a "trip note table" for future use - great stuff perhaps. Of course it could be a matter of "garbage in - garbage out" if my observations ain't worth much.

[Donnie]

I think I understand what you are looking for Henry. Let me play with it for a day or two. It will probably take a combination of queries and tables set up to do just what you want to do. Are the fields in your pdf the only fields you will be looking at for now? I think it will take 2 tables, as Mike pointed out, as you are asking to see horse records tied with race records. I just need to take a little time to think it thru so I don't have to redo it much if I get it wrong! :eek:
Back from Disney! What a FANTASTIC vacation!

[dehere]

Donnie, - I knew you'd come home and save the day. Really glad to hear that the Disney trip went well. Don't forget to drop a note when entries become available for PRM contest, eh?

Hold off a day or so with working on this Form issue. I finally broke down and actually read the manual (Access 2002 Inside Out - published by Microsoft for those of you wondering what manual to consider) and they have a whole section about creating subforms and forms. I have gotten a good start with what I am trying to do last night and wanna work on it again before I bother anyone else again. I'll let you know what I come up with. It might be helpful to those who use this sorta thing in their handicapping (Dan?).

To answer your question though, the data included in the form I posted was a starting point - as this access stuff always is. It'll grow as I discover new ways of dealing with this. I really liked MikeDee's thoughts about saving some of this input for future use when the horses run again - so, at the very least I can see having some fields that are saved for future use and some that are not.

[MikeDee]

I don't save the data Henry, of course you could. I clear the table every day when I import todays races, and then populate it with todays data.

The other thing you can do is that you can go through the races and put a comment in the ones you are interested in and then in your query you can set a filter, in the comment field, to "is not null". Now only the races with comments show up in the query output in post time order.

[AwolAtHTR]

hi Henry,

I found the missing piece to get the form to work.

However, I do not want to post the answer because it is like someone reading a mystery novel and you do not want to know the clue that solves the mystery. You may learn more by continuing your deciphering the samples from the manual.

so, I will wait.

duane

[dehere]

Duane, well, sheesh guy. Sure wish I knew what the problem was so I knew what it was I was solving. The exercise is also fun though so I'll keep rolling along until I come up with something workable.

Here's where I am at this point. I have a subform, based on a query, with good "horse level" data and which permits me to enter ranking and comment data for each horse that goes directly to the underlying table. I also have a master form that contains "race level" data from another query with a comment entry box. The problem is I am unable to actually enter data in that part of the form. It may be because I am using grouping to get the race level data.

Another problem I am having is with the navigation tools. I can use navigation bars to move from race to race and from horse to horse. What I would like to do though is use tabs to move from race to race and horse to horse.

So there's the puzzle as I see it at this point.

[dehere]

Okay, some progress to report. I have uploaded the current "state of the form". If I do a make-table with the race level data I can incorporate a race comment section in a second query. Now I just need to draft a macro which will automate all these steps. Progress is progress.

[AwolAtHTR]

yes, good progress Henry

However, I see the goal is to have a MAIN form with the SubForm has the detail.

My sample has a table named CHART with a field XfavGroup which I want to record.

the Subform has all the horses in the race.

I think this is the structure that need to get your Race Comments (MAIN) but you also want to set the Horse comments in the SubForm.

here is my current sample is attached. (..I hope..)

note the navigation bar at the bottom controls the Main form at the top
and the navigation bar just below the Subform controls those records.

I tried doing this with a query and gave up.

The MAIN form is a table with a Parent-Child relationship to the SubForm.

I did this with Access 97 and Access 2002. aaah, major, MAJOR, differences.

so, what is your access version?

-----------

aaah, oopps, the navigation bar for the Subform got cut off.
oh well, another lesson in posting thread stuff!!

[AwolAtHTR]

ok, I redid the screen shot

but added a horse Comment field and tinkered with the Main form labels.

here is revised screen shot WITH both navigation bars!!

[Donnie]

try this one out Henry.....

http:// home.mchsi.com/ ~mrdon45/ Henry.mdb

Copy and paste this into your browser and then remove the extra space I put after each "/"

[Donnie]

I haven't hidden any of the macros or tables that you don't need to see. But once you have a chance to look at it's operation, then I will hide the objects that don't need to be visible and I'll post it back up again. But the form that opens when you open the db allows you to enter either race comments or horse comments. When you close the form a macro runs that appends your race comments to a seperate RaceTable. So you can then query those races and comments in the future. If you try to close the form with the same race data, you will get an error message stating due to key violations, it cannot append 90 records....this is used to prevent duplicates in the race table. The race query is a Unique query to populate the Race table with one copy of each race, so there are no duplicates in that table.

[Donnie]

....I figger it should give you something to chew on for a while!

[AwolAtHTR]

hi Donnie,

I tried Henry.mdb using Access 2002 and the form failed with RaceTable does not exist.

I did have to archive to a CD and copy to my other system but that SHOULD not matter!


the tables in Henry.mdb are HX4 and PermRacesTable

is there something special I need to do with Access 2002 for this to work?

duane

[Donnie]

Duane--
try redownloading it...I just did to see if I have the same problem. I saved it to My Documents and had to right click, choose properties, and then Unblock, but all 3 tables are in there in the copy I just downloaded.
Here is a screenshot after I downloaded it from my website.

[AwolAtHTR]

well Donnie,

the problem was the operator (me!!) was paranoid about the first screen to appear that said 90 records would be added to a new table. Well, say NO and the data base will never work!!


aah, say Yes, and OK the form works BUT the SubForm has binary data not text.

I expect that is a compatiblity issue between XP-Access 2002 and your Access.
so, I am ignoring that data problem.

the fact is Yes, your form works.

What is your access version?

I could not find any Relationship definitions. How did you get the Form to work?

duane

[Donnie]

Duane--
I'll explain the entire process over my lunch break today....gotta go teach Access right now!

[Donnie]

Sorry...work got in the way today...I'll explain this evening how the forms are tied together.

[dehere]

Man, Donnie, I liked that Rogue Agent horse :)

I was busy all day and just got a chance to look at this now. It seems to be okay on the screen. I'll play around with a few things and see how it goes.

Thanks a boodle. (Chardonnay is on me for Sue in June)

[Donnie]

OK...let me point out a couple things first.

Duane posted: the problem was the operator (me!!) was paranoid about the first screen to appear that said 90 records would be added to a new table. Well, say NO and the data base will never work!!


I personally have gone into TOOLS>>>OPTIONS>>>>EDIT/FIND tab, and then on the upper right side is a CONFIRM area. I have turned off all three checkboxes in there. When you run action queries and macros, this disables the confirmation message you get from Access when you go to create new tables or delete out records and such. This in turn allows the macros to run with no stopping for user interaction.

Duane posted: What is your access version?


I currently use Access 2003. But I save all my files back as 2000 versions. Go to TOOLS>>>>OPTIONS>>>>ADVANCED tab. Upper right hand corner again....Default File Format is set to Access 2000. This way anything I design and send to anyone is saved back as a 2000 file, everyone should be able to use it.

Henry posted: (Chardonnay is on me for Sue in June)

Sue thanks you in advance.


Check my other thread later for information on PRM. They are adding $5000 to the prize fund and all monies for entry fees are being returned as prizes. So if they get 80 people again, that means that the top person will get half of that money (over $10K!). This could be a very good year to win it!

I would probably add another macro to that db that would save the horse data back into another table so you would end up with 4 tables....two working tables and two historical tables that hold the data with added comments. A trip handicapper could use this db straight off the download once it is done.

Anyway...the wife is home and we gotta walk the dogs....more later!

[DanG]

I personally have gone into TOOLS>>>OPTIONS>>>>EDIT/FIND tab, and then on the upper right side is a CONFIRM area. I have turned off all three checkboxes in there. When you run action queries and macros, this disables the confirmation message you get from Access when you go to create new tables or delete out records and such. This in turn allows the macros to run with no stopping for user interaction.

Donnie…

Quick thought…

Doesn’t using the function “Set Warnings” to “No” accomplish the same thing in Macro’s without turning off messages in options?

PS: I use the “Set Warnings” to “No” on the 1st line for almost every Macro I run and it seems to eliminate all those annoying messages.

[Donnie]

Yup, Dan, does the same thing, but I hate those warnings no matter what I am doing, so I turn them off fer good! Maybe I should start doing it that way when I create a database for others....??

[dehere]

That's funny. The download from Donnie worked fine this afternoon on my desktop. Now when I try to do it on my laptop I get the same binary data in the horse form that Duane was. Any ideas what might be happening? I have Access 2003 on both the desktop and the laptop.

[Donnie]

Henry...what exactly is the error message you are receiving?

[AwolAtHTR]

hi Donnie,

I get No Error messages

under the Form tab, Open HorseQuery and see same (apparently Binary data) displayed as when the RaceQuery is opened.

under the Query Tab, ALL queries work OK, ie, see the data in text

If Henry gets an error message then his must be different then mine.

duane

[Donnie]

Sorry guys, I can't replicate the problem. I can open any and all forms as well as any and all queries and everything displays properly.....???

[dehere]

nope - no error message here either - the weird thing is that it worked fine on the laptop earlier today. Now I am getting the same issues as Duane is - queries work fine but when I open the horse form I get the binary data. It may have something to do with the fact that the design window for the horse form sure looks different from the output when you view the form.

I am working on something else right now so I have left this task for awhile.

So many access problems - so little time.

[Donnie]

can you take a screen shot of it so I can see what you mean? Thanks!

[dehere]

Well, the first thing I get when I open the Henry.mdb is a warning that I will be deleting the table "RacesTable". I don't think I got that warning this afternoon.


This is the first screen shot - The second screen shot is just what show up under the HorseQuery form.

Hope this helps.

[Donnie]

Ok boys...that's not binary data...that means that you have a font missing on your system. The system you are using where it works has the same font I am using. The system you are using when it doesn't work doesn't have the same font, so the program is trying to substitute that font! At least that is how it appears. With the form open go up to your Menu System and choose FORMAT then FONT and assign a font like Times New Roman. See if it is then compatiable on both systems.

[Donnie]

Ohhh by the way....I will need to change a couple things....in playing with it, once you close the Form, any other comments you add will NOT be saved back to the PermTable. This is because of the primary keys being set. So I assigned the append query to a button instead. Is the layout adequate, before I go and make some functional changes?

[Donnie]

And Henry, you get that warning because your warnings are not turned off!

[AwolAtHTR]

say Henry,

I tried Donnie's '..change the FONT..' after finding the FORMAT tab.
well, I gave up.

my solution is to reconstruct the HorseQuery as the SubForm

So, Access 2002 screwed up something but I do not care about fancy fonts.
now it works and it is clean, simple, and slick.

hope you found the FONT change simpler and got it to work.

Looking forward to learning '..HOW TO..' create a Main/Subform
using an automated MakeQuery instead of a simple brute force (user does MakeQuery).

now, I am going to wait for Donnie's next version of his solution.


duane

[dehere]

Well, this font deal is kinda nutty. Oh, by the way, when I follow your directions Donnie, there is no selection for "font" in the format menu when the form is open. Its probably there but I'm too dense to find it.

Anyway, when I look at the form in Design view the font showing up is Ariel. I have Ariel on my computer. Also, the same font shows up in the Racequery form and that works fine. And, if change the DefaultView in the HorseQuery form from Datasheet to Continuous Form, it shows up fine with the horse data perfectly legible, albeit in a form that does not work when I switch to the RaceQuery form. So, I guess its hard to believe that this is a font issue. But what do I know?

In the meantime I have been working through my trial and error process with my own form/subform approach and it is working pretty darn well if I do say so myself. There are some things in Donnie's approach that I like and may want to inquire about, but maybe it makes more sense to work through the few remaining issues on my form/subform stuff than try to change Donnie's form to meet my needs.

Once I get things done on my iteration of things I will make the access database available.

[Donnie]

And that is why I used Datasheet view instead of continuous, because that form is supopose to be used only as a subform. When you open the form (to change the font) make sure you have that screen in restor mode and not maximized. Maybe that is why you are not seeing Format or Font......?? Don't really know on that one...but if you do what Duane suggest (rewrite) sounds like you end up with a working result anyways.

[dehere]

Okay, I'm not sure what was going on but I got the fonts to change. Basically, I just opened the HorseQuery form - not in design mode but in what I would call "view mode" (just double clicked it). It opened with funny fonts. I selected all records and changed font to Ariel and it worked fine.

[AwolAtHTR]

Re: Fonts

I feel we can close this issue as a bug with WindowsXP and Access. That is, WdwXP is doing something SPECIAL with the subform and somebody in Redmond, WA (ie Microsoft) might be interested but we do not care.
---------

Re: Henry's solution

When you are ready to share, I want to take a look at it.
even if not final and you want someone to kick the tires.

------------
Re: Donnie's solution

I would still rather read your description instead of decoding your solution. Especially since I have basic knowledge about Access's macro and want to learn more. I am looking forward to understanding how you have driven this Main/Subform solution with marcos.

duane

[dehere]

Duane,

Yeah, things kinda got dropped on this thread. But that doesn't mean things weren't being worked on in the meantime. I've completed my first iteration of this form-subform thing and if you wanna check it out and play with it you can get it here (http://www.dabblebabble.com). Click on the link for "Download Form-Subform File".

Trust me this file does not have the sophistication that Donnie's file does. I tried to work with Donnie's program but found it difficult with some of the special features that Donnie had incorporated. So this is what I have now.

A couple of notes. The email function on the menu does not work currently as it was set up to send the rankings and comments to me at my email address. If you ever wanna use this thing you would need to change the macro to include a proper email address.

The main purpose of this form-subform was to assist us in creating log files in a partnership for contests so what you see is only one third of the entire process. The form/subform functions are still there, though. That explains the export function on the menu a bit. We use that to send data to each other before race day.

The report that has been set up is really limited in this version of the program we use. In my main use of the data, I import the horse and race files to my main database where it is incorporated in a much more complete race summary. I have included a pdf file as an attachment to this post that shows what that looks like.

Anyway and for what its worth - this is my interpretation of the form-subform approach to data entry.

[Donnie]

Sorry guys. I kinda shelved this once i made up my mind that I was heading for HAW. Will revisit it this week and post up a "refined" product. Will be a couple days though! Sorry again! Back to reality I guess!

[AwolAtHTR]

---Trust me this file does not have the sophistication that Donnie's file does. I tried to work with Donnie's program but found it difficult with some of the special features that Donnie had incorporated.
-----
The main purpose of this form-subform was to assist us in creating log files in a partnership for contests so what you see is only one third of the entire process. The form/subform functions are still there, though. That explains the export function on the menu a bit. We use that to send data to each other before race day.
---

Henry,

Your sharing notes system is neat. Just by reading your user choices, I got a reasonable understanding of what is happening with the data. Oh yes, I did peak at your code which showed me that the Visual Basic by Access is basically the same, BUT, InMyOpinion, much more complicated then VBA 4.0. Hey, each person grows up with tools they learn and that process becomes SIMPLE for them.

About your data structure that supports the Main/Subform: Your created tables do NOT have a key defined. My solution for the Main/Sub to work was the definition of keys for each of the tables. With the correct keys, the LINK from Main to Subform worked. So, for me, how you and Donnie get the process to work ONLY with queries is still a mystery.

re: Shared notes

The sample screen was clearly, a nice simple system that could be used by those sharing the information. I liked your color coding of the K factor and consider sorting by that column for the report.

Your description of the output files was great. That is, telling the user '..what to do..' (ie use FTP) '.when the email does not work..' is really preparing the user to deal with the problem when (not IF) it happens during the real world (live) use of the processing.

so, I have two suggestions about the file names for your output. The anticipated problems this would solve are: 1) finding transfered files, 2) two users perform FTP at/near the same time, and 3) files are shared on other email servers.

First: The extension TXT be replaced by a code of your choice, say TEAM. With the TEAM extension, you can not only do a search *.TEAM and find all the transfered files but also the email downloader will always work. Aaah, the most recent version of HotMail does NOT perform a download of a TXT file but displays the file!!! This may not be an issue but just a pet peeve of mine.

Second: The file name revised to include Date.

With revisions, file named Henry20070402Horse.TEAM or maybe 20070402HenryRace.Team

Of course, these revisions may be complicating your already simple system. I offer them as an outsider who does not understand all your system issues. That is, I will not be offended if you said, '..you gotta be kidding me...' ---LOL---

duane