Preventing blank field

[dgunning]

I have created a form to use for entering new records. I open this form with
the dataEntry property set to true. There are text boxes bound to the field
values and then a button I created to add an additional record. (I want to
create a form that the user will be able to enter data without using the
mouse.) The event code for this add button is simply DoCmd.RunCommand
acCmdRecordsGoToNew.

Every record must have a value in the Store field, so I created a validation
rule for the store field - Is Not Null. But this isn't working -- if the
add button is clicked without filling in the Store value, a blank record is
added to the table. No error message is displayed. How can I get this
validation rule to work?

Thanks for any help.

 

[croy]

I have created a form to use for entering new records. I open this form with
the dataEntry property set to true. There are text boxes bound to the field
values and then a button I created to add an additional record. (I want to
create a form that the user will be able to enter data without using the
mouse.) The event code for this add button is simply DoCmd.RunCommand
acCmdRecordsGoToNew.

Every record must have a value in the Store field, so I created a validation
rule for the store field - Is Not Null. But this isn't working -- if the
add button is clicked without filling in the Store value, a blank record is
added to the table. No error message is displayed. How can I get this
validation rule to work?

Put your validation code in the associated field in the
underlying table.

Or...

If your "Store" field is for values that answere a question
like "which store?", or "Store Number", make a table for
"Stores" and use it as the lookup source for a combobox on
your form. Set the limit to list value to Yes.

 

[dgunning]

Thanks for the quick reply. The validation code IS in the associated field
in the underlying table.

I'd rather not create a table for stores if it can be avoided. This is just
a quick application to do one very specific thing that just takes a few hours
a month. Keeping a store table up to date -- we have over 7,000 stores --
would add a lot of needless complexity to it. That is, if I can get it to
work properly without one.

 

[Beetle]

In the table set the Required property of the field to Yes and you may
also want to set the Allow Zero Length property to No.

In the forms Before Update event you could use code like the following
as another level of validation;

Private Sub Form_BeforeUpdate (Cancel As Integer)

If Nz([Store], "") = "" Then
MsgBox "You must enter a Store value"
Cancel = True
Me![Store].SetFocus
End If

End Sub

Replace [Store] with the actual name of your Store field

 

[Mike Painter]

Don't allow a zero length field.

Thanks for the quick reply. The validation code IS in the associated
field in the underlying table.

I'd rather not create a table for stores if it can be avoided. This
is just a quick application to do one very specific thing that just
takes a few hours a month. Keeping a store table up to date -- we
have over 7,000 stores -- would add a lot of needless complexity to
it. That is, if I can get it to work properly without one.

 

[dgunning]

I'm making some progress -- by adding the BeforeUpdate code you listed below,
I'm eliminating the possibility of adding blank records to the table. But
now it's getting really difficult to close my form if you instead decide you
don't want to add any records after all. Let me give you some more details
about my form -- it seems like it should be pretty simple, but I sure am
having a tough time getting it to do what I want.

I removed the validation properties from the field in the underlying table,
it seemed that the beforeUpdate event was more effective in preventing blank
records.

The form has a Store Field and a number of checkboxes, all bound to fields
in a table. There is an add button and a close button. Code for the add
button is simply
DoCmd.RunCommand acCmdRecordsGoToNew
The close button's code is:
DoCmd.Close acForm, "frmRsales"
There is also some fixing up of the store field that I need to do. It's a
five character field, but if the user enters less than 5 characters, I add
leading 0's in the textbox's
exit event:
Me.STORE.Value = Format(Me.STORE.Value, "00000")

If I click the add button and then the done button, I get the need store
number message from the BeforeUpdate event, and then the form closes. I'd
rather not get the error message here since we're not trying to save the
record.

If I click the add button twice in succession, first I get the need store
number error message, and then an access error message -- run time error
2501: the run command action was cancelled, from the code in the add button
Click event.

Thanks for any suggestions on how to get this to work better.

In the table set the Required property of the field to Yes and you may
also want to set the Allow Zero Length property to No.

In the forms Before Update event you could use code like the following
as another level of validation;

