By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

convert CreateQueryDef of MDB project into equivalent version in ADP

P: 7
Hi

I am beginner in the access project.
can anyone suggest how to deal with this
" Set rq = Db.CreateQueryDef("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:
Expand|Select|Wrap|Line Numbers
  1. Dim Db As DAO.Database
  2. Dim rq As DAO.QueryDef
  3. Dim strSql As String
  4. Dim fResearch As Form
  5.  
  6. If IsOpen("f_research") Then
  7. Set fResearch = Forms!f_research
  8.  
  9. With fResearch
  10. .Refresh
  11. strSql = .lst_result_research.RowSource
  12.  
  13. 'Supprime la requête r_temp_rslt_research avant de la recréer
  14. On Error Resume Next
  15. DoCmd.DeleteObject acQuery, "r_temp_rslt_research"
  16. On Error GoTo 0
  17.  
  18. Set Db = CurrentDb()
  19. Set rq = Db.CreateQueryDef("r_temp_rslt_research", strSql)
  20. rq.Close
  21. Set rq = Nothing
  22. Set Db = Nothing
  23.  
  24.  
  25. .txt_tot_list_fees = Nz(DSum("listing_fees_amount", "r_temp_rslt_research"), 0)
  26. .txt_tot_cust_act = Nz(DSum("activity_cost", "r_temp_rslt_research"), 0)
  27. .txt_tot_free_goods = Nz(DSum("free_goods_tot", "r_temp_rslt_research"), 0)
  28. .txt_tot_price_off = Nz(DSum("price_off_tot", "r_temp_rslt_research"), 0)
  29.  
  30. .txt_tot_list_fees_bal = Nz(DSum("list_fees_bal_diff_0", "r_temp_rslt_research"), 0)
  31. .txt_tot_cust_act_bal = Nz(DSum("cust_act_bal_diff_0", "r_temp_rslt_research"), 0)
  32. .txt_tot_price_off_bal = Nz(DSum("price_off_bal_diff_0", "r_temp_rslt_research"), 0)
  33.  
  34. End With
  35. End If
  36.  
  37. Set fResearch = Nothing
Many Thanks
Rahul
Jun 9 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
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_research (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_research (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
Jun 9 '08 #2

P: 7
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_research (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_research (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_research.RowSource"
here lst_result_research 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_fees = Nz(DSum("listing_fees_amount", "t_temp_rslt_research"), 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_fees 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
Jun 10 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Jun 10 '08 #4

P: 7
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_research.RowSource"
SELECT * FROM sr_research WHERE Brand like 'Malts' AND (activity_brand_cancelled=0 OR activity_brand_cancelled Is Null) AND (activity_cancelled=0 OR activity_cancelled Is Null) ORDER BY [Customer name], no_activity_month, [No act], Brand;

There is a form "f_brand" where user can see lot of drop down box viz.'name','brand','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_research" 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_list_fees,txt_tot_cust_act,txt_tot_f ree_goods,txt_tot_price_off,
txt_tot_list_fees_bal,txt_tot_cust_act_bal and txt_tot_price_off_bal.

Query for r_temp_rslt_research:
SELECT *
FROM sr_research
WHERE (((sr_research.[Customer name]) Like '*Albin SA*') AND ((sr_research.activity_brand_cancelled)=False Or (sr_research.activity_brand_cancelled) Is Null) AND ((sr_research.activity_cancelled)=False Or (sr_research.activity_cancelled) Is Null))
ORDER BY sr_research.[Customer name], sr_research.no_activity_month, sr_research.[No act], sr_research.Brand;

In MDb "sr_research" is a query ,after migration it becomes a table in sql server(adp).Now in the table,fields involved are customer name (nvarchar,50)Brand(nvarchar,50),activity_brand_can celled(bit),activity_cancelled(bit),no_activity_mo nth(int),[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
Jun 11 '08 #5

P: 7
Hi Stewart
Here is the SQL for "strSql = .lst_result_research.RowSource"
SELECT * FROM sr_research WHERE Brand like 'Malts' AND (activity_brand_cancelled=0 OR activity_brand_cancelled Is Null) AND (activity_cancelled=0 OR activity_cancelled Is Null) ORDER BY [Customer name], no_activity_month, [No act], Brand;

There is a form "f_brand" where user can see lot of drop down box viz.'name','brand','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_research" 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_list_fees,txt_tot_cust_act,txt_tot_f ree_goods,txt_tot_price_off,
txt_tot_list_fees_bal,txt_tot_cust_act_bal and txt_tot_price_off_bal.

Query for r_temp_rslt_research:
SELECT *
FROM sr_research
WHERE (((sr_research.[Customer name]) Like '*Albin SA*') AND ((sr_research.activity_brand_cancelled)=False Or (sr_research.activity_brand_cancelled) Is Null) AND ((sr_research.activity_cancelled)=False Or (sr_research.activity_cancelled) Is Null))
ORDER BY sr_research.[Customer name], sr_research.no_activity_month, sr_research.[No act], sr_research.Brand;

In MDb "sr_research" is a query ,after migration it becomes a table in sql server(adp).Now in the table,fields involved are customer name (nvarchar,50)Brand(nvarchar,50),activity_brand_can celled(bit),activity_cancelled(bit),no_activity_mo nth(int),[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_research display the data.
Jun 11 '08 #6

P: 7
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_research display the data.
In short I would like to have a temporary table where I can insert records from
"strSql = .lst_result_research.RowSource"
I have tried doing it like this:
"strSql = .lst_result_research.RowSource"
str = "insert into t_temp_rslt_research ([No act],[Activity month],[Customer name],......"
str = str & "select [No act],[Activity month],[Customer name],....from " & strSql & ""
And when inserted successfully,probably i could do this:
.txt_tot_list_fees = Nz(DSum("listing_fees_amount", "t_temp_rslt_research"), 0)

Thanks
Rahul
Jun 11 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.