Why access reset data to empty

[ghost]

Greeting,
I have a form for inputting data in table. In this form there is a button
for adding data and it has the following code:
Dim ctl As Control

For Each ctl In Controls
If TypeOf ctl Is TextBox Or ComboBox Then
If Len(Nz(ctl.Value)) = 0 Then
MsgBox "please enter data " & ctl.name

Exit For
End If
End If
Next ctl


This code is for checking all fields and make sure they are not empty. The
problem is when I miss a field , a msg box appears to warn and once click ok
all pervious data disappears and I have to fill fields again . is there any
solution for this problem?

 

[Allen Browne]

The data should not "disappear." Perhaps Access is saving that record, and
taking you to a new one? Or perhaps something else is Undo-ing the form?

The safe way to do this is to move your code into the BeforeUpate event
procedure of the *form*. This kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
Dim strMsg As String

For Each ctl In Controls
If TypeOf ctl Is TextBox Or ComboBox Then
If IsNull(me.ctl) Then
Cancel = True
strMsg = strMsg & ctl.Name & " cannot be blank." & vbCrLf
End If
End If
Next
If (Cancel) And (strMsg <> vbNullString) Then
MsgBox strMsg, vbExclamation, "Invalid data"
End If
End Sub

If you really want to use your command button, just use this line in its
Click event procedure:
If Me.Dirty Then Me.Dirty = False
That forces the save, which triggers Form_BeforeUpdate, and prevents moving
on until all fields are entred.

Of course, you could achieve the same result without code, just by setting
the Required property of the fields in your table.

If this is not just an exercise, forcing a user to enter something in every
field is usually a very frustrating way to write a database. If you are just
stuck handling nulls, this might help:
Nulls: Do I need them?
at:
http://allenbrowne.com/casu-11.html

 

[Linq Adams via AccessMonster.com]

I expect you're right, Allen, about Access saving the record and moving to a
new one; the OP's code does nothing to prevent this, it merely pops a
messagebox if controls are empty. But I have another question:

Is

If TypeOf ctl Is TextBox Or ComboBox Then

valid syntax, or should it be

If TypeOf ctl Is TextBox Or TypeOf ctl IsComboBox Then

???

 

[Allen Browne]

Yes: you do need to use the TypeOf again.

Personally I find it easier to use:
If (ctl.ControlType = acTextBox) Or (ctl.ControlType = acComboBox) Then

 

[Linq Adams via AccessMonster.com]

Me, too! Old school maybe? Wrote my first RD in QuickBasic 4.5 for use in DOS!

 

[Allen Browne]

Me, too! Old school maybe?
Wrote my first RD in QuickBasic 4.5 for use in DOS!

Good night! You remember the days of GET and PUT?

 

[Linq Adams via AccessMonster.com]

Indeed! I actually wrote a relational database, of sorts, without knowing
what one was! This was around 1988 or so and I'd had my first PC for about a
month. Got tired of re-entering contact data for physicians, figured out how
to open a second data file, search for the name, then fill in all the info!
Picked up a copy of Dr. Dobbs two years later with the headline "Relational
Databases: The hardest job in Basic." Wondered what a RD was! Was surprised
to find out I'd already written one!

Linq ;0)>