Private Sub Form_BeforeUpdate (Cancel As Integer)

If Nz([Store], "") = "" Then
MsgBox "You must enter a Store value"
Cancel = True
Me![Store].SetFocus
End If

End Sub

Replace [Store] with the actual name of your Store field
--
_________

Sean Bailey

Thanks for the quick reply. The validation code IS in the associated field
in the underlying table.

I'd rather not create a table for stores if it can be avoided. This is just
a quick application to do one very specific thing that just takes a few hours
a month. Keeping a store table up to date -- we have over 7,000 stores --
would add a lot of needless complexity to it. That is, if I can get it to
work properly without one.

 

[Beetle]

In the Click event of your Done button (or Close button, I'm not sure
what it's called) you could put;

If Me.NewRecord Then Me.Undo
DoCmd.Close acForm, Me.Name

If the form still has the built in close button (X in the upper right corner)
then you may put the line;

If Me.NewRecord Then Me.Undo

in the forms Close event also.

That should solve the problem. However, based on what you've described
it sounds like you still may have another issue. The mere act of moving
the form to a new record does not actually create a new record. The record
is not actually created until someone (or something) begins inserting
values. If the Before Update event is firing even though you (or the users)
have not entered anything, then that means that something (i.e. some
code somewhere in the form's module) is inserting a value into one of the
fields, thereby starting to create a new record. You should double check
all the code for your form to see if you can determine what is causing this.
--
_________

Sean Bailey

I'm making some progress -- by adding the BeforeUpdate code you listed below,
I'm eliminating the possibility of adding blank records to the table. But
now it's getting really difficult to close my form if you instead decide you
don't want to add any records after all. Let me give you some more details
about my form -- it seems like it should be pretty simple, but I sure am
having a tough time getting it to do what I want.

I removed the validation properties from the field in the underlying table,
it seemed that the beforeUpdate event was more effective in preventing blank
records.

The form has a Store Field and a number of checkboxes, all bound to fields
in a table. There is an add button and a close button. Code for the add
button is simply
DoCmd.RunCommand acCmdRecordsGoToNew
The close button's code is:
DoCmd.Close acForm, "frmRsales"
There is also some fixing up of the store field that I need to do. It's a
five character field, but if the user enters less than 5 characters, I add
leading 0's in the textbox's
exit event:
Me.STORE.Value = Format(Me.STORE.Value, "00000")

If I click the add button and then the done button, I get the need store
number message from the BeforeUpdate event, and then the form closes. I'd
rather not get the error message here since we're not trying to save the
record.

If I click the add button twice in succession, first I get the need store
number error message, and then an access error message -- run time error
2501: the run command action was cancelled, from the code in the add button
Click event.

Thanks for any suggestions on how to get this to work better.

In the table set the Required property of the field to Yes and you may
also want to set the Allow Zero Length property to No.

In the forms Before Update event you could use code like the following
as another level of validation;

Private Sub Form_BeforeUpdate (Cancel As Integer)

If Nz([Store], "") = "" Then
MsgBox "You must enter a Store value"
Cancel = True
Me![Store].SetFocus
End If

End Sub

Replace [Store] with the actual name of your Store field
--
_________

Sean Bailey

Thanks for the quick reply. The validation code IS in the associated field
in the underlying table.

I'd rather not create a table for stores if it can be avoided. This is just
a quick application to do one very specific thing that just takes a few hours
a month. Keeping a store table up to date -- we have over 7,000 stores --
would add a lot of needless complexity to it. That is, if I can get it to
work properly without one.


:

On Wed, 12 Nov 2008 11:40:02 -0800, dgunning

I have created a form to use for entering new records. I open this form with
the dataEntry property set to true. There are text boxes bound to the field
values and then a button I created to add an additional record. (I want to
create a form that the user will be able to enter data without using the
mouse.) The event code for this add button is simply DoCmd.RunCommand
acCmdRecordsGoToNew.

Every record must have a value in the Store field, so I created a validation
rule for the store field - Is Not Null. But this isn't working -- if the
add button is clicked without filling in the Store value, a blank record is
added to the table. No error message is displayed. How can I get this
validation rule to work?

Put your validation code in the associated field in the
underlying table.

Or...

If your "Store" field is for values that answere a question
like "which store?", or "Store Number", make a table for
"Stores" and use it as the lookup source for a combobox on
your form. Set the limit to list value to Yes.

 

[dgunning]

Thanks, it's working well now.

In the Click event of your Done button (or Close button, I'm not sure
what it's called) you could put;

If Me.NewRecord Then Me.Undo
DoCmd.Close acForm, Me.Name

If the form still has the built in close button (X in the upper right corner)
then you may put the line;

If Me.NewRecord Then Me.Undo

in the forms Close event also.

That should solve the problem. However, based on what you've described
it sounds like you still may have another issue. The mere act of moving
the form to a new record does not actually create a new record. The record
is not actually created until someone (or something) begins inserting
values. If the Before Update event is firing even though you (or the users)
have not entered anything, then that means that something (i.e. some
code somewhere in the form's module) is inserting a value into one of the
fields, thereby starting to create a new record. You should double check
all the code for your form to see if you can determine what is causing this.
--
_________

Sean Bailey

I'm making some progress -- by adding the BeforeUpdate code you listed below,
I'm eliminating the possibility of adding blank records to the table. But
now it's getting really difficult to close my form if you instead decide you
don't want to add any records after all. Let me give you some more details
about my form -- it seems like it should be pretty simple, but I sure am
having a tough time getting it to do what I want.

I removed the validation properties from the field in the underlying table,
it seemed that the beforeUpdate event was more effective in preventing blank
records.

The form has a Store Field and a number of checkboxes, all bound to fields
in a table. There is an add button and a close button. Code for the add
button is simply
DoCmd.RunCommand acCmdRecordsGoToNew
The close button's code is:
DoCmd.Close acForm, "frmRsales"
There is also some fixing up of the store field that I need to do. It's a
five character field, but if the user enters less than 5 characters, I add
leading 0's in the textbox's
exit event:
Me.STORE.Value = Format(Me.STORE.Value, "00000")

If I click the add button and then the done button, I get the need store
number message from the BeforeUpdate event, and then the form closes. I'd
rather not get the error message here since we're not trying to save the
record.

If I click the add button twice in succession, first I get the need store
number error message, and then an access error message -- run time error
2501: the run command action was cancelled, from the code in the add button
Click event.

Thanks for any suggestions on how to get this to work better.

In the table set the Required property of the field to Yes and you may
also want to set the Allow Zero Length property to No.

In the forms Before Update event you could use code like the following
as another level of validation;

Private Sub Form_BeforeUpdate (Cancel As Integer)

If Nz([Store], "") = "" Then
MsgBox "You must enter a Store value"
Cancel = True
Me![Store].SetFocus
End If

End Sub

Replace [Store] with the actual name of your Store field
--
_________

Sean Bailey


:

Thanks for the quick reply. The validation code IS in the associated field
in the underlying table.

I'd rather not create a table for stores if it can be avoided. This is just
a quick application to do one very specific thing that just takes a few hours
a month. Keeping a store table up to date -- we have over 7,000 stores --
would add a lot of needless complexity to it. That is, if I can get it to
work properly without one.


:

On Wed, 12 Nov 2008 11:40:02 -0800, dgunning

I have created a form to use for entering new records. I open this form with
the dataEntry property set to true. There are text boxes bound to the field
values and then a button I created to add an additional record. (I want to
create a form that the user will be able to enter data without using the
mouse.) The event code for this add button is simply DoCmd.RunCommand
acCmdRecordsGoToNew.

Every record must have a value in the Store field, so I created a validation
rule for the store field - Is Not Null. But this isn't working -- if the
add button is clicked without filling in the Store value, a blank record is
added to the table. No error message is displayed. How can I get this
validation rule to work?

Put your validation code in the associated field in the
underlying table.

Or...

If your "Store" field is for values that answere a question
like "which store?", or "Store Number", make a table for
"Stores" and use it as the lookup source for a combobox on
your form. Set the limit to list value to Yes.