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

[Jimg1976]

Thanks for the reply Gasman,

Unfortunately no. I it has to do with this line in the Where statement because that is where the debug shows an error. However, I can not figure out what it is.

"tbl_incident.[Shift] = p5;")

 

[Gasman]

Yes, beacuse you do not have an AND after P4?

Look at P3

Thanks for the reply Gasman,

Unfortunately no. I it has to do with this line in the Where statement because that is where the debug shows an error. However, I can not figure out what it is.

"tbl_incident.[Shift] = p5;")

 

[Jimg1976]

Hi GasMan,

Sorry I wasn't clear. I did add that AND however I am still getting the same error.:banghead:

 

[Gasman]

Post your code please, within tags

 

[Jimg1976]

Private Sub Save_Click()
With CurrentDb.CreateQueryDef("", _
"UPDATE tbl_incident SET tbl_incident.[Description] = p0, " & _
"tbl_incident."[Cde]" = 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 AND  " & _
"tbl_incident.[Shift] = p5;")
.Parameters(0) = Forms!frm_edit![Description]
.Parameters(1) = Forms!frm_edit![Cde]
.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

I altered the word code in an attempt to make the code tags work properly

 

[Gasman]

Perhaps, but not correctly.

The error line is misleading I believe.?
You now have mispelled

 and have double quotes around it in the Update statement.?
Do you see these around the other fields? No :(, so remove them or go back to what I posted preciously, and work from hat.?

[QUOTE="Jimg1976, post: 1654808, member: 148139"][CODE]
Private Sub Save_Click()
With CurrentDb.CreateQueryDef("", _
"UPDATE tbl_incident SET tbl_incident.[Description] = p0, " & _
"tbl_incident."[Cde]" = 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 AND  " & _
"tbl_incident.[Shift] = p5;")
.Parameters(0) = Forms!frm_edit![Description]
.Parameters(1) = Forms!frm_edit![Cde]
.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

I altered the word code in an attempt to make the code tags work properly[/QUOTE]

 

[Jimg1976]

Gasman,

again thanks. Those Quotes and the changes to the word code were made to post it. Otherwise the Code tags do not work since one of my variables uses the word code. Thank you for all your input. I truly appreciate it.

 

[Jimg1976]

As far as going back to what works, Simply eliminating the last line of the code, the one I posted makes the code work.

 

[Jimg1976]

OK Gasman, thank you so much for putting up with me. I think I got it working. Instead of the comma in the update statement, I used the word AND and it seems to have worked.

 

[Gasman]

Gasman,

again thanks. Those Quotes and the changes to the word code were made to post it. Otherwise the Code tags do not work since one of my variables uses the word code. Thank you for all your input. I truly appreciate it.

Ah yes, I had that problem as well. Not sure what I did to overcomes it, but it shows correctly.

You only need [] if the field/control name has spaces in it. Code does not.

 

[Gasman]

OK Gasman, thank you so much for putting up with me. I think I got it working. Instead of the comma in the update statement, I used the word AND and it seems to have worked.

Can you please post the working code, within tags and no [] on code.
You did appear to be missing the , after each parameter in the Update statement, now that you mention it, except for the first parameter.

 

[Jimg1976]

Private Sub Save_Click()
With CurrentDb.CreateQueryDef("", _
"UPDATE tbl_incident SET tbl_incident.[Description] = p0 AND " & _
"tbl_incident.Code = p1 AND " & _
"tbl_incident.[Points] = p2 AND " & _
"tbl_incident.[Badge] = p3 AND " & _
"tbl_incident.[Date_of_Incident] = p4 AND " & _
"tbl_incident.[Shift] = p5 " & _
"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

It is ALMOST working. For some reason it is not updating the description field, it is replacing it with a 0. On to that problem. Thanks for all your input.:banghead:

 

[Gasman]

I think you should have

Private Sub Save_Click()
With CurrentDb.CreateQueryDef("", _
"UPDATE tbl_incident SET tbl_incident.[Description] = p0, " & _
"tbl_incident.Code = 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 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

at least to get it working.

Next I would ask why are you setting Badge, Incident and Shift when you are comparing against the same values.?

I was concentrating on the syntax, but when I read the whole statement, it does not make sense to me?

 

[Jimg1976]

Private Sub Save_Click()
With CurrentDb.CreateQueryDef("", _
"UPDATE tbl_incident SET tbl_incident.[Description] = p0, " & _
"tbl_incident.Code = p1 AND " & _
"tbl_incident.[Points] = p2 AND " & _
"tbl_incident.[Badge] = p3 AND " & _
"tbl_incident.[Date_of_Incident] = p4 AND " & _
"tbl_incident.[Shift] = p5 " & _
"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

I do not understand why the AND caused a problem in the first part of the update statement, however removing it makes the code update.

 

[Jimg1976]

I think you should have

Private Sub Save_Click()
With CurrentDb.CreateQueryDef("", _
"UPDATE tbl_incident SET tbl_incident.[Description] = p0, " & _
"tbl_incident.Code = 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 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

at least to get it working.

Next I would ask why are you setting Badge, Incident and Shift when you are comparing against the same values.?

I was concentrating on the syntax, but when I read the whole statement, it does not make sense to me?

The only field that should not be edited is Badge. Because that is the only field that will not change. The shift, the date, the code, the points etc. This is all dependent on human entry and needs to be corrected if a mistake is made. I have to compare it with the entry already in the table and the update it.

 

[Gasman]

I've not seen AND in an update statement, plus you are not using it for p0 ?

AFAIK the AND is for the WHERE clause. The Update clause merely has a , except for the last field.

 

[Gasman]

The only field that should not be edited is Badge. Because that is the only field that will not change. The shift, the date, the code, the points etc. This is all dependent on human entry and needs to be corrected if a mistake is made. I have to compare it with the entry already in the table and the update it.

Yes, but you are looking for a record with a field = P3, then updating it from P3? The same with P4 & P5.?

 

[Jimg1976]

I've not seen AND in an update statement, plus you are not using it for p0 ?

AFAIK the AND is for the WHERE clause. The Update clause merely has a , except for the last field.

There is a good chance that I formatted something incorrectly that caused the issue, as for some reason, with p0 it would update to 0 and not the field from the form. However, I am more convinced it was something I did that caused the issue, because it was not occuring for the rest of the p's

 

[Gasman]

Try my version with the , in the update clause.
Initially you only updated 3 fields, and so only had a comma after the first two.
If you increase the fiels being updated, the previous ones need a comma being added.?

 

[Jimg1976]

Yes, but you are looking for a record with a field = P4, then updating it from P4? The same with the others.?

I am looking for the entry that matches p3, p4, and p5 and then editing the field if needed. Because with those 3 fields matched, it should only find the one entry. Then, all fields except badge can be updated if needed.