Trouble with my forms due to run time error 2427

[washadog]

Hi,

I having trouble finding code for error 2427 when I entered an expression that have no value. I have six forms with datasheet in each one. If there no value in the datasheet, then run time error will trigger. What is my options on this? This project is very important because this will make or break my career advancement. I want to do this right. I need some help with this. Thank you.

 

[theDBguy]

Hi. Can you post the code getting the error?

 

[washadog]

I am working big military project called procurement tracking system. There are 6 primary roles for different users.
1. Data entry users- input data
2. Purchasing Agent
3. DOL/Warehouse user
4. Utility Shops Supervisor user
5. Accounting agent user
6. Status Report users

There are 6 different forms above. There are 7 tables on this project with 21 different queries. There are 25 different reports. This project is massive. I pretty much did this on my own. I did a lot of reading,you tube and previous vb experience to create this project. The accounting form has a datasheet view with it. Here is the code for this accounting agent form below. Can I do a public code event? I also have 5 other forms with similar codes as well. Do you need those as well? I am not sure why Microsoft did that on purpose. I have no idea to code this and where to.

Option Compare Database

Private Sub cboProcurement_Status_Click()
    'This will create audit trail when user edit record in the Accounting agent form.
     Call AuditChanges("LA # GeneratorID", "EDIT")
         
    'if user click selected value in procurement statue combo box then certain textboxes will
    'be unlock to edit record.
    If Me.cboProcurement_Status.Value = "Close out" Then
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.SetFocus
        Me.txtCost.Locked = False
        Me.txtNotes.Locked = False
        Me.txtCOMPLETED_DATE.Locked = True
        Me.txtNotes = ""
        Me.txtStatus_Date = Now
        Me.txtStatus_Date.Locked = False
    End If
    If Me.cboProcurement_Status.Value = "Completed" Then
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = True
        Me.txtCOMPLETED_DATE.Locked = True
    End If
    If Me.cboProcurement_Status.Value = "Return to PA follow-up" Then
        Me.txtNotes.SetFocus
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = False
        Me.txtCOMPLETED_DATE.Locked = True
        Me.txtNotes = ""
        Me.txtStatus_Date = Now
        Me.txtStatus_Date.Locked = True
    End If
    If Me.cboProcurement_Status.Value = "Return to SS Follow-up" Then
        Me.txtNotes.SetFocus
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = False
        Me.txtCOMPLETED_DATE.Locked = True
        Me.txtNotes = ""
        Me.txtStatus_Date = Now
        Me.txtStatus_Date.Locked = True
    End If
    If Me.cboProcurement_Status.Value = "Forward to Accounting Agent" Then
        Me.txtCost.SetFocus
        Me.txtCost.Locked = False
        Me.cboProcurement_Status.Locked = False
        Me.txtNotes.Locked = False
    End If
End Sub

Private Sub cmdEdit_Click()
    'when a user click combo box value in procurement status, then some textboxes will be
    'available to edit.
    If Me.cboProcurement_Status.Value = "Completed" Then
        Me.AllowEdits = True
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = True
        Me.cboProcurement_Status.Locked = False
        Me.txtNotes.Locked = True
    End If
    If Me.cboProcurement_Status.Value = "Close out" Then
        Me.AllowEdits = True
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = False
        Me.cboProcurement_Status.Locked = False
        Me.txtNotes.Locked = False
    End If
    If Me.cboProcurement_Status.Value = "Return to PA follow-up" Then
        Me.AllowEdits = True
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = True
        Me.cboProcurement_Status.Locked = False
        Me.txtNotes.Locked = False
    End If
    If Me.cboProcurement_Status.Value = "Forward to Accounting Agent" Then
        Me.AllowEdits = True
        Me.txtCost.Locked = True
        Me.cboProcurement_Status.Locked = False
        Me.txtNotes.Locked = True
    End If
    If Me.cboProcurement_Status.Value = "Return to SS Follow-up" Then
        Me.AllowEdits = True
        Me.txtNotes.SetFocus
        Me.txtCost.Locked = True
        Me.cboProcurement_Status.Locked = False
        Me.txtNotes.Locked = False
    End If
