Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Location
    UK
    Posts
    4

    Unanswered: Insert - Update Code

    Hi,
    I have produced the following code to insert data from a form into a Results table. The results table has a Primary key set on the field 'Personnel' to ensure that only one record can be saved for each employee. I want to adapt this code so that if an enty already exists in the Results table this will be overwritten with the latest data. Any ideas on how or what code I should use would be greatly appreciated.

    Many Thanks in Advance
    Simon
    Private Sub Save_Click()

    Dim strSQL As String

    strSQL = "Insert Into Results (Personnel, GradeGroup, PayLevel, GradeLevel, NSalary, Bonus, ABonus, SalSup, ASalSup, DDate) Values (" & Personnel & ",'" & GradeGroup & "','" & PayLevel & "', '" & GradeLevel & "', " & NSalary & ", " & Bonus & " , " & ABonus & " , " & SalSup & ", " & ASalSup & ",'" & DDate & "')"
    DoCmd.RunSQL strSQL

    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    alrighty... based on the fact that you're using VALUES instead of a select, I assume you're appending data one record at a time. Therefore, I would use an if statement with a DCount as criteria and run either an update, or insert statement based on the criteria. Eg:
    Code:
    Dim sql As String
    
    If DCount("yourField", "yourTable", "yourField = " & yourKeyControl) Then
       sql = "UPDATE yourTable SET ..." etc. 
    Else
       sql = "INSERT INTO yourTable (fields) ..."  etc
    End If
    
    DoCmd.RunSQL sql

  3. #3
    Join Date
    Nov 2003
    Posts
    23
    Use Dcount to see if the entity exists already.

    if it does
    run update sql
    else
    run insert sql

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by trager
    Use Dcount to see if the entity exists already.

    if it does
    run update sql
    else
    run insert sql
    That would be the short and sweet of it yes.


  5. #5
    Join Date
    Nov 2003
    Posts
    23
    What!

    You mean my code won't actually work!!

    Dang it

    :P

  6. #6
    Join Date
    Apr 2004
    Location
    UK
    Posts
    4
    Originally posted by Teddy
    That would be the short and sweet of it yes.

    Thankyou very much for that guys great help.
    Cheers

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by trager
    What!

    You mean my code won't actually work!!

    Dang it

    :P
    haha!

    I don't know why, but that made me laugh.

    Happy Friday folks!

    Cheers!

Posting Permissions

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