Update Query

J

JezLisle

Registered User.
Local time
Today, 17:16
Joined
Jul 27, 2007
Messages
67
I have an update query that I run to submit the records from my form into my SQL Server table.

This below is my code

Code: 
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 & "', [Interventions] = '" & Me.cboInterventions & "', [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

My problem is that, when I retrieve a record and amend it. by clicking on my submit button and running this update query it doesnt update the records in my table. why would this be?
 
I would suggest you try updating one field at a time, adding the next one back in until it breaks again. I'm guessing it may be a 'quotes' issue - ??
 
Try making your code readable...
Better, still not perfect probably....
Code: 
sQRY = ""
sQRY = sQRY & "UPDATE jez_SWM_Visits " 
sQRY = sQRY & "SET [NHSNo] = '" & Me.txtNHSNo & "'" 
sQRY = sQRY & ", [Surname] = '" & Me.txtSurname & "'"
sQRY = sQRY & ", [Forename] = '" & Me.txtForename & "'"
sQRY = sQRY & ", [Gender] = '" & Me.cboGender & "'"
sQRY = sQRY & ", [Address1] = '" & Me.txtAddress1 & "'"
sQRY = sQRY & ", [Address2] = '" & Me.txtAddress2 & "'"
sQRY = sQRY & ", [Address3] = '" & Me.txtAddress3 & "'"
sQRY = sQRY & ", [Postcode] = '" & Me.txtPostcode & "'"
sQRY = sQRY & ", [Telephone] = '" & Me.txtTelephone & "'"
sQRY = sQRY & ", [DateOfBirth] = '" & Me.txtDOB & "'"
sQRY = sQRY & ", [ReferralReasonDescription] = '" & Me.cboReferralRsn & "'"
sQRY = sQRY & ", [SourceDescription] = '" & Me.cboReferralSource & "'"
sQRY = sQRY & ", [DateOfReferral] = '" & Me.txtReferralDate & "'"
sQRY = sQRY & ", [VisitDate] = '" & Me.txtVisitDate & "'"
sQRY = sQRY & ", [OpenorClosed] = '" & Me.chkFinalVist & "'"
sQRY = sQRY & ", [Weight] = '" & Me.txtWeight & "'"
sQRY = sQRY & ", [Height] = '" & Me.txtHeight & "'"
sQRY = sQRY & ", [BMI] = '" & Me.txtBMI & "'"
sQRY = sQRY & ", [BloodPressure] = '" & Me.txtBlood & "'"
sQRY = sQRY & ", [ExerciseLevel] = '" & Me.txtExercise & "'"
sQRY = sQRY & ", [DietLevel] = '" & Me.txtDiet & "'"
sQRY = sQRY & ", [SelfEsteem] = '" & Me.txtSelf & "'"
sQRY = sQRY & ", [WaistSize] = '" & Me.txtWaist & "'"
sQRY = sQRY & ", [Comments] = '" & Me.txtComments & "'"
sQRY = sQRY & ", [Interventions] = '" & Me.cboInterventions & "'"
sQRY = sQRY & ", [SessionType] = '" & Me.cboSessionType & "'"
sQRY = sQRY & ", [NHSStaffName] = '" & Me.txtStaffName & "'"
sQRY = sQRY & ", [Arrived] = '" & Me.cboAttendance & "'"
sQRY = sQRY & ", [ActiveRecord] = -1"
sQRY = sQRY & ", [InputBy] = '" & fOSUserName & "'"
sQRY = sQRY & ", [InputDate] = '" & VBA.Now & "'"
sQRY = sQRY & ", [InputFlag] = -1"
sQRY = sQRY & "WHERE jez_SWM_Visits.VisitID = [B]Forms!frmVisits!txtVisitID [/B]"
DoCmd.RunSQL sQRY

I dont think you can use a form reference inside a sql statement to be executed like this. Replace it instead.
 
namliam said:
Try making your code readable...
Better, still not perfect probably....
Code: 
sQRY = ""
sQRY = sQRY & "UPDATE jez_SWM_Visits " 
sQRY = sQRY & "SET [NHSNo] = '" & Me.txtNHSNo & "'" 
sQRY = sQRY & ", [Surname] = '" & Me.txtSurname & "'"
sQRY = sQRY & ", [Forename] = '" & Me.txtForename & "'"
sQRY = sQRY & ", [Gender] = '" & Me.cboGender & "'"
sQRY = sQRY & ", [Address1] = '" & Me.txtAddress1 & "'"
sQRY = sQRY & ", [Address2] = '" & Me.txtAddress2 & "'"
sQRY = sQRY & ", [Address3] = '" & Me.txtAddress3 & "'"
sQRY = sQRY & ", [Postcode] = '" & Me.txtPostcode & "'"
sQRY = sQRY & ", [Telephone] = '" & Me.txtTelephone & "'"
sQRY = sQRY & ", [DateOfBirth] = '" & Me.txtDOB & "'"
sQRY = sQRY & ", [ReferralReasonDescription] = '" & Me.cboReferralRsn & "'"
sQRY = sQRY & ", [SourceDescription] = '" & Me.cboReferralSource & "'"
sQRY = sQRY & ", [DateOfReferral] = '" & Me.txtReferralDate & "'"
sQRY = sQRY & ", [VisitDate] = '" & Me.txtVisitDate & "'"
sQRY = sQRY & ", [OpenorClosed] = '" & Me.chkFinalVist & "'"
sQRY = sQRY & ", [Weight] = '" & Me.txtWeight & "'"
sQRY = sQRY & ", [Height] = '" & Me.txtHeight & "'"
sQRY = sQRY & ", [BMI] = '" & Me.txtBMI & "'"
sQRY = sQRY & ", [BloodPressure] = '" & Me.txtBlood & "'"
sQRY = sQRY & ", [ExerciseLevel] = '" & Me.txtExercise & "'"
sQRY = sQRY & ", [DietLevel] = '" & Me.txtDiet & "'"
sQRY = sQRY & ", [SelfEsteem] = '" & Me.txtSelf & "'"
sQRY = sQRY & ", [WaistSize] = '" & Me.txtWaist & "'"
sQRY = sQRY & ", [Comments] = '" & Me.txtComments & "'"
sQRY = sQRY & ", [Interventions] = '" & Me.cboInterventions & "'"
sQRY = sQRY & ", [SessionType] = '" & Me.cboSessionType & "'"
sQRY = sQRY & ", [NHSStaffName] = '" & Me.txtStaffName & "'"
sQRY = sQRY & ", [Arrived] = '" & Me.cboAttendance & "'"
sQRY = sQRY & ", [ActiveRecord] = -1"
sQRY = sQRY & ", [InputBy] = '" & fOSUserName & "'"
sQRY = sQRY & ", [InputDate] = '" & VBA.Now & "'"
sQRY = sQRY & ", [InputFlag] = -1"
sQRY = sQRY & "WHERE jez_SWM_Visits.VisitID = [B]Forms!frmVisits!txtVisitID [/B]"
DoCmd.RunSQL sQRY

I dont think you can use a form reference inside a sql statement to be executed like this. Replace it instead.
Click to expand...
I agree with Namliam

try changing the line to

sQRY = sQRY & "WHERE jez_SWM_Visits.VisitID = '" & Forms!frmVisits!txtVisitID & "'"
 
It beeing an ID, I guess it to be a number and would exclude the ' around it...
 
Quite agree :)

In that case it is simpler

sQRY = sQRY & "WHERE jez_SWM_Visits.VisitID = " & Forms!frmVisits!txtVisitID
 
Excellent, thanks for this :)

I have tried this and it works great, only problem is when I have a field that updates after input and this doesnt feed through for some reason
 
Hi,

I'm not familiar with SQL servers and I may be out of my league, BUT if your form has a Record Source then you might try...

Code: 
     If Me.Dirty Then
          DoCmd.RunCommand acCmdSaveRecord
     End If
 
     'Your SQL statement goes here.

This will make sure that all new data entries / edited entries are updated before running your SQL.

Regards,


Richard
 
Probably just needs to save the record before attempting to execute the sql statement. ?
 
I have tried this and not sure I have done right thing as I get an error message

Code: 
Private Sub cmdSubmit_Click()
Dim varResponse As Variant
Dim sQRY As String
Dim intNHSNo As String

    varResponse = MsgBox("Save Changes?", vbYesNo, cApplicationName)
        If varResponse = vbNo Then
            Me.Undo
                Exit Sub
        End If
        Call UnLockAll
        If Me.Dirty Then
          DoCmd.RunCommand acCmdSaveRecord
        End If
                sQRY = ""
                sQRY = sQRY & "UPDATE jez_SWM_Visits "
                sQRY = sQRY & "SET [NHSNo] = '" & Me.txtNHSNo & "'"
                sQRY = sQRY & ", [Surname] = '" & Me.txtSurname & "'"
                sQRY = sQRY & ", [Forename] = '" & Me.txtForename & "'"
                sQRY = sQRY & ", [Gender] = '" & Me.cboGender & "'"
                sQRY = sQRY & ", [Address1] = '" & Me.txtAddress1 & "'"
                sQRY = sQRY & ", [Address2] = '" & Me.txtAddress2 & "'"
                sQRY = sQRY & ", [Address3] = '" & Me.txtAddress3 & "'"
                sQRY = sQRY & ", [Postcode] = '" & Me.txtPostcode & "'"
                sQRY = sQRY & ", [Telephone] = '" & Me.txtTelephone & "'"
                sQRY = sQRY & ", [DateOfBirth] = '" & Me.txtDOB & "'"
                sQRY = sQRY & ", [ReferralReasonDescription] = '" & Me.cboReferralRsn & "'"
                sQRY = sQRY & ", [SourceDescription] = '" & Me.cboReferralSource & "'"
                sQRY = sQRY & ", [DateOfReferral] = '" & Me.txtReferralDate & "'"
                sQRY = sQRY & ", [VisitDate] = '" & Me.txtVisitDate & "'"
                sQRY = sQRY & ", [OpenorClosed] = '" & Me.chkFinalVist & "'"
                sQRY = sQRY & ", [Weight] = '" & Me.txtWeight & "'"
                sQRY = sQRY & ", [Height] = '" & Me.txtHeight & "'"
                sQRY = sQRY & ", [BMI] = '" & Me.txtBMI & "'"
                sQRY = sQRY & ", [BloodPressure] = '" & Me.txtBlood & "'"
                sQRY = sQRY & ", [ExerciseLevel] = '" & Me.txtExercise & "'"
                sQRY = sQRY & ", [DietLevel] = '" & Me.txtDiet & "'"
                sQRY = sQRY & ", [SelfEsteem] = '" & Me.txtSelf & "'"
                sQRY = sQRY & ", [WaistSize] = '" & Me.txtWaist & "'"
                sQRY = sQRY & ", [Comments] = '" & Me.txtComments & "'"
                sQRY = sQRY & ", [Interventions] = '" & Me.cboInterventions & "'"
                sQRY = sQRY & ", [SessionType] = '" & Me.cboSessionType & "'"
                sQRY = sQRY & ", [NHSStaffName] = '" & Me.txtStaffName & "'"
                sQRY = sQRY & ", [Arrived] = '" & Me.cboAttendance & "'"
                sQRY = sQRY & ", [ActiveRecord] = 1 "
                sQRY = sQRY & ", [InputBy] = '" & fOSUserName & "'"
                sQRY = sQRY & ", [InputDate] = '" & VBA.Now & "'"
                sQRY = sQRY & ", [InputFlag] = 1 "
                sQRY = sQRY & "WHERE jez_SWM_Visits.[VisitID] = " & Forms!frmVisits!txtVisitID
                DoCmd.RunSQL sQRY
End Sub

The error message I get is

Run-time '2455'
You entered an expression that has an invalid reference to the property Dirty

What does this mean?
 
Is the form bound to a table or query as the recordsource or are you taking the values from the text boxes and writing them to a table via the sql statement?
 
The form is unbound and I am taking the values from txtBox & cboBox and writing them to a table via the SQL Statement
 
Then I would think the whole 'dirty' approach is moot.

I'll go back to my original suggestion and recommend trying to build the sql statement one field at a time. I'm thinking you may be trying to put text qualifiers (quotes) around numerical data and/or visa versa?

And I'm not sure but you may need to put pound signs (date data type qualifiers) around date data...?
 
Hi JezLisle

I decided to design your project and come up with an sql statement that would work. I created a table and took a sampling of the fields you refer to in your code. I then created tables fill the two combo boxes. You will note that I used the same names for the fields. I then created an unbound form and created controls with same names as your controls. I then created an Update query that would perform the update you require.

In the Update To area of the Query buider I placed sample data to test the query. This is the sql statement that I created.

Code: 
[SIZE=3][FONT=Times New Roman]UPDATE jez_SWM_Visits SET jez_SWM_Visits.NHSNo = [COLOR=red]1475[/COLOR], jez_SWM_Visits.Surname = "[COLOR=red]Perez[/COLOR]", jez_SWM_Visits.Forename = "[COLOR=red]Richard[/COLOR]", jez_SWM_Visits.Gender = "[COLOR=red]Male[/COLOR]", jez_SWM_Visits.PostCode = "[COLOR=red]79915[/COLOR]", jez_SWM_Visits.DateOfBirth = #[COLOR=red]9/30/1944[/COLOR]#, jez_SWM_Visits.ReferralReasonDescription = [COLOR=red]1[/COLOR], jez_SWM_Visits.OpenorClosed = [COLOR=red]True[/COLOR], jez_SWM_Visits.Weight = [COLOR=red]140[/COLOR], jez_SWM_Visits.ActiveRecord = -1, jez_SWM_Visits.InputBy = [COLOR=green]fOSUserName()[/COLOR], jez_SWM_Visits.InputDate = [COLOR=green]Now()[/COLOR], jez_SWM_Visits.InputFlag = [COLOR=blue]-1[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]WHERE ((([jez_SWM_Visits].[VisitID])=[COLOR=#ff6600][Forms]![frmVisits]![txtVisitID][/COLOR]));[/SIZE][/FONT]

