If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Refresh or Clear Form

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #2 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,001
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #4 (permalink)  
Old
L33t Helpa Munky
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #6 (permalink)  
Old
L33t Helpa Munky
 
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
L33t Helpa Munky
 
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
Reply With Quote
  #9 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,064
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #11 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,064
you can use either ' or " to delimit strings, but you don't delimit numbers
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On