Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2008
    Posts
    25

    Unhappy Unanswered: VBA Update Query Question...

    I have a click button on a form which outputs a form and an email – which works great. However, What I want to do is add the ability to run an update query at the end of the routine.

    The form is populated with data from another query (QOpenIssues), and contains multiple records of different siteID’s. At the moment, I have in the query itself, in the siteid field a user prompt to enter the siteID. Once entered, the other criteria is status <> “Closed”. This updates, but requires the user to keep entering the different siteId’s as the loop works through the data.

    Is it possible (and hopefully someone will give some example!) that as part of the VBA, I could change the “DoCmd.OpenQuery "updateopenrecords"” with something that will filter the update query with the current siteid, ie., somehow adding the “me!siteid” to the docmd ?

    I have tried all sorts of ways, and either the update query closes all records, or it doesn’t close anything!?

    Any advice or examples would be gratefully received!

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    change the query to select the field SiteId, and add as criteria the field on your form.

  3. #3
    Join Date
    Jan 2008
    Posts
    25
    Thanks Chris07.

    That's what I had originally, and it doesn't work!?

    in the VBA - I have:
    DoCmd.OpenQuery "updateopenrecords"

    Then in the actual query (updateopenrecords), I have:

    Field Status - Criteria "New" - update to "Closed"
    Field SiteID - Criteria "siteid" - update to is left blank

    I only want to apply the update to the siteid relevant to the current record, not all records in the dataset.

    My logic was along the same lines as yours - but it doesn't update?

    Any other suggestions???

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    Is there any kind of autonumber or primary key associated with that record? If so, you can place that as criteria, link that to the form and you will have your desired result.

  5. #5
    Join Date
    Jan 2008
    Posts
    25
    Thanks for the help -

    The issue I hve is that opening the form provides 10000 records a day, and effectively there could be 2+ more records for the same siteID with the data. what I have is through VBA is that it produces a report and includes all the "new" records on one report for single siteid. What I then need to do is change the status to closed the current record (and any matching siteID's in the dataset), so that when the user goes to the next record, there are no duplications left in the dataset presented.

    The only common factor of the open records at the point of the user click is the siteid = 1234 & status = new.

    Any ideas?

  6. #6
    Join Date
    Oct 2009
    Posts
    204
    Ok I understand now - I thought you wanted to select a single record.

    What if you change this
    Field Status - Criteria "New" - update to "Closed"
    Field SiteID - Criteria "siteid" - update to is left blank


    to this
    Field Status - Criteria "New" - update to "Closed"
    Field SiteID - Criteria "1234" - update to is left blank

  7. #7
    Join Date
    Jan 2008
    Posts
    25
    This works fine, but only updates records for siteid 1234, when the user then moves to the next record, for siteid 4567, obviously the update will not work for this siteid.

    At present I have [Confirm Site ID] as the criteria on siteid, which prompts the user to enter the siteid, but with 10000+ records daily, the manpower this is taking is high to say the least!

    somehow, I need to get the query to understand that the siteid equals the current record site id!!!!

    As you can tell a novice at best!?

    Thanks for all your help on this - and anything else you can suggest will be appreciated.

  8. #8
    Join Date
    Oct 2009
    Posts
    204
    What does siteid have to do with the update? Could it be that you are simply updating everything status New to Closed? If that is the case, you can remove siteid from the query. Otherwise, is there a date field that you can use at all? Any other common field?

  9. #9
    Join Date
    Jan 2008
    Posts
    25
    I use the siteid field to update, purely from a user point of view - sort of - update as you go. As its taking so long each day to get through the records, the user may close and re-open the database several times a day, and therefore, at present, it will "start from where you left off"

    The date idea is a non runner, as they will often be transactions data/timed different -the only common field I have of all the records is the siteid (which is the customer no in my instance).

    Through VBA, Would I be able to add a filter to the record set to equal site id (therefore displaying all the records = 4567) and then run the update against the filtered records? I would then need to be able to remove the filter (I suppose a requery would do) - any thoughts/suggestions here?

  10. #10
    Join Date
    Oct 2009
    Posts
    204
    Would this be a certain siteid that the user would choose?

    If so, how about putting all of the siteids on either an unbound combo box or text box, then using that field as the siteid criteria in the query?

  11. #11
    Join Date
    Jan 2008
    Posts
    25
    doesn't seem to like this either!

  12. #12
    Join Date
    Oct 2009
    Posts
    204
    Can you post a copy of your database please?

  13. #13
    Join Date
    Jan 2008
    Posts
    25
    Chris07 -

    In a moment of madness - i seem to have cracked it! Using DLookUp("[siteid]","[issues]","[siteid] = '" & [Forms]![issue details]![siteid] & "'") in the query!

    I will test this and see how it works.

    a big THANK YOU for your patience and help on this - I will let you know how it goes tomorrow during testing.

    Thanks a million for your support - much appreciated....

Posting Permissions

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