Solved

SQL update  not writing the record tofile

Posted on 2013-01-17
10
290 Views
Last Modified: 2013-01-17
below is the statement. I did a debug print and it forms the record but is not writing it to the table below is the debug print. The records exist in the table to match on

UPDATE StateAccessFile2 SET StateAccessFile2.[E2EG6FemaleCount12thGrade] = '4', StateAccessFile2.[E2EG6MaleCount12thGrade] = '11', StateAccessFile2.[E2EG6FemaleCountMil] = '0', StateAccessFile2.[E2EG6MaleCountMil] = '0', StateAccessFile2.[E2EG6FemaleCountCol] = '0', StateAccessFile2.[E2EG6MaleCountCol] = '0', StateAccessFile2.[E2EG6FemaleCountColUnRel] = '0', StateAccessFile2.[E2EG6MaleCountColUnRel] = '0', StateAccessFile2.[E2EG6FemaleCountEmpRel] = '0', StateAccessFile2.[E2EG6MaleCountEmpRel] = '0', StateAccessFile2.[E2EG6FemaleCountEmpUnRel] = '0', StateAccessFile2.[E2EG6MaleCountEmpUnRel] = '0', StateAccessFile2.[E2EG6FemaleCountNot] = '0', StateAccessFile2.[E2EG6MaleCountNot] = '0', StateAccessFile2.[E2EG6FemaleCountTot] = '0', StateAccessFile2.[E2EG6MaleCountTot] = '0', StateAccessFile2.[techprepyn]= 'N', StateAccessFile2.[NonTradCrs]= 'N' WHERE CourseCode='4630' and CType='Concentrator' AND StateAccessFile2.[AdultYN]='N'Type='Concentrator' AND StateAccessFile2.[AdultYN]='N'

Here is the update statement

 SQlUpdQry2 = "UPDATE StateAccessFile" & PTypeLpCnt & " SET " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCount12thGrade] = '" & E2EGFemaleCount12thGradeStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCount12thGrade] = '" & E2EGMaleCount12thGradeStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountMil] = '" & E2EGFemaleCountMilStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountMil] = '" & E2EGMaleCountMilStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountCol] = '" & E2EGFemaleCountColStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountCol] = '" & E2EGMaleCountColStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountColUnRel] = '" & E2EGFemaleCountColUnrelStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountColUnRel] = '" & E2EGMaleCountColUnrelStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountEmpRel] = '" & E2EGFemaleCountEmpRelStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountEmpRel] = '" & E2EGMaleCountEmpRelStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountEmpUnRel] = '" & E2EGFemaleCountEmpUnRelStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountEmpUnRel] = '" & E2EGMaleCountEmpUnRelStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountNot] = '" & E2EGFemaleCountNotStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountNot] = '" & E2EGMaleCountNotStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountTot] = '" & E2EGFemaleCountTotStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountTot] = '" & E2EGMaleCountTotStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[techprepyn]= '" & TechPrepVal & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[NonTradCrs]= '" & NonTradVal & "' " & _
        "WHERE CourseCode='" & CBEDSCrsNum & "' and CType='" & CTypeArr(CTypeLpCnt) & "' AND StateAccessFile" & PTypeLpCnt & ".[AdultYN]='" & ProgTypeVal & "'"
0
Comment
Question by:needhelpfast569
  • 5
  • 4
10 Comments
 
LVL 33

Expert Comment

by:paulmacd
ID: 38787607
It seems likely your WHERE condition isn't being met.  If you SELECT FROM StateAccessFile WHERE CourseCode='" & CBEDSCrsNum & "' and CType='" & CTypeArr(CTypeLpCnt) & "' AND StateAccessFile" & PTypeLpCnt & ".[AdultYN]='" & ProgTypeVal & "'" is anything returned?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38787640
<<it forms the record but is not writing it to the table>>

What is the rest of your code?

Are you actually executing your SQL?

After the big  SQlUpdQry2 = "UPDATE... etc", you need the following:

CurrentDB.Execute SQlUpdQry2, dbFailOnError

Open in new window

0
 

Author Comment

by:needhelpfast569
ID: 38787828
mbizup, thanks you are right it was the program was falling thru and not doing the excute.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38787911
Did that help you resolve it?  If so, please remember to close the question.

Or post back if you need additional help.
0
 

Author Comment

by:needhelpfast569
ID: 38787918
doing the excute but still not updating the record????
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 61

Expert Comment

by:mbizup
ID: 38787947
Did you add dbFailOnError to your Execute statement?

CurrentDB.Execute SQlUpdQry2, dbFailOnError

If so, do you get any specific error messages?

Try copy/pasting your Debug.Print output into your query builder, and try running this directly from the query builder:

UPDATE StateAccessFile2 SET StateAccessFile2.[E2EG6FemaleCount12thGrade] = '4', StateAccessFile2.[E2EG6MaleCount12thGrade] = '11', StateAccessFile2.[E2EG6FemaleCountMil] = '0', StateAccessFile2.[E2EG6MaleCountMil] = '0', StateAccessFile2.[E2EG6FemaleCountCol] = '0', StateAccessFile2.[E2EG6MaleCountCol] = '0', StateAccessFile2.[E2EG6FemaleCountColUnRel] = '0', StateAccessFile2.[E2EG6MaleCountColUnRel] = '0', StateAccessFile2.[E2EG6FemaleCountEmpRel] = '0', StateAccessFile2.[E2EG6MaleCountEmpRel] = '0', StateAccessFile2.[E2EG6FemaleCountEmpUnRel] = '0', StateAccessFile2.[E2EG6MaleCountEmpUnRel] = '0', StateAccessFile2.[E2EG6FemaleCountNot] = '0', StateAccessFile2.[E2EG6MaleCountNot] = '0', StateAccessFile2.[E2EG6FemaleCountTot] = '0', StateAccessFile2.[E2EG6MaleCountTot] = '0', StateAccessFile2.[techprepyn]= 'N', StateAccessFile2.[NonTradCrs]= 'N' WHERE CourseCode='4630' and CType='Concentrator' AND StateAccessFile2.[AdultYN]='N'Type='Concentrator' AND StateAccessFile2.[AdultYN]='N'

Open in new window

0
 

Author Comment

by:needhelpfast569
ID: 38788004
added the dbfailonerror to the stmt and receiver a 128 error code
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38788058
1.  What was the exact text of the error message?

2.  Also try running the text from debug.print directly from the query builder (see comment http:#a38787947) and let us know if it updates correctly or fails.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38788100
dbFailOnError is a Constant whose value is 128.  If you got that from hovering the mouse over dbfailOnError in the debugger, that is to be expected.

dbFailOnError makes your execute statement return a specific error number and message if the query itself fails. Without dbFailOnError, your code will continue to execute normally without any warning messages - even if your query has failed (so dbFailOnError mprovides you with important debugging info if your query has problems).

So does the query execute without error, and just not update anything?

Or is it not executing because of errors in the query?

(The reason for adding dbFailOnError is to help you make that distinction)
0
 

Author Closing Comment

by:needhelpfast569
ID: 38788152
LOADING THE STMT INTO QUERY WORKED GREAT THE PROBLEM WAS THAT i WAS MISSING A VALUE SO THE UPDATE COULD NOT FIND A MATCH. THANKS
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now