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

Search Form

P: 43
Hi i hope someone help me on this..Im doing a search form..In my search form I have 7 fields to search these are:

caseID [textbox]
case Summary [combo box]
Resolution Summary [combo box]
Status Summary [combo box]
Escalated To [combo box]
Date From [textbox]
Date To [textbox]

When I want to search a record all the details will display in the subform..And also I would like to use any of the fileds above in searching..

please help me..i am new in using access database..


thank you in advance...

-cassey
Jan 18 '07 #1
Share this Question
Share on Google+
6 Replies


nico5038
Expert 2.5K+
P: 3,072
When you know how to write VBA, then use stringing to build a filter like:

Expand|Select|Wrap|Line Numbers
  1. dim strFilter as string
  2.  
  3. strFilter = ""
  4.  
  5. IF len(nz(Me.Textbox1)) > 0 then
  6. ' textbox is filled
  7. strFilter = strFilter & " and textfield1='" & me.textbox1 & "'"
  8. endif
  9.  
  10. IF len(nz(Me.combo1)) > 0 then
  11. strFilter = strFilter & " and numfield1=" & me.combo1
  12. endif
  13.  
  14. ...etc.
  15.  
  16. ' Check filter is filled and strip off first " and " string
  17. IF len(strFilter) > 0 then
  18. me.subformname.form.filter = mid(strFilter,5)
  19. me.subformname.form.filteron = true
  20. else
  21. me.subformname.form.filteron = false
  22. endif
Getting the idea ?

Nic;o)
Jan 18 '07 #2

P: 43
When you know how to write VBA, then use stringing to build a filter like:

Expand|Select|Wrap|Line Numbers
  1. dim strFilter as string
  2.  
  3. strFilter = ""
  4.  
  5. IF len(nz(Me.Textbox1)) > 0 then
  6. ' textbox is filled
  7. strFilter = strFilter & " and textfield1='" & me.textbox1 & "'"
  8. endif
  9.  
  10. IF len(nz(Me.combo1)) > 0 then
  11. strFilter = strFilter & " and numfield1=" & me.combo1
  12. endif
  13.  
  14. ...etc.
  15.  
  16. ' Check filter is filled and strip off first " and " string
  17. IF len(strFilter) > 0 then
  18. me.subformname.form.filter = mid(strFilter,5)
  19. me.subformname.form.filteron = true
  20. else
  21. me.subformname.form.filteron = false
  22. endif
Getting the idea ?

Nic;o)

Hi!tanx for the reply..im going to try this one..il update you once its done..tanx much!
Jan 19 '07 #3

P: 43
Hi!tanx for the reply..im going to try this one..il update you once its done..tanx much!
Ive tried the code but its seems that it didnot work..when I want to search in combo box theres an error?can you please explain further?

thank you!
Jan 19 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Ive tried the code but its seems that it didnot work..when I want to search in combo box theres an error?can you please explain further?

thank you!
This code assumes your combo box has a number field as a unique identifier which would be standard practice. If however your combo box is bound to a text field then change the combo filter to the same as the previous textbox filter with the single quotes.

Mary
Jan 19 '07 #5

P: 43
This code assumes your combo box has a number field as a unique identifier which would be standard practice. If however your combo box is bound to a text field then change the combo filter to the same as the previous textbox filter with the single quotes.

Mary

Hi can you give me an example??I am just new in doing database..How about the record source of my subform?
Jan 19 '07 #6

nico5038
Expert 2.5K+
P: 3,072
The code was a sample for two types of information, text (needs surrounding single quotes) and numbers (no quotes needed). Date fields will need surrounding # characters and the fieldnames in the code were just a sample, you'll need to code an IF statement for every field/combo you have on your form and use the Name for the control on the form to make this work.

Just use the code to do the job for one field, test it and when it works add the next IF, etc. That way you can check when and where it goes wrong and we can assist you.

Nic;o)
Jan 19 '07 #7

Post your reply

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