Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Refresh or Clear Form

    I have an input form that once all TextBox are input has a Save button which runs an UPDATE to the record in the DB (Access FE, SQLServer BE)

    How can I get it to clear the form once the UPDATE has run, I had previously been told to use

    Docmd.gotorecord,,acnewrec

    but unsure on this as I dont understand what it does

    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.txtDummy.SetFocus
    End Sub
    How can I do this?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,092
    That command only works on bound forms.
    You may have to just set each textbox' value to ""
    You can easily write a loop to iterate over the textbox objects and do this.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have tried the me.TextBox.Value = "" and I get an error on some of the TextBox's saying
    "You tried to assign the NULL value to a variable that is not a Variant data type"
    What would this appear for, as all I was trying to do is clear any values in TextBox's or Combo's on the form?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Try Me.Textbox = Null or Me.Textbox = ""
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have tried both of those sugestions and I still have a problem, the Thread4 works ok for all TextBox's that are bound but I have a TextBox which is unbound as I input that via a InputForm. How can I clear this so that next time its a blank field for a new input?

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Make sure you check the Name property of those unbound boxes. The code should be the same: Me.FormControlName = "".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have made sure all the code match's the TextBox or Combo's I tried it with just using the unbound field and entering just that and nothing else.

    I now get another error
    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 but the problem is the is when I click on the AddNew it falls over and debugs on line

    Code:
    Me.RecordSource = sQRY
    I 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 = ""
    End Sub

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think those Chr$(10)'s might be a problem tbh.

    If you are trying to put quotes into that SQL statement, you should try this:

    Code:
    sQRY = "SELECT jez_SWM_InputDetails.* FROM jez_SWM_InputDetails WHERE " & _
    "jez_SWM_InputDetails.NHSNo = """ & varInput & """"
    Also, that seems to be a different problem to that of just blanking out the controls ready for a new record. If this is a bound form, have you tried just using a goto new record command?

    Docmd.GotoRecord,,,, acNewRec (not sure on the number of commas).
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,433
    Quote Originally Posted by StarTrekker
    I think those Chr$(10)'s might be a problem tbh.

    If you are trying to put quotes into that SQL statement, you should try this:

    Code:
    sQRY = "SELECT jez_SWM_InputDetails.* FROM jez_SWM_InputDetails WHERE " & _
    "jez_SWM_InputDetails.NHSNo = """ & varInput & """"
    Also, that seems to be a different problem to that of just blanking out the controls ready for a new record. If this is a bound form, have you tried just using a goto new record command?

    Docmd.GotoRecord,,,, acNewRec (not sure on the number of commas).
    Ive always preferred the chr$(34) for putting quotes in VBA strings, for me its just easier on the eye, and less chance of me or the IDE screwing things up. The only time its come to grief is when someone decide to use a " int he data stream, which was kludged by replacing the " with 2 ', which to most users was indistinguishable

    Code:
    sQRY = "SELECT jez_SWM_InputDetails.* FROM jez_SWM_InputDetails WHERE " & _
    "jez_SWM_InputDetails.NHSNo = " & chr$(34) & varInput & chr$(34)
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have used Chr$(10) as I need the varInput in single quotes as the field is numeric in the DB
    does that make a difference?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,433
    you can use either ' or " to delimit strings, but you don't delimit numbers
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •