|
|
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.
|
|
On Wed, 12 Nov 2008 11:40:02 -0800, dgunning
<dgunning[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >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.
--
croy
|
|
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.
"croy" wrote:
[Quoted Text] > On Wed, 12 Nov 2008 11:40:02 -0800, dgunning
> <dgunning[ at ]discussions.microsoft.com> wrote:
>
> >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.
>
> --
> croy
>
|
|
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
"dgunning" wrote:
[Quoted Text] > 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.
>
>
> "croy" wrote:
>
> > On Wed, 12 Nov 2008 11:40:02 -0800, dgunning
> > <dgunning[ at ]discussions.microsoft.com> wrote:
> >
> > >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.
> >
> > --
> > croy
> >
|
|
Don't allow a zero length field.
dgunning wrote:
[Quoted Text] > 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.
>
>
> "croy" wrote:
>
>> On Wed, 12 Nov 2008 11:40:02 -0800, dgunning
>> <dgunning[ at ]discussions.microsoft.com> wrote:
>>
>>> 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.
>>
>> --
>> croy
|
|
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.
"Beetle" wrote:
[Quoted Text] > 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
>
>
> "dgunning" wrote:
>
> > 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.
> >
> >
> > "croy" wrote:
> >
> > > On Wed, 12 Nov 2008 11:40:02 -0800, dgunning
> > > <dgunning[ at ]discussions.microsoft.com> wrote:
> > >
> > > >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.
> > >
> > > --
> > > croy
> > >
|
|
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
"dgunning" wrote:
[Quoted Text] > 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.
>
>
>
>
>
> "Beetle" wrote:
>
> > 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
> >
> >
> > "dgunning" wrote:
> >
> > > 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.
> > >
> > >
> > > "croy" wrote:
> > >
> > > > On Wed, 12 Nov 2008 11:40:02 -0800, dgunning
> > > > <dgunning[ at ]discussions.microsoft.com> wrote:
> > > >
> > > > >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.
> > > >
> > > > --
> > > > croy
> > > >
|
|
Thanks, it's working well now.
"Beetle" wrote:
[Quoted Text] > 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
>
>
> "dgunning" wrote:
>
> > 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.
> >
> >
> >
> >
> >
> > "Beetle" wrote:
> >
> > > 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
> > >
> > >
> > > "dgunning" wrote:
> > >
> > > > 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.
> > > >
> > > >
> > > > "croy" wrote:
> > > >
> > > > > On Wed, 12 Nov 2008 11:40:02 -0800, dgunning
> > > > > <dgunning[ at ]discussions.microsoft.com> wrote:
> > > > >
> > > > > >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.
> > > > >
> > > > > --
> > > > > croy
> > > > >
|
|
|