udpate table data from form code

[wasim]

Hi,

I have a code in form, when i click command button, it should update 2 fields in the table. But it is not updating. I think there is something wrong in code. If it is only one field update, it works fine. It is not updating if there are 2 fields to update.


CurrentDb.Execute "UPDATE APPLICATION SET APPLICATION.STATUSID = 1 AND APPLICATION.STATUSREMARKSID =2" _
& " WHERE (((APPLICATION.APPLICATIONID)=" & Me.APPLICATIONID & "));", dbFailOnError

Please help me to sort out this

wasim

 

[pbaldy]

You'd separate the fields with a comma, not AND.

 

[wasim]

Thanks. I have one more issue. I have form with list box. I want to update the table from below
CurrentDb.Execute "UPDATE POSITIONAPPLIED SET POSITIONAPPLIED.PEVALID = " & Me.EVALID _
& " WHERE (((POSITIONAPPLIED.POSITIONAPPLIEDID)= " & Me.lsthosp.Column(0, i) & ") And ((POSITIONAPPLIED.APPLICATIONID)= " & Me.lsthosp.Column(3, i) & "));", dbFailOnError

But it updates all the lines in the table. I want to update only the pertilcular line where the above conditon meets. Why this updates all the records in the table?

Please help me out.

wasim

 

[wasim]

Hi,


separate the field by coma is not working. Please check the query below and suggest the correct syntax.

CurrentDb.Execute "UPDATE APPLICATION SET APPLICATION.STATUSID = 1 AND APPLICATION.STATUSREMARKSID =Me.HROFFICERRECID" _
& " WHERE (((APPLICATION.APPLICATIONID)=" & Me.APPLICATIONID & "));", dbFailOnError


Thanks

wasim

 

[JHB]

..
separate the field by coma is not working. Please check the query below and suggest the correct syntax.

CurrentDb.Execute "UPDATE APPLICATION SET APPLICATION.STATUSID = 1 AND APPLICATION.STATUSREMARKSID =Me.HROFFICERRECID" _
& " WHERE (((APPLICATION.APPLICATIONID)=" & Me.APPLICATIONID & "));", dbFailOnError

It is not exactly the same code as you showed in post #1.
I think what pbaldy mention was correct, if you replaced the AND with a comma the code in post #1 would work.
I think the correct syntax for this one is, (else show the error number & message):

CurrentDb.Execute "UPDATE APPLICATION SET STATUSID = 1, STATUSREMARKSID =" & Me.HROFFICERRECID _
& " WHERE APPLICATIONID=" & Me.APPLICATIONID & ";", dbFailOnError

 

[arnelgp]

CurrentDb.Execute "UPDATE [APPLICATION] SET [STATUSID] = 1, [STATUSREMARKSID] = " & Me.HROFFICERRECID _
& " WHERE ((([APPLICATIONID])=" & Me.APPLICATIONID & "));", dbFailOnError

 

[pbaldy]

If you're still having trouble, this is a common debugging method:

http://www.baldyweb.com/ImmediateWindow.htm

 

[wasim]

Dear Arnelgp.

Pefect!! It Works. Thank you.

Wasim

 

[wasim]

dear Arnelgp,

I want to execute below code. The form with list box. When i click the command button, it should execute the code and update only the field where Positionappliedid is egual to list box PostionappliedID.
But it updates the PEVALID for all the records. How i can sort out this issue?
The code is as below.

CurrentDb.Execute "UPDATE [POSITIONAPPLIED] SET [PEVALID] = " & Me.EVALID _
& " WHERE [POSITIONAPPLIEDID]= " & Me.lsthosp.Column(0, i) & " And [APPLICATIONID]= " & Me.lsthosp.Column(3, i) & ";", dbFailOnError

Please help to sort out this issue.

wasim

 

[pbaldy]

Have you tried the debugging method in post 7?

 

[arnelgp]

try adding a breakpoint (F9 to add) on the click event of your button, then add:

debug.print Me.lsthosp.Column(0, i)
debug.print Me.lsthosp.Column(3, i)

what data do you see? are these correct in your listbox?