Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help w/adding new record (Access 2000)

    Hi folks, I was hoping someone could help.
    I'm relatively inexperience with coding. What I'm trying to do is copy a record from an existing one in an Events table (tblEvents) that is selected from a list box on a different form. I've gotten the code to work in finding the appropriate record and populating a set of variables. For some reason, though, the .Update command isn't updating the record.

    The pertinent section of code is below (sorry in advance if I should have attached it as a file):

    Private Sub lstChoices_KeyDown(KeyCode As Integer, Shift As Integer)
    On Error GoTo Err_KeyDecision
    Dim rsEvent As Recordset
    Set rsEvent = CurrentDb.OpenRecordset("tblEvents")

    Dim intEventID As Integer, strEvent As String
    Dim strEventDesc As String, booAnnual As Boolean
    Dim strPreface As String, mHeader As String, mFooter As String
    Dim mHeader2 As String, mFooter2 As String
    Dim strfilter As String
    If ((KeyCode = vbKeyReturn) And (Me!lstChoices.ItemsSelected.count <> 0)) Then
    strfilter = strfilter & "[EventID] = " & Me!lstChoices.Column(0, Me!lstChoices.ItemsSelected(0))

    With rsEvent
    .MoveFirst
    .FindFirst strfilter
    intEventID = !EventID
    strEvent = !Event
    strEventDesc = "" & !EventDesc
    booAnnual = !Annual
    strPreface = "" & !Preface
    mHeader = "" & !Header
    mFooter = "" & !Footer
    mHeader2 = "" & !Header2
    mFooter2 = "" & !Footer2
    .AddNew
    !Event = strEvent
    !EventDesc = strEventDesc
    !Annual = booAnnual
    !Preface = strPreface
    !Header = mHeader
    !Footer = mFooter
    !Header2 = mHeader2
    !Footer2 = mFooter2
    .Update
    MsgBox "Record has been updated if you can see this."
    End With

    I inserted message boxes all the way down the line, and everything executes except for the .Update statement. I can't figure out why.

    Thanks in advance for your help.

    Charlie T.

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

    Re: Help w/adding new record (Access 2000)

    You copy several string fields. If one of them is null, the value copied will be a zero-length string "". By default, string fields don't accept zero length strings (it can be changed in table design). Could that be the cause of the update failing?

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Help w/adding new record (Access 2000)

    Could you use an append query instead?
    Pat

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help w/adding new record (Access 2000)

    You hit it on the head. Thanks!
    It works fine now after adding Nz(blah blah blah).

Posting Permissions

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