Why is record is saving before data is entered in all fields?

[Guest]

I have a form (frmDocuments) with a subform (sfrmDocsOnly) that is opened by
double-clicking a record selector from a subdatasheet on another form. When
the new form is opened, I want it to check to see if any records are returned
in the subform and, if not, add a new record with the [ClientID]
auto-populated. It appears to work fine. The frmDocuments opens and it
appears to have a new record added with sfrmDocsOnly and the [ClientID] is
auto-populated once I start typing. However, as soon as I move, even before
I can start typing, I get the following error:

"The field 'tblDocuments.Doc Type' cannot contain a Null value because the
Required property for this field is set to True. Enter a value in this
field."

It is referring to the first field that I must fill in, and it is a required
field, and needs to be. It appears that the record is saving before I even
enter anything. If I click "OK" on the error message I can continue to add
the record, but when I go to the next record or exit the form, I get the same
message. I can't figure this out at all.

Following is the code for the DblClick event:

Private Sub Form_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stOpenArgs As String

stDocName = "sfrmDocuments-NewClients"
stLinkCriteria = "[ClientID] =" & Me![Client ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.[ClientID]

End Sub

And following is the code in the Load event:

Private Sub Form_Load()

If Len(Me.OpenArgs & "") > 0 Then
DoCmd.GoToRecord , , acNewRec
Me.[ClientID] = Me.OpenArgs
End If

End Sub

Can anyone help me figure this out? I have been searching the help files,
books and the discussion group with no luck. Any help is greatly appreciated.

If further information is needed, please advise.

Thanks in advance.

 

[YDP]

Had a similar problem and it resolved itself when I went
to the main table and manually deleted any rows that were
created with Null values in the required field. How they
got there, who knows. It went away. Not saying it your
solution, but something to look at. Check the tables for
blank rows that could have been created.
YDP

-----Original Message-----
I have a form (frmDocuments) with a subform

(sfrmDocsOnly) that is opened by

double-clicking a record selector from a subdatasheet on another form. When
the new form is opened, I want it to check to see if any records are returned
in the subform and, if not, add a new record with the [ClientID]
auto-populated. It appears to work fine. The frmDocuments opens and it
appears to have a new record added with sfrmDocsOnly and the [ClientID] is
auto-populated once I start typing. However, as soon as I move, even before
I can start typing, I get the following error:

"The field 'tblDocuments.Doc Type' cannot contain a Null value because the
Required property for this field is set to True. Enter a value in this
field."

It is referring to the first field that I must fill in, and it is a required
field, and needs to be. It appears that the record is saving before I even
enter anything. If I click "OK" on the error message I can continue to add
the record, but when I go to the next record or exit the form, I get the same
message. I can't figure this out at all.

Following is the code for the DblClick event:

Private Sub Form_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stOpenArgs As String

stDocName = "sfrmDocuments-NewClients"
stLinkCriteria = "[ClientID] =" & Me![Client ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me. [ClientID]

End Sub

And following is the code in the Load event:

Private Sub Form_Load()

If Len(Me.OpenArgs & "") > 0 Then
DoCmd.GoToRecord , , acNewRec
Me.[ClientID] = Me.OpenArgs
End If

End Sub

Can anyone help me figure this out? I have been searching the help files,
books and the discussion group with no luck. Any help is greatly appreciated.

If further information is needed, please advise.

Thanks in advance.
.

 

[Guest]

Thanks, I'll check that. I have a feeling this is a different issue, but I
think I figured a different way of working it that doesn't cause this
problem. Thanks for your help.

Had a similar problem and it resolved itself when I went
to the main table and manually deleted any rows that were
created with Null values in the required field. How they
got there, who knows. It went away. Not saying it your
solution, but something to look at. Check the tables for
blank rows that could have been created.
YDP

-----Original Message-----
I have a form (frmDocuments) with a subform

(sfrmDocsOnly) that is opened by

double-clicking a record selector from a subdatasheet on another form. When
the new form is opened, I want it to check to see if any records are returned
in the subform and, if not, add a new record with the [ClientID]
auto-populated. It appears to work fine. The frmDocuments opens and it
appears to have a new record added with sfrmDocsOnly and the [ClientID] is
auto-populated once I start typing. However, as soon as I move, even before
I can start typing, I get the following error:

"The field 'tblDocuments.Doc Type' cannot contain a Null value because the
Required property for this field is set to True. Enter a value in this
field."

It is referring to the first field that I must fill in, and it is a required
field, and needs to be. It appears that the record is saving before I even
enter anything. If I click "OK" on the error message I can continue to add
the record, but when I go to the next record or exit the form, I get the same
message. I can't figure this out at all.

Following is the code for the DblClick event:

Private Sub Form_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stOpenArgs As String

stDocName = "sfrmDocuments-NewClients"
stLinkCriteria = "[ClientID] =" & Me![Client ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me. [ClientID]

End Sub

And following is the code in the Load event:

Private Sub Form_Load()

If Len(Me.OpenArgs & "") > 0 Then
DoCmd.GoToRecord , , acNewRec
Me.[ClientID] = Me.OpenArgs
End If

End Sub

Can anyone help me figure this out? I have been searching the help files,
books and the discussion group with no luck. Any help is greatly appreciated.

If further information is needed, please advise.

Thanks in advance.
.

 

[Guest]

change identy to yes and identity seed to 1 for example. that will work. You
must have a value in a field that is unigue!

"D'Lilah" je napisal:

Thanks, I'll check that. I have a feeling this is a different issue, but I
think I figured a different way of working it that doesn't cause this
problem. Thanks for your help.

Had a similar problem and it resolved itself when I went
to the main table and manually deleted any rows that were
created with Null values in the required field. How they
got there, who knows. It went away. Not saying it your
solution, but something to look at. Check the tables for
blank rows that could have been created.
YDP

-----Original Message-----
I have a form (frmDocuments) with a subform

(sfrmDocsOnly) that is opened by

double-clicking a record selector from a subdatasheet on another form. When
the new form is opened, I want it to check to see if any records are returned
in the subform and, if not, add a new record with the [ClientID]
auto-populated. It appears to work fine. The frmDocuments opens and it
appears to have a new record added with sfrmDocsOnly and the [ClientID] is
auto-populated once I start typing. However, as soon as I move, even before
I can start typing, I get the following error:

"The field 'tblDocuments.Doc Type' cannot contain a Null value because the
Required property for this field is set to True. Enter a value in this
field."

It is referring to the first field that I must fill in, and it is a required
field, and needs to be. It appears that the record is saving before I even
enter anything. If I click "OK" on the error message I can continue to add
the record, but when I go to the next record or exit the form, I get the same
message. I can't figure this out at all.

Following is the code for the DblClick event:

Private Sub Form_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stOpenArgs As String

stDocName = "sfrmDocuments-NewClients"
stLinkCriteria = "[ClientID] =" & Me![Client ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me. [ClientID]

End Sub

And following is the code in the Load event:

Private Sub Form_Load()

If Len(Me.OpenArgs & "") > 0 Then
DoCmd.GoToRecord , , acNewRec
Me.[ClientID] = Me.OpenArgs
End If

End Sub

Can anyone help me figure this out? I have been searching the help files,
books and the discussion group with no luck. Any help is greatly appreciated.

If further information is needed, please advise.

Thanks in advance.
.

 

[Jamie Richards]

D'Lilah,

At some point before you leave the new record, try execute the following:

With Docmd
.RunCommand acCmdSelectRecord
.RunCommand acCmdSaveRecord
End With

The idea is to get the value you wrote to the control, committed to the
table. See how you go.

Jamie

Thanks, I'll check that. I have a feeling this is a different issue, but
I
think I figured a different way of working it that doesn't cause this
problem. Thanks for your help.

Had a similar problem and it resolved itself when I went
to the main table and manually deleted any rows that were
created with Null values in the required field. How they
got there, who knows. It went away. Not saying it your
solution, but something to look at. Check the tables for
blank rows that could have been created.
YDP

-----Original Message-----
I have a form (frmDocuments) with a subform

(sfrmDocsOnly) that is opened by

double-clicking a record selector from a subdatasheet on another form. When
the new form is opened, I want it to check to see if any records are returned
in the subform and, if not, add a new record with the [ClientID]
auto-populated. It appears to work fine. The frmDocuments opens and it
appears to have a new record added with sfrmDocsOnly and the [ClientID] is
auto-populated once I start typing. However, as soon as I move, even before
I can start typing, I get the following error:

"The field 'tblDocuments.Doc Type' cannot contain a Null value because the
Required property for this field is set to True. Enter a value in this
field."

It is referring to the first field that I must fill in, and it is a required
field, and needs to be. It appears that the record is saving before I even
enter anything. If I click "OK" on the error message I can continue to add
the record, but when I go to the next record or exit the form, I get the same
message. I can't figure this out at all.

Following is the code for the DblClick event:

Private Sub Form_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stOpenArgs As String

stDocName = "sfrmDocuments-NewClients"
stLinkCriteria = "[ClientID] =" & Me![Client ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me. [ClientID]

End Sub

And following is the code in the Load event:

Private Sub Form_Load()

If Len(Me.OpenArgs & "") > 0 Then
DoCmd.GoToRecord , , acNewRec
Me.[ClientID] = Me.OpenArgs
End If

End Sub

Can anyone help me figure this out? I have been searching the help files,
books and the discussion group with no luck. Any help is greatly appreciated.

If further information is needed, please advise.

Thanks in advance.
.

 

[Guest]

Thank you, but I am not sure I understand what you are saying. However, I
set up the form differently and got it working another way. Thanks again.

change identy to yes and identity seed to 1 for example. that will work. You
must have a value in a field that is unigue!

"D'Lilah" je napisal:

Thanks, I'll check that. I have a feeling this is a different issue, but I
think I figured a different way of working it that doesn't cause this
problem. Thanks for your help.

Had a similar problem and it resolved itself when I went
to the main table and manually deleted any rows that were
created with Null values in the required field. How they
got there, who knows. It went away. Not saying it your
solution, but something to look at. Check the tables for
blank rows that could have been created.
YDP
-----Original Message-----
I have a form (frmDocuments) with a subform
(sfrmDocsOnly) that is opened by
double-clicking a record selector from a subdatasheet on
another form. When
the new form is opened, I want it to check to see if any
records are returned
in the subform and, if not, add a new record with the
[ClientID]
auto-populated. It appears to work fine. The
frmDocuments opens and it
appears to have a new record added with sfrmDocsOnly and
the [ClientID] is
auto-populated once I start typing. However, as soon as
I move, even before
I can start typing, I get the following error:

"The field 'tblDocuments.Doc Type' cannot contain a Null
value because the
Required property for this field is set to True. Enter a
value in this
field."

It is referring to the first field that I must fill in,
and it is a required
field, and needs to be. It appears that the record is
saving before I even
enter anything. If I click "OK" on the error message I
can continue to add
the record, but when I go to the next record or exit the
form, I get the same
message. I can't figure this out at all.

Following is the code for the DblClick event:

Private Sub Form_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stOpenArgs As String

stDocName = "sfrmDocuments-NewClients"
stLinkCriteria = "[ClientID] =" & Me![Client ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.
[ClientID]

End Sub

And following is the code in the Load event:

Private Sub Form_Load()

If Len(Me.OpenArgs & "") > 0 Then
DoCmd.GoToRecord , , acNewRec
Me.[ClientID] = Me.OpenArgs
End If

End Sub

Can anyone help me figure this out? I have been
searching the help files,
books and the discussion group with no luck. Any help is
greatly appreciated.

If further information is needed, please advise.

Thanks in advance.
.

 

[Guest]

Thanks, Jamie. The strange thing is I am getting the message as I enter the
record, not before I leave the record. However, I set the form up
differently and got it to work. Now the only problem when I enter new
records for the same client, the client name at the top of the form blanks
out and as soon as I start typing it fills back in with the client's name.
Any ideas?

Thanks again, Jamie.

D'Lilah,

At some point before you leave the new record, try execute the following:

With Docmd
.RunCommand acCmdSelectRecord
.RunCommand acCmdSaveRecord
End With

The idea is to get the value you wrote to the control, committed to the
table. See how you go.

Jamie

Thanks, I'll check that. I have a feeling this is a different issue, but
I
think I figured a different way of working it that doesn't cause this
problem. Thanks for your help.

Had a similar problem and it resolved itself when I went
to the main table and manually deleted any rows that were
created with Null values in the required field. How they
got there, who knows. It went away. Not saying it your
solution, but something to look at. Check the tables for
blank rows that could have been created.
YDP
-----Original Message-----
I have a form (frmDocuments) with a subform
(sfrmDocsOnly) that is opened by
double-clicking a record selector from a subdatasheet on
another form. When
the new form is opened, I want it to check to see if any
records are returned
in the subform and, if not, add a new record with the
[ClientID]
auto-populated. It appears to work fine. The
frmDocuments opens and it
appears to have a new record added with sfrmDocsOnly and
the [ClientID] is
auto-populated once I start typing. However, as soon as
I move, even before
I can start typing, I get the following error:

"The field 'tblDocuments.Doc Type' cannot contain a Null
value because the
Required property for this field is set to True. Enter a
value in this
field."

It is referring to the first field that I must fill in,
and it is a required
field, and needs to be. It appears that the record is
saving before I even
enter anything. If I click "OK" on the error message I
can continue to add
the record, but when I go to the next record or exit the
form, I get the same
message. I can't figure this out at all.

Following is the code for the DblClick event:

Private Sub Form_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stOpenArgs As String

stDocName = "sfrmDocuments-NewClients"
stLinkCriteria = "[ClientID] =" & Me![Client ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.
[ClientID]

End Sub

And following is the code in the Load event:

Private Sub Form_Load()

If Len(Me.OpenArgs & "") > 0 Then
DoCmd.GoToRecord , , acNewRec
Me.[ClientID] = Me.OpenArgs
End If

End Sub

Can anyone help me figure this out? I have been
searching the help files,
books and the discussion group with no luck. Any help is
greatly appreciated.

If further information is needed, please advise.

Thanks in advance.
.

 

[Van T. Dinh]

I think this got something to do with the AutoSave "feature" of the Form /
Subform combination.

You did not state which Form (the Form being used as the MainForm? or the
Form being used as the Subform?) the posted Form_Load belongs to but my
guess is that it belongs to the frmDocuments. If this the case, your code
starts the process of data entry for a new Record in the frmDocuments.

Now for the AutoSave "feature": there are 2 Data Objects in the Form/Subform
combination, namely the MainForm and the Subform. When you change the
active data object, i.e. moving from the MainForm to the Subform or vice
versa, Access AutoSave feature will try to save the CurrentRecord in the
current active Data Object *before* moving to the other Data Object. This
is to preserve the Referential Integrity of the One-to-Many relationship
which the Form / Subform combination is designed to accomodate.

In your case, I think the Access AutoSave feature tries to save the current
Record (on the MainForm "frmDocuments") but the Field [Doc Type] does not
allow Null and therefore you got the posted error.

Try open your Form / Subform as you did previously but enter a valid value
for [Doc Type] (and any other required Fields for the frmDocuments
RecordSource) before moving to the Subform and see what happens ...

With the Form / Subform combination, you need to complete the new Record on
the MainForm before you can move to the Subform. In fact, this is why I
normally hide the Subform until the user fills out data on the MainForm and
click a "Save" CommandButton to explicitly save the "One" Record before I
make the Subform (for the "Many" Records) visible.

--
HTH
Van T. Dinh
MVP (Access)

I have a form (frmDocuments) with a subform (sfrmDocsOnly) that is opened by
double-clicking a record selector from a subdatasheet on another form. When
the new form is opened, I want it to check to see if any records are returned
in the subform and, if not, add a new record with the [ClientID]
auto-populated. It appears to work fine. The frmDocuments opens and it
appears to have a new record added with sfrmDocsOnly and the [ClientID] is
auto-populated once I start typing. However, as soon as I move, even before
I can start typing, I get the following error:

"The field 'tblDocuments.Doc Type' cannot contain a Null value because the
Required property for this field is set to True. Enter a value in this
field."

It is referring to the first field that I must fill in, and it is a required
field, and needs to be. It appears that the record is saving before I even
enter anything. If I click "OK" on the error message I can continue to add
the record, but when I go to the next record or exit the form, I get the same
message. I can't figure this out at all.

Following is the code for the DblClick event:

Private Sub Form_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stOpenArgs As String

stDocName = "sfrmDocuments-NewClients"
stLinkCriteria = "[ClientID] =" & Me![Client ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.[ClientID]

End Sub

And following is the code in the Load event:

Private Sub Form_Load()

If Len(Me.OpenArgs & "") > 0 Then
DoCmd.GoToRecord , , acNewRec
Me.[ClientID] = Me.OpenArgs
End If

End Sub

Can anyone help me figure this out? I have been searching the help files,
books and the discussion group with no luck. Any help is greatly appreciated.

If further information is needed, please advise.

Thanks in advance.

 

[Guest]

Van:

Thanks for your response. Unfortunately, the [Doc Type] control is on the
subform. It appears that Access is attempting to save an empty record as I
try to enter the subform to add records. However, I have reworked the form
and it appears to be working okay now, so far.

Thanks again for your help.

D'Lilah

I think this got something to do with the AutoSave "feature" of the Form /
Subform combination.

You did not state which Form (the Form being used as the MainForm? or the
Form being used as the Subform?) the posted Form_Load belongs to but my
guess is that it belongs to the frmDocuments. If this the case, your code
starts the process of data entry for a new Record in the frmDocuments.

Now for the AutoSave "feature": there are 2 Data Objects in the Form/Subform
combination, namely the MainForm and the Subform. When you change the
active data object, i.e. moving from the MainForm to the Subform or vice
versa, Access AutoSave feature will try to save the CurrentRecord in the
current active Data Object *before* moving to the other Data Object. This
is to preserve the Referential Integrity of the One-to-Many relationship
which the Form / Subform combination is designed to accomodate.

In your case, I think the Access AutoSave feature tries to save the current
Record (on the MainForm "frmDocuments") but the Field [Doc Type] does not
allow Null and therefore you got the posted error.

Try open your Form / Subform as you did previously but enter a valid value
for [Doc Type] (and any other required Fields for the frmDocuments
RecordSource) before moving to the Subform and see what happens ...

With the Form / Subform combination, you need to complete the new Record on
the MainForm before you can move to the Subform. In fact, this is why I
normally hide the Subform until the user fills out data on the MainForm and
click a "Save" CommandButton to explicitly save the "One" Record before I
make the Subform (for the "Many" Records) visible.

--
HTH
Van T. Dinh
MVP (Access)

I have a form (frmDocuments) with a subform (sfrmDocsOnly) that is opened by
double-clicking a record selector from a subdatasheet on another form. When
the new form is opened, I want it to check to see if any records are returned
in the subform and, if not, add a new record with the [ClientID]
auto-populated. It appears to work fine. The frmDocuments opens and it
appears to have a new record added with sfrmDocsOnly and the [ClientID] is
auto-populated once I start typing. However, as soon as I move, even before
I can start typing, I get the following error:

"The field 'tblDocuments.Doc Type' cannot contain a Null value because the
Required property for this field is set to True. Enter a value in this
field."

It is referring to the first field that I must fill in, and it is a required
field, and needs to be. It appears that the record is saving before I even
enter anything. If I click "OK" on the error message I can continue to add
the record, but when I go to the next record or exit the form, I get the same
message. I can't figure this out at all.

Following is the code for the DblClick event:

Private Sub Form_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stOpenArgs As String

stDocName = "sfrmDocuments-NewClients"
stLinkCriteria = "[ClientID] =" & Me![Client ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.[ClientID]

End Sub

And following is the code in the Load event:

Private Sub Form_Load()

If Len(Me.OpenArgs & "") > 0 Then
DoCmd.GoToRecord , , acNewRec
Me.[ClientID] = Me.OpenArgs
End If

End Sub

Can anyone help me figure this out? I have been searching the help files,
books and the discussion group with no luck. Any help is greatly appreciated.

If further information is needed, please advise.

Thanks in advance.