473,320 Members | 2,073 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Project Tracking DB - Adding new row error

42
I'm receiving error "Error entering Project. Invalid use of Null". I cannot determine why the VB script is not adding the "ID" number and writing to various tables. The database was inherited from another department.

Expand|Select|Wrap|Line Numbers
  1. 1.Private Sub btnCreateProject_Click()
  2. 2. Dim dbs As DAO.Database
  3. 3. Dim sSql As String
  4. 4. Dim projRowID As Integer
  5. 5. Dim CompletionDate As String
  6. 6. Dim dateRequested As String
  7. 7. Dim dateItemComplete As Date
  8. 8. Dim yNumber As Integer
  9. 9. Dim yName As String
  10. 10. Dim REFNUM As Integer
  11. 11. Dim ITprior As Variant
  12. 12. Dim teamPrior As Variant
  13. 13. Dim dateUAT As Date
  14. 14. Dim dateProd As Date
  15. 15. Dim dateIT As Date
  16. 16. 
  17. 17. 
  18. 18. REFNUM = DMax("REFNUM", "FA678_PROJECT") + 1
  19. 19. If UserID = "" Then
  20. 20.     UserID = 0
  21. 21. End If
  22. 22. 
  23. 23. 'IT priority null?
  24. 24. If IsNull(Me.txt_ITprior.Value) Then
  25. 25.     ITprior = "Null"
  26. 26. Else
  27. 27.     ITprior = Me.txt_ITprior.Value
  28. 28. End If
  29. 29. 'team priority null?
  30. 30. If IsNull(Me.txt_teamPrior.Value) Then
  31. 31.     teamPrior = "Null"
  32. 32. Else
  33. 33.     teamPrior = Me.txt_teamPrior.Value
  34. 34. End If
  35. 35. 
  36. 36. 'UAT Date null?
  37. 37. If IsNull(Me.txt_DtUAT.Value) Then
  38. 38.     dateUAT = #1/1/1901#
  39. 39. Else
  40. 40.     dateUAT = Me.txt_DtUAT.Value
  41. 41. End If
  42. 42. 
  43. 43. 'Prod Date null?
  44. 44. If IsNull(Me.txt_DtProd.Value) Then
  45. 45.     dateProd = #1/1/1901#
  46. 46. Else
  47. 47.     dateProd = Me.txt_DtProd.Value
  48. 48. End If
  49. 49. 
  50. 50. 'IT Date null?
  51. 51. If IsNull(Me.txt_DtToIT.Value) Then
  52. 52.     dateIT = #1/1/1901#
  53. 53. Else
  54. 54.     dateIT = Me.txt_DtToIT.Value
  55. 55. End If
  56. 56. 
  57. 57. 
  58. 58. 
  59. 59. On Error GoTo errorHandler
  60. 60. Set dbs = CurrentDb
  61. 61. 
  62. 62.     If Me.txtZnum = 0 Then
  63. 63.         isZNum = False
  64. 64.     Else
  65. 65.         isZNum = True
  66. 66.     End If
  67.  
  68.  
  69.  
Attached Files
File Type: docx FORM.docx (66.9 KB, 76 views)
File Type: txt Code.txt (10.4 KB, 116 views)
Nov 13 '18 #1
6 1266
twinnyfo
3,653 Expert Mod 2GB
Bre035,

What would be incredibly and way more helpful would be for you to at least tell us at which line (at your nearly 400 lines of code) that the error occurs.

Without that, I just can't see too many people wanting to scour your code to try an find where that error occurs.

You can also review this tutorial on debugging, which can give you some pointers as well.

Let us know a bit more and I'm sure we can hepp!
Nov 14 '18 #2
Bre035
42
It’s erroring at the insert where I’m assuming it’s not creating the ID or ProjRowID. I’ll attempt to edit the code submitted.
Nov 14 '18 #3
twinnyfo
3,653 Expert Mod 2GB
Bre035
I cannot determine why...
However, I can fully understand this statement. I know you inherited this db, but the code you listed is kind of a mess (to put it politely). I am sure there must have been some sort of "reason" for doing it the way they did, but for the life of me, I can't conceive of any reasonable explanation.

Even removing all the commented our lines, and consolidating the code into a more readable format, I still get this (which is not much better).

Usually, to add a new record, you just add a new record and the information is readily availabel so that an INSERT query is not required.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. On Error Resume Next
  3.     If UserRACFID = "" Then
  4.         UserRACFID = Environ("USERNAME")
  5.     End If
  6.  
  7.     If UserName = "" Then
  8.         getInitialInfo (UCase(UserRACFID))
  9.     End If
  10.  
  11.     If Me.txtZnum = 0 Then
  12.         isZNum = False
  13.     Else
  14.         isZNum = True
  15.     End If
  16.  
  17.     Me.cboAnalyst.SetFocus
  18.     Me.cboAnalyst.Value = UserName
  19.  
  20.     Me.txtRACF.SetFocus
  21.     Me.txtRACF.Value = UCase(UserRACFID)
  22.  
  23. End Sub
  24.  
  25. Private Sub btnCreateProject_Click()
  26. Dim dbs As DAO.Database
  27. Dim sSql As String
  28. Dim projRowID As Integer
  29. Dim CompletionDate As String
  30. Dim dateRequested As String
  31. Dim dateItemComplete As Date
  32. Dim yNumber As Integer
  33. Dim yName As String
  34. Dim REFNUM As Integer
  35. Dim ITprior As Variant
  36. Dim teamPrior As Variant
  37. Dim dateUAT As Date
  38. Dim dateProd As Date
  39. Dim dateIT As Date
  40.  
  41. REFNUM = DMax("REFNUM", "FA678_PROJECT") + 1
  42. If UserID = "" Then
  43.     UserID = 0
  44. End If
  45.  
  46. 'IT priority null?
  47. If IsNull(Me.txt_ITprior.Value) Then
  48.     ITprior = "Null"
  49. Else
  50.     ITprior = Me.txt_ITprior.Value
  51. End If
  52.  
  53. 'team priority null?
  54. If IsNull(Me.txt_teamPrior.Value) Then
  55.     teamPrior = "Null"
  56. Else
  57.     teamPrior = Me.txt_teamPrior.Value
  58. End If
  59.  
  60. 'UAT Date null?
  61. If IsNull(Me.txt_DtUAT.Value) Then
  62.     dateUAT = #1/1/1901#
  63. Else
  64.     dateUAT = Me.txt_DtUAT.Value
  65. End If
  66.  
  67. 'Prod Date null?
  68. If IsNull(Me.txt_DtProd.Value) Then
  69.     dateProd = #1/1/1901#
  70. Else
  71.     dateProd = Me.txt_DtProd.Value
  72. End If
  73.  
  74. 'IT Date null?
  75. If IsNull(Me.txt_DtToIT.Value) Then
  76.     dateIT = #1/1/1901#
  77. Else
  78.     dateIT = Me.txt_DtToIT.Value
  79. End If
  80.  
  81. On Error GoTo errorHandler
  82. Set dbs = CurrentDb
  83.  
  84.     If Me.txtZnum = 0 Then
  85.         isZNum = False
  86.     Else
  87.         isZNum = True
  88.     End If
  89.  
  90.     'Check for required Fields to be filled
  91.     'txtSDMR.SetFocus
  92.     If Not isZNum Then
  93.         txtSDMR.SetFocus
  94.         If txtSDMR.Text = "" Then
  95.             If MsgBox("Is this a Yxxxxx project?", _
  96.                       vbYesNo, _
  97.                       "Blank SDMR/SOW") = vbYes Then
  98.                 yName = UCase(InputBox( _
  99.                     "Please Enter the Yxxxx ID for the project."))
  100.                 If yName = "" Then Exit Sub
  101.                 yNumber = getNextNum(yName)
  102.                 txtSDMR.Text = yName & "-" & yNumber
  103.                 Me.Refresh
  104.             Else
  105.                 MsgBox "No SDMR entered. Reference # will be: " & REFNUM
  106.                 'Exit Sub
  107.             End If
  108.         End If
  109.     Else
  110.         MsgBox "No SDMR entered. Reference # will be: " & REFNUM
  111.     End If
  112.  
  113.     cboTeam.SetFocus
  114.     If cboTeam.Text = "" Then
  115.         MsgBox "Please Enter Team."
  116.         Exit Sub
  117.     End If
  118.  
  119.     Me.cboPriority.SetFocus
  120.     If Me.cboPriority.Text = "" Then
  121.         MsgBox "Please Enter Priority."
  122.         Exit Sub
  123.     End If
  124.  
  125.     Me.cboOwnerName.SetFocus
  126.     If Me.cboOwnerName.Text = "" Then
  127.         MsgBox "Please Enter Requestor."
  128.         Exit Sub
  129.     End If
  130.  
  131.     Me.cboAnalyst.SetFocus
  132.     If Me.cboAnalyst.Text = "" Then
  133.         MsgBox "Please Enter Analyst."
  134.         Exit Sub
  135.     End If
  136.  
  137.     Me.cboCountry.SetFocus
  138.     If Me.cboCountry.Text = "" Then
  139.         MsgBox "Please Enter Country."
  140.         Exit Sub
  141.     End If
  142.  
  143.     Me.cboPlatform.SetFocus
  144.     If Me.cboPlatform.Text = "" Then
  145.         MsgBox "Please Enter Platform."
  146.         Exit Sub
  147.     End If
  148.  
  149.     Me.txtTitle.SetFocus
  150.     If Me.txtTitle.Text = "" Then
  151.         MsgBox "Please Enter Title."
  152.         Exit Sub
  153.     End If
  154.  
  155.     Me.txtScope.SetFocus
  156.     If Me.txtScope.Text = "" Then
  157.         MsgBox "Please Enter scope."
  158.         Exit Sub
  159.     End If
  160.  
  161.     Me.cmb_Category.SetFocus
  162.     If Me.cmb_Category.Text = "" Then
  163.         MsgBox "Please Enter Category."
  164.         Exit Sub
  165.     End If
  166.  
  167.     If Not isZNum Then
  168. dtCompl:
  169.         dateItemComplete = _
  170.             InputBox("When do you estimate Spec Completion Date?", _
  171.                 "Business Specs Estimated date")
  172.         If dateItemComplete < Date Then
  173.             MsgBox "Wrong Date entered. Please enter again."
  174.             GoTo dtCompl
  175.         End If
  176.     Else
  177.         dateItemComplete = Date
  178.     End If
  179.  
  180.     'Insert Row into Project
  181.     sSql = _
  182.         "INSERT INTO FA678_Project ( " & _
  183.             "REFNUM, SDMR , Team, Priority, " & _
  184.             "PriorityID, RequestorName, AnalystName, " & _
  185.             "AnalystID, AnalystRACFID, AnalystSupport, " & _
  186.             "programmerNames, Phase, PHASEID, Title, " & _
  187.             "PercentComplete, Scope, Country, " & _
  188.             "DTPROJECTCREATED, Platform, estCompleteDate, " & _
  189.             "JazzNum, ITPRIORITY, TEAMPRIORITY, " & _
  190.             "CATEGORY, DTESTIMATETEST, CINDYSLIST, " & _
  191.             "DATETOIT, DTESTIMATEPROD, RELATEDSDMRS ) "
  192.     sSql = sSql & _
  193.         "Select " & REFNUM & " as Expr100, "
  194.     If Not isZNum Then
  195.         txtSDMR.SetFocus
  196.     End If
  197.     If isZNum Then
  198.         Me.txtTitle.SetFocus
  199.         zNum = GetZNum(Me.txtTitle.Text)
  200.         If zNum = "ERROR" Then
  201.             MsgBox "Error entering Project. Try again or contact db Manager."
  202.             Exit Sub
  203.         End If
  204.         sSql = sSql & _
  205.             "'" & "Z" & zNum & "' as Expr17, '"
  206.     Else
  207.         sSql = sSql & _
  208.             "'" & txtSDMR.Text & "' as Expr17, '"
  209.     End If
  210.  
  211.     cboTeam.SetFocus
  212.     sSql = sSql & _
  213.         cboTeam.Text & "' as Expr1, '"
  214.     cboPriority.SetFocus
  215.     sSql = sSql & _
  216.         cboPriority.Text & "' as Expr2, '"
  217.     Select Case UCase(cboPriority.Text)
  218.         Case "HIGH"
  219.             sSql = sSql & "1" & "' as Expr3, '"
  220.         Case "MEDIUM"
  221.             sSql = sSql & "2" & "' as Expr3, '"
  222.         Case "LOW"
  223.             sSql = sSql & "3" & "' as Expr3, '"
  224.         Case Else
  225.             sSql = sSql & "4" & "' as Expr3, '"
  226.     End Select
  227.     cboOwnerName.SetFocus
  228.     sSql = sSql & _
  229.         cboOwnerName.Text & "' as Expr4, '" & _
  230.         cboAnalyst.Text & "' as Expr5, " & _
  231.         UserID & " as Expr6, '" & _
  232.         Me.txtRACF.Text & "' as Expr7, '" & _
  233.         txtAnalystSupport.Text & "' as Expr8, '" & _
  234.         txtProgrammers.Text & "' as Expr9, '" & _
  235.         "Writing Business Specs' as Expr10, " & _
  236.         "2 as Expr34, '" & _
  237.         txtTitle.Text & "' as Expr11, " & _
  238.         "5 as Expr12, '" & _
  239.         txtScope.Text & "' as Expr13, '" & _
  240.         cboCountry.Text & "' as Expr14, " & _
  241.         "Date() as Expr20, '" & _
  242.         cboPlatform.Text & "' as Expr15, #" & _
  243.         dateItemComplete & "# as Expr16, '" & _
  244.         Me.txtJazzNum.Text & "' as Expr18, " & _
  245.         ITprior & " as Expr21, " & _
  246.         teamPrior & " as Expr22, '" & _
  247.         Me.cmb_Category.Text & "' as Expr19, #" & _
  248.         dateUAT & "# as Expr30, '" & _
  249.         Me.cmb_cindList.Text & "' as Expr31, #" & _
  250.         dateIT & "# as Expr32, #" & _
  251.         dateProd & "# as Expr33, '" & _
  252.         Me.txt_relatedSDMRs.Text & "'"
  253.  
  254.     Debug.Print sSql
  255.  
  256.     dbs.Execute sSql, dbFailOnError
  257.     'DoCmd.RunSQL sSql
  258.  
  259.     If Not isZNum And Me.txtSDMR.Value <> "" Then
  260. dtReq:
  261.         dateRequested = _
  262.             InputBox("Please enter the date that " & _
  263.                 "you requested the SDMR/SOW number.", _
  264.                 "Next Item Estimated Completion Date")
  265.         If dateRequested > Date Then
  266.             MsgBox "Wrong Date entered. Please enter again."
  267.             GoTo dtReq
  268.         End If
  269.     Else
  270.         dateRequested = Date
  271.     End If
  272.     'Insert Row into CompletedStatus for the first completed Status
  273.     sSql = _
  274.         "INSERT INTO " & _
  275.         "FA678_CompletedStatus ( " & _
  276.             "PROJROWID, REFNUM, SDMR, Status, " & _
  277.             "Sequence, DateComplete, EnteredBy ) " & _
  278.         "Values ("
  279.     projRowID = _
  280.         DMax("ID", _
  281.              "FA678_PROJECT", _
  282.              "[FA678_PROJECT].[REFNUM]=" & REFNUM)
  283.     sSql = sSql & _
  284.         projRowID & "," & REFNUM & ", '"
  285.     If isZNum Then
  286.         sSql = sSql & _
  287.             "Z" & zNum & "', '"
  288.     Else
  289.         txtSDMR.SetFocus
  290.         sSql = sSql & _
  291.             txtSDMR.Text & "', '"
  292.     End If
  293.     sSql = sSql & _
  294.         "Project Requested" & "', '"
  295.     sSql = sSql & 1 & "','"
  296.     sSql = sSql & dateRequested & "', '"
  297.     sSql = sSql & UserRACFID & "')"
  298.     Debug.Print sSql
  299.     'DoCmd.RunSQL sSQL
  300.     dbs.Execute sSql, dbFailOnError
  301.  
  302.     If isZNum Then
  303.         myopenform "NEWPROJ", "Z" & zNum
  304.     Else
  305.         myopenform "NEWPROJ", REFNUM
  306.     End If
  307.  
  308.     DoCmd.Close acForm, "NewProject"
  309.     Exit Sub
  310.  
  311. errorHandler:
  312.     MsgBox "Error entering Project. " & Err.Description
  313.  
  314. End Sub
  315.  
  316. Private Sub Command338_Click()
  317.     Forms![MasterForm].Visible = True
  318.     DoCmd.Close acForm, "NewProject"
  319. End Sub
  320.  
  321. Private Sub cboOwnerName_Change()
  322.  
  323.     Debug.Print cboOwnerName.Text
  324.     Debug.Print cboOwnerName.Value
  325.  
  326. End Sub
