Update Query problem:

[wlgzhang]

Hi all,
I have encountered an update Query prolem.
the following is my code:

ResultSql = "UPDATE ExamResult SET ExamResult.Rsult = " & DoubleQuote(Me![Result]) _
& " WHERE (((ExamResult.AppFileNo)Like '* " & AppId & "*') AND " _
& "((ExamResult.ExamName)Like '*" & CurrExamName & "*') AND " _
& "((ExamResult.SessionYear) " & DoubleQuote(Me![SessionYear]) & "*')) "

DoCmd.RunSQL ResultSql

I got error msg:"Can not the filed, update ExamResult.Result.
Aslo, I would like get ride off the Parameter input box at runtime Counld anyone tell me how to cope the error and eliminate the parameter input box. I need do update this field in the backgraound.

Any help is very much appreciated.

Wei Zhang

 

[wlgzhang]

Update Query Problem

Hi all,
I have fixed my update query hence the query is working right now. However, I do not know how to get ride off the parameter input box at run time Counld anyone help me to come up the solution, syntex?
Thanks for any help.
Wei

 

[WayneRyan]

w,

When Access prompts you like that it means that it can't resolve (assign a
value to) something.

You refer to the field as:

ExamResult.Rsult and as ExamResult.Result

Check the names again.

Wayne

 

[wlgzhang]

Hi WayneRyan,
Thank you for your help.
I did had type on that field mane, and I had fiexed after I post the question for a while. Know, the works correclty, but I do not like the pop up dialog box witch ask me input the value that I have assigned to the field in the update query. I tried to do by add parameter statement to the query like that:

ResultSql = "PARAMETER " & DoubleQuote(Me![Result]) & " Text(255) " _
& " UPDATE ExamResult SET ExamResult.Result = " & DoubleQuote(Me![Result]) _
& " WHERE (((ExamResult.AppFileNo) Like '*" & AppId & "*') AND " _
& "((ExamResult.ExamName)Like '*" & CurrExamName & "*') AND " _
& "((ExamResult.SessionYear) Like '*" & DoubleQuote(Me![SessionYear]) & "*')) "

But it even gone worse.
I got error message:"syntex error in parameter cluase"
I stuck at this point. Can you halp me again.

Thank you very much.

Wei

 

[WayneRyan]

Wei,

Can you post a sample DB?

Tools --> Database Utilities --> Compact/Repair
Then ZIP
Then attach

Wayne

 

[wlgzhang]

Hi Pat,
I did follow all of your suggestion, still got runtime error: syntax error in parameters clause.
If I get rid off the parameter clause, I always get pop up dialog box witch ask me to put the value of Result. I do not know why.

the size of the DB is about 12MB, I have to take time to make a smaller the sample DB to post.

Wei.

 

[Bat17]

When debugging SQL I find it handy to print the sqlstring to the debug window ( ctr-G) so that you can see what you are actualy trying to build the query with, you can even paste it into the query builder to see what happens in design view

debug.print ResultSql

HTH

Peter

 

[wlgzhang]

Update Query Problems:

Hi All,
Thank you for all Your replay.
I find out where the problem is (missing a pair of single quotation )and fixed it. Now the code works and no more
pop up dialog boxes to bord me.

The code has been little changed as Shown on color marked place:

ResultSql = " UPDATE ExamResult SET ExamResult.Result = '"& DoubleQuote(Me![Result]) & "'" _
& " WHERE (((ExamResult.AppFileNo) = '" & DoubleQuote(Me.[AppFileNo]) & "') AND " _
& "((ExamResult.ExamName)= '" & DoubleQuote(Me.[ExamName]) & "') AND " _
& "((ExamResult.SessionYear) = '" & DoubleQuote(Me.[SessionYear]) & "')) "

DoCmd.RunSQL ResultSql