RunSQL Update question

[Damob9K]

Hello,

Quick question:

When you do a RunSQL Update "tablename" Set Field1='" & Me.text1 & "', Field2='" & Me.text2 & "' etc etc , do the field names need to be in the same order as they go in the table, and what happens if you are only updating say the 2nd field 4th field and the last two fields ?

This relates to a problem I am having with using the Update function, but I just want to make sure I'm not doing this all wrong from the start !

Thanks

Damob

 

[pbaldy]

To my knowledge the order of fields in the update statement doesn't matter, and you can update any fields you need to.

 

[Damob9K]

Ah OK thanks for clearing that up, this means that my problem is a bit more complicated than I thought !!

Going to try a few more things before posting the whole shebang.

Thanks again Paul.

 

[pbaldy]

No problem. If you're having trouble with the SQL, you may find this technique helpful:

http://www.baldyweb.com/ImmediateWindow.htm

 

[Damob9K]

Thanks for that, yep i'm already using the debug.print but it's not giving me any clues as to why things aren't working.

Basically the last two fields that I am trying to update, which are empty before updating, will not save, the second from last is a date field, and I am just now swapping it to the last entry to see if this is what's stopping it from working.

I will post a much bigger explanation once i've extinguished all my brain cells :banghead:

Thanks again.

 

[jdraw]

Can you post the sql?

 

[Damob9K]

OK here is the full-ish description :

StockData table contains multiple columns of which I want only a few to go into the repair table.

RepairData table has 4 fields taken from StockData (PC number,serial number,type etc) and stores extra fields for repair information.

Workshop form has a subform "WorkshopSubForm" with the source from "RepairDataQuery"

Workshop form has a button "Add a device for repair" which launches "AddPC2RepairSearchForm" which allows the user to enter a PC number or serial number, check to see if either are valid, and if so opens a form "AddPC2RepairDetailForm"

"AddPC2RepairDetailForm" displays data that is not changeable i.e. PC number,serial number,type etc. with a few unbound text input boxes "Fault Description", "BookedinDate" etc.

I then have a button called "Add Device to the Repair List" with this code:

Private Sub cmdadditem_Click()
If (Me.repairstatusIN) = "In Repair Queue" Then Me.Status.Value = "Waiting Repair"
If (Me.repairstatusIN) = "Investigating Fault" Then Me.Status.Value = "Under Repair"
If (Me.repairstatusIN) = "Being Tested" Then Me.Status.Value = "Under Repair"
If (Me.repairstatusIN) = "Being Repaired" Then Me.Status.Value = "Under Repair"
If (Me.repairstatusIN) = "Being Risd" Then Me.Status.Value = "Under Repair"

Rem Me.Location.Value = "L07"
Dim strSQL As String
DoCmd.SetWarnings False

strSQL = "INSERT INTO RepairData" & _
         
"(PCNumber,SerialNumber,DeviceType,DeviceModel,OSType,Status,Location,SWRef,User,DepartmentName,Bookedinby,BookedinDate,RepairStatus,FaultDescription)" & _
         "VALUES" & _
         "('" & Me.[PCNumber] & "', " & _
         "'" & Me.[SerialNumber] & "', " & _
         "'" & Me.[DeviceType] & "', " & _
         "'" & Me.[DeviceModel] & "', " & _
         "'" & Me.[ostype] & "', " & _
         "'" & Me.[Status] & "', " & _
         "'" & Me.[Location] & "', " & _
         "'" & Me.[swrefin] & "', " & _
         "'" & Me.[User] & "', " & _
         "'" & Me.[DepartmentName] & "', " & _
         "'" & Me.[bookedinbyIn] & "', " & _
         "'" & Me.[bookedindateIN] & "', " & _
         "'" & Me.[repairstatusIN] & "', " & _
         "'" & Me.[faultdesIN] & "');"

