Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DAO to Edit Table (2000)

    I received some wonderful DAO code (below) from Hans at Woody's. I had been using a form (repeated opening and closing) to add new records to a table taken from a multi-select listbox. I have never used DAO before, but with a few changes of his code, it worked beautifully.

    I now have a new, but similar problem with my application. I want to pull values off a multiselect, but instead of adding new records to the table, I want it check if the listbox value, StudentID already exists in the table. If it does, update the record with new values from the form. If the selected StudentID is not in the table, go ahead and start a new record. StudentID is the primary key. Is there a way to modify the below code to do this? I found an example which used 'seek' to locate the record to be updated, but am told seek will not work with a split database. I think I need .findfirst, but have not been able to give it the correct criteria string.
    Thanks, for your help!
    Ralph

    Sub AppendToTable()

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO to Edit Table (2000)

    Hello Ralph,

    You could modify the central part of the code For Each ... Next varItem as follows (additions in bold):

    ' Loop through selected items of list box
    For Each varItem In Me.lstStudents.ItemsSelected
    ' Test if StudentID is already present
    rst.FindFirst "StudentID = " & Me.lstStudents.ItemData(varItem)
    If rst.NoMatch Then
    ' Create new record
    rst.AddNew
    ' Get student ID from list box
    rst!StudentID = Me.lstStudents.ItemData(varItem)
    Else
    ' Edit existing record
    rst.Edit
    End If
    ' Other data from combo boxes, text boxes etc., add more if necessary
    rst!AbsenceType = Me.cbxItemType
    rst!Excused = Me.chkExcused
    rst!AbsenceDate = Me.txtAbsenceDate
    ' Save record
    rst.Update
    Next varItem

Posting Permissions

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