Search Form

[cassey14]

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

[nico5038]

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

dim strFilter as string
 
strFilter = ""
 
IF len(nz(Me.Textbox1)) > 0 then
' textbox is filled
strFilter = strFilter & " and textfield1='" & me.textbox1 & "'"
endif
 
IF len(nz(Me.combo1)) > 0 then
strFilter = strFilter & " and numfield1=" & me.combo1
endif
 
...etc.
 
' Check filter is filled and strip off first " and " string
IF len(strFilter) > 0 then
me.subformname.form.filter = mid(strFilter,5)
me.subformname.form.filteron = true
else
me.subformname.form.filteron = false
endif

Getting the idea ?

Nic;o)

[cassey14]

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

dim strFilter as string
 
strFilter = ""
 
IF len(nz(Me.Textbox1)) > 0 then
' textbox is filled
strFilter = strFilter & " and textfield1='" & me.textbox1 & "'"
endif
 
IF len(nz(Me.combo1)) > 0 then
strFilter = strFilter & " and numfield1=" & me.combo1
endif
 
...etc.
 
' Check filter is filled and strip off first " and " string
IF len(strFilter) > 0 then
me.subformname.form.filter = mid(strFilter,5)
me.subformname.form.filteron = true
else
me.subformname.form.filteron = false
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!

[cassey14]

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!

[MMcCarthy]

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

[cassey14]

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?

[nico5038]

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)