Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004

    Unanswered: Access SQL Update Problem


    I'm having a bit of trouble with an update query, here's whats going on:

    I have 2 tables, when I INSERT data into the first table, I also want a value in table 2 to decrement by 1. The insert query is fine, so I won't post it, but could someone please look at my update query and let me know whats going wrong please?

    Ok, first I tried:
    Dim dabs As DAO.Database
            Dim recs As DAO.Recordset
            Dim slots As Integer
            Dim recid As Integer
            Set dabs = CurrentDb
            Set recs = dabs.openrecordset("SELECT PiMS_AvailableSessions.PAS_Slots FROM PiMS_AvailableSessions WHERE PiMS_AvailableSessions.PAS_ID=" & comDate & ";")
            slots = recs!PAS_Slots - 1
            txtSlotsHidden.Visible = True
            txtSlotsHidden.Value = slots
            recid = comDate
            Set recs = Nothing
            Set dabs = Nothing
                SQL = "UPDATE PiMS_AvailableSessions SET " & _
                      "(PiMS_AvailableSessions.PAS_Slots = " & txtSlotsHidden & ") " & _
                      "WHERE (PiMS_AvailableSessions.PAS_ID = " & recid & ")"
                CurrentDb.Execute SQL
            MessageBox = MsgBox("PiMS Training Session Booked", vbOKOnly, "Dates Added")
    txtSlotsHidden = 3 and recid = 7 (I have tested hard-coding the values but still no solution)

    If anyone can give me a hand, or let me know of a better way of doing this, please do


  2. #2
    Join Date
    Jul 2004
    This is a bit confusing. First, you're talking about an update and an append query in the same breath. - In your recordset . . . . WHERE PiMS_AvailableSessions.PAS_ID=" & comDate & ";" . . . .where does comDate come from? - Is this line supposed to assign comDate to recid from the recordset ???? -> recid = comDate

  3. #3
    Join Date
    Sep 2004

    I had two separate queries: one to insert the data from the form, and one to update another table based on one value from the form...The insert query works fine, so I didn't bother showing that

    comDate is an integer value, but I replaced it with recid just to get rid of the speech marks. I know that part of the code is a bit messy, it's just cos I was playing around with stuff

    When I'm debugging the code, and I mouse-over each variable, it tells me that they are 3 and 7 respectively, which they should be (so there's no problem passing on the variables). That's what I dunno where the problem is


  4. #4
    Join Date
    Sep 2004
    Anyone? Please?

Posting Permissions

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