none
Unbound Form not working RRS feed

  • Question

  • I am working on my first unbound forms. I have a mainform and Subform. It is a single form dataview.

    The Issue that I am having is that nothing on the form is working. The validation or saving. the form  is not doing anything.

    I finally got the save button to work!!

    The issue that I am now having is getting this to validate. Am I putting it in the wrong event? Or am I coding it incorrectly?

    Validation Code For duplicate ID

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim Response As String
    If IsNull(Me.[PROTEST_NUM]) Then
    Cancel = True
    MsgBox "Protest Number cannot be empty.Please enter protest Number to proceed!   "
    Me.PROTEST_NUM.SetFocus
        ElseIf Nz(DCount("PROTEST_NUMBER", "ADDRESS", "PROTEST_NUMBER = " & Me.PROTEST_NUM.Value & ""), vbNullString) > 0 _
        Or Nz(DCount("PROTEST_NUMBER", "CHARGES", "PROTEST_NUMBER = " & Me.PROTEST_NUM.Value & ""), vbNullString) > 0 Then
        'If DCount("PROTEST_NUMBER", "ADDRESS", "PROTEST_NUMBER = '" & Me.PROTEST_NUM.Value & "'") > 0 Then
        Cancel = True
    Response = MsgBox("This Number already exists." & vbCrLf & _
                "Please enter a different Number." & vbCrLf & _
                "Click OK to go to the Update Screen." & vbCrLf & _
                "Click Cancel to enter a different Number", _
                vbOKCancel, "DUPLICATE NUMBER")
           
           Me.PROTEST_NUMBER.Undo
            If Response = vbCancel Then
            Cancel = True
      Exit Sub
     Else
      DoCmd.OpenForm "PROTEST_UPDATE", acNormal, , , , , PROTEST_NUMBER.Value
              DoCmd.Close acForm, "NEW_PROTEST_ENTRY"
     End If
         
           
    Else
      MsgBox "PROTEST NUMBER NOT FOUND"
    End If

    End Sub





    • Edited by Chibut Thursday, December 15, 2016 6:59 PM
    Thursday, December 15, 2016 2:47 PM

All replies

  • I Got the validation to work by moving it to :

    Private Sub PROTEST_NUM_AfterUpdate()

    is this the correct way of doing it?

    I also noticed one thing, it only does the validation once. So this means that it will allow the user to save it after .

    where is the best place to place this validation?

    • Edited by Chibut Thursday, December 15, 2016 7:13 PM
    Thursday, December 15, 2016 6:50 PM
  • I also noticed one thing, it only does the validation once. So this means that it will allow the user to save it after .

    where is the best place to place this validation?

    Hi Chibut,

    Validation should be done in the BeforeUpdate event.

    If a value does not pass the validation, you can set the Cancel parameter to True. In that case the focus stays in the current control for a better value.

    Imb.

    Thursday, December 15, 2016 8:11 PM
  • how I proceed to do so  working with an unbound form?

    Thanks

    Thursday, December 15, 2016 8:15 PM
  • how I proceed to do so  working with an unbound form?

    Hi Chibut,

    After all controls are filled (some may still have a Null value) you can write the data to a record. There are several ways to do that.

    One way could be:

        Set cur_set = CurrentDb.OpenRecordset("This_tbl WHERE This_id = 0")
        'this opens an empty recordset
        cur_set.AddNew
        cur_set!Field1 = Control1
        cur_set!Field2 = Control2
        ...
        cur_set.Update
        cur_set.Close

    Of course, you substitute you own names for Table, Fields and Controls.

    Imb.

    Edit:

    If you want to grab the This_id of the newly created record, you can add a line before the cur_set.Update statement:

        new_id = cur_set!This_id

    Imb.

    • Edited by Imb-hb Thursday, December 15, 2016 9:44 PM edit
    Thursday, December 15, 2016 9:35 PM
  • below is the final statement. I also have another insert statement in subform.

    How do I put an error message in the update statement to let the user know that the update failed? is there a better way to do the below error check?

    thanks all

    Private Sub SaveNewEntry_Click()
    Dim err As Integer
    Dim Response As String

    'Check that all fields are filled in

    BL.SetFocus
    If BL.Text = "" Then
    err = err + 1
    MsgBox "Please fill in the Block Number!", vbOKOnly, "MISSING BLOCK NUMBER"
    End If

    LT.SetFocus
    If LT.Text = "" Then
    err = err + 1
    MsgBox "Please fill in the Lot Number!", vbOKOnly, "MISSING LOT NUMBER"
    'MsgBox "Please fill in the Lot Number!" & err
    End If

    'Check For Duplicate
    PROTEST_NUM.SetFocus
    If Nz(DCount("PROTEST_NUMBER", "ADDRESS", "PROTEST_NUMBER = " & Me.PROTEST_NUM.Value & ""), vbNullString) > 0 _
        Or Nz(DCount("PROTEST_NUMBER", "CHARGES", "PROTEST_NUMBER = " & Me.PROTEST_NUM.Value & ""), vbNullString) > 0 Then
        err = err + 1
    Response = MsgBox("This Number already exists." & vbCrLf & _
                "Please enter a different Number." & vbCrLf & _
                "Click OK to go to the Update Screen." & vbCrLf & _
                "Click Cancel to enter a different Number", _
                vbOKCancel, "DUPLICATE NUMBER")
       
            If Response = vbOK Then
        DoCmd.OpenForm "PROTEST_UPDATE", acNormal, , , , , PROTEST_NUM.Value
        Exit Sub
            End If
    End If

    'if no errors insert data
    If err < 1 Then
    '
       Dim rsADDRESS As Recordset
        Set rsADDRESS = CurrentDb.OpenRecordset("ADDRESS")
        rsADDRESS.AddNew
        rsADDRESS!PROTEST_NUMBER = Me.PROTEST_NUM
        rsADDRESS!PH_NUM = Me.PH_NUM
        rsADDRESS!STREET = Me.STREET_NAME
        rsADDRESS!BL = BL
        rsADDRESS!LT = LT
        rsADDRESS!BORO = BORO
        rsADDRESS.Update
        rsADDRESS.Close

        Me!CHARGES_subform.Form.Update_Charges Me.PROTEST_NUM
     
        DoCmd.Hourglass False

        'DoCmd.SetWarnings True

      'MsgBox "Updated"
    MsgBox "New contact has been successfully added"
    Else
    MsgBox "An Error has occurred, please check and try again"
    End If
    End Sub

    Friday, December 16, 2016 3:37 PM
  • How do I put an error message in the update statement to let the user know that the update failed? is there a better way to do the below error check?

    Hi Chibut,

    The best way is to follow your housestyle that you have developped to communicate with the user.

    Personally I never generate the typical error-messages. Instead I generate Messages (as you did in your code) to inform the user what can be done to correct the wrong input.

    This can be done in several events. In the BeforeUpdate event you can handle invalid values. In the AfterUpdate event, or the Exit event, you can handle the consequences of changes. In most cases I prefer the Exit event over the AfterUpdate event, because I can also handle those changes that are made by code and not by user interaction.

    Finally, in the Update sub itself you can generate user messages, for instance when you have controls that need to have a value, but the user did not fill in anything. In such cases the Update sub is exited. Only when ALL is correct, a new record is created.

    Imb.

    Saturday, December 17, 2016 12:52 PM