DoCmd.RunSQL strSQL
DoCmd.Close acForm, "AddPC2RepairDetailForm"
DoCmd.SetWarnings True
DoCmd.OpenForm "WorkShopForm"

End Sub

This adds all the data from the form including the data entry from the unbound text boxes and creates a new record in the RepairData table which has an

autonumber "ID" primary field.

So far so good, this all works perfectly, minus a few tweaks I want to add later.

Going back to "WorkshopForm" this will now diplay the data which was added by the above.
There is another button "Update Repair Details" which opens "UpdateRepairForm" based on the records ID.

"UpdateRepairForm" gets it's data from "RepairDataQuery" which in turn queries "RepairData" table with some filters, again so far so good.

"UpdateRepairForm" allows for some of the original fields to be changed (others are locked like the job ref and bookedindate) it has a "RepairStatus" combo box and two new unbound text input boxes :"RepairDate" and "RepairDescription" RepairDate allows for manual input or there is a button "Today" that puts today's date in, and RepairDescription in manual entry.

I then have another button "Update Repair" that amends the current record - this is where things go wrong.

When I click the button, it immediately comes up with this message box : Write Conflict "This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. ..."

There are 3 buttons "Save Record" "copy to clipboard" "Drop Changes" , naturally I click "save record"

Now if I have only changed one of the fields that already existed, and after I get the above message, the changes have been saved to the table, but if I enter in the date and repair description into the form, they never save to the RepairData table ?

Code as follows:

Private Sub cmdupdaterepair_Click()
If (Me.RepairStatus) = "In Repair Queue" Then Me.Status.Value = "Waiting Repair"
If (Me.RepairStatus) = "Investigating Fault" Then Me.Status.Value = "Under Repair"
If (Me.RepairStatus) = "Being Tested" Then Me.Status.Value = "Under Repair"
If (Me.RepairStatus) = "Being Repaired" Then Me.Status.Value = "Under Repair"
If (Me.RepairStatus) = "Being Risd" Then Me.Status.Value = "Under Repair"
If (Me.RepairStatus) = "Repaired" Then Me.Status.Value = "Stock"

Rem Me.Location.Value = "L07"
Dim strSQL As String
DoCmd.SetWarnings False

strSQL = "UPDATE RepairData " & _
         "SET OSType='" & Me.ostype & "', Location='" & Me.Location & "', User='" & Me.User & "', DepartmentName='" & Me.DepartmentName & _
         "', Status='" & Me.Status & "', RepairStatus='" & Me.RepairStatus & "', RepairDescription='" & Me.RepairDescription & "', RepairDate='" & Me.RepairDate & "' " & _
         "WHERE ID=" & Me.ID & ";"
Debug.Print strSQL
DoCmd.RunSQL strSQL
DoCmd.Close acForm, "UpdateRepairForm"
DoCmd.SetWarnings True
DoCmd.OpenForm "WorkShopForm"
End Sub

Sorry, I started to type this all out in notepad and the formatting has gone a bit iffy copying it into here !

Please let me know if other info would be helpful.

Many thanks

Damob

 

[pbaldy]

It sounds like maybe the form is bound to the same table/record the code is updating? Typically you would do one or the other. Try adding

If Me.Dirty Then Me.Dirty = False

to force a save of the record prior to running your update.

 

[Damob9K]

OMG I'm a Divide by zero :banghead:

I have no clue as to why I decided to use the SQL UPDATE statement, I guess because I had spent a day figuring out the SQL INSERT INTO statement I thought that's the way to do the next bit ! - wrong

When you said "Typically you would do one or the other" the penny dropped !

This is what happens when you don't look at Access for 6 months - stupidity sets in

Got it working correctly (doing it the correct way) in 2 minutes !!

AH well I learnt a few things on the way, SQL Insert into and Update statements are a PITA getting the syntax right, and as Einstein said human stupidity is infinite

Thanks for your pointers Paul.

Damob - stepping away from the computer !

 

[pbaldy]

Happy to help!