By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,736 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Problem relating to new records - not adding to table

damonreid
Expert 100+
P: 114
Access 2003
Microsoft Windows XP Pro

Hey,
I am currently pulling my hair out here.
I have a Form for adding new projects to a database, the only problem is that when I close the form it doesn't insert that information into the table. This is a major problem as it is suppose to use the information to populate several other tables with key information and open another form based on some of that information (in this case project number).

Default View: Single Form
Allow Edits: No
Allow Deletions: No
Allow Additions: Yes
Data Entry: Yes
Recordset Type: Dynaset

Command37 is my exit button (inherited)

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Command37_Click()
  5. 'On Error GoTo Err_Command37_Click
  6. 'Check which goovernance stream the project follows.
  7. 'If this field is null or a 0 length string then display the
  8. 'error message.
  9.     Select Case Governance_Stream
  10.     Case Null
  11.         MsgBox ("Please enter a valid Project number")
  12.     Case ""
  13.         MsgBox ("Please enter a valid Project number")
  14.     Case Else
  15.  
  16. 'Otherwise define variables
  17.         Dim stDocName As String
  18.         Dim stLinkCriteria As String
  19.  
  20. 'Assign the value of stDocName
  21.         stDocName = "Existing Project Details Information"
  22.  
  23. 'Set the criteria for opening the new form as the project number
  24. 'should be the same as the newly added project number
  25.         stLinkCriteria = "[Project Number]=" & "'" & Me![Project Number] & "'"
  26.  
  27. 'Create a new recordset to add a new dashbour with the same project number and
  28. 'governance stream as well as project category, the rest are default values.
  29.                 Dim MyRST As Recordset
  30.                 Set MyRST = CurrentDb.OpenRecordset("Dashboard", dbOpenDynaset)
  31.                 With MyRST
  32.                     .AddNew
  33.                     ![Project Number] = Me.Project_Number.Value
  34.                     ![Governance Stream] = Me.Governance_Stream.Value
  35.                     ![Scope] = "Same"
  36.                     ![Schedule] = "Same"
  37.                     ![Resources] = "Same"
  38.                     ![Budget] = "Same"
  39.                     ![Process] = "Same"
  40.                     ![Technology] = "Same"
  41.                     ![Change Acceptance] = "Same"
  42.                     ![Compliance] = "Same"
  43.                     ![Support] = "Same"
  44.                     ![Overall Project] = "Same"
  45.                     ![Current Status - Scope] = "2 - Amber"
  46.                     ![Current Status - Schedule] = "2 - Amber"
  47.                     ![Current Status - Resources] = "2 - Amber"
  48.                     ![Current Status - Budget] = "2 - Amber"
  49.                     ![Current Status - Process] = "2 - Amber"
  50.                     ![Current Status - Technology] = "2 - Amber"
  51.                     ![Current Status - Change Acceptance] = "2 - Amber"
  52.                     ![Current Status - Compliance] = "2 - Amber"
  53.                     ![Current Status - Support] = "2 - Amber"
  54.                     ![Current Status - Overall Project] = "2 - Amber"
  55.                     ![Governance Stream] = Governance_Stream
  56.                     ![Project Cat (OE)] = Project_Cat__OE_.Value
  57.                     ![Project Cat (CPMP)] = Project_Cat__CPMP_.Value
  58.                     ![Project Cat (Sys)] = Project_Cat__Sys_.Value
  59.                     .Update
  60.                 End With
  61.                 MyRST.Close
  62.  
  63. 'Initial project risk added to the project, default values
  64.                 Dim MyRST3 As Recordset
  65.                 Set MyRST3 = CurrentDb.OpenRecordset("Risk Log", dbOpenDynaset)
  66.                 With MyRST3
  67.                     .AddNew
  68.                     ![Risk Description] = "Project Approval Pending"
  69.                     ![Raised] = Me.Request_Date.Value
  70.                     ![Project Number] = Me.Project_Number.Value
  71.                     .Update
  72.                 End With
  73.                 MyRST3.Close
  74.  
  75. 'Initial score agreement added.
  76. 'CurrentUser is assigned as the requester, the rest of the values are default.
  77.                 Dim MyRST5 As Recordset
  78.                 Set MyRST5 = CurrentDb.OpenRecordset("Scope Change", dbOpenDynaset)
  79.                 With MyRST5
  80.                     .AddNew
  81.                     ![Associated Project Number] = Me.Project_Number.Value
  82.                     ![Request Date] = Me.Request_Date.Value
  83.                     ![Requested By] = CurrentUser
  84.                     ![Change Details] = "Original Scope Requirments Agreed"
  85.                     ![Impact Budget] = False
  86.                     ![Impact Schedule] = False
  87.                     ![Approved] = True
  88.                     ![Approval Date] = Me.Request_Date.Value
  89.                     ![Approved By] = CurrentUser
  90.                     .Update
  91.                 End With
  92.                 MyRST5.Close
  93. 'Close the form once updated
  94.             DoCmd.Close
  95. 'Open the form with the same name as stDocName string
  96.             DoCmd.OpenForm stDocName, , , stLinkCriteria
  97.  
  98.         End Select
  99.  
  100. End Sub
I get the following error when I attempt to do this:
Run-time error '2427' :

Expression has no value.

This appears to be on the .update part of the code as it has not added the information to the Project Details table and as such the required fields in the other tables are referencing a project number that doesn't exist. I just can't get it to update for some reason!
Sep 5 '07 #1
Share this Question
Share on Google+
1 Reply


damonreid
Expert 100+
P: 114
Disregard, was trying to duplicate a record name (several fields must be unique in this database) and as such it would not add a new record.

Damon
Sep 5 '07 #2

Post your reply

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