Sub form data problems

[niak32]

My first post, YAY,

I have a database which is controlled by the date, one one particular Tab I have statistics, since there is so much data to collect, I had to make 6 tables to enter the data, these tables are just an extension of the main table i.e. there is only one record in each table for the current date, they do not collect multiple records for the same date.

img.photobucket.com/albums/v716/Fernargen/stats.jpg

For one day there is over 1440 fields, allot of these are not used but still need to be there.

MY problem: if no data is entered for one table, it does not save and therefore doesn't show up in the report, cause it doesn't show up in the report the totals muck up, since the totals muck up, ALL the totals muck up.

img.photobucket.com/albums/v716/Fernargen/statsreport1.jpg

As long as a user at least clicks on the table, a record for that date is created and it all goes smoothly, what i want and have tried to do, is to make a button that saves the tables \ or creates the records and saves them.

I have tried a few different methods, setting the focus on all of them one after the other, tried changing data (which changed controls (a tick box)) which changed the data but didn't save the record.


Forms![frm_Main]![StatsLG01SubFrm]![use] = "on"
Forms![frm_Main]![StatsLG02SubFrm]![use] = "on"
Forms![frm_Main]![StatsDO01SubFrm]![use] = "on"
Forms![frm_Main]![StatsDO02SubFrm]![use] = "on"
Forms![frm_Main]![StatsPat01SubFrm]![use] = "on"
Forms![frm_Main]![StatsSup01SubFrm]![use] = "on"

Forms![frm_Main]![StatsLG02SubFrm]![01res].SetFocus
DoCmd.Save acTable, "StatsLG02"

Forms![frm_Main]![StatsDO01SubFrm]![01res].SetFocus
Forms![frm_Main]![StatsDO02SubFrm]![01res].SetFocus
Forms![frm_Main]![StatsPat01SubFrm]![01res].SetFocus
Forms![frm_Main]![StatsSup01SubFrm]![01res].SetFocus

[ResTotal].SetFocus
[FATotal].SetFocus
[PATotal].SetFocus
[AMBTotal].SetFocus
[PolTotal].SetFocus
[ShkTotal].SetFocus
[LCTotal].SetFocus
[OthTotal].SetFocus

this is some of the setup for this form which might answer some questions

img.photobucket.com/albums/v716/Fernargen/stats1-2.jpg

Date is the primary key, sub tables have no primary key, don't need them imo, should really be a one to one relationship if thats possible as there is never duplicate records of the date.

All this code does squat. I hope i have explained myself ok here, this has been bugging me for a few months now.

 

[niak32]

>>>For one day there is over 1440 fields, allot of these are not used but still need to be there<<<

1440 fields Seems rather unnecessary. Please could you explain .

great, the first person that gives a reply knocks what i am trying to do.

12 fields per location, 20 locations per table, 6 tables, some of that is hidden, as this is a master for for a few databases.

It's like that because it has to be.

What i am after, is advise on how to code something that jumps from table to table in the form and saves the data.

If you can't help towards that, don't post.

since i can't post hyper links till i have 10 posts, you have to copy the links that i put them and paste them in your browser. seeing the form will explain allot i would imagine

 

[boblarson]

great, the first person that gives a reply knocks what i am trying to do.

12 fields per location, 20 locations per table, 6 tables, some of that is hidden, as this is a master for for a few databases.

It's like that because it has to be.

Not necessarily - I think you'll find that we don't blindly throw out suggestions just because someone says it has to be done that way. We are all about giving good and correct instruction, even if it means asking questions and working out the kinks. Your design may be appropriate, but based on the many years of Access experience we (as well as Uncle Gizmo has) we have, we are skeptical that this is a good design.

What i am after, is advise on how to code something that jumps from table to table in the form and saves the data.

That doesn't sound like it is properly normalized and a good design. Therefore, that is why we need more information before returning a good answer.

If you can't help towards that, don't post.

Be careful about insulting those who have many more years of experience that you. What you ask may be okay, but what you ask is likely not a good thing and therefore as I said before we are about getting the APPROPRIATE solution which may also include redesign.

since i have under 10 posts, you have to copy the links that i put them and paste them in your browser. seeing the form will explain allot i would imagine

And, since I just looked at the screenshots, I am now even further skeptical that you have the appropriate design for your database. I think we need to first visit your table design.

 

[GPGeorge]

" if no data is entered for one table, it does not save and therefore doesn't show up in the report, cause it doesn't show up in the report the totals muck up, since the totals muck up, ALL the totals muck up.

Statements like that are almost a 100% indication there is a problem with the way the tables are designed. That sort of stuff is almost always a by-product of inappropriate table design. One would not even need to hear that you have literally hundreds of fields in a table to realize that this database is in need of redesign to make it usable. The huge number of fields is just confirmation of that.

Please take a step back, present some more information about the workflow and business requirements this database is supposed to support. We'll help you achieve a valid table design so you can proceed.

 

[niak32]

This page of the database is for collecting statistics for each location.

Used to be in a continuous form, radio call would come in, you select the location from a drop down list, then enter in the statistics.

reports based on this would show only what locations had statistics taken instead of the whole lot.

Client demand makes it that all locations are displayed and always in the same place, i chose very big tables instead of one hundred or so little tables as is the only way that i could see it being done.

I am sorry if i have caused offense, perhaps i am too used to the younger generation on forums which would say very useless things just for the sake of posting.

Please let me know if there is code suitable for saving a record on a sub form. This is the 4th or so forums in the past 2 months and no one seems to know.

 

[niak32]

However if you follow your own route, (as is your right to do so) you will come across unique problems that no one has any idea of how to answer, and to be honest little incentive to answer them, why waste time trying to sort a problem that will never rear its ugly head again, particularly if there is a known and recognized solution.

I'm finding this more and more in my life, creating unique problems that boggle people sometimes from the choices I make (more on the electronics and programing side of things than databasing) Looks like if its popping up here as well, thats something that i have to look at in my life in general and decide upon what to do about it.

Perhaps i should be looking at this from the reporting side of things. Tried following this path once before but with little success.

Sub totals of the reports get their figures from the sub report, if the tables not there, it shows up as "error". I have tried with little success to make the sub total show as zero if there is no record for that date. Is that possible?

 

[Rabbie]

.

Sub totals of the reports get their figures from the sub report, if the tables not there, it shows up as "error". I have tried with little success to make the sub total show as zero if there is no record for that date. Is that possible?

Look up the NZ function in Access help. This is very useful if you have null fields in your data.

 

[niak32]

Look up the NZ function in Access help. This is very useful if you have null fields in your data.

Thankyou for your suggestion Rabbie, while i couldn't get the Nz function working in reports (tested fine in forms though) it led me down a different path long enough to find an answer.

=IIf(rpt_StatsLG01.Report.HasData=True,1,0)
=IIf([LGC1]=1,"big sum",0)

This changed the totals on my reports to show 0 if there is no data, and the sub reports themselves have a message behind them that says "No Data"

This should make the database smaller as it is not recording tables that have no data as i originally wanted.

Didn't you notice this line:

particularly if there is a known and recognized solution.

If you answer our questions, we may be able to suggest an existing recognized solution. This problem appears to be getting you down, why don't you have few days away from it and come back at it from a fresh angle. It's amazing how different a problem can seem once you have had a break from it.

Sorry, didn't realize that you were asking a specific question. Please restate your question relating to this comment if you wouldn't mind. While I have an answer, another one will perhaps leave me with another path to follow.

 

[niak32]

I alluded to a solution earlier, where I described how I think outlook works.

I have seen several examples which use this method, this isn't the best one but it's the only one I can find at short notice download it here:::: enter some information and view it on the report. Then have a look in the table "tblData" I think you will get an idea of the process I am referring to.

Thanks for that. More or less, thats how this statistics form started out, all data that was added was as small as it could be, anything that was repeated, linked to its own table so numbers were recorded in place of names (20 chars + some). Same for the rest of the database, just not this form.

Thank you all for your patience and assistance in helping me with this matter.