Trying to run update for table screwing it up (1 Viewer)

[Jimg1976]

I am trying to update a table

It is tbl_incident

from data on a form called frm_edit

I am using the following code, and instead of changing only the information that corresponds to the Date_of_Incident , it is changing all entries in tbl_incident that have the badge number. I am stumped as this was working perfectly until I had an issue and the database got corrupted. I had to rebuild it and now it no longer works.

"Dim strSQL As String
 
strSQL = "UPDATE tbl_incident SET tbl_incident![Description] = Forms!frm_edit![Description], tbl_incident![Code] = Forms!frm_edit![Code] ,tbl_incident![Points] = Forms!frm_edit![points]" _
& "WHERE tbl_incident![Badge] = Forms!frm_edit![Badge] AND tbl_incident![Date_of_Incident] = Forms!frm_edit![Date_Of_incident]"
 
If Me.Dirty Then Me.Dirty = False
DoCmd.RunSQL (strSQL)"

If I haven't given you enough info please ask. I am a little flustered right now.:banghead::banghead:

 

[Gasman]

What does Debug.Print strSQL show?

 

[arnelgp]

With Currentdb.CreateQuerydef("", _
"UPDATE tbl_incident SET tbl_incident.[Description] = p0, " & _
"tbl_incident.

 = p1, " & _
  "tbl_incident.[Points] = p2 " & _
"WHERE tbl_incident.[Badge] = p3  AND " & _
   "tbl_incident.[Date_of_Incident] = p4;")
  .Parameters(0) = Forms!frm_edit![Description]
  .Parameters(1) = Forms!frm_edit![Code]
  .Parameters(2) = Forms!frm_edit![points]
  .Parameters(3) = Forms!frm_edit![Badge]
  .Parameters(4) = Forms!frm_edit![Date_Of_incident]
  .Execute
End With

 

[Jimg1976]

UPDATE tbl_incident SET tbl_incident![Description] = Forms!frm_edit![Description], tbl_incident![Code] = Forms!frm_edit![Code] ,tbl_incident![Points] = Forms!frm_edit![points]WHERE tbl_incident![Badge] = Forms!frm_edit![Badge] AND tbl_incident![Date_of_Incident] = Forms!frm_edit![Date_Of_incident]

This is the output from the immediate window when I run the code

 

[arnelgp]

"UPDATE tbl_incident SET tbl_incident![Description] = '" & _
Forms!frm_edit![Description] & "', tbl_incident!

 = '" & _
  Forms!frm_edit![Code] & "',tbl_incident![Points] = '" & _
  Forms!frm_edit![points] & _
"WHERE tbl_incident![Badge] = '" & Forms!frm_edit![Badge] & "' AND " & _
   "tbl_incident![Date_of_Incident] = " & Format(Forms!frm_edit![Date_Of_incident],"\#mm\/dd\/yyyy\#")

 

[Gasman]

Well I spotted that there was no space before the WHERE, but was not sure if you had to concatenate the form controls with the sql or could use as is.

Arnel shows you must do this, though I do like the parameter approach which is easier to read.?

UPDATE tbl_incident SET tbl_incident![Description] = Forms!frm_edit![Description], tbl_incident![Code] = Forms!frm_edit![Code] ,tbl_incident![Points] = Forms!frm_edit![points]WHERE tbl_incident![Badge] = Forms!frm_edit![Badge] AND tbl_incident![Date_of_Incident] = Forms!frm_edit![Date_Of_incident]

This is the output from the immediate window when I run the code

 

[Jimg1976]

Thank you Arnel and Gas. I will try this and see if it helps and let you know.

 

[Pat Hartman]

When you save the SQL as a querydef, you would use form field references. When you build the SQL on the fly as you are, you would NOT use that method. I was going to fix the SQL for you but part of it is missing. I'm not sure what you are doing but you pasted it twice with a similar error both times. You seem to have the code tag embedded in the string. Can you please try one more time to paste the string. This time don't use the code tag.

Also, why are you not using a bound form? You shouldn't need to run an update query at all.

 

[Jimg1976]

I am going to upload the database in a zip file. I am a neophyte when it comes to Access and am sure it is not done properly, but the form I have an issue with is frm_edit. When hitting the Save button, sometimes it works fine, other times it edits all incidents previous to the most current. Any help would be appreciated.

 

[Jimg1976]

With Currentdb.CreateQuerydef("", _
"UPDATE tbl_incident SET tbl_incident.[Description] = p0, " & _
"tbl_incident.

 = p1, " & _
  "tbl_incident.[Points] = p2 " & _
"WHERE tbl_incident.[Badge] = p3  AND " & _
   "tbl_incident.[Date_of_Incident] = p4;")
  .Parameters(0) = Forms!frm_edit![Description]
  .Parameters(1) = Forms!frm_edit![Code]
  .Parameters(2) = Forms!frm_edit![points]
  .Parameters(3) = Forms!frm_edit![Badge]
  .Parameters(4) = Forms!frm_edit![Date_Of_incident]
  .Execute
End With[/QUOTE]

This worked perfectly. Thank you so much!

 

[arnelgp]

you're welcome!

 

[Guus2005]

With Currentdb.CreateQuerydef("", "UPDATE tbl_incident SET tbl_incident.[Description] = p0, tbl_incident.

 = p1, tbl_incident.[Points] = p2 WHERE tbl_incident.[Badge] = p3  AND tbl_incident.Date_of_Incident = p4;")
      .Parameters(0) = Forms!frm_edit![Description]
      .Parameters(1) = Forms!frm_edit![Code]
      .Parameters(2) = Forms!frm_edit![points]
      .Parameters(3) = Forms!frm_edit![Badge]
      .Parameters(4) = Forms!frm_edit![Date_Of_incident]
      .Execute
End With[/QUOTE]
I like what you did here. On the fly adding and using query parameters and filling them.

Thanks!

 

[arnelgp]

maybe you can modify my function fnAnySQL(), you may search it on this forum.

 

[Jimg1976]

This worked perfectly. Thank you so much!

I am trying to add a new field to this statement and it isn't working. I am sure it has to do with me not having much knowledge. I was hoping for some help.

The new field is called shift. I need it compared and then updated with the rest of the fields. I am having no luck.

Here is what I tried:

With CurrentDb.CreateQueryDef("", _
"UPDATE tbl_incident SET tbl_incident.[Description] = p0, " & _
"tbl_incident.

 = p1, " & _
"tbl_incident.[Points] = p2 " & _
"WHERE tbl_incident.[Badge] = p3 AND " & _
"tbl_incident.[Date_of_Incident] = p4" & _
"tbl_incident.[Shift] = p5;")
.Parameters(0) = Forms!frm_edit![Description]
.Parameters(1) = Forms!frm_edit![Code]
.Parameters(2) = Forms!frm_edit![Points]
.Parameters(3) = Forms!frm_edit![Badge]
.Parameters(4) = Forms!frm_edit![Date_of_Incident]
.Parameters(5) = Forms!frm_edit![Shift]
.Execute
End With

What did I miss?

 

[Gasman]

Firstly you are not updating shift?, merely comparing against it?

I've only removed the [] from Code as it was screwing the code tags and display.

Try

With CurrentDb.CreateQueryDef("", _
    "UPDATE tbl_incident SET tbl_incident.[Description] = p0, " & _
    "tbl_incident.[Code = p1, " & _
    "tbl_incident.[Points] = p2 " & _
    "WHERE tbl_incident.[Badge] = p3 AND " & _
    "tbl_incident.[Date_of_Incident] = p4 AND " & _
    "tbl_incident.[Shift] = p5;")
    .Parameters(0) = Forms!frm_edit![Description]
    .Parameters(1) = Forms!frm_edit!Code
    .Parameters(2) = Forms!frm_edit![Points]
    .Parameters(3) = Forms!frm_edit![Badge]
    .Parameters(4) = Forms!frm_edit![Date_of_Incident]
    .Parameters(5) = Forms!frm_edit![Shift]
    .Execute
End With

HTH

 

[Jimg1976]

Hi Gasman,

I am comparing it to make sure it is the appropriate entry, and updating it if it gets changed on the form. Thanks for your assistance.

 

[Gasman]

We might be talking at cross purposes here, but all your updating is here

 "UPDATE tbl_incident SET tbl_incident.[Description] = p0, " & _
    "tbl_incident.Code = p1, " & _
    "tbl_incident.[Points] = p2 " & _

and your comparison is here

    "WHERE tbl_incident.[Badge] = p3 AND " & _
    "tbl_incident.[Date_of_Incident] = p4 AND " & _
    "tbl_incident.[Shift] = p5;")

 

[Jimg1976]

Gasman,

Thank you for pointing out the obvious. Sorry, I did not even see that. WOW. :banghead:

 

[Jimg1976]

Ok Here is another Stupid question. I tried updating the statement and it says I am missing an operator. I can't figure it out. Here is what I did.


With CurrentDb.CreateQueryDef("", _
"UPDATE tbl_incident SET tbl_incident.[Description] = p0, " & _
"tbl_incident.

 = p1, " & _
"tbl_incident.[Points] = p2 " & _
"tbl_incident.[Badge] = p3 " & _
"tbl_incident.[Date_of_Incident] = p4 " & _
"tbl_incident.[Shift] = p5 " & _
"WHERE tbl_incident.[Badge] = p3 AND " & _
"tbl_incident.[Date_of_Incident] = p4 " & _
"tbl_incident.[Shift] = p5;")
.Parameters(0) = Forms!frm_edit![Description]
.Parameters(1) = Forms!frm_edit![Code]
.Parameters(2) = Forms!frm_edit![Points]
.Parameters(3) = Forms!frm_edit![Badge]
.Parameters(4) = Forms!frm_edit![Date_of_Incident]
.Parameters(5) = Forms!frm_edit![Shift]
.Execute

 

[Gasman]

"AND" after P4 ?

I thought at first I might have missed it from my post, but I did not. (phew)