Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    1

    Unanswered: Access Loop Function

    Hi I am designing a database; and I am having trouble writing a much needed function; because I have no VB experience!

    Basically I have a database that holds product information. Each supplier sends me 10000s of new prices each month. Each product has a "discontinued" field (true/false). So when I get a new price list, I can discontinue the old one; But keep it on the system so we have access to the data linked to old orders....

    Now I have a form which will list All the data from a specific supplier, and using a form filter I get the form to show only the records I need to mark as discontinued. Now I have the information on the form How do I go about putting in a loop function??

    Basically I just want to hit a button, and It will run a loop function for all shown records, marking the "Discontinued" field as True....

    I am a total newbie to VB so please forgive me if this is really easy!!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its not a vba function you want per se
    its a SQL query (assuming you can easily define what triggers the update requirement). you cna wrap that query in a fucntion if you wish, it could also be run as a macro

    so first off you need to work out if you can easily define only thoise records that need updating
    having done that work out the SQL. that will be soemthign like

    update mytable set mycolumn=avalue where
    (thiscolumn = blah and that column = blah di blah) OR anothercolumn = whachamacallit

    the issue is going to be to defien what the wehre criteria are.

    if you need to substitue values in the SQL function each time the query is run, then you may need to run the query in a function

    that function woudl be something like

    strSQL = "update mytable set mycolumn=avalue where "
    strSQL = build your where clause as required
    docmd****nsql strSQL
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    loops are programming, databases are sql

    in Access it is called an Update Query

    do a little research on this and you'll find it...

Posting Permissions

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