Still a mess!
Nov 14 '18 #4
Bre035
42
I wish I could share the database which would probably help to understand the workings.
Nov 14 '18 #5
twinnyfo
3,653 Expert Mod 2GB
That's probably something well-beyond what anyone here would like to tackle in our spare time (and certainly beyond teh scope of this thread).

I have inherited DB's before, and the best advice is to keep using it as long as it works (but it seems like your doesn't) while at the same time, rebuilding and restructuring it from the ground up, and then roll out the new model.

It is a lot of hard work, but if you tackle it piece by piece, it can (and has been) done. We would also certainly be glad to work through those pieces as you come across them.

I know this doesn't answer the questoin at hand, though.

Is there any way the form can simply use the Table FA678_Project as its record source (like most other Forms would be set up)?
Nov 14 '18 #6
Bre035
42
It does but it also inserts the row into a status table to set statuses of the project at different intervals.
Nov 14 '18 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: mcstock | last post by:
before i begin to roll my own, can anybody recommend a web-based application design & tracking tool that provides: functional hierarchy/network definition work break-down structures module...
1
by: JJY | last post by:
Hi. I created a project a few days ago. Now, when I try to open the project, it gives me the following error. Unable to open Web project 'KB'. The file path 'C:\Inetpub\wwwroot\KB' does not...
0
by: Lucas Tam | last post by:
Hi all, I posted a message a couple minutes early and I'm not sure if it was too clear. I'm interested in adding custom messages to the validation control - is there an easy way to do this?...
0
by: Amil | last post by:
I have a reusable web application (e.g. portal). I often do a project copy to create new virtual apps. This works fine. Now I want to take that reusable app (not the solution and it's projects)...
2
by: Michel Couche | last post by:
I would like to build an ASP.Net project tracking tool for our department. I am looking for a starting template/tutorial that would help me taking the best approach. Any link to a tutorial ?...
4
by: Ben Amada | last post by:
Hello! There are a couple of visitors (out of dozens) to this web page who are causing the following error to occur: "Object reference not set to an instance of an object." I'm actually...
4
by: Raed Sawalha | last post by:
When I try to create a new Web Application Project, a message appear showing the following information: For this project is established as Web access default mode as as file shared resource, but...
0
by: Bennett Haselton | last post by:
It seems whenever I add a new field to a database table that's queried by DataAdapters in my project, I get run-time errors because I don't go back and re-generate all the DataAdapters to read the...
4
by: Smoothcoder | last post by:
Hi, there, smart guys! I have an AMD 64 3000+ based system, with Windows XP Professional on it; I recently installed Visual Studio 2003 Enterprise Architect; I created a new C# ASP.NET Web...
2
by: pghalex | last post by:
Hi, everyone! Couple things about me-I'm a manager for a marketing department for a financial services company, so I'm not really a "developer" but I do design a lot of macros and custom Word...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.