Full Version: Coorect table data by VB?!!
UtterAccess Forums > Microsoft® Access > Access Automation
would like to correct some data in my tables
My conditions:
If COMPTE=612210 and Extension<>"CFRN1" then EXTENSION="CFRN9"
If COMPTE=612210 and Extension<>"CFRN2" then EXTENSION="CFRN9"
If COMPTE=612220 and Extension<>"CFRN4" then EXTENSION="CFRN10"
If COMPTE=612220 and Extension<>"CFRN10" then EXTENSION="CFRN10"
I use this code:

Select Case COMPTE
                  Case 612210: Select Case EXTENSION
                    Case Is <> "CFRN1" Or "CFRN2" Or "CFRN2" Or "CFRN2" Or "CFRN2": EXTENSION = "CFRN9"
                  End Select
               Case 612220: Select Case EXTENSION
                    Case Is <> "CFRN4" Or "CFRN10": EXTENSION = "CFRN10"
                 End Select
End Select

But, as a result, the correction isn't done
What's the problem?
Could help me tu correct this code
I would not use queries please!!
Thank you
First, The correct syntax for Access is:
Select Case variable
Case x
Case y
Case z
End Select
But where and how are you using this?
Your example is relating to a sample ond one condition.
ut, in my example, I would use 2 conditions at the same time (COMPTE & EXTENSION).
The purpose is to correct some Data of EXTENSION when the conditions aboe are satisfited.
Without using queries
You can't update a table without using an Update query. I realize your criteria was more complex, but the point is you were using the Case IS whihc was incorrect.
scott- huh? "(You can't update a table without using an Update query)"

well you might not want to, but using recordsets you can.

however, Update queries are fast and efficient, and I can't think of why you wouldn't want to use one ,even in this situation.

dim s as string
="UPDATE YourTable SET YourTable.Extension = 'CFRN9' WHERE (YourTable.Compte=612210 AND YourTable.Extension<>'CFRN1' AND YourTable.Extension<>'CFRN2')"
currentdb.execute s
s="UPDATE YourTable SET YourTable.Extension = 'CFRN10' WHERE (YourTable.Compte=612220 AND YourTable.Extension<>'CFRN4' AND YourTable.Extension<>'CFRN10')"
currentdb.execute s

Edited by: glue on Thu Feb 24 13:19:28 EST 2005.
whoops, I wasn't thinking along those lines. I was thinking function vs query not query vs recordset.
Before I discovered the wonders of update queries, I had written literally thousands of lines of unnecessary recordset code over the previous few years. So I like to try to keep other people from making the same mistake. I never considered telling them that it can't be done without update queries -- but that might be the best approach, I wish somebody would've told me that !:>
If you still want help setting up the select case statement, let me know. Update queries are faster (often MUCH faster) and require way less coding, and I've been replacing all my clunky recordset code w/ them since I saw the error of my ways.
Thank you glue.
ut, please how could I convert recordset to case select function? I want to know where's my faults
You are comparing apples and oranges here. A Select Case and a table update are two different things. You may use a Select Case to get a value that will be used to update a table, but how you update the table is indifferent to the Select Case.
o be more specific, lets say you have a function named UpdExtension(). That function uses a Select Case to determine what value to update Expression to, based on the value of other fields. You can then use that functioin in either a query or DAO coding.
...SET [Extension] = UpdExtension([COMPTE],[EXTENSION])
rs.Extension = UpdExtension([COMPTE],[EXTENSION])
HAs Glue said, using an update query is more efficient but it doesn't change the SELECT CASE at all.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.