missing data warning

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

Hello All

I have a form on which I want to warn the user when data is missing from
certain fields - but I need to allow them to proceed (i.e. close the record)
even without the data if necessary. I have tried setting the following event
procedure as the form's BeforeUpdate or AfterUpdate events:

If IsNull([fax].Value) And IsNull([add1].Value) Then
Beep
MsgBox ("Please enter an address and/or a fax number")
End If

but the problem is that if the user is updating the record by simply moving
to the next record (or by creating a new record by doing ctrl + as they are
in the habit of doing) the messagebox pops up but then the form moves to the
next (or new) record - so that they are in danger of adding the address/fax
data to the wrong record. I need somehow to prevent the movement to the
next/new record until the user has somehow 'accepted' the lack of
address/fax data for the current record.

I cannot see how to do this - if anyone could help I would be very grateful.

Many thanks
Leslie Isaacs
 
Hi,
use the before update event as you were trying. It has a Cancel = True
variable to prevent movement to a new record, so try that before your message
box,
HTH
Good luck
 
Hello Oliver

Many thanks for your very speedy reply!

I tried what you suggested, so now the complete event procedure is:

Private Sub Form_beforeUpdate(Cancel As Integer)
If IsNull([fax].Value) And IsNull([add1].Value) Then
Cancel = True
Beep
MsgBox ("Please enter an address and/or a fax number")
End If
End Sub

.... but now I find that I cannot get past the msgbox with the [add1] and
[fax] fields empty - each time I click OK on the msgbox then try to move to
a new record, the msgbox reappears! (remember I need to allow the user to
proceed with these fields empty). What have I done wrong?

Thanks for your help.
Les


freakazeud said:
Hi,
use the before update event as you were trying. It has a Cancel = True
variable to prevent movement to a new record, so try that before your
message
box,
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


Leslie Isaacs said:
Hello All

I have a form on which I want to warn the user when data is missing from
certain fields - but I need to allow them to proceed (i.e. close the
record)
even without the data if necessary. I have tried setting the following
event
procedure as the form's BeforeUpdate or AfterUpdate events:

If IsNull([fax].Value) And IsNull([add1].Value) Then
Beep
MsgBox ("Please enter an address and/or a fax number")
End If

but the problem is that if the user is updating the record by simply
moving
to the next record (or by creating a new record by doing ctrl + as they
are
in the habit of doing) the messagebox pops up but then the form moves to
the
next (or new) record - so that they are in danger of adding the
address/fax
data to the wrong record. I need somehow to prevent the movement to the
next/new record until the user has somehow 'accepted' the lack of
address/fax data for the current record.

I cannot see how to do this - if anyone could help I would be very
grateful.

Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
Hi,
you haven't done anything wrong. The cancel=true will not allow people to
leave the record unless the condition is fullfilled. I don't see a reason to
validate unless the control is required. You could try to use the .setfocus
comand to set focus back to the control you want to have focus, but still it
will always set focus back to the control unless you somehow specify that the
user really WANTS to leave the record!
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


Leslie Isaacs said:
Hello Oliver

Many thanks for your very speedy reply!

I tried what you suggested, so now the complete event procedure is:

Private Sub Form_beforeUpdate(Cancel As Integer)
If IsNull([fax].Value) And IsNull([add1].Value) Then
Cancel = True
Beep
MsgBox ("Please enter an address and/or a fax number")
End If
End Sub

.... but now I find that I cannot get past the msgbox with the [add1] and
[fax] fields empty - each time I click OK on the msgbox then try to move to
a new record, the msgbox reappears! (remember I need to allow the user to
proceed with these fields empty). What have I done wrong?

Thanks for your help.
Les


freakazeud said:
Hi,
use the before update event as you were trying. It has a Cancel = True
variable to prevent movement to a new record, so try that before your
message
box,
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


Leslie Isaacs said:
Hello All

I have a form on which I want to warn the user when data is missing from
certain fields - but I need to allow them to proceed (i.e. close the
record)
even without the data if necessary. I have tried setting the following
event
procedure as the form's BeforeUpdate or AfterUpdate events:

If IsNull([fax].Value) And IsNull([add1].Value) Then
Beep
MsgBox ("Please enter an address and/or a fax number")
End If

but the problem is that if the user is updating the record by simply
moving
to the next record (or by creating a new record by doing ctrl + as they
are
in the habit of doing) the messagebox pops up but then the form moves to
the
next (or new) record - so that they are in danger of adding the
address/fax
data to the wrong record. I need somehow to prevent the movement to the
next/new record until the user has somehow 'accepted' the lack of
address/fax data for the current record.

I cannot see how to do this - if anyone could help I would be very
grateful.

Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Leslie Isaacs said:
Hello Oliver

Many thanks for your very speedy reply!

I tried what you suggested, so now the complete event procedure is:

Private Sub Form_beforeUpdate(Cancel As Integer)
If IsNull([fax].Value) And IsNull([add1].Value) Then
Cancel = True
Beep
MsgBox ("Please enter an address and/or a fax number")
End If
Click to expand...

Try adding Me.Undo here
End Sub
Click to expand...

Keith.
www.keithwilby.com
 
This would undo ALL controls though, so if some can stay empty then try to
just set the value of the one empty which is in the evaluation so that the
code in the before update event would not fire anymore.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


Keith Wilby said:
Leslie Isaacs said:
Hello Oliver

Many thanks for your very speedy reply!

I tried what you suggested, so now the complete event procedure is:

Private Sub Form_beforeUpdate(Cancel As Integer)
If IsNull([fax].Value) And IsNull([add1].Value) Then
Cancel = True
Beep
MsgBox ("Please enter an address and/or a fax number")
End If
Click to expand...

Try adding Me.Undo here
End Sub
Click to expand...

Keith.
www.keithwilby.com
Click to expand...
 
Hello Oliver and Keith

Thanks for your replies.

I may not have explained what I need very well.

Ideally, all records will have data in fields [add1] and [fax] BUT
(unfortunately) in some cases this data with not be known. Thereore I cannot
set the Required property (in the underlying table) to Yes - and I must be
able to edit other fields on the form, and then move on to other records,
while leaving [add1] and [fax] empty. All I wanted to achieve was a warning
to the user that, for the current record, [add1] and [fax] are empty and
that it would be preferable (but not obligatory) to find and enter this
data. But if the user cannot (be bothered to!) find this data, the system
will have to accept that.

I hope this makes my problem clearer - and that there is a way to do what I
want!

Thanks for the continued help.
Les


freakazeud said:
This would undo ALL controls though, so if some can stay empty then try to
just set the value of the one empty which is in the evaluation so that the
code in the before update event would not fire anymore.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


Keith Wilby said:
Leslie Isaacs said:
Hello Oliver

Many thanks for your very speedy reply!

I tried what you suggested, so now the complete event procedure is:

Private Sub Form_beforeUpdate(Cancel As Integer)
If IsNull([fax].Value) And IsNull([add1].Value) Then
Cancel = True
Beep
MsgBox ("Please enter an address and/or a fax number")
End If
Click to expand...

Try adding Me.Undo here
End Sub
Click to expand...

Keith.
www.keithwilby.com
Click to expand...
Click to expand...
 
Leslie Isaacs said:
Ideally, all records will have data in fields [add1] and [fax] BUT
(unfortunately) in some cases this data with not be known. Thereore I
cannot set the Required property (in the underlying table) to Yes - and I
must be able to edit other fields on the form, and then move on to other
records, while leaving [add1] and [fax] empty. All I wanted to achieve was
a warning to the user that, for the current record, [add1] and [fax] are
empty and that it would be preferable (but not obligatory) to find and
enter this data. But if the user cannot (be bothered to!) find this data,
the system will have to accept that.

I hope this makes my problem clearer - and that there is a way to do what
I want!
Click to expand...

Ah, right, I've just re-read your OP. How about setting the bookmark
property in the before update event so that you can move back to the record
originally being edited in after update if necessary (I'm not sure of the
exact syntax for Bookmark):

'Set Bookmark here in Before Update

'In After update
If IsNull(txtMyTextBox) Then
If MsgBox("You've left some fields blank - continue to next
record?",vbYesNo,"Confrim") = vbNo Then
'Go to bookmarked record here
End if

I've not had time to test this but it might be worth a try.

Keith.
 
Hello Keith

OK, I now have:

Private Sub Form_beforeUpdate(Cancel As Integer)
Dim rs As Object
Set rs = Me.Recordset.Clone
End Sub

... and ...

Private Sub Form_afterUpdate(Cancel As Integer)
If IsNull([fax].Value) And IsNull([add1].Value) Then
Beep
If MsgBox("The address and fax number are blank - enter now", vbYesNo,
"Confrim") = vbNo Then
Me.Bookmark = rs.Bookmark
End If
End If
End Sub

.... but now the form won't even open!
I get a message saying that "the expression OnOpen you entered as the event
property setting produced the following error: Procedure declaration does
not match the description of event or procedure having the same name".
That's odd, because I haven't changed the OnOpen event.

Presumably my syntax is wrong with either setting or going to the bookmark:
I would be extremely grateful if you could advise further.

Many thanks again
Les


Keith Wilby said:
Leslie Isaacs said:
Ideally, all records will have data in fields [add1] and [fax] BUT
(unfortunately) in some cases this data with not be known. Thereore I
cannot set the Required property (in the underlying table) to Yes - and I
must be able to edit other fields on the form, and then move on to other
records, while leaving [add1] and [fax] empty. All I wanted to achieve
was a warning to the user that, for the current record, [add1] and [fax]
are empty and that it would be preferable (but not obligatory) to find
and enter this data. But if the user cannot (be bothered to!) find this
data, the system will have to accept that.

I hope this makes my problem clearer - and that there is a way to do what
I want!
Click to expand...

Ah, right, I've just re-read your OP. How about setting the bookmark
property in the before update event so that you can move back to the
record originally being edited in after update if necessary (I'm not sure
of the exact syntax for Bookmark):

'Set Bookmark here in Before Update

'In After update
If IsNull(txtMyTextBox) Then
If MsgBox("You've left some fields blank - continue to next
record?",vbYesNo,"Confrim") = vbNo Then
'Go to bookmarked record here
End if

I've not had time to test this but it might be worth a try.

Keith.
Click to expand...
 
Leslie Isaacs said:
Hello Keith

OK, I now have:

Private Sub Form_beforeUpdate(Cancel As Integer)
Dim rs As Object
Set rs = Me.Recordset.Clone
End Sub

... and ...

Private Sub Form_afterUpdate(Cancel As Integer)
If IsNull([fax].Value) And IsNull([add1].Value) Then
Beep
If MsgBox("The address and fax number are blank - enter now", vbYesNo,
"Confrim") = vbNo Then
Me.Bookmark = rs.Bookmark
End If
End If
End Sub
Click to expand...
Apologies in advance as I don't have time to test what I'm suggesting just
now but I would try:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone 'No "." between "Recordset" and "Clone"

The line "Me.Bookmark = rs.Bookmark" doesn't seem quite right, maybe someone
could jump in here ...

Keith.
 
Back
Top