Access trying to save a blank record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DJJ - 28 Jun 2006 18:20 GMT

I have a form with text boxes, combo boxes and a sub-form. Whenever the
user scrolls to the last record on the main form using the navigation
buttons and then moves to the empty (new) record and then scrolls back to
the previous record, Access attempts to save the blank record and generates
an ODBC (SQLServer) error "Cannot enter null value".

Is there anyway to stop Access trying to save the blank record whenever the
user (without any intention of entering a new record) scrolls to it by
accident?

DJ

Reply to this Message

Rick Brandt - 28 Jun 2006 18:44 GMT

> I have a form with text boxes, combo boxes and a sub-form. Whenever
> the user scrolls to the last record on the main form using the
[quoted text clipped - 6 lines]
> whenever the user (without any intention of entering a new record)
> scrolls to it by accident?

Access won't try to save a record (new or existing) unless some of the data
has changed. Do you have code or macro that is altering data each time you
navigate?

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

DJJ - 28 Jun 2006 22:06 GMT

Yes I have a check box that has its value set to false in order to correctly
call a standard module procedure in the Current Form Event that locked or
unlocks the controls. I can't get around that.

I've tried to creating a message box that requests the user if they want to
create a new record or not and then use the DoCmd to try and cancel all
events and move to another record if the user selects no but it doesn't
work. I don't know if there's another solution for this.

Private Sub Form_Current()

Dim intnewrec As Integer

Dim Msg, Style, Title, Response, MyString

intnewrec = Me.NewRecord

If intnewrec = True Then

Me.chkLocked.Value = False

Msg = "Do you want to create a new record?"

Style = vbYesNo + vbCritical + vbDefaultButton2

Title = "New Record?"

Response = MsgBox(Msg, Style, Title)

If Response = vbNo Then

DoCmd.CancelEvent

DoCmd.GoToRecord acDataForm, "frmMc2CollNameDataEntry",
acFirst

Exit Sub

End If

End If

Call RecdLocked(Me, Me.frmCollNameDataEntrySub, Me.chkLocked)

End Sub

>> I have a form with text boxes, combo boxes and a sub-form. Whenever
>> the user scrolls to the last record on the main form using the
[quoted text clipped - 10 lines]
> data has changed. Do you have code or macro that is altering data each
> time you navigate?

Reply to this Message

Rick Brandt - 29 Jun 2006 12:25 GMT

> Yes I have a check box that has its value set to false in order to
> correctly call a standard module procedure in the Current Form Event
> that locked or unlocks the controls. I can't get around that.

You'll have to explain that in more detail. Editing a record every time you
navigate to it is a terrible idea. I'm sure there is an alternative method to
accomplish what you are doing without changing the record every time.

Otherwise your current event can test for NewRecord and not change the record.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

DJJ - 30 Jun 2006 14:17 GMT

Okay,
Here is the code from the standard module that locks and unlocks controls on
a form. The form is locked or unlocked whenever the user checks a checkbox
on the form called chkLocked. It works very well when scrolling back and
forth through the records but I found that the call to the procedure became
ambivalent when scrolling into a new (blank) record. Especially if the last
record (EOF) was locked then the new record would also behave like it was
locked until the user checked and unchecked the check box. Setting the
value of chkLocked to false on a new record helped clear up that ambivalence
but then created this new problem.

Public Sub RecdLocked(frm As Form, sfrm As SubForm, chk As CheckBox)
On Error Resume Next
Dim ctl As Control
If chk.Value = True Then
For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox
.Locked = True
Case acComboBox
.Locked = True
Case acSubform
.Locked = True
End Select
End With
Next ctl
frm.AllowDeletions = False
With sfrm.Form
.AllowDeletions = False
.AllowAdditions = False
End With
Else
For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox
.Locked = False
Case acComboBox
.Locked = False
Case acSubform
.Locked = False
End Select
End With
Next ctl
frm.AllowDeletions = True
With sfrm.Form
.AllowDeletions = True
.AllowAdditions = True
End With
End If
Set ctl = Nothing
Set frm = Nothing
Set sfrm = Nothing
End Sub

>> Yes I have a check box that has its value set to false in order to
>> correctly call a standard module procedure in the Current Form Event
[quoted text clipped - 7 lines]
> Otherwise your current event can test for NewRecord and not change the
> record.

Reply to this Message

DJJ - 30 Jun 2006 19:07 GMT

I think I figured it out. I was getting an ODBC insert error. I assigned a
default value to the checkbox and it seems to work fine now. It always
something simple!

Thanks for pointing me in the right direction.

DJJ

Reply to this Message