Access2000: Default values and new record

[Arvi Laanemets]

Hi

Without any default values, when user is on new record, no other new records
can be created. When the user leaves the new record without entering
anything, the record is not created.

Now I have a form, where several cells have default values - one of them on
table-level, others on form level. And when the user doesn't enter anything,
the new record is saved when he leaves it. Even worse - he can create
unlimited number of new records with only default values entered
automatically.

Is there some easy solution for this problem, short of using the design with
all controls on form unbound (read data - edit data - save edited data)?


Thanks in advance!
Arvi Laanemets

 

[Brendan Reynolds]

There are a number of possible solutions.

You could ensure that you have at least one required field with no default
value.

You could add code to the Form_BeforeUpdate event procedure to compare the
values of the controls against their default values, and if they match set
Cancel = True. Something like so ...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Trim$(Me.Text0 & vbNullString) = Me.Text0.DefaultValue Then
If Trim$(Me.Text2 & vbNullString) = Me.Text2.DefaultValue Then
Cancel = True
End If
End If

End Sub

You could use a table-level validation rule. Something like ...

Field1 <> 0 Or Field2 <> 'x'

.... where 0 and 'x' are the default values.

 

[Arvi Laanemets]

Hi Brendan

Thanks, it almost works now (I used AfterUpdate event - because some default
values are inserted by code in form, I probably can't use table level
validation here). But when I am on new record, then whenever I try to
navigate to some other record, I'm returned to new record. I have to press
Esc at first, and only then I can move to another record. Can I correct this
also?


Arvi Laanemets


PS. There are 3 Form-level events for this form: Current, BeforeUpdate, and
OnLostFocus. They are listed here below in somewhat simplified form with
comments.

Private Sub Form_Current()
...
If Nz(txtField1, 0) = 0 Then
' A default value for bound control is calculated - when the control
is empty
' , the calculated value from unbound control is inserted
txtField1 = txtUnbound1
End If
...
' Values for some controls are calculated
' (I had to save some calculated values to table to avoid infinite
calculation loops)
Me.txtField2 = Me.txtUnbound2
...
' The row source for a combo is recalculated for every row
Me.cbbTankimine.RowSource = "SELECT ..."
Me.cbbTankimine.Requery
...
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
' It is your advice used here. As the form is for registering car routes,
any record without car id is abundant
If Nz(Me.cbbCarId, 0) = 0 Then
Cancel = True
End If
End Sub

Private Sub Form_LostFocus()
If Nz(Me.cbbCarID,0) <> 0 Then
Me.txtField3 = Me.txtUnbound3
Me.Dirty = False
End Id
End Sub

 

[Brendan Reynolds]

Try adding a Me.Undo ...

If Nz(Me.cbbCarId, 0) = 0 Then
Cancel = True
Me.Undo
End If

Also, on reflection it occurs to me that the Form_BeforeInsert event
procedure might be a better place for this code than the Form_BeforeUpdate
event procedure. The BeforeUpdate event is fired when editing existing
records as well as when inserting new ones. The BeforeInsert event is fired
only when inserting a new record, which is what we want.

BTW: The LostFocus event of a form is never fired if the form includes any
controls capable of receiving focus, e.g. any enabled text box, combo box,
list box, command button, check box, toggle button or option button. So the
code in the Form_LostFocus event procedure is probably redundant.

 

[Arvi Laanemets]

Thanks, it worked with both events. At moment I decided to use BeforeInsert
event - ofcourse it may be usefult when abundant entries anywhere are
removed automatically, but I'm afraid it's slippery ground there.

The LostFocus event is there probably, because I needed to save a record,
before another form is activated (I have an unbound multi-page form with
separate forms on different pages. Some of them get p.e. record sources for
combos from tables, which are sources for other forms). I tried various
events to save form's source data when another sheet/form is activated - I'm
not sure at moment, is this LostFocus event simply remained undeleted, or is
it really needed. I'll check it later.


Arvi Laanemets