Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005

    Unanswered: What is going on with my append query?

    OK, odd, odd things are afoot here in the old append query.
    The code follows, and it's not that complex. Basically what I'm trying to do is take a number returned by a list box (here i'm using a text box because i thought it would be easier) and saving it to a table for use in another function. Here's the code for the sql statement that's doing the magic...

    "INSERT INTO tmpVisitCards ( [Member ID] )" & _
    " SELECT [Forms]![frmVisitorCards]![Text7] AS Expr1" & _
    " FROM tmpVisitCards;"

    Now, here's where things get really, really odd, so to speak. You see, I must have a value in the table tmpVisitCards before it will allow me to add another. Then, the next time I try to add a record, it adds 2 records. Then 4. Then 8. Well, by the time I add the 40th record you can see I'm starting to run out of integers for my key row. First off, why is it doing this to me? Second off (which would also be really useful), how can I make it stop? And by stop I mean make it accept the number first time and also only put one entry in for each additional click. Thank you all for your (I know it will be) timely helps.

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Alright, your query looks funny. it looks like you're trying to do some kind of dynamic column definition based on the code, but I don't think that's your intention... Are you trying to only add whatever value is in frmVisitorCards!text7 to your table? If so, try a syntax like so:

    "INSERT INTO tmpVisitCards ([member id]) " & _
    "VALUES (" & forms!frmVisitorCards!Text7 & ")"

    That's assuming [Member ID] is a numberic field, you'll need to add single quotes otherwise.

    PS: You really shouldn't put spaces in table/field names, trust me.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2004
    metro Detroit
    Not so odd actually. What is the last line of your sql statement telling access to do? If there are no records, you can't select any records to append. If there are records you're selecting and appending all of them (doubling the number of records).

  4. #4
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    You want the VALUES structure rather than the SELECT structure:

    INSERT INTO TableName(FieldName)

  5. #5
    Join Date
    Oct 2005


    Yeah, I don't know what was really going on there, I don't speak SQL well enough to understand everything, and really it's just a miracle this thing's working that well. Oh, and the spaces in names thing wasn't my idea, really. Inherited code.

Posting Permissions

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