End Sub

Private Sub DESCRIPTION_Enter()
'Cursor point end of note when viewing description field.
    Me!DESCRIPTION.SelStart = Me!DESCRIPTION.SelLength
End Sub

Private Sub Form_AfterUpdate()
    'In a client database, the RefreshRecord method is equivalent to the Refresh method of the Form object.
    'In a web database, see the RefreshRecord macro action.
    DoCmd.RefreshRecord
    'Carries out the Requery action in Visual Basic.
    DoCmd.Requery
    
   'This will create audit trail when user edit record in the purchasing agent form.
   Call AuditChanges("LA # GeneratorID", "EDIT")
   'if a user left any textbox or combo box blank or null on the purchasing agent form, then
   'a list of custom error messages for each field will be display. It will also set focus to
   'that field until required value is met.
    
    If Me.cboProcurement_Status.Value = "Close out" Then
            If IsNull(Me.txtCost) Then
                MsgBox "You must enter Final cost of Material(s) or Service", vbInformation, "Cost Field"
                Cancel = True
                Me.txtCost.SetFocus
                End If
            If Len(Me.txtNotes.Value & vbNullString) = 0 Then
                MsgBox "You must enter notes.", vbInformation, "Notes Field"
                Cancel = True
                Me.txtNotes.SetFocus
            End If
    If Me.cboProcurement_Status.Value = "Return to PA follow-up" Then
        If Len(Me.txtNotes.Value & vbNullString) = 0 Then
                MsgBox "You must enter notes.", vbInformation, "Notes Field"
                Cancel = True
                Me.txtNotes.SetFocus
         End If
         If Me.cboProcurement_Status.Value = "Return to SS Follow-up" Then
        If Len(Me.txtNotes.Value & vbNullString) = 0 Then
                MsgBox "You must enter notes.", vbInformation, "Notes Field"
                Cancel = True
                Me.txtNotes.SetFocus
         End If
     End If
    End If
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    'This will create audit trail when user edit record in the purchasing agent form.
   Call AuditChanges("LA # GeneratorID", "EDIT")
   'if a user left any textbox or combo box blank or null on the purchasing agent form, then
   'a list of custom error messages for each field will be display. It will also set focus to
   'that field until required value is met.
   If Me.cboProcurement_Status.Value = "Close out" Then
            If IsNull(Me.txtCost) Then
                MsgBox "You must enter Final cost of Material(s) or Service", vbInformation, "Cost Field"
                Cancel = True
                Me.txtCost.SetFocus
                End If
            If Len(Me.txtNotes.Value & vbNullString) = 0 Then
                MsgBox "You must enter notes.", vbInformation, "Notes Field"
                Cancel = True
                Me.txtNotes.SetFocus
            End If
    Else
    If Me.cboProcurement_Status.Value = "Return to PA follow-up" Then
        If Len(Me.txtNotes.Value & vbNullString) = 0 Then
                MsgBox "You must enter notes.", vbInformation, "Notes Field"
                Cancel = True
                Me.txtNotes.SetFocus
         End If
    Else
    If Me.cboProcurement_Status.Value = "Return to SS Follow-up" Then
        If Len(Me.txtNotes.Value & vbNullString) = 0 Then
                MsgBox "You must enter notes.", vbInformation, "Notes Field"
                Cancel = True
                Me.txtNotes.SetFocus
         End If
     End If
    End If
End If
' This procedure checks to see if the data on the form has
   ' changed. If the data has changed, the procedure prompts the
   ' user to continue with the save operation or to cancel it. Then
   ' the action that triggered the BeforeUpdate event is completed.

   Dim ctl As Control
   On Error GoTo Err_BeforeUpdate
   ' The Dirty property is True if the record has been changed.
   If Me.Dirty Then
      ' Prompt to confirm the save operation.
      If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
              "Save Record") = vbNo Then
         Me.Undo
      End If
   End If

Exit_BeforeUpdate:
   Exit Sub

Err_BeforeUpdate:
   MsgBox Err.Number & "No Record(s)", vbInformation & Err.DESCRIPTION
   Resume Exit_BeforeUpdate
End Sub

