Trying to run update for table screwing it up

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

Look at P3


Jimg1976 said:
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;")
Click to expand...
 
Hi GasMan,

Sorry I wasn't clear. I did add that AND however I am still getting the same error.:banghead::eek:
 
Post your code please, within tags
 
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
 
Perhaps, but not correctly. :(

The error line is misleading I believe.?
You now have mispelled
Code: 
 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]
 
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.
 
As far as going back to what works, Simply eliminating the last line of the code, the one I posted makes the code work.
 
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.
 
Jimg1976 said:
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.
Click to expand...

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

You only need [] if the field/control name has spaces in it. Code does not.
 
Last edited:
Jimg1976 said:
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.
Click to expand...

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.
 
Code: 
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:
 
I think you should have
Code: 
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.? :confused:

I was concentrating on the syntax, but when I read the whole statement, it does not make sense to me?
 
Code: 
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.
 
Gasman said:
I think you should have
Code: 
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.? :confused:

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


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.
 
Last edited:
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.
 
Jimg1976 said:
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.
Click to expand...

Yes, but you are looking for a record with a field = P3, then updating it from P3? The same with P4 & P5.?
 
Gasman said:
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.
Click to expand...

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
 
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.?
 
Gasman said:
Yes, but you are looking for a record with a field = P4, then updating it from P4? The same with the others.?
Click to expand...

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.
 
Gasman said:
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.?
Click to expand...

It is entirely possible. Your code did work just fine. Thank you so much for all your attention.
 
You must log in or register to reply here.

Similar threads

N
Solved How to update bulk selling prices at one go
Replies
5
Views
491
nector
N
N
Solved How to deal with locking system in MS access safely in point of sales environment
Replies
6
Views
631
GustavBrock
GustavBrock
M
Search data from one table to append to another table
Replies
8
Views
460
Mark_
M
Mike Krailo
Solved Need help with before update code logic?
Replies
10
Views
1,239
Mike Krailo
Mike Krailo
Gasman
Solved Update broke Conditional Formatting?
Replies
13
Views
765
Gasman
Gasman

Users who are viewing this thread

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