query by forms (1 Viewer)

[Sudha]

I have created a form for a query. Now I want the input from the form to be the parameter criteria for the query and then display the result in the same form.

 

[prao]

Are you trying to filter and show the form.

 

[Sudha]

No, I have a combo box using which I want to select a value and then that value shoud be the criteria(the parameter box option) for the query and then display the result in the same form(as this form is for this query

 

[prao]

Ok, I got it. It is very simple. Just build the query dynamically and set the query to the record source and do a requery.

Ex : We will take the customer form.
When you select a customer name from the combo box, the form will show the details for that customer.

//Write this code in the On change event of the combo box.

custname = me.cmbname.column(0)
sql = "select * from customer where customer_name = '" & custname & "'"
me.recordsoruce = sql
me.requery

"cmbname" is the combo box name.

I think this will help you. If you have any problem, please email me the form to prao@campsys.com.

 

[Sudha]

I think I have put my problem wrongly. let me give you some more details. I have a query based on two tables joined by 'Staff No'. The fileds in my query are 'Discipline', 'Category', 'Week No', Hour',Rate'. I have used the group by option. I have created a form for this query and in the form I have an unbound combo box with row source as 'select distinct....' and the after update as 'me.requery'. Now in the query for the week no I have placed the criteria as [forms]![formname]![comboboxname]. What should I choose in the total column(since I am using the groupby option)? Right now I have used the 'where' option and hence had to uncheck the show box and had to create another column for week no with group by option and show box checked.

 

[prao]

What do you mean by total column. Do you calculate "Total" for any column or what ?
I cannot understand your question.
Can you be more precise and tell me what happens to your form. Whether it is not displaying the data or getting an error ?

 

[Sudha]

I am sorry, it is not the total column, but the total row(where group by condition is given or press View and select total). My form is not displaying any data as the is no value in the combo box to choose from. If I give any value by typing it,then also no data is displayed.

 

[prao]

I am not able to understand what you say.
Can you email me the form, if you dont have any problem. By the by, where are you from ?
I have given my email address in one of the reply.