Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.

hi
without seeing our code, i can only guess that you are allowing background
queries. I would suggest that you add something like this.......
range("A1").querytable.refresh backgroundquery:=false
this will pause code until the refresh is completed. and since your project
is dependent on a formula, i would add the calculate command after each
refresh.

post your code and i'll see if i can rework it.

Regards
FSt1

"Accesshelp" wrote:

Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.

FSt1,

Thanks for your response. I do not have any code for my querytables.

The querytables are created via DataImport External DataNew Database Query.

Thanks.

"FSt1" wrote:

hi
without seeing our code, i can only guess that you are allowing background
queries. I would suggest that you add something like this.......
range("A1").querytable.refresh backgroundquery:=false
this will pause code until the refresh is completed. and since your project
is dependent on a formula, i would add the calculate command after each
refresh.

post your code and i'll see if i can rework it.

Regards
FSt1

"Accesshelp" wrote:

Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.

hi
sorry. i assumed that since you posted under programming that you had code.
how do you refresh the queries.

regards
FSt1

"Accesshelp" wrote:

FSt1,

Thanks for your response. I do not have any code for my querytables.

The querytables are created via DataImport External DataNew Database Query.

Thanks.

"FSt1" wrote:

hi
without seeing our code, i can only guess that you are allowing background
queries. I would suggest that you add something like this.......
range("A1").querytable.refresh backgroundquery:=false
this will pause code until the refresh is completed. and since your project
is dependent on a formula, i would add the calculate command after each
refresh.

post your code and i'll see if i can rework it.

Regards
FSt1

"Accesshelp" wrote:

Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.

The queries are refreshed automatically after each value changes. The
problem is when refresh is done, all the queries are refreshed before my
vlookup formula to do the calculation.

Thanks.

"FSt1" wrote:

hi
sorry. i assumed that since you posted under programming that you had code.
how do you refresh the queries.

regards
FSt1

"Accesshelp" wrote:

FSt1,

Thanks for your response. I do not have any code for my querytables.

The querytables are created via DataImport External DataNew Database Query.

Thanks.

"FSt1" wrote:

hi
without seeing our code, i can only guess that you are allowing background
queries. I would suggest that you add something like this.......
range("A1").querytable.refresh backgroundquery:=false
this will pause code until the refresh is completed. and since your project
is dependent on a formula, i would add the calculate command after each
refresh.

post your code and i'll see if i can rework it.

Regards
FSt1

"Accesshelp" wrote:

Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.

hi
sorry to take so long. got hung up.
how have you got this set up to refresh automaticly. there has to be a
trigger.
i still think you problem is allowing a background query. you may have to
get away from the automatic thing and opt for a 1 refresh at a time.

Regards
FSt1

"Accesshelp" wrote:

The queries are refreshed automatically after each value changes. The
problem is when refresh is done, all the queries are refreshed before my
vlookup formula to do the calculation.

Thanks.

"FSt1" wrote:

hi
sorry. i assumed that since you posted under programming that you had code.
how do you refresh the queries.

regards
FSt1

"Accesshelp" wrote:

FSt1,

Thanks for your response. I do not have any code for my querytables.

The querytables are created via DataImport External DataNew Database Query.

Thanks.

"FSt1" wrote:

hi
without seeing our code, i can only guess that you are allowing background
queries. I would suggest that you add something like this.......
range("A1").querytable.refresh backgroundquery:=false
this will pause code until the refresh is completed. and since your project
is dependent on a formula, i would add the calculate command after each
refresh.

post your code and i'll see if i can rework it.

Regards
FSt1

"Accesshelp" wrote:

Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.