Subform numbering error

  • Thread starter Thread starter Bobbye R
  • Start date Start date
B

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.
 
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
 
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


Graham Mandeno said:
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

Bobbye R said:
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.
Click to expand...
Click to expand...
 
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 is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.
 
Back
Top