Main form with subform that returns no records

[MorningSun]

I have a Main Form that display's information pertaining to Employee Audits. There are too many auditable points to put on one form so I have a few sections divided out into subforms on tabs. My first issue was that I could not give the user the option to print the Audit Form to include all tabs and subforms at once. I solved this issue by saving the form as a report and moving each subform to be visible instead of on tabs.

The current issue is that when and Audit is completed, if there are no audited points on one of the subforms, no record is created in the subform's underlying table. This is fine except that when I pull the report based on the form, the text boxes containing the score of the subform points just says #error.... due to there being no existing record for that subform.

Technically, there does not need to be a record for my purposes but I need to get that calculation to return a zero instead of an error so that further calculations can include that total.

Any suggestions?
Thanks!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[WildHare]

An overall answer to your problem might be the use of the NZ() function to zero any null entries, but I'm not sure it's really what you need - I'm trying to get a handle on your problem, and it seems like you have a one-to-many relationship (or perhaps, more precisely, one-to-several-many's) and if you do not have a record for one of the particular 'many' side guys, there's nothing to sum up, thus the #error message.

Without knowing exactly how you're creating the report source data, it's hard to give a specific answer, but I'd look into the NZ() function as above. If you NZ() any summations or counts or whatever, of fields in the -many side tables, it should return a ZERO instead of an #error flag.

You can't COUNT or SUM what's not there, but you can set it to ZERO, e.g. nz(sum([Many-side-guy])) should return a zero if there are no [many-side-guy] records.

Hope this helps....

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[MorningSun]

I tried the NZ function but I'm not really suming from the subform to the main form, just calling totals and I could not get it to work.

It will only be a one to one relationship but there is the possibility that the subform does not contain any information (the person whom the audit is being performed on did not work on any of the items within the subform and therefore they are not included in his/her score).

I understand that the main form is looking for a value from the table behind the subform that is not there and that is why the errors are there but I cannot figure out how to work around this.

The only solution may be to force the data entry form to add a record to the subform everytime an audit is performed even though all of the values in the subform would be zero.
I was hoping to avoid unnecessary records but it may be unavoidable.

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[WildHare]

I hate adding dummy records just to keep an error from occurring - that usually means there's a flaw in the design / logic somewhere.

Is your data in ONE big table with a lot of fields, or two tables with an exact ONE to ONE link, or in a ONE to MANY situation?

When you say "calling totals", what exactly are you totalling? If you total a field from a multiple record set, and some of the fields are NULL, the NZ() function will alleviate that.

Or are you "adding" field values across a record, and having a problem there? Either way, NZ() should be doing the trick.

Post back with a better description of the data relationship,and I'm sure we'll figure this out.

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[MorningSun]

