Assign Detail Number

[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

 

[John Vinson]

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.

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

 

[Kevin Sprinkel]

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

 

[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 Vinson]

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?

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! :-{)

 

[Kevin Sprinkel]

Thanks, John; works beautifully.

Kevin