run an update query

[wshs]

i have a following code..

Private Sub cmdOK_Click()
If Me.tempBal < 0 Then
MsgBox "The amount is larger than the balance"
Else
CurrentProject.Connection.Execute _
"Update tbl_fsource " & "SET balan =" & Me.tempBal & " WHERE BFSIX='" & Me.LstBud.Column(7) & "'"
CurrentProject.Connection.Execute _
"update tbl_fsource " & "SET voupa = voupa + " & Me.txtAmount & " Where bfsix='" & Me.LstBud.Column(7) & "'"
MsgBox "Your transaction has been completed"
End If
Me.LstBud.Requery
DoCmd.OpenForm "frma_purrequisitionmenu", , "qry_fsource_query"
exit_cmdOK_Click:
DoCmd.Close acForm, Me.Name
Exit Sub

End Sub


query is nothing more than adding few columns to get a total. table updates correctly if i run the query manually but with the code above, query doesnt update (or doesnt even run it seems like) anything...

any help?

 

[Golom]

Don't see anything obviously wrong. Try something like this

Private Sub cmdOK_Click()
    Dim SQL                         As String
    Dim RecordsAffected             As Long

    If Me.tempBal < 0 Then
        MsgBox "The amount is larger than the balance"
    Else

        SQL = "UPDATE tbl_fsource SET " & _
              "balan = " & Me.tempBal & ", " & _
              "voupa = voupa + " & Me.txtAmount & " " & _
              "WHERE [BFSIX] = '" & Me.LstBud.Column(7) & "'"

        CurrentProject.Connection.Execute SQL, RecordsAffected

        Debug.Print SQL
        Debug.Print RecordsAffected

        MsgBox "Your transaction has been completed"
    End If
    Me.LstBud.Requery
    DoCmd.OpenForm "frma_purrequisitionmenu", , "qry_fsource_query"
exit_cmdOK_Click:
    DoCmd.Close acForm, Me.Name
    Exit Sub

End Sub

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[wshs]

thank you. still not working though. maybe it's because the query needs to run before the form is closed???
is there a way to run it in the ELSE cause?

 

[Golom]

If you're seeing the message Your transaction has been completed then you have run the Execute in the ELSE clause.

Set a breakpoint on the MsgBox line after the Debug.Print statements and when it stops, press Ctrl-G to open the immediate window. You should see the SQL statement that actually ran and the number of records that were changed.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[wshs]

else clause is executing fine.. both balan and voupa are updating correctly. It's just that the query is not running.

qry sql:
UPDATE tbl_Fsource SET tbl_Fsource.Totex = [cashd]+[voupa]+[payrl]+[contp], tbl_Fsource.Balan = [fcast]-[totex];

 

[Golom]

Hmmmm ... I don't see that code anywhere in the stuff that you posted. Is it somewhere else in the program ... possibly in an event that's not being fired?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[wshs]

oh that's the sql of the query that's suppose to update after voupa and balan are updated...

 

[Golom]

Understand that ... but where is it in your code?

Just having it defined as a query isn't sufficient. You need to execute it just as you are executing the SQL that you are building in the sample code you posted.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[wshs]

DoCmd.OpenForm "frma_purrequisitionmenu", , "qry_fsource_query"


i thought this would run the query? am i wrong?

sorry. new to this. so how would i run the query in vba?