Private Sub Form_Activate()
     'The Requery method does one of the following:
    'Reruns the query on which the form or control is based.
    'Displays any new or changed records or removes deleted records from the table on which the form or control is based.
    'Updates records displayed based on any changes to the Filter property of the form.
    'Carries out the Requery action in Visual Basic.
    Me.Requery
    Me.Refresh
    If Me.cboProcurement_Status.Value = "Close out" Then
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = False
        Me.txtNotes.Locked = False
        Me.txtCOMPLETED_DATE.Locked = True
    Else
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = True
        Me.txtCOMPLETED_DATE.Locked = True
        
    End If
    If Me.cboProcurement_Status.Value = "Completed" Then
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = True
        Me.txtCOMPLETED_DATE.Locked = True
    End If
    If Me.cboProcurement_Status.Value = "Return to PA follow-up" Then
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = False
        Me.txtCOMPLETED_DATE.Locked = True
    Else
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = True
        Me.txtCOMPLETED_DATE.Locked = True
    End If
    If Me.cboProcurement_Status.Value = "Forward to Accounting Agent" Then
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = False
        Me.txtCOMPLETED_DATE.Locked = True
    Else
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = True
        Me.txtCOMPLETED_DATE.Locked = True
    End If
     If Me.cboProcurement_Status.Value = "Return to SS Follow-up" Then
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = False
        Me.txtCOMPLETED_DATE.Locked = True
    Else
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = True
        Me.txtCOMPLETED_DATE.Locked = True
    End If
End Sub
Private Sub Form_Current()
    'no edit allow on the form when the user click on another record on datasheet.
    'If the user click on edit button,
    'then user able to edit record.  It will set focus to the first field combo procurement status.
    Me.AllowEdits = False
    Me.cboProcurement_Status.SetFocus
End Sub

Private Sub Form_GotFocus()
    'The Requery method does one of the following:
    'Reruns the query on which the form or control is based.
    'Displays any new or changed records or removes deleted records
    'from the table on which the form or control is based.
    'Updates records displayed based on any changes to the Filter property of the form.
    'These events occur when the focus moves in response to a user action, such as pressing
    'the TAB key or clicking the
    'object, or when you use the SetFocus method in Visual Basic or the SelectObject,
    'GoToRecord, GoToControl, or GoToPage action in a macro.
    Me.Requery
End Sub
 

Private Sub Form_Load()
    'when data entry user log in, the data entry form load will be lock textboxes regardless combo box value.
    'This prevent unwanted data entry error. There is set focus on the first field
    'combo box procurement status.
    If Me.cboProcurement_Status.Value = "Close out" Then
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = True
        Me.txtCOMPLETED_DATE.Locked = True
    End If
    If Me.cboProcurement_Status.Value = "Completed" Then
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = True
        Me.txtCOMPLETED_DATE.Locked = True
    End If
    If Me.cboProcurement_Status.Value = "Return to PA follow-up" Then
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = True
        Me.txtCOMPLETED_DATE.Locked = True
    End If
    If Me.cboProcurement_Status.Value = "Forward to Accounting Agent" Then
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = True
        Me.txtCOMPLETED_DATE.Locked = True
    End If
    If Me.cboProcurement_Status.Value = "Return to SS Follow-up" Then
        Me.cboProcurement_Status.SetFocus
        Me.txtCost.Locked = True
        Me.txtNotes.Locked = True
        Me.txtCOMPLETED_DATE.Locked = True
    End If
End Sub

Private Sub Form_Undo(Cancel As Integer)
    'if the user use esc button, then all fields will be lock and return to old value.
    If Me.cboProcurement_Status.Value = "Completed" Then
       Me.AllowEdits = False
    End If
    If Me.cboProcurement_Status.Value = "Close out" Then
       Me.AllowEdits = False
    End If
    If Me.cboProcurement_Status.Value = "Forward to Accounting Agent" Then
       Me.AllowEdits = False
    End If
    If Me.cboProcurement_Status.Value = "Return to SS Follow-up" Then
       Me.AllowEdits = False
    End If
    If Me.cboProcurement_Status.Value = "Return to PA Follow-up" Then
       Me.AllowEdits = False
    End If
       
End Sub


Private Sub txtNotes_Enter()
'Cursor point at the end of notes to edit notes.
    Me!txtNotes.SelStart = Me!txtNotes.SelLength
End Sub

 

[theDBguy]

Hi. Thanks. Which line is getting highlighted when you click Debug?

 

[washadog]

It happen on Private Sub Form_AfterUpdate() and Private Sub Form_Load()
It start at If Me.cboProcurement_Status.Value = "Close out" line. That is where i get the error message.

 

[washadog]

I can send you the attachment. Let me know. Many thanks.

 

[theDBguy]

I can send you the attachment. Let me know. Many thanks.

Hi. If you can’t attach it here, you can email it to me. Address in my profile or website.

 

[washadog]

I send you a link to my project. The project is too big over 35 MB. Let me know if you got the file. Thank you.

 

[isladogs]

I've added code tags to make your post more readable.
Whilst DBG has offered support outside the forum thread, normally we ask members to post stripped down versions of their databases to the forum.
Doing that means you get a range of responses. Also others benefit from the exchange of information.

A few observations
1. ALWAYS add the line Option Explicit as the second line of each code module to ensure variables are always explicitly defined. There is an option to require variable declarations in VBE options. Tick it.
2. There is a lot of very similar code that could be simplified.
If me.cboProcurement.valu = …. in most of your code events
Using Select Case would be better. Easier to read/maintain and probably marginally faster.
You could use the Tag property to set groups of controls visible/hidden.
See my example database which shows the idea http://www.mendipdatasystems.co.uk/set-controls/4594398114
3. I would recommend adding error handling to all procedures
4. From your description, you could possibly combine all 6 forms into one with different controls possibly displayed depending on the user role. If this were possible it would further simplify your development work.
4. Regarding error 2427 (expression has no value), you may find this link useful
https://social.msdn.microsoft.com/Forums/office/en-US/b092c1dd-2003-4488-a7b9-b536f973e064/handling-runtime-error-2427-you-entered-an-expression-that-has-no-value

Hope that helps

 

[theDBguy]

I send you a link to my project. The project is too big over 35 MB. Let me know if you got the file. Thank you.

Hi. Since I can't upload it back to your OneDrive folder, here's what I did to avoid the error.

Private Sub Form_Load()
    'when data entry user log in, the data entry form load will be lock textboxes regardless combo box value.
    'This prevent unwanted data entry error. There is set focus on the first field
    'combo box procurement status.
    
    'thedbguy@gmail.com
    '5/26/2019
    'check if form is empty to avoid error
    If Me.Recordset.RecordCount > 0 Then
        If Me.cboProcurement_Status.Value = "Close out" Then
            Me.cboProcurement_Status.SetFocus
            Me.txtCost.Locked = True
            Me.txtNotes.Locked = True
            Me.txtCOMPLETED_DATE.Locked = True
        End If
        If Me.cboProcurement_Status.Value = "Completed" Then
            Me.cboProcurement_Status.SetFocus
            Me.txtCost.Locked = True
            Me.txtNotes.Locked = True
            Me.txtCOMPLETED_DATE.Locked = True
        End If
        If Me.cboProcurement_Status.Value = "Return to PA follow-up" Then
            Me.cboProcurement_Status.SetFocus
            Me.txtCost.Locked = True
            Me.txtNotes.Locked = True
            Me.txtCOMPLETED_DATE.Locked = True
        End If
        If Me.cboProcurement_Status.Value = "Forward to Accounting Agent" Then
            Me.cboProcurement_Status.SetFocus
            Me.txtCost.Locked = True
            Me.txtNotes.Locked = True
            Me.txtCOMPLETED_DATE.Locked = True
        End If
        If Me.cboProcurement_Status.Value = "Return to SS Follow-up" Then
            Me.cboProcurement_Status.SetFocus
            Me.txtCost.Locked = True
            Me.txtNotes.Locked = True
            Me.txtCOMPLETED_DATE.Locked = True
        End If
    End If
End Sub

Hope it helps...

 

[washadog]

When the accounting form load, the error message did not show up. I send you email on this problem with error message when before update. Let me know. Thanks.