Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    32

    Unanswered: INSERT query Only workds Once!

    Ok I have this code :


    Dim db As Database
    Dim sSQL As String
    Dim Lead As Integer
    Dim Callid As Integer
    Dim intCounter As Integer
    Dim leadNum As Integer

    leadNum = Me.lstLeads.ListCount()
    Callid = Me.txtCallID

    MsgBox leadNum


    For intCounter = 0 To (leadNum - 1)
    Set db = CurrentDb()
    MsgBox Me.lstLeads.ItemData(intCounter)
    Lead = Me.lstLeads.ItemData(intCounter)
    sSQL = " INSERT INTO tblCalltoLeadRelations ([Call ID], [Lead ID]) VALUES (" & Callid & ", " & Lead & ");"
    MsgBox sSQL
    db.Execute (sSQL)
    db.Close

    Next intCounter

    OK. So it works for the first item in the listbox, writes to the table fine. But after that No other data gets written. No errors or anything.

    Perplexed beyond belief.

    Sid

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    One guess would be that one of the fields being inserted is set to not allow duplicates. You can add the argument dbFailOnError to the Execute method to get an error message. More info in Help on the Execute method.
    Paul

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are call id & leadid numeric values.. if so no need to encapsulate the values in quote marks
    are the values changing in your debug/msg box of the sql

    the important thing at present is to work out where the error lies

    ..is the programs sending the correct data to the db
    or
    ..is the db not handling the datat that is being sent to it.

    bear in mind its impossible to diagnose such things remotely as we don't know what values are being sent, and we don't know how the tables/columns are defined

    for performance reasons you don't need to open and close the db inside the loop
    Im not even certain whether you need to define a variable for the db, you may be able to get away with
    Code:
    currentdb().execute
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2009
    Posts
    32
    Well I went to the table and one of the columns was set to no duplicates so i changed that.
    As far as if it is sending the right info the msgbox fires befor it executes and shows that the string is changing and is correct. Its just 2 numbers. The first number is alwasy the same. But it still only writes the first executed string to the table and none of the others.

    Trying to get the dbFailOnError but its giving me a compiler error... idk...

    Im sure it something small but Its kicking my butt. Worked on it all day yesterday and have all day to work on it today as well... and the next if i dont get it fixed....

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    How are you trying to use dbFailOnError? Did you look in Help to see the proper syntax? Do you really want to write all items in the listbox to the table, or just selected items?
    Paul

  6. #6
    Join Date
    Nov 2009
    Posts
    32
    db.execute (sSQL, dbFailOnError )

    turns read and compiler says expecting a =

    Basicaly its like this:
    txtCallid = 304 (or some other number)

    lstLeads =
    123
    432
    134524
    34324

    I want it to write to a table:

    Call id | Lead Id
    304 | 123
    304 | 432
    304 | 134524
    304 | 34324

    And yes i want it to do it to all the numbers in the list box.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I don't think you don't want the parentheses. Try

    db.execute sSQL, dbFailOnError
    Paul

  8. #8
    Join Date
    Nov 2009
    Posts
    32
    Sweet that worked and it told me that

    runtime error 3022

    the changes you requested to the table were not successful because they would create duplicate values in the index primary key or relationship... yadda yadda

    IDK! I have removed the M:N links, set the indexs to duplicates OK for both, and it still will not let me put in a duplicate id!

  9. #9
    Join Date
    Nov 2009
    Posts
    32
    I think ive fixed it... re did the relationships

Posting Permissions

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