SEARCH FORM

[MICHELE]

I AM DESIGNING A DATABASE FOR MY COMPANY. I HAVE BEEN TRYING TO MAKE A FORM SO THAT PEOPLE CAN SEARCH ALL RECORDS BY ANY FIELD WITHOUT USING ANY VISUAL BASIC. THERE ARE ABOUT 20 FIELDS WE NEED TO BE ABLE TO SEARCH. ANY IDEAS?

 

[DML]

By "without using Visual Basic", do you mean you don't want your users to have to use Visual Basic? You can "build" a SQL statement based on the user's input in Visual Basic, which would allow the user to choose any number of fields and a value for those fields. Is that what you are trying to do?

 

[MICHELE]

I am not very familiar with visual basic. I have been using Access for a little over 1 year so I'm still learning. I'm pretty good at writing macros with the drop down lists. I've tried a few things that won't work so I was hoping for some other ideas.

 

[DML]

I think that you might have to build a criteria statement using a string variable if you want your form to be that dynamic. You could then set the filter property ( the filter would be like the WHERE clause of a SQL statement, but without the word WHERE) of the form (or report) to that string. Here's a start:

You will need to build a form to allow the user to enter fields and values. I would suggest putting the available search fields in a combo box so you don't have to worry about mis-spellings (you should probably put the datatype as well because it will matter in sytax).

On the click event of a command button:

Dim strFilter as String

if me.txtFieldOne <> "" then

if me.txtValueOne <> "" then
'/// use this if datatype is not text
strCriteria = "[" & me.txtFieldOne & "]= " & me.txtValueOne
'/// use this if datatype is text
strCriteria = "[" & me.txtFieldOne & "]=' " & me.txtValueOne & " ' "
'/// use this if datatype is date
strCriteria = "[" & me.txtFieldOne & "]=#" & me.txtValueOne & "#"
end if '// end (if me.txtValueOne <> "")
end if '// end (if me.txtFieldOne <> "")

I hope this is not too confusing for you. I know that you are a bit unfamiliar with VBA, but now looks like a good time to start! Just take it one step at a time. Get one set of criteria to work before working on multiple ones.

To continue...After you have set the first criteria, you will need to use the AND operator in the string. You can check the length of strFilter to see if you have started building it yet -- if len(strFilter)=0 then...

This is only a suggestion and there are many ways to do it, so I will stop here. Post again if you want to give it a try and need more help (or e-mail me)