Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

SQL update not writing the record tofile

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
needhelpfast569
Asked:
needhelpfast569
  • 5
  • 4
1 Solution
 
Paul MacDonaldDirector, Information SystemsCommented:
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
 
mbizupCommented:
<<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
 
needhelpfast569Author Commented:
mbizup, thanks you are right it was the program was falling thru and not doing the excute.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mbizupCommented:
Did that help you resolve it?  If so, please remember to close the question.

Or post back if you need additional help.
0
 
needhelpfast569Author Commented:
doing the excute but still not updating the record????
0
 
mbizupCommented:
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
 
needhelpfast569Author Commented:
added the dbfailonerror to the stmt and receiver a 128 error code
0
 
mbizupCommented:
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
 
mbizupCommented:
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
 
needhelpfast569Author Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now