RunTime Error 2107

[JezLisle]

I am getting the RunTime Error 2107 "The value you entered doesn't meet the validation rule defined for the field or control"

When I try and run my code below. What happens is the first time the cmdAddNew_Click() runs it works well and creates my record. I can input all the form and then run the Submit_Click() which then UPDATES the record created in the DB. At the end of this routine it will then clear the TextBox's or the Combo's (these are all bound) which works great but the problem is the txtNHSNo which is unbound and is the problem as when I click on the AddNew it falls over and debugs on line

Me.RecordSource = sQRY

I dont understand why it would do this, all I want to do is clear the form for a fresh input.

Private Sub cmdAddNew_Click()
Dim varInput As Variant
Dim rs As DAO.Recordset
Dim sQRY As String
'**************************************
    varInput = InputBox("Enter the NHS Number", "Add new Data")
    If varInput = "" Then Exit Sub
            Set rs = CurrentDb.OpenRecordset("SELECT jez_SWM_InputDetails.* FROM jez_SWM_InputDetails WHERE jez_SWM_InputDetails.PersonalID = 1 ", dbOpenDynaset, dbSeeChanges)
    rs.AddNew
    rs.Fields![NHSNo] = varInput
    rs.Update
    rs.Close
    Set rs = Nothing
'**************************************
        sQRY = "SELECT jez_SWM_InputDetails.* FROM jez_SWM_InputDetails WHERE " & _
                    "jez_SWM_InputDetails.NHSNo = " & Chr$(10) & varInput & Chr$(10)
    Me.RecordSource = sQRY
    Me.txtNHSNo.Value = varInput
    Me.txtOpenClose.Value = "Open"
    Me.txtForename.SetFocus
End Sub

Private Sub cmdSubmit_Click()
Dim varInput As Variant
Dim sQRY As String
'**************************************
    varResponse = MsgBox("Save Changes?", vbYesNo, cApplicationName)
        If varResponse = vbNo Then
            Me.Undo
                Exit Sub
        End If
'**************************************
                sQRY = "UPDATE jez_SWM_INPUTDETAILS " & _
                            "SET [NHSNo] = '" & Me.txtNHSNo & "', [Surname] = '" & Me.txtSurname & "', [Forename] = '" & Me.txtForename & "', [Gender] = '" & Me.cboGender & "', [Address1] = '" & _
                            Me.txtAddress1 & "', [Address2] = '" & Me.txtAddress2 & "', [Address3] = '" & Me.txtAddress3 & "', [Postcode] = '" & Me.txtPostcode & "', [Telephone] = '" & _
                            Me.txtTelephone & "', [DateOfBirth] = '" & Me.txtDOB & "', [ReferralReasonDescription] = '" & Me.cboReferralRsn & "', [SourceDescription] = '" & _
                            Me.cboReferralSource & "',  [DateOfReferral] = '" & Me.txtReferralDate & "', [DateReferralRecieved] = '" & VBA.Now & "', [OpenorClosed] = '" & _
                            Me.txtOpenClose & "', [StartingWeight] = '" & Me.txtStartWeight & "', [FinalWeight] = '" & Me.txtFinalWeight & "', [Height] = '" & Me.txtHeight & "', [StartingBMI] = '" & Me.txtStartBMI & "', " & _
                            "[FinalBMI] = '" & Me.txtFinalBMI & "', [StartingBloodPressure] = '" & Me.txtStartBlood & "', [FinalBloodPressure] = '" & Me.txtFinalBlood & "', [StartingExerciseLevel] = '" & _
                            Me.txtStartExercise & "', [FinalExerciseLevel] = '" & Me.txtFinalExercise & "', [StartingDietLevel] = '" & Me.txtStartDiet & "', [FinalDietLevel] = '" & Me.txtFinalDiet & "', " & _
                            "[StartingSelfEsteemScore] = '" & Me.txtStartSelf & "', [FinalSelfEsteemScore] = '" & Me.txtFinalSelf & "', [StartingWaistCircumference] = '" & Me.txtStartWaist & "', " & _
                            "[FinalWaistCircumference] = '" & Me.txtFinalSelf & "', [Comments] = '" & Me.txtComments & "', [SessionType] = '" & Me.cboSessionType & "', [NHSStaffName] = '" & _
                            Me.txtStaffName & "', [Arrived] = '" & Me.cboAttendance & "', [ActiveRecord] = -1, [InputBy] = '" & fOSUserName & "', [InputDate] = '" & VBA.Now & "', " & _
                            "[InputFlag] = -1 " & _
                            "WHERE jez_SWM_INPUTDETAILS.PersonalID = Forms!frmMain!txtPersonalID "
                DoCmd.RunSQL sQRY
    Me.lblBMIInfo.Visible = False
    Me.txtDummy.SetFocus
    Me.txtNHSNo = ""
    
    Me.txtForename = ""
    Me.txtSurname = ""
    Me.txtAddress1 = ""
    Me.txtAddress2 = ""
    Me.txtAddress3 = ""
    Me.txtPostcode = ""
    Me.txtTelephone = ""
    Me.cboGender = ""
    Me.txtDOB = ""
    Me.cboReferralRsn = ""
    Me.cboReferralSource = ""
    Me.txtReferralDate = ""
    Me.txtRecievedDate = ""
    Me.txtOpenClose = ""
    Me.txtHeight = ""
    Me.StartingWeight = ""
    Me.txtFinalWeight = ""
    Me.txtStartWaist = ""
    Me.txtFinalWaist = ""
    Me.txtStartBlood = ""
    Me.txtFinalBlood = ""
    Me.txtStartExercise = ""
    Me.txtFinalExercise = ""
    Me.txtStartDiet = ""
    Me.txtFinalDiet = ""
    Me.txtStartSelf = ""
    Me.txtFinalSelf = ""
    Me.cboSessionType = ""
    Me.txtStaffName = ""
    Me.cboAttendance = ""
    Me.txtComments = ""
    Me.chkActive = ""
    Me.txtInputUser = ""
    Me.txtInputDate = ""
    Me.chkInputFlag = ""
End Sub

 

[marleymanner1]

I wonder if the Chr$(10) as anything to do with this? What is that anyway? I know Chr(13) is like a line feed or something. Is Chr(10) a space? I wonder if that's screwing this stuff up....?

 

[ByteMyzer]

marleymanner1,

Chr(13) is a carriage return. Chr(10) is a linefeed.


JezLisle,

Try changing the following line of your code thus:

        sQRY = "SELECT jez_SWM_InputDetails.*" _
             & " FROM jez_SWM_InputDetails" _
             & " WHERE jez_SWM_InputDetails.NHSNo = " & Chr$([b][i]34[/i][/b]) _
             & varInput & Chr$([b][i]34[/i][/b])