Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing Data in BeforeUpdate Event (2000/SR-1)

    I have a form with four controls on it: three text boxes and one listbox.

    The listbox contains two columns: An EventID and a Description. The data for the listbox is gathered from a separate table (ListboxInfo) using a lookup. The table contains 36 static lookup records.

    The text boxes are bound to fields in a table (EventLog) containing five fields: ContactID, EventID, DueDate, DoneDate, and Notes. The form is also bound to EventLog.

    When the form is opened, it is opened from another form with contact data, from which I obtain the linking ContactID, thereby limiting records to a single person. I now need to create/edit/update records in EventLog anytime I enter data into one of the three text boxes and move to another item in the listbox.

    I have tried placing code in the BeforeUpdate event procedure to change/add data to the current record but receive the following error: 'This action was cancelled by an associated object.' Here's the code I've tried.

    Private Sub lstBox_BeforeUpdate(Cancel As Integer)
    If Me.Recordset.NoMatch Then
    Me.Recordset.AddNew 'I tried .Edit here also
    Me.Recordset.Fields("ContactID") = Forms!frmEscrow.ContactID
    Me.Recordset.Fields("EventID") = lstDescription
    If Len(Due) > 0 Then Me.Recordset.Fields("Due") = Due
    If Len(Done) > 0 Then Me.Recordset.Fields("Done") = Done
    If Len(Notes) > 0 Then Me.Recordset.Fields("Note") = Notes
    Me.Recordset.Update
    Me.Recordset.Requery
    End If
    End Sub

    Private Sub lstBox_Click()

    Me.Recordset.FindFirst "EventID=" & lstDescription

    If Me.Recordset.NoMatch Then
    'Unlink the table for proper display of data fields when no records present
    Due.ControlSource = ""
    Done.ControlSource = ""
    Notes.ControlSource = ""
    Due = ""
    Done = ""
    Notes = ""
    Else
    'Link the textboxes to the table
    Due.ControlSource = "Due"
    Done.ControlSource = "Done"
    Notes.ControlSource = "Note"
    End If
    End Sub

    I've tried this with and without the .AddNew statement but get an error 'Cancel or CancelUpdate without AddNew or Edit.' whenever I access the .Fields() statements.

    I need to know if it's possible to update/add records in this manner and if I'm on the right track?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changing Data in BeforeUpdate Event (2000/SR-1)

    I think you're getting confused over what the BeforeUpdate event represents. BeforeUpdate happens after a field (or form) has been changed but before the change is actually written back to the database. You can't update the recordset from a BeforeUpdate event. That's like a snake swallowing its own tail!
    Charlotte

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Data in BeforeUpdate Event (2000/SR-1)

    Actually, I have it working with the exception that I am unable to refresh the query to display an added record.

    If there is a better way to do this, I could use some suggestions. I really need to ability to use the listbox to perform the work. It makes data entry much easier than using other methods I've tried.

Posting Permissions

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