Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2008
    Posts
    59

    Unanswered: Button Always Appends 8 Rows Instead of 1

    Hi,
    I have created a form with a text box. Under the text box is a button with a command to open an append query and enter the contents of the text box along with two other fields into a table. This works fine except that it always appends 8 identical rows instead of just the 1 it should be doing. I can't for the like of me work out the problem!

    The code for the button click is:

    Code:
    Private Sub cmdSaveNote_Click()
    On Error GoTo Err_cmdSaveNote_Click
    
        Dim stDocName As String
    
        stDocName = "qry_append_notes"
        DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    Exit_cmdSaveNote_Click:
        Exit Sub
    
        MsgBox ([New Note Input])
       
        Me.Refresh
    
    Err_cmdSaveNote_Click:
        MsgBox Err.Description
        Resume Exit_cmdSaveNote_Click
    
    End Sub
    The append query is quite basic. There are 3 fields. The first one just enters today's date so has field value "Date Written: Date()". The second one takes the contents of the text box so is "Notes: [Forms]![frmSearch]![AddNoteBox]" and the last one takes the URN, which is my unique ID and has value "URN: [Forms]![frmSearch]![URN]".

    Does anyone know what could be causing this? Any help would be muchly appreciated!!!

    Thanks
    Russ

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    We'll need to see the code for the query. I'm 99% sure what is going on but that is the important bit, not the VBA.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2008
    Posts
    59
    Okay, I tried to describe it but is quite hard. Will the SQL do? It's:

    Code:
    INSERT INTO tbl_Notes ( Date_Written, Notes, URN )
    SELECT Date() AS [Date Written], [Forms]![frmSearch]![AddNoteBox] AS Notes, [Forms]![frmSearch]![URN] AS URN
    FROM tbl_notes;
    Cheers

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The SQL is fine, ta.

    Right - the problem is easily fixed, but let's see if you can see why the problem occurs eh?

    Code:
    SELECT *, Date() AS [Date Written], [Forms]![frmSearch]![AddNoteBox] AS Notes, [Forms]![frmSearch]![URN] AS URN 
    FROM tbl_notes;
    If you run that code, do you now see why you are inserting loads of rows?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2008
    Posts
    59
    Hi,
    If I run that query I get Duplicate Output Destination 'Date_Written'. Is that because the asterisk causing everything to add twice. Sorry, I'm relatively new to Access and I'm not sure I understand what I'm supposed to be seeing by adding the asterisk into my query. If you can explain maybe a little bit more I will happily try and work it out.

    Thanks!

  6. #6
    Join Date
    Nov 2008
    Posts
    59
    Hold on, I've got it!

    I just needed to get rid of the FROM tblNotes line. It was completely superfluous. So simple really. Thanks for your help! Maybe if you could explain what adding the asterisk into the query was supposed to do still would be really helpful for me. I find it really hard to diagnose problems in Access so any help in that front will be readily received.

    Thanks!
    Russ

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - poop.
    Code:
    SELECT *, Date() AS [Date Written2], [Forms]![frmSearch]![AddNoteBox] AS [Notes2], [Forms]![frmSearch]![URN] AS [URN2]
    FROM tbl_notes;
    Also, I've just noticed something that is germain - are you using Access 2007?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2008
    Posts
    59
    I'm using 2003 although someone else has worked on the database using 2007. Why do you ask?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just in case you hadn't noticed
    Code:
    Exit_cmdSaveNote_Click:
        Exit Sub
    
        MsgBox ([New Note Input])
       
        Me.Refresh
    The MsgBox line and Me.Refresh will never execute.

    Just wanted to point it out
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by slaterino
    Why do you ask?
    Because this:
    Code:
    SELECT 1
    was invalid in XP. I assumed it was in 2003, but I've just noticed it works in 2007.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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