OpenRecordset...

[Mark]

Hopefull this will solve it

I have had quite a bit of help from this newsgroup and
from that help, the following VBA quey was produced.

Public Sub Query_Value()

Dim str As String

str = "SELECT Sum([FAOstat].[" &
Form_ElementCalcualtion.Text1 & "]*0.01) AS ProJSupply
FROM (FAOstat INNER JOIN [Food Consumption] ON
FAOstat.Food_code = [Food Consumption].Food_code) INNER
JOIN [Country Total] ON ([Food Consumption].Country_Name
= [Country Total].Country) AND ([Food
Consumption].Food_code = [Country Total].Food_Code);"

MsgBox (str)

Set daoRecordset = CurrentDb.OpenRecordset(str)


End Sub


But the result of the query is not produced (ideally in a
table!). Why is that?

Thanks in advance
mark

 

[Michel Walsh]

Hi,


Because you have built a SELECT query. If you want make a table, build a
MAKE TABLE query. Just two words to add, INTO, a key work, and the
newTableName:


str = "SELECT Sum([FAOstat].[" &
Form_ElementCalcualtion.Text1 & "]*0.01) AS ProJSupply INTO
newTableNameHere
FROM (FAOstat INNER JOIN [Food Consumption] ON
FAOstat.Food_code = [Food Consumption].Food_code) INNER
JOIN [Country Total] ON ([Food Consumption].Country_Name
= [Country Total].Country) AND ([Food
Consumption].Food_code = [Country Total].Food_Code);"


Since this is an ACTION query, we EXECUTE it, rather than opening it:


CurrentDb.Execute str, dbFailOnError





Hoping it may help,
Vanderghast, Access MVP

 

[Mark]

Michel

THANK YOU!!

Not only for the original code but for the correction

With kind regards,
Mark

-----Original Message-----
Hi,


Because you have built a SELECT query. If you want make a table, build a
MAKE TABLE query. Just two words to add, INTO, a key work, and the
newTableName:


str = "SELECT Sum([FAOstat].[" &
Form_ElementCalcualtion.Text1 & "]*0.01) AS ProJSupply INTO
newTableNameHere
FROM (FAOstat INNER JOIN [Food Consumption] ON
FAOstat.Food_code = [Food Consumption].Food_code) INNER
JOIN [Country Total] ON ([Food Consumption].Country_Name
= [Country Total].Country) AND ([Food
Consumption].Food_code = [Country Total].Food_Code);"


Since this is an ACTION query, we EXECUTE it, rather than opening it:


CurrentDb.Execute str, dbFailOnError





Hoping it may help,
Vanderghast, Access MVP

Hopefull this will solve it

I have had quite a bit of help from this newsgroup and
from that help, the following VBA quey was produced.

Public Sub Query_Value()

Dim str As String

str = "SELECT Sum([FAOstat].[" &
Form_ElementCalcualtion.Text1 & "]*0.01) AS ProJSupply
FROM (FAOstat INNER JOIN [Food Consumption] ON
FAOstat.Food_code = [Food Consumption].Food_code) INNER
JOIN [Country Total] ON ([Food Consumption].Country_Name
= [Country Total].Country) AND ([Food
Consumption].Food_code = [Country Total].Food_Code);"

MsgBox (str)

Set daoRecordset = CurrentDb.OpenRecordset(str)


End Sub


But the result of the query is not produced (ideally in a
table!). Why is that?

Thanks in advance
mark

.