Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    21

    Unanswered: Controlling addition of records in continuous forms

    I have a continuous form that I am using to display all the records currently in my table for a particular ID. Users should be allowed to add new records, but *only* by clicking on the Add Record button (i.e., not by scrolling to the bottom). I know (or at least I think) the key is controlling the AllowAdditions.

    When I enter the form AllowAddition is set to False. It remains false until the user clicks the Add Record button. But, then things kind of fall apart. I've tried resetting AllowsAdditions to False in a number of places, but nothing seems to be working. The thing that makes it worse is that I am setting the Subject ID behind the scenes, so the ID for the records that are created by scrolling/paging/navigating beyond the last record are missing the ID. (ETA: Not sure if it makes any difference for the purpose of my inquiry, but... the primary key for this table is an autonumber -- this table holds data in the many end of a one-to-many relationship with the SubjectID, and it is the SubjectID that I am populating.)

    The form has three buttons:
    Save/Close --- should save changes and close form (in header)
    Add Visit -- should add a single visit and populate the ID (in header)
    Cancel -- should cancel updates to the current record (appears on each record)

    I know (suspect) this shouldn't be that difficult and that I am overthinking it, but I have spent literally days trying to figure this out and my head is about to explode. Any help would be sincerely appreciated. The code for my form events and buttons follows. (I'm kind of embarrassed to post it since I know it's a mess, but I am at my wits end. )

    Code:
    Option Compare Database
    
    Private Sub Form_AfterUpdate()
    'after leaving a record, write changes
    DoCmd.Save
    
    End Sub
    
    
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If IsNull(Me.txtVISDATE) Or IsNull(Me.cboVISTYPE) Or IsNull(Me.txtVISHOSPDAY) Or _
    	 IsNull(Me.cboVISSOURCE) Then
        
            MsgBox "One or more of the following required fields was not completed: Visit date, _
    	 visit type, days in hospital, data source.", _
            vbCritical, _
            "Canceling Update"
            Me.txtVISDATE.SetFocus
            Cancel = True
         
        End If
        
    End Sub
    
    
    Private Sub Form_Current()
    
    'if new record, ID is passed as OpenArgs
    If Not IsNull(Me.OpenArgs) Then Me.IDSUBJECT = Me.OpenArgs
    
    End Sub
    
    
    'Click event associated with Add New Record button
    Private Sub cmdAddNew_Click()
    
    'This was added to prevent trying to add another new record while previous one was incomplete
        If IsNull(Me.txtVISDATE) Or IsNull(Me.cboVISTYPE) Or IsNull(Me.txtVISHOSPDAY) Or _
           IsNull(Me.cboVISSOURCE) Or (Me.cboVISTYPE = 17 And IsNull(Me.txtVISOTHER)) Then
            MsgBox "You must complete entry of the first record before you may add a second."
            
        Else
            'Force Save
            Dirty = False
            'grab current id
            Dim current As Double
            current = Me.IDSUBJECT
            
            'allow additions of new records
            Me.AllowAdditions = True
        
            'add a new record with current id
            DoCmd.GoToRecord , , acNewRec
            Me.IDSUBJECT = current
            
            'set focus to date field
            Me.txtVISDATE.SetFocus
    
        End If
        
    
    End Sub
    
    'Code associated with Cancel button located on each record
    Private Sub cmdCancel_Click()
    On Error GoTo Err_cmdCancel_Click
    
    'if no new changes, display message
    If Not Me.Dirty Then
        MsgBox "No new changes."
    'if on first visit record for a patient
    ElseIf Me.NewRecord And Not IsNull(Me.OpenArgs) Then
        Me.Undo
        DoCmd.Close
    Else
        'Undo updates to current record
        Me.Undo
        Me.AllowAdditions = False
        Me.Requery
    End If
    
    Exit_cmdCancel_Click:
        Exit Sub
    
    Err_cmdCancel_Click:
        MsgBox Err.Description
        Resume Exit_cmdCancel_Click
        
    End Sub
    
    
    'Code associated with Save/Close button for form
    Private Sub cmdClose_Click()
    'Check for blank fields; if one or more fields are blank, display a message and cancel the update.
    'Note: Comments is not a required field
        
        'Bypass validation if record is marked for deletion
        If Not VDELETE = -1 Then
            
            If IsNull(Me.txtVISDATE) Or IsNull(Me.cboVISTYPE) Or IsNull(Me.txtVISHOSPDAY) Or _
    	 IsNull(Me.cboVISSOURCE) Or (Me.cboVISTYPE = 17 And IsNull(Me.txtVISOTHER)) Then
        
            MsgBox "One or more of the following required fields was not completed: Visit date,  _
    	visit type, days in hospital, data source.", _
                vbCritical, _
                "Canceling Update"
            Else:
                DoCmd.Close acForm, "frmPatientVisits"
            End If
        
        End If
    
    End Sub
    Last edited by db_girl; 07-12-09 at 00:31.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you try to requery the subform after the addition of a new record?

    Have a nice day!

  3. #3
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36
    You have to reload the data in order to see the new record. I would suggest to create a separate form that will add the new record. The form should have a save command to insert new record in the table.

    Then when you save the record and close the form there must be a Global Flag which indicates we have a new record. The main form should have a timer event to check for the new record. When the timer checks the global flag and we have a new record flag set to True then a requery should occur.

    This is the how I do it.

  4. #4
    Join Date
    Jun 2009
    Posts
    21
    Quote Originally Posted by danisapfirov
    You have to reload the data in order to see the new record. I would suggest to create a separate form that will add the new record.
    This is exactly what I ended up doing. (Although I just did a requery on the original form to show the new record.)

Posting Permissions

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