The main form has a table and the subform has a separate table that are linked by the audit number. I have it set at a 1 to many relationship right now even though there should only be one record in the subform to match the record on the main form. (I'm not real versed with relationships... ). But the issue is that it MAY be a 1 to 1 relationship but it could also be a 1 to 0 relationship too. It shouldn't ever be a 1 to many.

When I say that I am "calling totals" I am in fact totaling across a single record. The main form has a text box that pulls the totals from the subform to the mainform and then totals those with a series of other totals on the main form for an over all audit score.

All is fine as long as the subform has a record to match the audit number on the main form. But when the subform does not have any info (no items audited on that section), the errors rightfully appear (there's nothing there). The subform is set to allow it to shrink, so it doesn't show on the report which is what I want it to do but I still need those totals to show either zero or NA instead of an error. I think the NZ is not working because it's an absolute void rather than Null or Empty.

Is this clearing it up for you at all?
Thanks so much for your time!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[WildHare]

Clearing somewhat..I just got confused a bit due to your transpostion of forms and table in your vernacular..I tend to think of tables having FORMS showing data, rather than FORMS having a table..but that may be just semantics..LOL

Without going into the possible bad-ness of having a singular entity split into a 1~1 table structure, there may be a way out of your problem.

If you have NO data in the sub-form table, you can test for that with a kind of clunky Immediate-IF statement...

SumGuy = Field1 + Field2 + IIF(Field3, Field3, 0) ....

Thus, if field3 is a field in the (possibly non-existant) other side of the 1~1 relationship, and it DOES exist, its value will be used, otherwise a zero will be plugged in..

I think this might solve your immediate problem.



If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[MorningSun]

I tried the IIF thing too. On the main form like this first:
=IIF([reportmerlin]![merlinsubpoints]=0,0,[reportmerlin]![merlinsubpoints]

And then like this according to your last post:
=IIf([reportmerlin]![merlinsubpoints],[reportmerlin]![merlinsubpoints],0)

Neither works. I would think that the first would work but if there is no value, it doesn't =0, I also threw in an IIF.....is null,0,.... to no avail.

The reason that I've had to do the 1 to 1 relationship is because I have too many controls/field for one form/table. Believe me, I would have avoided it if I could have!!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[WildHare]

I hesitated to say this, but if you have "too many fields for one form/table", you might have a flawed design. It might be that you want to do a slight redesign, and split the "audit points" to a separate table where a key value and a non-key value make up the record, along with a foreign key back to the employee record, e.g.

Employee: Smith, Joe
(1 side)


Audits Table: (many side)
AudKey- DAI (Dots All I's)
AudValue- NO
EmpGuy: Smith, Joe

AudKey-CAT (Crosses all Ts')
AudValue: YES
EmpGuy: Smith, Joe

Joe Smith : Audit Score : 1

Are we on the same page here at all?

I'll be checking back..

Jim





This way, you could enter as many AUDIT guys as you want for as many EMPLOYEES as you want, and not worry about non existant records

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[MorningSun]

Im not sure if we are on the same page. I have to be able to recall the actual audit form for each employee at any given time. This means that I have to store a value for each individual audit point(option box)so that it can be recalled which leads to the vast quantity of fields.

I'm not sure I follow what you are saying above.

I could send it to you if you'd like to see how it currently works.... just tell me where.

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[WildHare]

Ok - I have a few minutes here..between crashes..

Blithely ignoring the perils of putting a valid email address on a page, send it to jhare@usa.net

There HAS to be an easy solution to this - I think we're both just shouting down the wrong lane...

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[legalkill]

Did you guys come up with that answer becasue I am trying to figure out the same problem.

 

[MorningSun]

Goodness! This was so long ago! I ended up going down several different avenues and I think it was actually a mixture of solutions that I eventually came up with. I can't say that any are the "best" but here were a couple of my tricks.

I made sure that all the default setting on my subform were zero. I used the IIF and NZ functions as suggested above and finally got it to work somehow. Even though in my report there is no record in the subform, using the nz and the IIF to insert 0 and NA where appropriate on the main report finally worked. In other subreports that were based on queries, I made sure that the queries returned zeros by using NZ when no records were available. In some instances, using nz resulted in a zero but for some reason the zero acts as text rather than a number....??? In these instances, I used nz([item],0)*1 to make it a number that other calculations can be based on.

I've had to come up with many other creative solutions as I've worked on this database so if you can give me a little more detail as to your situation I may be able to pass some more helpful tidbits to you but be aware that I am by no means an expert programmer or designer, just decent at solving problems by any means possible!!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[legalkill]

Thanks for the tips i am trying to run some calculations on a form using a total from a subform that may not have any records. It ends up giving the #error in the calculated fields dependant on the total.

 

[MorningSun]

Have you tried:

IIF([subformname!subformfield]is null, 0,[subformname!subformfield])

in your calculated fields?

How about (nz[subformname!subformfield],0)?

On some of my forms I had to create a text box on the main form that is set visible to NO where I did "pre" calculations. That text box was based on the subform instead of trying to run calculations on the main form directly from the subform.



Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.