Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2003
    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!

    Sub AppendToTable()

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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
    ' Get student ID from list box
    rst!StudentID = Me.lstStudents.ItemData(varItem)
    ' Edit existing record
    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
    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