Help with DoCmd.RunSQL Update

[IJOC]

I am using the code below to update fields values based on the after update entry in a field. However when I try to run it it wants to update all records in the table and not just the specific CardID. Any ideas on what I am doing wrong?

DoCmd.RunSQL "Update tblCards set intCardWantQOH=intCardWantQOH-" & Nz(hold_WLqty, 0) & " where pkeyCardID=""" & Me.pkeyCardID & """"
DoCmd.RunSQL "Update tblCards set intCardWantQOH=intCardHaveQOH+" & Nz(Me.cmdWantQOHPlus, 0) & " where pkeyCardID=""" & Me.pkeyCardID & """"
Me.intCardWantQOH.Requery

 

[genomon]

Looks like too many double quotes at the end of each SQL string are invalidating your where clauses. Try this:

DoCmd.RunSQL "Update tblCards set intCardWantQOH=intCardWantQOH-" & Nz(hold_WLqty, 0) & " where pkeyCardID=""" & Me.pkeyCardID & """
DoCmd.RunSQL "Update tblCards set intCardWantQOH=intCardHaveQOH+" & Nz(Me.cmdWantQOHPlus, 0) & " where pkeyCardID=""" & Me.pkeyCardID & """

Cogito eggo sum – I think, therefore I am a waffle.

 

[genomon]

SHAME ON ME! Try replacing " " " in the code i posted with " ' " . That is, replace 3 double quotes with double-single-double.

Cogito eggo sum – I think, therefore I am a waffle.

 

[IJOC]

So this what I have now and I am still getting the same results. Confused.

DoCmd.RunSQL "Update tblCards set intCardWantQOH=intCardWantQOH-" & Nz(hold_WLqty, 0) & " where pkeyCardID=" ' " & Me.pkeyCardID & " ' "
DoCmd.RunSQL "Update tblCards set intCardWantQOH=intCardHaveQOH+" & Nz(Me.cmdWantQOHPlus, 0) & " where pkeyCardID=" ' " & Me.pkeyCardID & " ' "
Me.intCardWantQOH.Requery

 

[genomon]

I am pretty slow today. Try this:

DoCmd.RunSQL "Update tblCards set intCardWantQOH=intCardWantQOH-" & Nz(hold_WLqty, 0) & " where pkeyCardID= ' " & Me.pkeyCardID & " ' "
DoCmd.RunSQL "Update tblCards set intCardWantQOH=intCardHaveQOH+" & Nz(Me.cmdWantQOHPlus, 0) & " where pkeyCardID= ' " & Me.pkeyCardID & " ' "

Cogito eggo sum – I think, therefore I am a waffle.

 

[genomon]

I meant this:

DoCmd.RunSQL "Update tblCards set intCardWantQOH=intCardWantQOH-" & Nz(hold_WLqty, 0) & " where pkeyCardID= '" & Me.pkeyCardID & "'"
DoCmd.RunSQL "Update tblCards set intCardWantQOH=intCardHaveQOH+" & Nz(Me.cmdWantQOHPlus, 0) & " where pkeyCardID= '" & Me.pkeyCardID & "'"

Cogito eggo sum – I think, therefore I am a waffle.

 

[dhookom]

I make a practice of always declaring a variable like strSQL, setting the value of strSQL, maybe debug.print strSQL, and then run it. It's too difficult to debug issues when you don't use variables.

Dim strSQL as String
strSQL = "Update tblCards set intCardWantQOH=intCardWantQOH-" & Nz(hold_WLqty, 0) & " where pkeyCardID= '" & Me.pkeyCardID & "'"
Debug.Print strSQL
DoCmd.RunSQL strSQL
strSQL = "Update tblCards set intCardWantQOH=intCardHaveQOH+" & Nz(Me.cmdWantQOHPlus, 0) & " where pkeyCardID= '" & Me.pkeyCardID & "'"
Debug.Print strSQL
DoCmd.RunSQL strSQL

Duane
Hook'D on Access
MS Access MVP

 

[IJOC]

Thanks.

Ok that appears to have gone the trick. Now the only problem I am having is that when I enter a value it shows up for very record in the datasheet but it does only update current records. For example if I am on pkeyCardID 422 and I enter 1 it populates all records in the datasheet with 1 but when i tab out and update using the code it only updates the pkeyCardID 422, which is what I want. I don't want all the other records to say 1. Any ideas?

 

[IJOC]

Sorry i meant continuous form view.

 

[dhookom]

it populates all records in the datasheet

is this a field/control bound to a field in the record source?

Duane
Hook'D on Access
MS Access MVP

 

[IJOC]

It is unbound

 

[dhookom]

If it's unbound, it will always only show one value.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya IJOC . . .

Also ... I find it hard to believe you intend to update the same field twice! ... as in:

set [blue]intCardWantQOH[/blue]=intCardWantQOH-"
set [blue]intCardWantQOH[/blue]=intCardHaveQOH+"

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]