Update Table With Query Value Using VBA

[Dwight]

I have a series of nested parameter queries that calculate a value. I need to store this value in a table. And yes, I know storing a calculated value is poor practice but in this situation it is necessary.

Usually, I would use an update query to move the value from the query to the table. However, since one of the nested queries is a summary query this will not work. I have been studying how to do this in VBA (both help files and reading through older posts) but it is a challenge because I’m still learning VBA.

The query parameters will be entered via a form. I need help understanding how I can get the code to pass these parameters to my query(s), run the query to create the calculated value, and then move the calculated value to the correct table field.

I am just as happy if you direct me to a resource that covers this topic and or has an example that I can follow than if you provide an explanation.

Thank you,

Dwight

 

[sfreeman@co.mer]

Let me get this right... you have a query that generates the value you need to be appended to a table?

If so, just create a second append query, that queries your original query for the value you need.

In VBA, you could use the DLookup function to get the same value.

 

[Dwight]

Thanks Sam F. The append query took about 3 minutes to setup and works fine. I just set up another table rather than trying to update a column in an existing table.

I'll just keep telling myself that 5 hours of researching the VBA help files yesterday was good for me.......

Thanks again,

Dwight