Sql Statement in form field default value property

[Daniel Jacobs]

I want to set the default value of a table based form field to be the highest
current value of that record + 1. I assumed that I would simply put a SQL
statement in the default value property as below...

=(SELECT Max(t_consignment_lines.line_number) +1
FROM t_consignment_lines
where t_consignment_lines.customer_name=" "J Smith" AND
t_consignment_lines.customer_reference="1")

However, all I get is "#Name?" in the field.

Can somebody tell me what I am doing wrong please

 

[Allen Browne]

No: as you found, you can't use a SQL statement directly in a control's
properties such as ControlSource or DefaultValue.

Instead use an event of the form to get the highest value used, add 1, and
assign the value to the control. Form_BeforeUpdate would be the best event,
as this fires at the last possible moment, and reduces the chance that 2
users get the same answer. If you need it before that, you could use
Form_BeforeInsert.

This kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.ID) Then
Me.line_number= Nz(DMax("line_number", "t_consignment_lines"), 0) +
1
End If
End Sub