I then opened the table jez_SWM_Visits and inputted a 1 in the txtVisitID control and closed it.

I loaded frmVisits and inputted the value of 1 in the control txtVisitID to test the query. I then ran the above update query, with the form open, and everything worked fine. Record 1 received the sample data as shown above.

Red represents inputted data from the forms controls.
Green represents data computed by functions.
Orange represents data from txtVisitID on Form frmVisits
Blue represent data that you have indicated is hard coded into your sql statement.

I then opened the form that has the controls that are referred to in the above query. I copied the sql statement and pasted it in the cmdSubmit_Click event and began editing it. This is code/sql statement I developed.

Code: 
[FONT=Times New Roman][SIZE=3]Private Sub [COLOR=black]cmdSubmit_Click [/COLOR]()[/SIZE][/FONT]
 
[SIZE=3][FONT=Times New Roman]Dim sQry As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = "UPDATE jez_SWM_Visits SET "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "NHSNo = " & Me.txtNHSNo & ", "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "[COLOR=red]Surname[/COLOR] = [COLOR=red]'[/COLOR]" & Me.txtSurName & "[COLOR=red]'[/COLOR], "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "[COLOR=red]Forename[/COLOR] = [COLOR=red]'[/COLOR]" & Me.txtForename & "[COLOR=red]'[/COLOR], "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "[COLOR=red]Gender[/COLOR] = [COLOR=red]'[/COLOR]" & Me.cboGender & "[COLOR=red]'[/COLOR], "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "[COLOR=red]PostCode[/COLOR] = " & Me.txtPostCode & ", "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "[COLOR=blue]DateOfBirth[/COLOR] = [COLOR=blue]#[/COLOR]" & Me.txtDOB & "[COLOR=blue]#[/COLOR], "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "ReferralReasonDescription = " & Me.cboReferralRsn & ", "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "[COLOR=green]OpenorClosed[/COLOR] = " & Me.chkFinalVist & ", "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "Weight = " & Me.txtWeight & ", "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "ActiveRecord = -1, " [I]'This is the value I changed[/I] [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "[COLOR=red]InputBy[/COLOR] = fOSUserName(), "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "[COLOR=blue]InputDate[/COLOR] = Now(), "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "InputFlag = -1 "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]sQry = sQry & "WHERE (((jez_SWM_Visits.VisitID)=[Forms]![frmVisits]![txtVisitID]));"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.RunSQL sQry[/FONT][/SIZE]
 
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

I Loaded the form, clicked on the cmdSubmit button and the query ran perfectly.

Black indicates numeric fields
Red indicates text fields
Blue indicates date/time fields
Green indicates Yes/No fields

Findings:

  • The numeric values have no qualifiers
  • The string values use the single quote ' qualifiers.
  • The date values use the number sign # qualifiers
  • The function references were left hard coded.
  • The Form reference was left inside the sql statement. You can place it outside if you like.
Special testing.

Because I don’t know the types of fields that are being used for the two combo boxes, text or numeric, I decided to test the two combo controls by setting up the table fields to two different types, a text field and a numeric field. Note that the Gender fieldis set up as a text field and the ReferralReasonDescription field is set up as a numeric field. The Gender field with display a text value and, the table will receive one of two value: Male and Female. The ReferralReasonDescription control will show the Referral Reason Description but the actual value in the table will be the numeric PK of the table. I did this so I could conclude whether or not to place the single quote ' qualifiers.

When programming the qualifiers I had the jez_SWM_Visits table open so I could see the field type of each field and decide whether or not to surround the control with qualifiers and which kind of qualifier to use.

If you would like, try copying the above code into your procedure. You can then edit it, adding the other fields and control references, change the qualifiers to suit your underlying table def. I hope I have helped you with your programming requirement.

Oh one last thing. I intentionally removed the single quotes from the statement and got the Access “Enter Parameter Value” dialog window. I then intentinally removed the # qualifiers from txtDOB, ran the code,and I got a weird date for DateOfBirth field.


Conclusion: sql statements must be perfectly stated including using qualifiers where needed. Knowing the underlying field types so your statement can be perfectly programmed. Using the sql statement from a query and placing sample data helps to build a query in a procedure.

I really do hope this helps you in understanding the building of queries and that it will get your DB running smoothly.

Richard

Correction: I changed ActiveRecord to a Yes/No field after realizing that your original statement showed a -1 value instead of 1 as shown in my sql statement and change the value in the sql statement to -1 as per your original sql. Sorry if this inconvenienced you.
 
Last edited:
You must log in or register to reply here.

Similar threads

J
Clear form after update
Replies
7
Views
6,833
gemma-the-husky
gemma-the-husky
J
Drop Changes or Copy to clipboard
Replies
4
Views
2,001
JezLisle
J
J
RunTime Error 2107
Replies
2
Views
2,560
ByteMyzer
B

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom