0

I'm trying to capture the DCount of four different queries before a form can update them in VBA in Access 2002.

The hope is to compare the old record count for each query with a current record count after the Save button has been clicked to see if the query record count has increased since last entry. If any of the queries have gained an additional record, a message box would then appear and notify the user.

I have four different private functions created that capture these values from outside of the subroutine. I'm able to set four variables to the value of each of these functions as they return integers.

When called they automatically update to the current record count and the pop up warning of the record increase in any (or all) of these queries never happens.

How do I capture (and keep) the previous state of these queries while being able to compare it to the current state to see if there's a change?

user3608243
  • 63
  • 6
  • 2
    Without seeing any of your code, how about one set of 4 variables for "before" and a second set for "after"? IOW, 8 variables instead of 4. – HansUp Jul 14 '15 at 16:55
  • ^ Also, exactly how do these new records get into the queries you want to check? – Newd Jul 14 '15 at 17:41
  • The form is updated by the user. It seems to update the tables involved (and therefore the queries) as soon as the information is entered. It's really strange. I'm trying to find where in the code it updates the tables. If there was some way to lock the values of the original variables. If I could freeze the variables with the original record count values prior to adding a new record and then comparing the record count values after the save button is complete, it'd accomplish what I need. – user3608243 Jul 14 '15 at 17:45
  • @HansUp I've done that, but it keeps updating almost immediately so that both values are equal. – user3608243 Jul 14 '15 at 17:46
  • See whether the form's Before Update event is useful for whatever it is you want to do. – HansUp Jul 14 '15 at 17:50
  • So vba constants don't like function values. There must be a way I can get the value of that function into a variable some how that a constant will accept it. – user3608243 Jul 14 '15 at 18:42
  • 1
    By definition a constant can not change, therefore you will not be able to set to a variable at runtime. If they can vary, they have to be variables. – Don Jewett Jul 14 '15 at 19:08
  • 1
    Sounds like you need the BeforeUpdate event – Don Jewett Jul 14 '15 at 19:09

1 Answers1

1

Question: How do I capture (and keep) the previous state of these queries while being able to compare it to the current state to see if there's a change?

Short answer: Store them in a table.

Longer answer:

  • Create a table for the values you want to semi-permanently store
  • In your post Save routine:
    1. Get the new values from your private functions
    2. Read the values from the table
    3. Compare stored values to your new values
    4. Run whatever code in response (display message?)
    5. Overwrite the stored values in the table with the new ones.

Remember that the database is for semi-permanent data storage, which includes metadata (data about your data). The results of previous queries is a type of metadata, so it makes logical sense to store them of you will need them later. This isn't the only solution to your problem, but it is a logical one.

Don Jewett
  • 1,867
  • 14
  • 27
  • You know, this sounds like it could work! Going to give this a try. – user3608243 Jul 14 '15 at 19:23
  • This worked great! I created the table, loaded the values there (when needed, flushed the records and reset the autonumber value when I was done with the table for good housekeeping), and it held the values when needed while doing the comparison! Thank you VERY much Don! – user3608243 Jul 15 '15 at 18:24
  • 1
    Excellent! Glad to hear it worked out so well for you! – Don Jewett Jul 15 '15 at 18:25