convert CreateQueryDef of MDB project into equivalent version in ADP

[rahuldev999]

Hi

I am beginner in the access project.
can anyone suggest how to deal with this
" Set rq = Db.CreateQueryD ef("r_temp_rslt _research", strSql)"
in the below code.As its a MDB application which needs to be converted into ADP.
Any kind of help would be highly appreciated.

Below is the snippet of code:

Dim Db As DAO.Database
Dim rq As DAO.QueryDef
Dim strSql As String
Dim fResearch As Form
 
If IsOpen("f_research") Then
Set fResearch = Forms!f_research
 
With fResearch
.Refresh
strSql = .lst_result_research.RowSource
 
'Supprime la requête r_temp_rslt_research avant de la recréer
On Error Resume Next
DoCmd.DeleteObject acQuery, "r_temp_rslt_research"
On Error GoTo 0
 
Set Db = CurrentDb()
Set rq = Db.CreateQueryDef("r_temp_rslt_research", strSql)
rq.Close
Set rq = Nothing
Set Db = Nothing
 
 
.txt_tot_list_fees = Nz(DSum("listing_fees_amount", "r_temp_rslt_research"), 0)
.txt_tot_cust_act = Nz(DSum("activity_cost", "r_temp_rslt_research"), 0)
.txt_tot_free_goods = Nz(DSum("free_goods_tot", "r_temp_rslt_research"), 0)
.txt_tot_price_off = Nz(DSum("price_off_tot", "r_temp_rslt_research"), 0)
 
.txt_tot_list_fees_bal = Nz(DSum("list_fees_bal_diff_0", "r_temp_rslt_research"), 0)
.txt_tot_cust_act_bal = Nz(DSum("cust_act_bal_diff_0", "r_temp_rslt_research"), 0)
.txt_tot_price_off_bal = Nz(DSum("price_off_bal_diff_0", "r_temp_rslt_research"), 0)
 
End With
End If
 
Set fResearch = Nothing

Many Thanks
Rahul

[Stewart Ross]

Hi Rahul. I have added code tags for your code to make it easier to read.

The querydef line you refer to (line 19) creates a stored query definition for a query named r_temp_rslt_res earch (available as a query in the Access query window once created) from the rowsource SQL of a control on form f_research (line 7).

The control is called lst_result_rese arch (line 11), and would appear to be a listbox. As a querydef cannot be overwritten once created there is a delete line which removes the previous version of that querydef at line 15, before the new version is stored (line 19).

The purpose of the line appears to be to make the listbox rowsource query available outside of the procedure itself - the querydef is not used within your procedure thereafter.

I can't guess at what the rowsource query itself does, as there are no indications of its purpose in the code.

-Stewart

[rahuldev999]

Hi Rahul. I have added code tags for your code to make it easier to read.

The querydef line you refer to (line 19) creates a stored query definition for a query named r_temp_rslt_res earch (available as a query in the Access query window once created) from the rowsource SQL of a control on form f_research (line 7).

The control is called lst_result_rese arch (line 11), and would appear to be a listbox. As a querydef cannot be overwritten once created there is a delete line which removes the previous version of that querydef at line 15, before the new version is stored (line 19).

The purpose of the line appears to be to make the listbox rowsource query available outside of the procedure itself - the querydef is not used within your procedure thereafter.

I can't guess at what the rowsource query itself does, as there are no indications of its purpose in the code.

-Stewart

Hi Stewart

Thanks for your reply.
"strSql = .lst_result_res earch.RowSource "
here lst_result_rese arch is a listbox.
so rowsource of the listbox needs to be inserted into a view or temporary table so that i can replace that view or temporary table in the below lines
.txt_tot_list_f ees = Nz(DSum("listin g_fees_amount", "t_temp_rslt_re search"), 0)
and so on.
I have tried with view but without success.
But I think temporary table is a way where i can insert the data of the rowsource (mentioned above) and perhaps assigned the values to the
txt_tot_list_fe es and etc etc.but again i donno how to do.

Hope you will be in a postion from where you can suggest even more....!

Thanks!
Rahul

[Stewart Ross]

Hi Rahul. I am still not clear on what you need help with. It is difficult to comment further on what you are trying to achieve without knowing any of the fields involved, or the structure of the table/queries. You have an advantage in having the database in front of you; here, all we have to go on is what you tell us.

I would ask you to post the SQL you have tried (from the rowsource of the listbox, as stored in the Access querydef) and the metadata for the tables involved (the actual field names and types, and which fields are primary or secondary keys).

I am sure there are more direct ways to achieve a sum of fees than to create a querydef dynamically to do so.

Please advise also what the listbox is really doing - what choices does the user see and why?

-Stewart

[rahuldev999]

Hi Rahul. I am still not clear on what you need help with. It is difficult to comment further on what you are trying to achieve without knowing any of the fields involved, or the structure of the table/queries. You have an advantage in having the database in front of you; here, all we have to go on is what you tell us.

