Assign Detail Number

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

tblEstimates is a detail table for tblProjects, linked by
ProjectNumber. tblEstimates primary key is an AutoNumber,
but there is also a numeric field EstimateNumber which we
use elsewhere in our process.

There is an Estimates subform (fsubEstimates) embedded on
a main form (frmProjects). I would like the estimate
number (control txtEstimateNumber) to default to the next
available number, that is [Max(EstimateNumber) of this
Project] + 1.

Can anyone help?

TIA

Kevin
 
There is an Estimates subform (fsubEstimates) embedded on
a main form (frmProjects). I would like the estimate
number (control txtEstimateNumber) to default to the next
available number, that is [Max(EstimateNumber) of this
Project] + 1.
Click to expand...

This is pretty easy. Use the BeforeInsert event of fsubEstimates with
code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtEstimateNumber = NZ(DMax("[EstimateNumber]", _
"[your-table-name]", "[Project] = " & Me!txtProject)) + 1
End Sub
 
Thanks, John; your answers are always appreciated.

Your assessment, reminded me, though, of Inspector
Lestrade of the Sherlock Holmes mysteries. Being totally
baffled by the case himself, Holmes explained in full
detail the deductive logic which led him to the solution.

Lestrade's response (paraphrased): "How terribly easy!"

Thanks again.
Best regards.

Kevin Sprinkel
 
John,

I've entered:

Me!txtEstimateNumber = Nz(DMax("[EstimateNumber]", _
"tblEstimates", "[JobNumber] = " _
& Me!txtJobNumberEstimates)) + 1

I've got an error message:

Run-time error '3075':
Syntax error (missing operator) in query
expression '[JobNumber]='.

Control references seem right. Do you understand what
I've done wrong?

Kevin Sprinkel
 
John,

I've entered:

Me!txtEstimateNumber = Nz(DMax("[EstimateNumber]", _
"tblEstimates", "[JobNumber] = " _
& Me!txtJobNumberEstimates)) + 1

I've got an error message:

Run-time error '3075':
Syntax error (missing operator) in query
expression '[JobNumber]='.

Control references seem right. Do you understand what
I've done wrong?
Click to expand...

If JobNumber is a Text field you need the syntactically required
quotemarks (sorry, should have covered that eventuality!)

Me!txtEstimateNumber = Nz(DMax("[EstimateNumber]", _
"tblEstimates", "[JobNumber] = '" _
& Me!txtJobNumberEstimates & "'")) + 1

and sorry about the "simple" comment - I was once watching a master
woodworker assemble a tongue-in-groove joint and he said the same
thing. Simple... yeah, RIGHT! :-{)
 
Back
Top