Subform numbering error

[Bobbye R]

I have a job form with a subform that numbers the reports submitted for each
job. I set my default for reportNumber as follows:
=DMax("[ReportNumber]+1 ","tblPWreportLog","[jobnumber] =
Forms!frmJobs![JOBNO] ")
When I initially open my form the first job looks something like this:
reportnumber date title
1 03/5/08 3
2 03/20/08 3
#Error
If I move to another record and come back to the first record the error has
changed to a 3 which is what I want. I just don't know how to make the 3
appear when I open the form.

The second thing that happens is when I add a new job on the main form. I
don't know how to force the first report number to equal 1 (which I thought
was my default) I get #Error.

 

[Graham Mandeno]

Hi Bobbye

I suspect the first problem is being caused by a timing glitch. I think the
subform is opening before [JOBNO] on the main form has a value.

The second problem is for the same reason: for a new record, [JOBNO] is
null.

So in both cases your criteria string simply says "[jobnumber]=", which is
invalid.

Try using this expression instead:
=Nz(DMax("[ReportNumber]","tblPWreportLog",
"[jobnumber]=Nz(Forms!frmJobs![JOBNO],0)"), 0) + 1

 

[Bobbye R]

Hi Graham,
I entered the expression you wrote (I'm afraid I don't understand nz or what
the zero stand for but I'll look it up tomorrow)
I still get an error when I initially open the form. (I'll keep trying
there too.)
When I enter a new job on the main form the reportnumber on the subform =
#Error and when I input info on the subform the reportnumber changes from
#Error to 0 instead of 1. Any other suggestions?
--
Access 2000 User, Thanks in advance Bobbye

Hi Bobbye

I suspect the first problem is being caused by a timing glitch. I think the
subform is opening before [JOBNO] on the main form has a value.

The second problem is for the same reason: for a new record, [JOBNO] is
null.

So in both cases your criteria string simply says "[jobnumber]=", which is
invalid.

Try using this expression instead:
=Nz(DMax("[ReportNumber]","tblPWreportLog",
"[jobnumber]=Nz(Forms!frmJobs![JOBNO],0)"), 0) + 1

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a job form with a subform that numbers the reports submitted for
each
job. I set my default for reportNumber as follows:
=DMax("[ReportNumber]+1 ","tblPWreportLog","[jobnumber] =
Forms!frmJobs![JOBNO] ")
When I initially open my form the first job looks something like this:
reportnumber date title
1 03/5/08 3
2 03/20/08 3
#Error
If I move to another record and come back to the first record the error
has
changed to a 3 which is what I want. I just don't know how to make the 3
appear when I open the form.

The second thing that happens is when I add a new job on the main form. I
don't know how to force the first report number to equal 1 (which I
thought
was my default) I get #Error.

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.