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

    Unanswered: convert SQL to VBA

    Hello again,


    I'm trying to change the value of a field in one of my orders to zero when i click on a YES/NO field.

    It's a quotation database (still working on it :-( ) and when i click on the YES/NO field I would like the List_Price to change to 0.

    It works with the query below, which I run through a macro. In orer to get it to work I have had to write the current order (Ref_ID) and the current part i want changing to zero to a text box on the main form. I then reference them in the query.

    Is there a more simplier way of updating the field in VBA because it seems very long winded?

    When I run the query it also says the data has been changed by another user. Do you want to copy/keep or discard the data. Is there a way of stoppping this message?

    thanks

    Marcus



    UPDATE tbl_Orders SET tbl_Orders.List_Price = 0
    WHERE (((tbl_Orders.fkeyPSA_ID)=[Forms]![Main]![Ref_ID]) AND ((tbl_Orders.AutoNumber)=[Forms]![Main]![current_part]));
    Last edited by marcusmacman; 11-17-10 at 12:43.

  2. #2
    Join Date
    Nov 2010
    Posts
    84
    Let us first of other problems. To remove the message, in your macro to insert the action before, where you will define SetWarnings No and after the update action SetWarnings Yes.

    Regarding the first question may be best to attach your database

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

    I tried putting setwarning off before and after i ran the update query but it didn't work. The pop up says Write conflict with the option of SaveRecord / CopytoClipboard or Drop changes.

    cheers
    marcus

  4. #4
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    any ideas anyone? I'm pretty stuck on this one.

  5. #5
    Join Date
    Nov 2010
    Posts
    84
    Can you attach the database to be able to see the problem.

  6. #6
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    i can't mate. Don't worry I can live with it until I get better at VBA coding. thanks for looking tho.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Your error probably occurs because you're changing a value with code that the form is also bound to. Generally you either want to use code to update data in a table or a bound form, but not both. The "other user" in your case is the bound form. Why not just change the value on the bound form?

    Me.List_Price = 0
    Paul

  8. #8
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    God dame....How stupid am I?? It's a good job it's nearly the week end or I might go and hang myself. I've really should leave this database stuff to the experts. Needless to say Me.List_Price = 0 worked a treat
    Last edited by marcusmacman; 11-19-10 at 12:56.

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    LOL! By my clock it's about time to head for a pub across the pond there!
    Paul

Posting Permissions

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