RunTime Error 2107

[TedMosby]

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 = sQRYI 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

[kevchadders]

What is stored in sQRY just as you get to the line it falls down on (e.g. Me.RecordSource = sQRY)

[dee-u]

Me.RecordSource = sQRY

Me refers to the form, you should use rs instead.

[smendoza]

nm I read the msg wrong...

[TedMosby]

Changing the code

Me.RecordSource = sQRY

to

rs.RecordSource = sQRY

will not work as it has a complie error of
Method or Data member not found

What would that be?

[dee-u]

Just copy pasted your own code to come up with the following

Set rs = CurrentDb.OpenRecordset(sQRY, dbOpenDynaset, dbSeeChanges)