-
RunTime Error 2107
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
Code:
Me.RecordSource = sQRYI dont understand why it would do this, all I want to do is clear the form for a fresh input.
Code:
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
Code:
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
-
Re: RunTime Error 2107
What is stored in sQRY just as you get to the line it falls down on (e.g. Me.RecordSource = sQRY)
-
Re: RunTime Error 2107
Code:
Me.RecordSource = sQRY
Me refers to the form, you should use rs instead.
-
Re: RunTime Error 2107
nm I read the msg wrong...
-
Re: RunTime Error 2107
Changing the code
Code:
Me.RecordSource = sQRY
to
Code:
rs.RecordSource = sQRY
will not work as it has a complie error of
Method or Data member not found
What would that be?
-
Re: RunTime Error 2107
Just copy pasted your own code to come up with the following
Code:
Set rs = CurrentDb.OpenRecordset(sQRY, dbOpenDynaset, dbSeeChanges)