I would ask you to post the SQL you have tried (from the rowsource of the listbox, as stored in the Access querydef) and the metadata for the tables involved (the actual field names and types, and which fields are primary or secondary keys).

I am sure there are more direct ways to achieve a sum of fees than to create a querydef dynamically to do so.

Please advise also what the listbox is really doing - what choices does the user see and why?

-Stewart

Hi Stewart
Here is the SQL for "strSql = .lst_result_res earch.RowSource "
SELECT * FROM sr_research WHERE Brand like 'Malts' AND (activity_brand _cancelled=0 OR activity_brand_ cancelled Is Null) AND (activity_cance lled=0 OR activity_cancel led Is Null) ORDER BY [Customer name], no_activity_mon th, [No act], Brand;

There is a form "f_brand" where user can see lot of drop down box viz.'name','bra nd','activity', 'status' etc.from where the value "Brand",a drop down box(in this case 'Malts') is selected and records are displayed in the list box "lst_result_res earch" for that brand in the same form.
There is button on the same form on the click of which will display the sum of columns in the text boxes:txt_tot_l ist_fees,txt_to t_cust_act,txt_ tot_free_goods, txt_tot_price_o ff,
txt_tot_list_fe es_bal,txt_tot_ cust_act_bal and txt_tot_price_o ff_bal.

Query for r_temp_rslt_res earch:
SELECT *
FROM sr_research
WHERE (((sr_research.[Customer name]) Like '*Albin SA*') AND ((sr_research.a ctivity_brand_c ancelled)=False Or (sr_research.ac tivity_brand_ca ncelled) Is Null) AND ((sr_research.a ctivity_cancell ed)=False Or (sr_research.ac tivity_cancelle d) Is Null))
ORDER BY sr_research.[Customer name], sr_research.no_ activity_month, sr_research.[No act], sr_research.Bra nd;

In MDb "sr_researc h" is a query ,after migration it becomes a table in sql server(adp).Now in the table,fields involved are customer name (nvarchar,50)Br and(nvarchar,50 ),activity_bran d_cancelled(bit ),activity_canc elled(bit),no_a ctivity_month(i nt),[no act](int).Pls note no keys in the sr_research.
I think this is the whole scenario,little cumbersome.

Thanks for your effort,
Many Thanks
Rahul

[rahuldev999]

Hi Stewart
Here is the SQL for "strSql = .lst_result_res earch.RowSource "
SELECT * FROM sr_research WHERE Brand like 'Malts' AND (activity_brand _cancelled=0 OR activity_brand_ cancelled Is Null) AND (activity_cance lled=0 OR activity_cancel led Is Null) ORDER BY [Customer name], no_activity_mon th, [No act], Brand;

There is a form "f_brand" where user can see lot of drop down box viz.'name','bra nd','activity', 'status' etc.from where the value "Brand",a drop down box(in this case 'Malts') is selected and records are displayed in the list box "lst_result_res earch" for that brand in the same form.
There is button on the same form on the click of which will display the sum of columns in the text boxes:txt_tot_l ist_fees,txt_to t_cust_act,txt_ tot_free_goods, txt_tot_price_o ff,
txt_tot_list_fe es_bal,txt_tot_ cust_act_bal and txt_tot_price_o ff_bal.

Query for r_temp_rslt_res earch:
SELECT *
FROM sr_research
WHERE (((sr_research.[Customer name]) Like '*Albin SA*') AND ((sr_research.a ctivity_brand_c ancelled)=False Or (sr_research.ac tivity_brand_ca ncelled) Is Null) AND ((sr_research.a ctivity_cancell ed)=False Or (sr_research.ac tivity_cancelle d) Is Null))
ORDER BY sr_research.[Customer name], sr_research.no_ activity_month, sr_research.[No act], sr_research.Bra nd;

In MDb "sr_researc h" is a query ,after migration it becomes a table in sql server(adp).Now in the table,fields involved are customer name (nvarchar,50)Br and(nvarchar,50 ),activity_bran d_cancelled(bit ),activity_canc elled(bit),no_a ctivity_month(i nt),[no act](int).Pls note no keys in the sr_research.
I think this is the whole scenario,little cumbersome.

Thanks for your effort,
Many Thanks
Rahul

In addition to the above:
There is a search button "cmd_search " on the form "f_brand" on the click of which listbox lst_result_rese arch display the data.

[rahuldev999]

In addition to the above:
There is a search button "cmd_search " on the form "f_brand" on the click of which listbox lst_result_rese arch display the data.

In short I would like to have a temporary table where I can insert records from
"strSql = .lst_result_res earch.RowSource "
I have tried doing it like this:
"strSql = .lst_result_res earch.RowSource "
str = "insert into t_temp_rslt_res earch ([No act],[Activity month],[Customer name],......"
str = str & "select [No act],[Activity month],[Customer name],....from " & strSql & ""
And when inserted successfully,pr obably i could do this:
.txt_tot_list_f ees = Nz(DSum("listin g_fees_amount", "t_temp_rslt_re search"), 0)

Thanks
Rahul