query don't run from vba (1 Viewer)

basilyos

Registered User.
Local time
Today, 12:44
Joined
Jan 13, 2014
Messages
252
i have a make table query that i want to run it after adding new data


Code:
DoCmd.Save
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qry_Sum_Edbarat"
the new data will be saved the set warnings will be set to false but the make table query doesn't run, but if i double click on the query it will run and make the table and everytime and double click the query run


so what should i do to resolve this problem ?


thanks
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 19:44
Joined
Jul 9, 2003
Messages
16,245
If you look in Microsoft help here:- DoCmd.OpenQuery method (Access)

You will see that it states:- "You can use the OpenQuery method to open a select or crosstab query in Datasheet view, Design view, or Print Preview."

I think that also means that the DoCmd.OpenQuery method (Access) cannot run action queries, as I think they are termed.
 

isladogs

MVP / VIP
Local time
Today, 19:44
Joined
Jan 14, 2017
Messages
18,186
Sorry Tony but that's not true. In fact the next sentence from the same paragraph in that link explicitly says so:

You can use the OpenQuery method to open a select or crosstab query in Datasheet view, Design view, or Print Preview. This action runs an action query. You can also select a data entry mode for the query.

@Basilyos
Although you can do this, make table queries should be used rarely if at all as repeated use will cause database bloat & possibly instability.
There is often a better approach

Please post the query SQL for your make table query
 

basilyos

Registered User.
Local time
Today, 12:44
Joined
Jan 13, 2014
Messages
252
so how to resolve this problem.
am sure there is a way
because it's impossible to let the user close the form and run the query manually
 

basilyos

Registered User.
Local time
Today, 12:44
Joined
Jan 13, 2014
Messages
252
Code:
SELECT tbl_Personal_Sanctions_02.PID, tbl_Personal_Sanctions_02.Sanction_Type, Sum(tbl_Personal_Sanctions_02.Sanction_Value) AS SumOfSanction_Value INTO tbl_Sum_Edbarat
FROM tbl_Personal_Sanctions_02
GROUP BY tbl_Personal_Sanctions_02.PID, tbl_Personal_Sanctions_02.Sanction_Type
HAVING (((tbl_Personal_Sanctions_02.Sanction_Type)<>""));
 

isladogs

MVP / VIP
Local time
Today, 19:44
Joined
Jan 14, 2017
Messages
18,186
I can't see any reason why it doesn't work.

Using DoCmd.SetWarnings False means you won't know if its running or not. If the table already exists you will just overwrite it 'sliently'

Try disabling that line temporariliy
If it now works restore that line AND add DoCmd.SetWarnings True afterwards

If it still fails, try this instead (with set warnings switched on while you test)

Code:
DoCmd.RunSQL "SELECT tbl_Personal_Sanctions_02.PID, tbl_Personal_Sanctions_02.Sanction_Type, Sum(tbl_Personal_Sanctions_02.Sanction_Value) AS SumOfSanction_Value INTO tbl_Sum_Edbarat
FROM tbl_Personal_Sanctions_02
GROUP BY tbl_Personal_Sanctions_02.PID, tbl_Personal_Sanctions_02.Sanction_Type
HAVING (((tbl_Personal_Sanctions_02.Sanction_Type)<>""));"
 

basilyos

Registered User.
Local time
Today, 12:44
Joined
Jan 13, 2014
Messages
252
when i set warning to true it works but after second run not from the first run


any other solution to sum the data instead of make table query ??
 

basilyos

Registered User.
Local time
Today, 12:44
Joined
Jan 13, 2014
Messages
252
guys i don't why but i run the query after closing the form and now everything work i tested with many many records
 

Tieval

Still Clueless
Local time
Today, 19:44
Joined
Jun 26, 2015
Messages
475
guys i don't why but i run the query after closing the form and now everything work i tested with many many records
You probably have saved it properly now, switching from design view to form view doesn't necessarily do this.
 

Users who are viewing this thread

Top Bottom