Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: Update Query Problem with AutoNumber

    Hi there,

    This is a maintenance/service quotation database.
    AutoNumber is a primary key in tbl_Orders

    Firstly
    I have a List price field i need to update if the "No_Years_Service" field changes. The SQL below works fine if i replace the CurrentRecordNumber with an Integer. But i need it to update the row which is being held in CurrentRecordNumber

    Dim CurrentRecordNumber As Integer
    CurrentRecordNumber = AutoNumber

    DoCmd****nSQL " UPDATE tbl_Orders SET tbl_Orders.List_Price = [List_Price]*[No_Years_Service] WHERE (((tbl_Orders.AutoNumber)=CurrentRecordNumber))"

    when the sql run a pop up window appears asking for a value for CurrentRecordNumber

    any help welcome

    thanks
    Marcus
    Last edited by marcusmacman; 03-04-12 at 09:44.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Where and how do you declare AutoNumber and what value does it contain?
    Have a nice day!

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi there,

    thanks for responding to the thread.

    AutoNumber is the field name in tbl_Orders with data type set to AutoNumber. It is also the primary key in the table.thanks
    marcus

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sinndho was referring to this:
    Code:
    Dim CurrentRecordNumber As Integer
    CurrentRecordNumber = AutoNumber
    How is CurrentRecordNumber being populated? You have not shown where AutoNumber (in the above code) is being assigned a value.
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    ok. AutoNumber is a field in the subform taken from tbl_Orders.
    Each new record gets populated with a unique number. Access provides this function when designing the table (i guessing you know that tho)

    So in the code afer this line
    CurrentRecordNumber = AutoNumber

    CurrentRecordNumber contains the unique integer for the record which has focus.

    I want to be able to use this number contained in CurrentRecordNumber for the update query.

    hope this makes sense?

    thanks
    marcus
    Last edited by marcusmacman; 03-05-12 at 10:19.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    CurrentDb.Execute "UPDATE tbl_Orders SET List_Price = List_Price * No_Years_Service WHERE AutoNumber = " & CurrentRecordNumber, dbFailOnError
    Have a nice day!

  7. #7
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks Sinndho,

    just had to change CurrentDb.Execute to DoCmd****nSQL (I don't know why)

    After it updates the record a message pops up saying SAVE COPY or DROP changes which i think i can live with.

    thanks again
    Marcus

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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