Clear form after update

[JezLisle]

I have a submit records cmdButton.

As shown in the code below it updates the opened record in the SQL BE DB. How can I get it to clear all the values from the form, to enable me to input a new record.

I have tried

Me.txtNHSNo = ""
Me.txtNHSNo.Value = NULL
Me.txtNHSNo.Value = ""

and these all dont work, as when click cmdButton to create new record it doesnt allow it. The error message I get it
'The Value you entered doesnt meet the validation rule defined for the field or control' What does this mean?

Private Sub cmdSubmit_Click()
Dim varResponse As Variant
Dim sQRY As String
Dim rs As DAO.Recordset
Dim intNHSNo As Long
'**************************************
'    On Error GoTo Err
    varResponse = MsgBox("Save Changes?", vbYesNo, cApplicationName)
        If varResponse = vbNo Then
            Me.Undo
                Exit Sub
        End If
'**************************************
                sQRY = "UPDATE jez_SWM_Visits " & _
                            "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 & "', [VisitDate] = '" & Me.txtVisitDate & "', [OpenorClosed] = '" & Me.chkFinalVist & "'," & _
                            "[Weight] = '" & Me.txtWeight & "', [Height] = '" & Me.txtHeight & "', [BMI] = '" & Me.txtBMI & "', [BloodPressure] = '" & Me.txtBlood & "', [ExerciseLevel] = '" & _
                            Me.txtExercise & "', [DietLevel] = '" & Me.txtDiet & "', [SelfEsteem] = '" & Me.txtSelf & "', [WaistSize] = '" & Me.txtWaist & "', [Comments] = '" & _
                            Me.txtComments & "', [SessionType] = '" & Me.cboSessionType & "', [NHSStaffName] = '" & Me.txtStaffName & "', [Arrived] = '" & Me.cboAttendance & "', " & _
                            "[ActiveRecord] = -1, [InputBy] = '" & fOSUserName & "', [InputDate] = '" & VBA.Now & "', [InputFlag] = -1 " & _
                            "WHERE jez_SWM_Visits.VisitID = Forms!frmVisits!txtVisitID "
                DoCmd.RunSQL sQRY
    Me.lblBMIInfo.Visible = False
    Me.txtDummy.SetFocus
    Me.txtNHSNo = "" 
'All other TextBox and Combo are here taken out for this
    'Err:
'    basError.LogError VBA.Err, VBA.Error$, "Form_frmMain - cmdSubmit_Click()"
End Sub

 

[DCrake]

This will relate to one specific control on the form. When you want to clear the contents of a control you can use the Me.Control = "" syntax however if you have default values or validation rules on certain controls make sure you reset them also

David

 

[JezLisle]

So instead of Me.txtNHSNo = ""

use Me.Control = "" instead

What would I need to set the Control as?

 

[boblarson]

If your form is bound then you just need to move to a new record (which is what I suspect you need to do as you are getting validation errors when trying to clear the form). If you are going to use the SQL statement to update, make sure that your form is unbound.

 

[DCrake]

The Me.Control was aircode what I meant was that the Me.NHSNo = ""
is correct syntax

 

[gemma-the-husky]

if you are using a bound form, then you DONT WANT to clear fields - since a bound form will be showing data for the current record, and clearing this data will clear real data from your table

if it is an unbound form then I would just call an explicit initiallise sub

fiedl1 = 0
field2= ""
field3 = 0

you could iterate the forms controls, but it is easier to control by setting the fields you need ot process explicitly.

 

[JezLisle]

No my Form is bound, I dont want to clear the record, I just want to clear the data within the form. So I can create a new record.

 

[gemma-the-husky]

thats just it

if you have a bound form, clearing the data will clear the data for the current record

in code you want

runcommand accmdrecordsgotonew

OR

click the * (new record) in the navigation buttons at the bottom of the form