Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    london
    Posts
    7

    Cool Unanswered: INSERT query problem, small code....PLEASE HELP!!

    Hi there, well, I have a problem with my INSERT query, I have tblDueDates, tblClient and three main forms.……..but I get errors when trying to insert payments. I have tblClient and tblDueDates and 1.frmDueDates, to insert and display the due dates for the payments , 2.frmPayDateList in order to display the Due Dates of all clients in a list according to their due dates and 3.frmClientsPayedrecord, to see the payments of each client after the user clicked on the ‘payed’ button on the 2.frmPayDateList form. Form frmDueDates and frmClientsPayrecord are linked to tblDueDates.

    But I get an error when clicking on that ‘payed’ button on the frmPayDateList. I use an INSERT query to insert the payments into that one table tblDueDates and also it should avoid duplication with this query and it’s where clause!!!!

    I have attached a small sample zip file to view the query code on that ‘payed’ button on the 2.frmPayDateList form. Can you help me out to see what is wrong here?? Thanks…..
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    The first problem you have is that you don't want to use an Insert Statement. Insert is used to add new records to the table.

    You want to use an update statement. Try...

    strSQL = "UPDATE tblDueDates SET tblDueDates.date_pay = (#" & Me.DueDate & "#), tblDueDates.amount = (1000) WHERE tblDueDates.[clientID] = '" & Me.clientID & "' AND tblDueDates.[DueDate] = #" & Me.DueDate & "#;"

    S-

  3. #3
    Join Date
    Nov 2003
    Location
    london
    Posts
    7

    Question DELETE query...info....

    Hi there Sbaxter, thanks for the info…I actually moved the due dates and pay dates into one table, it’s less work and its fine. One thing though, I have added an undo button to represent an undo situation after a user has clicked on the ‘pay’ button and decided to undo the info after clicking the ‘pay’ button. I used a Delete query, so it can delete the fields that were updated from the ‘pay’ button. I used the following code and it seems to work fine but it deletes an entire row instead of just the fields specified in the query with it’s criteria where clause for that one table.

    strSQL = "Delete tblDueDates.date_pay, tblDueDates.amount from tblDueDates " & _
    "Where tblDueDates.[date_pay] = #" & Me.DueDate & "# " & _
    "AND tblDueDates.[clientID] = '" & Me.clientID & "' " & _
    "AND tblDueDates.[creditID] = " & Me.creditID & ";"

    'MsgBox strSQL ‘ I tested it with this and it seemed ok…but it deletes the entire row
    DoCmd.RunSQL strSQL

    Is there a problem with the query syntax “Which I don’t think”??
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    again, you need to use an Update Query. The delete query deletes the record that meets your criteria. Try setting you fields to Null or some other appropriate value.

    S-

Posting Permissions

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