Creating a mult-search tool/form 1

[SherryLynn]

I need to create a search tool in a database to search multiple criteria. I would like it to all be on one form where I can choose which criteria I would like to search. For example, by last name, file number, etc. From the records which come up to match, I would like to have a "button" where I can go to that record's details (the form for entering new/additional information for that record) and carry on from there. Does anyone have any ideas on how I can do this? I am not very familiar with VBA either.

Thanks,
Sherry

 

[Aivars]

Hi!

Create command button and write codes in On Click procedure like following:

Private Sub cmdSearch_Click()
dim strSQL as string
dim strWhere as string

'Make SQL clause
strSQL = "Select * From MyTable "
'Make Where condition text
strWhere = "Where NumericField = " & me!Criteria1 & _
" And TextField = '" & me!Criteria2 & _
"' And DateField = #" & me!Criteria3 & "#"
'etc.
'use <'> for separating text values
'<#> - for separating date values

'Consolide SQL and Where clauses
strSQL = strSQL & strWhere & &quot;;&quot;

'Set subform recordsource (or list box rowsource)
me.SubformName.Form.recordssource = strSQL
'or
me.lstSelected.rowsource = strSQL

End Sub

Aivars

 

[mrfilez]

This is about the closest thing I have found to be a solution to my problem. Right now I am only looking to get records from one field. It will expand to a total of eight.
I used the code you provided and get an error every time I hit the button:

Run-time error '2001'.

You canceled the previous operation.

This is the code that I have so far. I am not very good at all with VB or SQL. I know just about nothing about either.

Private Sub cmdFind_Click()
Dim strSQL As String
Dim strWhere As String

'Make SQL clause... tblMix is a table of lookup fields
'and is the table from which I wish
'to pull my information
strSQL = &quot;Select * From tblMix &quot;
'Make Where condition text... Right now I just wish to get
'search working on one field, CommonID
strWhere = &quot;Where CommonID = '&quot; & Me!txtFind & &quot;'&quot; _

'Consolisate SQL and Where clauses
strSQL = strSQL & strWhere & &quot;;&quot;

'Set Subform recordsource (or list box rowsource)
Me.Form.RecordSource = strSQL
'or
'me.lstSelected.rowsource = strSQL

End Sub

I am not sure where my mistake is. If you need more information than this please let me know. Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome1.fedex.com
mrfilez@midsouth.rr.com

 

[Aivars]

Hi, Don!

I didn't find any mistake in your codes, exclude:

strWhere = &quot;Where CommonID = '&quot; & Me!txtFind & &quot;'&quot; _ this mark is provided for carry-over of command row. But I think it isn't called error message.

Try to do debugging your codes!

If you'll not find wrongs send me part of your application (form and simple table(s) what you use in this form). I'll check out your codes and I'm sure it's not big mistake.

Aivars

alaganovskis@hotmail.com

 

[SherryLynn]

Hi Aivars,

Thank you for responding to my post. I am really not familiar with VBA and I am not sure exactly where to put the code. Do I create a new form and put a command button on that? When I am adding the code to the OnClick procedure, I am a little lost as to which parts of the code I substitute with which information from my end (ie. field names, table names, etc.). There are four different tables that I would be pulling information from. In the code you provided, in the line strSQL = &quot;Select * From MyTable&quot; do I replace MyTable with the first table name? In the line strWhere = &quot;Where NumericField = &quot; & me!Criteria1 & what do I substitute here? Would this be the file number field? Like I said I am really unfamiliar with this stuff so I need lots of direction. I really appreciate your help.

Thanks, Sherry

 

[mrfilez]

I believe that if you already have a form setup for the doing the search you just create the button on the form.
I tell you what... I will highlight in Red what you substitute with your own fieldname or table name.


'You can name the button anything you want, just make sure that the name in the Private Sub line matches it
Private Sub cmdSearch_Click()
dim strSQL as string
dim strWhere as string

'Make SQL clause ' Replace MyTable with your table's name
strSQL = &quot;Select * From MyTable &quot;

'Make Where condition text'This is an example of searching in a Numeric field. If your FileNumber is a numeric field then you would replace the NumericField with your fields name. The criteria part of the statement is what you want to search for.
strWhere = &quot;Where NumericField = &quot; & me!Criteria1 & _'The same goes for Text and Date field.
&quot; And TextField = '&quot; & me!Criteria2 & _
&quot;' And DateField = #&quot; & me!Criteria3 & &quot;#&quot;
'etc.
'use <'> for separating text values
'<#> - for separating date values

'Consolide SQL and Where clauses
strSQL = strSQL & strWhere & &quot;;&quot;

'Set subform recordsource (or list box rowsource)
me.SubformName.Form.recordssource = strSQL
'or
me.lstSelected.rowsource = strSQL

End Sub

I hope this helps you a little. Please let me know if this helped you, confused you, or what. I am not very good at Access myself, so I am not real sure as to how to explain something if it is something I have figured out.
Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome1.fedex.com
mrfilez@midsouth.rr.com

 

[scking]

I have built a form which allows the user to select a filter type from a combo box based on values. The AfterUpdate uses a select statement to read the combo box. A select statement in AfterUpdate the feeds another combo box with the set of acceptable values for that search type. For instance, if you selected 'Ship Type' the values of the other combo box would be set to the list of acceptable values for 'Ship Type'. The user then selects whatever value desired and the filter form sets the filter for the form it is bound to using the Me.OpenArgs. This form is amazingly efficient and handles different search type properties, text, boolean, dates, etc.

Steve King
Growth follows a healthy professional curiosity

 

[mrfilez]

But does it show all of the information for all records searched for using the keyword? Does it work with a table where all of the fields are lookup fields consisting of an ID field and a text field?

Thus if I searched in a NameField for Dave. Would it return all of the information for each record that has the name Dave in the name field, even if the information is actually an ID# and Dave? Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome1.fedex.com
mrfilez@midsouth.rr.com

 

[scking]

The filter form is a totally customizable tool. If instance, if you selected a text value which was actually represented in code I would pass the code value as a parameter through a function which would return the code value I use to search. It might get the code value from a value list in a select statement or a SQL query to the source table but it could return the code value for lookup. It means you might have to write a number of short functions to get code values but the ability to select specific items to search and filter from is worth it to me. If you are interested I'll provide you an .mdb file with the form you could use as an example.

Steve King Growth follows a healthy professional curiosity

 

[mrfilez]

That would be wonderful. I would like to take a look at this in action. Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome1.fedex.com
mrfilez@midsouth.rr.com

 

[SherryLynn]

Hi Steve,

Could you send me the mdb file? I'd really like to try that out. My email is yanch@escape.ca

Thanks very much,
Sherry


Hi Don,

Thanks for your help. I will try that out.
Sherry

 

[snowyej]

Steve,

I am trying to do something similar to what Sherry is doing. If possible, could you send the form my way as well? My email address is snowy_ej@yahoo.com.

Thanks much!
Elizabeth

 

[TheKing]

I am also making a search form, I got it to do the search right but what I would like it to do is find the field names that I have in my sql statement (the sql statement will change each time the program is run). I would also like to check out any search program examples anyone might have and see new ways of doing things that I have not thought of.

Thanks
TheKing

 

[BMats]

Steve - I am also working on a search form and finding out how difficult it is with lookup fields. I would love to have a copy of that mdb file you offered Sherry.

My email address is: rfmatola@yahoo.com

To date, the only solution I have is very specific to the database I am working on. I have had to write a make table query to create a new table with all the lookup fields filled in.

I then have a function that searches each field for the search string.

I got it working, but its ugly. I'm hoping to refine it into something more adaptable to other databases.

Thanks,
Bob

 

[gc]

Hi Steve,

This is a great idea. Could you send me the mdb file? I'd really like to try that out. My email is gchen@snihc.com

Thank you very very much,

George

 

[sdimaggio]

Steve,

I sure would like to take a look as well.

Sdimaggio@onebox.com

Thanks.

Steve

 

[hyousef]

Hi Steve,
I am doing something similar. I am doing something about &quot;cascading comboboxex&quot; and i am getting this error:
Run-time error '2001'.

You canceled the previous operation.

Could you please send me the mdb file, so it might help.
hyousef4@hotmail.com

Thanks a lot

 

[joacal]

Please, how did you get around the 'You canceled the previous operation' problem, if at all?

 

[scking]

One way:

OnError GoTo HandleErr

' Your procedure code here

Exit_Proc:
Exit Sub

HandleErr:
Select Case Err.Number
Case 2456 ' Whatever the error number for the error
Resume Exit_Proc
Case Else
End Select

Steve King Growth follows a healthy professional curiosity

 

[PMrider]

Hi Steve,
Can you please send me the copy of this MDB file too.

Thank you

Alan

akolln@srt.com