Subform with various tables fields?

Cobo

Registered User.
Local time
Today, 13:18
Joined
Mar 3, 2010
Messages
17
Hello all,

I have the following database (I hope normalization is ok here):
http://img718.imageshack.us/img718/4016/relationships.png

I would like to make a form where you select a date and below it there's a datasheet that would have all the necessary fields of every table. I mean I should select the date and be able to input (or read) the following fields correctly: RaceID, Hour, Course, Name, Distance, Selection, Price, Stake, ProfitLoss.
Of course, that would need to put RaceDate & RaceID (autonumber) correctly on both the tables that have it, as well as SelectionID (autonumber) on the tables that have it.

I don't know if it's possible to do it all at once in the same datasheet. A worse but acceptable solution would be to be able to select a date, then a race datasheet for that date and below those another datasheet with the Selections & Bets information. I have tried and searched about how to bind things correctly but can't find anything exactly as I state.

Thanks a lot for you help,

Cobo
 
Make things simple in terms of creating new records and editing existing records by having a form for each table.

I don't enter data into subforms but others do, personal preference.

Entering a date on the main form and viewing the results of a query based upon the other tables is fine but not ideal to try and enter or update data in the subform.
 
1. you can't do everything in one sheet.

2. If you want to use subforms you will need to use subformS - more than one.

3. Don't upload pics to imageshack but upload them to this site instead so those of us who get the public photo sharing sites blocked at work can see your screenshots.
 
Hello again and thanks for replying.

What I really want is a "fast" way to input data... so one sheet would be better than more than one. Anyway, I tried putting various subforms... the only problem is that I don't know how to bind fields between subforms. I have tried through the "Source" property but haven't achieved it. In that case the main form would contain a Date Box, with a subform fore Races, another one for Selections, and a last one for Bets.
I don't know how I can achieve to have the same RaceID on Races and Selections subform as well as the same SelectionID on Selections and Bets Subform.
All the exampls I've found in the forum are with people havin problems with comboboxes columns... which is not my case.
So, in short, is there an easy way to bind fields between subforms so that data integrity isn't lost?

Thanks a lot for your time and help. Cheers!

P.S: Sorry about the images... I'm new to the forum and didn't realize that. I attatch it here just in case.
 

Attachments

  • relationships.png
    relationships.png
    24.3 KB · Views: 86
Based on your relationships diagram, your main form would be the Races table and then you would have a subform for Selections (in single form view) and then you would have your Bets as a subform on the Selections subform.

Your Master/Child links for Races to Selections would be on the RACEID field and then the Master/Child links between Selections and Bets would be SelectionID.
 
Based on your relationships diagram, your main form would be the Races table and then you would have a subform for Selections (in single form view) and then you would have your Bets as a subform on the Selections subform.

Your Master/Child links for Races to Selections would be on the RACEID field and then the Master/Child links between Selections and Bets would be SelectionID.
I'll try and have a look at how to relate those... I know I related Master/Child fieldsin the past, but I really can't remember nor find the property right now.

Anyway... I want to browse by date, so I guess that has to be definetely my main form. As I want quickness in inputing data and the database is not really complex... maybe I'll sacrifice normalization and put all the data I'm interested in the same table. I know is far from ideal, but I either:
1. Study Access a little bit harder (and from your answer it seems hard to impossible to achieve what I want).
2. Program something in the manner of WinForms/WPF + ADO.Net to do what I want.
3. Sacrifice restrictions and put it all in the same place.

Thanks a lot for helping!
 
1. Study Access a little bit harder (and from your answer it seems hard to impossible to achieve what I want).
Not at all. If you want to upload the database you have now, with those tables, I'll be happy to create a set of forms that will work with those tables. They may not be exactly as you had envisioned things but you might be able to use that as a starting point and modify some.
 
Not at all. If you want to upload the database you have now, with those tables, I'll be happy to create a set of forms that will work with those tables. They may not be exactly as you had envisioned things but you might be able to use that as a starting point and modify some.
That's too kind on you... and I would have never expected that. Just in case you have time and don't mind to have a look, I have attached the databse in question. As it's a new project it doesn't have any data on it.

Anyway, although I thank you a lot for your willing to help, it really is beyond necessary and should be at the very bottom of your priorities list ;).

Thank you so much!
 

Attachments

That's too kind on you... and I would have never expected that. Just in case you have time and don't mind to have a look, I have attached the databse in question. As it's a new project it doesn't have any data on it.

Anyway, although I thank you a lot for your willing to help, it really is beyond necessary and should be at the very bottom of your priorities list ;).

Thank you so much!
Okay, here you go. I modified at least one of the tables to make the ID's NUMBER and not text. Also, you don't need the DATES table.

And on the main form there is a combo box to select the course from the course table.

You can play around with it and see what you can do. But right now it should be functional.
 

Attachments

Okay, here you go. I modified at least one of the tables to make the ID's NUMBER and not text. Also, you don't need the DATES table.

And on the main form there is a combo box to select the course from the course table.

You can play around with it and see what you can do. But right now it should be functional.
I've downloaded it and I'm trying it our right now. Many many thanks for taking the time to do this for me.

I really didn't about this possibility. Although as you said is not what I had in mind with a little tweak it's going to be perfect!

Again, thank you very much for this, it's great to find such helpful people these days :).

Thanks! Bye!
 
Hello again,

boblarson, after trying out your form it works great. If you could help with some little things it would be all donde and really perfect to what I need. So if you or anyone can:
1. I'm struggling with this tiny but important bit. WHen I enter a date for a race it automatically changes the date for the selections it has below, when what I really want is to see which races there are with the new date entered. If I click the arrows for oging from record to record it works ok, but as you may guess, if I have to go some months ahead or back, it can be painful.
Of course the problem if I changed it to behave like looking at the records it has for certain date (which I don't know how to do by the way) would be entering new races as it would look for records for a date which still hasn't.
So I don't know if I may have to do a form for entering records and a form for visualizing the information. If that is, how could I make it look for the records related to a certain date?
Maybe the easiest way to do is to make a query and then make a form from that query?

2. I think I messed up a little bit with relationships as a Race may have several selections but each selection only has one Bet. As that is, It would be great to have their forms the other way round, i.e. a Datasheet for the selections and textboxes for the Bets.

3. I only used the Course table primary key as text because there are no two courses with the same name, so I thought tha'd be okay to put it as a key. I don't know if this has any shortcomings.

I'm going to investigate nd see if I can do something. Thanks a lot!
 

Users who are viewing this thread

Back
Top Bottom