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. - 1.Private Sub btnCreateProject_Click()
-
2. Dim dbs As DAO.Database
-
3. Dim sSql As String
-
4. Dim projRowID As Integer
-
5. Dim CompletionDate As String
-
6. Dim dateRequested As String
-
7. Dim dateItemComplete As Date
-
8. Dim yNumber As Integer
-
9. Dim yName As String
-
10. Dim REFNUM As Integer
-
11. Dim ITprior As Variant
-
12. Dim teamPrior As Variant
-
13. Dim dateUAT As Date
-
14. Dim dateProd As Date
-
15. Dim dateIT As Date
-
16.
-
17.
-
18. REFNUM = DMax("REFNUM", "FA678_PROJECT") + 1
-
19. If UserID = "" Then
-
20. UserID = 0
-
21. End If
-
22.
-
23. 'IT priority null?
-
24. If IsNull(Me.txt_ITprior.Value) Then
-
25. ITprior = "Null"
-
26. Else
-
27. ITprior = Me.txt_ITprior.Value
-
28. End If
-
29. 'team priority null?
-
30. If IsNull(Me.txt_teamPrior.Value) Then
-
31. teamPrior = "Null"
-
32. Else
-
33. teamPrior = Me.txt_teamPrior.Value
-
34. End If
-
35.
-
36. 'UAT Date null?
-
37. If IsNull(Me.txt_DtUAT.Value) Then
-
38. dateUAT = #1/1/1901#
-
39. Else
-
40. dateUAT = Me.txt_DtUAT.Value
-
41. End If
-
42.
-
43. 'Prod Date null?
-
44. If IsNull(Me.txt_DtProd.Value) Then
-
45. dateProd = #1/1/1901#
-
46. Else
-
47. dateProd = Me.txt_DtProd.Value
-
48. End If
-
49.
-
50. 'IT Date null?
-
51. If IsNull(Me.txt_DtToIT.Value) Then
-
52. dateIT = #1/1/1901#
-
53. Else
-
54. dateIT = Me.txt_DtToIT.Value
-
55. End If
-
56.
-
57.
-
58.
-
59. On Error GoTo errorHandler
-
60. Set dbs = CurrentDb
-
61.
-
62. If Me.txtZnum = 0 Then
-
63. isZNum = False
-
64. Else
-
65. isZNum = True
-
66. End If
-
-
-
6 1266
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!
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.
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. - Private Sub Form_Load()
-
On Error Resume Next
-
If UserRACFID = "" Then
-
UserRACFID = Environ("USERNAME")
-
End If
-
-
If UserName = "" Then
-
getInitialInfo (UCase(UserRACFID))
-
End If
-
-
If Me.txtZnum = 0 Then
-
isZNum = False
-
Else
-
isZNum = True
-
End If
-
-
Me.cboAnalyst.SetFocus
-
Me.cboAnalyst.Value = UserName
-
-
Me.txtRACF.SetFocus
-
Me.txtRACF.Value = UCase(UserRACFID)
-
-
End Sub
-
-
Private Sub btnCreateProject_Click()
-
Dim dbs As DAO.Database
-
Dim sSql As String
-
Dim projRowID As Integer
-
Dim CompletionDate As String
-
Dim dateRequested As String
-
Dim dateItemComplete As Date
-
Dim yNumber As Integer
-
Dim yName As String
-
Dim REFNUM As Integer
-
Dim ITprior As Variant
-
Dim teamPrior As Variant
-
Dim dateUAT As Date
-
Dim dateProd As Date
-
Dim dateIT As Date
-
-
REFNUM = DMax("REFNUM", "FA678_PROJECT") + 1
-
If UserID = "" Then
-
UserID = 0
-
End If
-
-
'IT priority null?
-
If IsNull(Me.txt_ITprior.Value) Then
-
ITprior = "Null"
-
Else
-
ITprior = Me.txt_ITprior.Value
-
End If
-
-
'team priority null?
-
If IsNull(Me.txt_teamPrior.Value) Then
-
teamPrior = "Null"
-
Else
-
teamPrior = Me.txt_teamPrior.Value
-
End If
-
-
'UAT Date null?
-
If IsNull(Me.txt_DtUAT.Value) Then
-
dateUAT = #1/1/1901#
-
Else
-
dateUAT = Me.txt_DtUAT.Value
-
End If
-
-
'Prod Date null?
-
If IsNull(Me.txt_DtProd.Value) Then
-
dateProd = #1/1/1901#
-
Else
-
dateProd = Me.txt_DtProd.Value
-
End If
-
-
'IT Date null?
-
If IsNull(Me.txt_DtToIT.Value) Then
-
dateIT = #1/1/1901#
-
Else
-
dateIT = Me.txt_DtToIT.Value
-
End If
-
-
On Error GoTo errorHandler
-
Set dbs = CurrentDb
-
-
If Me.txtZnum = 0 Then
-
isZNum = False
-
Else
-
isZNum = True
-
End If
-
-
'Check for required Fields to be filled
-
'txtSDMR.SetFocus
-
If Not isZNum Then
-
txtSDMR.SetFocus
-
If txtSDMR.Text = "" Then
-
If MsgBox("Is this a Yxxxxx project?", _
-
vbYesNo, _
-
"Blank SDMR/SOW") = vbYes Then
-
yName = UCase(InputBox( _
-
"Please Enter the Yxxxx ID for the project."))
-
If yName = "" Then Exit Sub
-
yNumber = getNextNum(yName)
-
txtSDMR.Text = yName & "-" & yNumber
-
Me.Refresh
-
Else
-
MsgBox "No SDMR entered. Reference # will be: " & REFNUM
-
'Exit Sub
-
End If
-
End If
-
Else
-
MsgBox "No SDMR entered. Reference # will be: " & REFNUM
-
End If
-
-
cboTeam.SetFocus
-
If cboTeam.Text = "" Then
-
MsgBox "Please Enter Team."
-
Exit Sub
-
End If
-
-
Me.cboPriority.SetFocus
-
If Me.cboPriority.Text = "" Then
-
MsgBox "Please Enter Priority."
-
Exit Sub
-
End If
-
-
Me.cboOwnerName.SetFocus
-
If Me.cboOwnerName.Text = "" Then
-
MsgBox "Please Enter Requestor."
-
Exit Sub
-
End If
-
-
Me.cboAnalyst.SetFocus
-
If Me.cboAnalyst.Text = "" Then
-
MsgBox "Please Enter Analyst."
-
Exit Sub
-
End If
-
-
Me.cboCountry.SetFocus
-
If Me.cboCountry.Text = "" Then
-
MsgBox "Please Enter Country."
-
Exit Sub
-
End If
-
-
Me.cboPlatform.SetFocus
-
If Me.cboPlatform.Text = "" Then
-
MsgBox "Please Enter Platform."
-
Exit Sub
-
End If
-
-
Me.txtTitle.SetFocus
-
If Me.txtTitle.Text = "" Then
-
MsgBox "Please Enter Title."
-
Exit Sub
-
End If
-
-
Me.txtScope.SetFocus
-
If Me.txtScope.Text = "" Then
-
MsgBox "Please Enter scope."
-
Exit Sub
-
End If
-
-
Me.cmb_Category.SetFocus
-
If Me.cmb_Category.Text = "" Then
-
MsgBox "Please Enter Category."
-
Exit Sub
-
End If
-
-
If Not isZNum Then
-
dtCompl:
-
dateItemComplete = _
-
InputBox("When do you estimate Spec Completion Date?", _
-
"Business Specs Estimated date")
-
If dateItemComplete < Date Then
-
MsgBox "Wrong Date entered. Please enter again."
-
GoTo dtCompl
-
End If
-
Else
-
dateItemComplete = Date
-
End If
-
-
'Insert Row into Project
-
sSql = _
-
"INSERT INTO FA678_Project ( " & _
-
"REFNUM, SDMR , Team, Priority, " & _
-
"PriorityID, RequestorName, AnalystName, " & _
-
"AnalystID, AnalystRACFID, AnalystSupport, " & _
-
"programmerNames, Phase, PHASEID, Title, " & _
-
"PercentComplete, Scope, Country, " & _
-
"DTPROJECTCREATED, Platform, estCompleteDate, " & _
-
"JazzNum, ITPRIORITY, TEAMPRIORITY, " & _
-
"CATEGORY, DTESTIMATETEST, CINDYSLIST, " & _
-
"DATETOIT, DTESTIMATEPROD, RELATEDSDMRS ) "
-
sSql = sSql & _
-
"Select " & REFNUM & " as Expr100, "
-
If Not isZNum Then
-
txtSDMR.SetFocus
-
End If
-
If isZNum Then
-
Me.txtTitle.SetFocus
-
zNum = GetZNum(Me.txtTitle.Text)
-
If zNum = "ERROR" Then
-
MsgBox "Error entering Project. Try again or contact db Manager."
-
Exit Sub
-
End If
-
sSql = sSql & _
-
"'" & "Z" & zNum & "' as Expr17, '"
-
Else
-
sSql = sSql & _
-
"'" & txtSDMR.Text & "' as Expr17, '"
-
End If
-
-
cboTeam.SetFocus
-
sSql = sSql & _
-
cboTeam.Text & "' as Expr1, '"
-
cboPriority.SetFocus
-
sSql = sSql & _
-
cboPriority.Text & "' as Expr2, '"
-
Select Case UCase(cboPriority.Text)
-
Case "HIGH"
-
sSql = sSql & "1" & "' as Expr3, '"
-
Case "MEDIUM"
-
sSql = sSql & "2" & "' as Expr3, '"
-
Case "LOW"
-
sSql = sSql & "3" & "' as Expr3, '"
-
Case Else
-
sSql = sSql & "4" & "' as Expr3, '"
-
End Select
-
cboOwnerName.SetFocus
-
sSql = sSql & _
-
cboOwnerName.Text & "' as Expr4, '" & _
-
cboAnalyst.Text & "' as Expr5, " & _
-
UserID & " as Expr6, '" & _
-
Me.txtRACF.Text & "' as Expr7, '" & _
-
txtAnalystSupport.Text & "' as Expr8, '" & _
-
txtProgrammers.Text & "' as Expr9, '" & _
-
"Writing Business Specs' as Expr10, " & _
-
"2 as Expr34, '" & _
-
txtTitle.Text & "' as Expr11, " & _
-
"5 as Expr12, '" & _
-
txtScope.Text & "' as Expr13, '" & _
-
cboCountry.Text & "' as Expr14, " & _
-
"Date() as Expr20, '" & _
-
cboPlatform.Text & "' as Expr15, #" & _
-
dateItemComplete & "# as Expr16, '" & _
-
Me.txtJazzNum.Text & "' as Expr18, " & _
-
ITprior & " as Expr21, " & _
-
teamPrior & " as Expr22, '" & _
-
Me.cmb_Category.Text & "' as Expr19, #" & _
-
dateUAT & "# as Expr30, '" & _
-
Me.cmb_cindList.Text & "' as Expr31, #" & _
-
dateIT & "# as Expr32, #" & _
-
dateProd & "# as Expr33, '" & _
-
Me.txt_relatedSDMRs.Text & "'"
-
-
Debug.Print sSql
-
-
dbs.Execute sSql, dbFailOnError
-
'DoCmd.RunSQL sSql
-
-
If Not isZNum And Me.txtSDMR.Value <> "" Then
-
dtReq:
-
dateRequested = _
-
InputBox("Please enter the date that " & _
-
"you requested the SDMR/SOW number.", _
-
"Next Item Estimated Completion Date")
-
If dateRequested > Date Then
-
MsgBox "Wrong Date entered. Please enter again."
-
GoTo dtReq
-
End If
-
Else
-
dateRequested = Date
-
End If
-
'Insert Row into CompletedStatus for the first completed Status
-
sSql = _
-
"INSERT INTO " & _
-
"FA678_CompletedStatus ( " & _
-
"PROJROWID, REFNUM, SDMR, Status, " & _
-
"Sequence, DateComplete, EnteredBy ) " & _
-
"Values ("
-
projRowID = _
-
DMax("ID", _
-
"FA678_PROJECT", _
-
"[FA678_PROJECT].[REFNUM]=" & REFNUM)
-
sSql = sSql & _
-
projRowID & "," & REFNUM & ", '"
-
If isZNum Then
-
sSql = sSql & _
-
"Z" & zNum & "', '"
-
Else
-
txtSDMR.SetFocus
-
sSql = sSql & _
-
txtSDMR.Text & "', '"
-
End If
-
sSql = sSql & _
-
"Project Requested" & "', '"
-
sSql = sSql & 1 & "','"
-
sSql = sSql & dateRequested & "', '"
-
sSql = sSql & UserRACFID & "')"
-
Debug.Print sSql
-
'DoCmd.RunSQL sSQL
-
dbs.Execute sSql, dbFailOnError
-
-
If isZNum Then
-
myopenform "NEWPROJ", "Z" & zNum
-
Else
-
myopenform "NEWPROJ", REFNUM
-
End If
-
-
DoCmd.Close acForm, "NewProject"
-
Exit Sub
-
-
errorHandler:
-
MsgBox "Error entering Project. " & Err.Description
-
-
End Sub
-
-
Private Sub Command338_Click()
-
Forms![MasterForm].Visible = True
-
DoCmd.Close acForm, "NewProject"
-
End Sub
-
-
Private Sub cboOwnerName_Change()
-
-
Debug.Print cboOwnerName.Text
-
Debug.Print cboOwnerName.Value
-
-
End Sub
Still a mess!
I wish I could share the database which would probably help to understand the workings.
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)?
It does but it also inserts the row into a status table to set statuses of the project at different intervals.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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?...
|
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)...
|
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 ?...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |