Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: Force entry in forms but not tables

    Hi,
    I have a form that gets its data from an an external source. Some of the data that populates the underlying table is missing from the import and requires tha the user complete this missing data on a form.
    How can I make sure that when a user goes into a record using the form, that they fill in the missing data?

    Regards
    John

  2. #2
    Join Date
    May 2004
    Posts
    6
    By using the forms BeforeUpdate event
    Code:
         If IsNull(SomeField) or IsNull(SomeOtherField) Then
             msgbox "Please enter required data", vbOKOnly
         End If

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi,
    Thanks for the prompt response, unfortunately the code doesn't seem to be working as the you can exit the form without any error message.

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull([Latest_Chased_Date]) Or IsNull([DiaryDate]) Then
    MsgBox "Please enter required data", vbOKOnly
    End If
    End Sub

    Any ideas?

    Regards
    John

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Sticker
    Hi,
    Thanks for the prompt response, unfortunately the code doesn't seem to be working as the you can exit the form without any error message.

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull([Latest_Chased_Date]) Or IsNull([DiaryDate]) Then
    MsgBox "Please enter required data", vbOKOnly
    End If
    End Sub

    Any ideas?

    Regards
    John
    John,

    Put a test in your Unload event (Form_Unload method) and make a validation function and call it ... Ex:

    Code:
    Function FieldValidations() As Boolean
        On Error GoTo Err_FV
        
        Dim ErrorMsg As String
        Dim AllIsOK As Boolean
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        AllIsOK = True
        
        ' Check for resetting the status
        If PO_Status <> StatusGroup.Value Then
            If PO_Status = PO_VOIDED_STAT And StatusGroup.Value <> PO_InProcess_STAT Then
                AllIsOK = False
                ErrorMsg = "The status is invalid after reactivating this purchase order."
            ElseIf PO_Status > StatusGroup.Value Then
                AllIsOK = False
                ErrorMsg = "The status cannot be rolled back to a previous state."
            End If
        End If
        
        If DtRequiredTxt.Value & "" = "" Then
            AllIsOK = False
            ErrorMsg = "The Date Required is missing." & vbCrLf
        ElseIf Not IsDate(DtRequiredTxt.Value) Then
            AllIsOK = False
            ErrorMsg = "The Date Required is invalid." & vbCrLf
        End If
        If SugSupplierComboBox.Value & "" = "" Then
            AllIsOK = False
            ErrorMsg = ErrorMsg & "The supplier has not been selected." & vbCrLf
        End If
        If RequestedByComboBox.Value & "" = "" Then
            AllIsOK = False
            ErrorMsg = ErrorMsg & "The requestor has not been selected." & vbCrLf
        End If
        If DeliverToTxt.Value & "" = "" Then
            AllIsOK = False
            ErrorMsg = ErrorMsg & "Where to deliver is missing." & vbCrLf
        End If
        If ItemCount < 1 Then
            AllIsOK = False
            ErrorMsg = ErrorMsg & "There must be at least (1) item for this Purchase Order." & vbCrLf
        End If
        
        If StatusGroup.Value = 1 Then       ' In Process
            If PAPR_Txt.Value & "" = "" Then
                AllIsOK = False
                ErrorMsg = ErrorMsg & "The olimpic id is missing." & vbCrLf
            End If
            If BlanketTxt.Value & "" = "" Then
                AllIsOK = False
                ErrorMsg = ErrorMsg & "The blanket # is missing." & vbCrLf
            End If
            If BTypeComboBox.Value & "" = "" Then
                AllIsOK = False
                ErrorMsg = ErrorMsg & "The blanket type has not been selected." & vbCrLf
            End If
            If ExpirationTxt.Value & "" = "" Then
                AllIsOK = False
                ErrorMsg = ErrorMsg & "The expiration date is missing." & vbCrLf
            ElseIf Not IsDate(ExpirationTxt.Value) Then
                AllIsOK = False
                ErrorMsg = ErrorMsg & "The expiration date is invalid." & vbCrLf
            End If
        ElseIf StatusGroup.Value = 2 Then   ' Validated
            If PO_Txt.Value & "" = "" Then
                AllIsOK = False
                ErrorMsg = ErrorMsg & "The purchase order # is missing." & vbCrLf
            End If
        End If
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenForwardOnly
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        'Validate that each item has a W.O. and a Quantity.
        SQLString = "SELECT * FROM " & RequestedItemsTbl & ";"
        TrgRecSet.Open SQLString, FormsConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            Do While TrgRecSet.EOF = False
                If (TrgRecSet.Fields(6).Value <= 0) Or (TrgRecSet.Fields(15).Value & "" = "") Then
                    AllIsOK = False
                    ErrorMsg = ErrorMsg & "There is at least one missing work order # or quantity not greater than (0) zero."
                    Exit Do
                End If
                TrgRecSet.MoveNext
            Loop
        End If
        TrgRecSet.Close
        FormsConnect.Close
        
        Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        
        If Not AllIsOK Then MsgBox ErrorMsg, vbCritical, "System Monitor"
        FieldValidations = AllIsOK
        
    Exit_FV:
        Exit Function
        
    Err_FV:
        
        MsgBox Err.Number & ": " & Err.Description
        FieldValidations = False
        Resume Exit_FV
    End Function
    All your do is something like:

    If Not FieldValidations Then
    Cancel=1
    Exit Sub
    Endif

    The Unload event has a Cancel paramenter that allows you to prevent exiting ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I have a similar situation where the record must have entries in certain fields before a procedure can be run and there are about 20 fields.

    I have a macro that opens a form if any of the fields in question are blank. The form that opens displays all of the fields in question and any of the fields which are blank have their background changed to red.

    In my case the macro that does this is part of a macro that opend another form which contains the controls for various procedures. But you could have in run on current.

    Mike

  6. #6
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi guys thanks for your input.

    Mike Owen,
    I've amended the code you kindly gave me and made a new function as follows

    Function FieldValidations() As Boolean
    On Error GoTo Err_FV

    Dim ErrorMsg As String
    Dim AllIsOK As Boolean


    AllIsOK = True

    If IsNull(comments_code.Value) Then
    AllIsOK = False
    ErrorMsg = ErrorMsg & "You have not entered a Comment Code, please enter one" & vbCrLf
    End If


    If Not AllIsOK Then MsgBox ErrorMsg, vbCritical, "System Monitor"
    FieldValidations = AllIsOK

    Exit_FV:
    Exit Function

    Err_FV:

    MsgBox Err.Number & ": " & Err.Description
    FieldValidations = False
    Resume Exit_FV
    End Function

    After this I put as you suggested a bit of code in the Form_Unload event along the lines of

    Private Sub Form_Unload(Cancel As Integer)

    Call FieldValidations
    If Not FieldValidations Then
    Cancel = 1

    End If


    End Sub


    Unfortunately I then get the following error "424 : object required", and my only option is to "OK", the form then exits without giving me an error message.

    Obviously I have something wrong in the code, but as you know I'm a VB thickie so have no idea what

    Mike375,
    I tried putting a macro in to check that the field(s) had been entered this correctly gave me the error message, but only after the form had been closed


    Thansk to both of you for you help

    Regards
    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •