searching quantity by using operator

[mathanraj76]

Hi..
I have a problem with searching query

1. I have one textbox named "txtqty"


2. two combobox with named "cbooperator" and "cbomagazine"

wht i want to do is

if i select cbooperator sign > then type quantity into txtqty textbox and select cbomagazine and click search command button
then it must show all records according to my search criteria..
it must show in my qryall subform
and the data must save in qryall

for an example
> 1 FFT then it must show all the quantity from 2 to 10 with magazine name FFT

I need help on this because i don't how to do it..

I have attached sample database..

Thanks
Mathanraj

[June7]

This effort is greatly complicated because your data structure is not normalized.

Your "Q" fields are set as text type. If this is supposed to be a quantity value then the fields should be number type. Your data will not filter/sort properly with these text fields. The number 10 will sort before the number 2. So after changing the field type, code like:

Private Sub cmdSearch_Click()
Me.qryall_subform.Form.FilterOn = False
Me.qryall_subform.Form.Filter = "[Q" & Me.cbomagazine & "]" & Me.cboOperator & Me.txtqty
Me.qryall_subform.Form.FilterOn = True
End Sub

Could do this with a single form. Put the search controls in form header and the data controls in detail section. Set the form as Continuous view and arrange the controls to look like Datasheet. Then the code would be:

Private Sub cmdSearch_Click()
Me.FilterOn = False
Me.Filter = "[Q" & Me.cbomagazine & "]" & Me.cboOperator & Me.txtqty
Me.Form.FilterOn = True
End Sub

[mathanraj76]

Yes it working as i want ..
u r really great and expert..

How to sent the data which show filter on the form to query table so that i can export to ms excel ..

Thanks
Mathanraj

[June7]

That is a LOT more complicated. I think will have use code to modify query definition with QueryDefs collection. Review http://office.microsoft.com/en-us/ac...005186774.aspx

[mathanraj76]

wht i meant is when i use the operator and type in the qty and select magazine and it will show the result on the form..
This data which shows on the form must sent to query table and
from the query table i will able sent to ms excel

form an example
if > 1 fft then it will show all result on the forms and this result must sent to query table.

I Have some code where i get from net but i need to do inside this code
I Have the sample code..

Thanks
mathanraj

[June7]

That code is all what I suggested. What is issue? Is code not working? Why - error message, wrong results, nothing happens?

Review link at bottom of post for debugging guidelines.

[mathanraj76]

Yeah that code is not working..

[June7]

Why? What happens when you run the code? Have you debugged?

[mathanraj76]

yes i did debug but nothing error r shown ..
but when i open the query table it's show me the error message"Query must have at least one destination field"
I don't where is wrong and i really under pressure..
by monday i need to submit this project..

I really need your help ..
Thanks
mathanraj

[June7]

Provide db with your attempted code.

[mathanraj76]

I have attached the db

1.if i choose the operator and type the data and choose the magazine it will show the result in my subform
2.whatever show in my subform must save into the query "tbltesting" when i click the command button

currently i receiving error message..

Thanks
Mathanraj

[June7]

Why do you reference a table/query called [Magazine and qty] that doesn't even exist in your db?

Is this what you want?
qdf.SQL = "SELECT qryall.* FROM qryall WHERE Q" & Me.cbomagazine & Me.cboOperator & Me.txtqty & " ORDER BY " & Me.txtqty & ";"

[mathanraj76]

wow.. u r.. really very good..
That what i want and you did it..
u r really expert..

thank you very much.

thanks
Mathanraj