Access trying to save a blank record

  • Thread starter Thread starter DJJ
  • Start date Start date
D

DJJ

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
 
DJJ said:
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?
Click to expand...

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?
 
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
 
DJJ said:
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.
Click to expand...

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.
 
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
 
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
 
Back
Top