Search form, multiple fields in conjunction with one another

[damageinc86]

I am brand new to access, and was beginning to set up a database that will hold info about all of my physical photographs I took back in my film days, because I'd like to assign each photo a number, and spot in a book/box, and index all that info in this database so I can easily search and find a photo without having to wonder which box and package was that in again?

So I have tried to follow all of these different tutorial vids on youtube to attempt to get a grasp on the concept of a search form, but they are mostly just one text box, or they run a query or something, and every time I work through this my database search starts to break down because something is happening when I start adding more than my PhotoNumber search.

I know there's probably some simple concept behind this that if I just knew how, could implement. But nothing I've found seems to address what I'm trying to achieve. I attached a screenshot of my form the way it is. I can easily do an = expression to show a record matching whatever PhotoNumber I type in. The last tutorial I watched had a filter linked to the search button itself using the macro builder and having all the statements in there. But when I add the second statement of Subject with the wildcards, then once I search once in the subject text box, and then clear my entry and type a photonumber in to search with, it just returns all the results in the database.

Also, once I added another OR statements for wildcards on city, it won't even return any results for any field at all. It seems like anything past the first statement just breaks the whole thing.

Is there anyone who can help me make this search form work? All I want to do is be able to combine terms to search, do a single term search, or do a nothing (clear) term search and get all records. Such as, all photos taken in 2004, month of July, city *wildcard*, and subject *wildcard*. Basically everything needs to be able to be combined for a search, and everything needs to be a wildcard except year and photonumber. Eventually I'd also like to have a drop-down list for selected a State abbreviation to add to the search criteria also.

 

[sneuberg]

You mentioned macros. I strongly suggest you learn VBA. To get started I suggest this video series.

For this problem I suggest you build a filter in code piece by piece and then apply it at the end. I've attached a database which demonstrates what I mean. This database uses the customer table from the Northwind database. If you look at the code for the Search button you will see that I start by initializing the variable VarFilter to null, i.e.,

Dim VarFilter As Variant
VarFilter = Null

After that there is (or would be) a section of code for each textbox on the search form that builds the filter for that textbox for example:

If Not IsNull(Me.[COLOR="blue"]Last_Name[/COLOR]) Then
    If IsNull(VarFilter) Then
        VarFilter = "[COLOR="darkred"][Last Name][/COLOR] Like '" & Me.[COLOR="blue"]Last_Name[/COLOR] & "' "
    Else
        VarFilter = VarFilter & " [COLOR="seagreen"]And[/COLOR] [COLOR="darkred"][Last Name] [/COLOR]Like '" & Me.[COLOR="blue"]Last_Name[/COLOR] & "' "
    End If
End If

Note that first the textbox is checked to see if anything has been entered. If not the section of code is skipped and VarFilter remains unchanged. If there is nothing in VarFilter then the condition is added to it without the And . If VarFilter already has a condition in it that the condition is extended with And. Note that the field name [Last Name] is this example must be in the recordset of the subform and in this example it is a text field. If it were a numeric field then the single quotes would not be added.

You would add a section of code like this for each textbox. In the example database I added one more for Company.

After the filter is build, if one has been built, it is applied to the subform with the following code:

If Not IsNull(VarFilter) Then
    Me.sfrmCustomers.Form.Filter = VarFilter
    Me.sfrmCustomers.Form.FilterOn = True
Else
    Me.sfrmCustomers.Form.FilterOn = False
End If

Please note that I didn't embed any wild cards in this. As this is written it would be up to the user to add wild cards, e.g. *. Also I noticed as I was describing this code that it could be simplified but I leave that to you.

 

[Uncle Gizmo]

I have compiled three sets of YouTube videos (Play Lists) which demonstrate how to build your own search form from scratch...

If you watch the last set of videos first:- Microsoft Access - Easy Search Criteria

You will see an example with option groups.

The three play lists cover the subject of a search form from a simple example to a reasonably sophisticated solution. You should be able to find a level that suits you.

MS Access - Building Search Criteria
MS Access - Building Advanced Search Criteria
Microsoft Access - Easy Search Criteria

BTW, these examples are for just one subform, you should be able to modify it to pass the SQL statement to all the subforms.

Also if you want to search every field for the same search term, then that should be a simple modification.

If you get stuck and need help, then post the video url and the time index and I can, advise you...

 

[damageinc86]

thanks for the ideas, I appreciate it. It all seems so simple in concept, and sort of makes sense when I read the code and stuff. I couldn't even figure out how to go about transferring my fields and txtboxes to that customers template in practice though. I just don't understand the connections, and where i would put things within that VB code window. :banghead: I just wish I "got it".

 

[JHB]

Post your database, zip it because you haven't post 10 post yet, then I'll show you how to set up the search function.

 

[damageinc86]

Sure thing. Here it is. Thanks.

 

[JHB]

Try it now, database attached.

 

[damageinc86]

WOW, thank you! That is doing the trick perfectly! I can see it better now in the code, maybe if I study it a bit I can begin to see it clearer. This is exactly what i wanted, thanks again I really appreciate it.

 

[Shamass94]

Hi #JHB
I saw what you do to #damageins86 and I do something like it
but it didn't work and give me error
plz check the attached pics to know what I am talking about

 

[JHB]

I think you're using lookup as table fields.
Else post your database with some sample data, zip it because you haven't post 10 post yet.

 

[damageinc86]

Now I'm wondering about actual Visual Basic, and making a standalone program to search. I tried to connect the database, and got that working, re-created the access search fields and buttons. I guess there is a little bit of difference in coding, because when i put the code from the access vb stuff, it didn't work. I am content with the access form, but is there a way to have a visual basic windows forms program by itself to search the database without access?

 

[Shamass94]

Yes I zip it but I have many problem in this Access
I am working on it, but I will be appreciated if you check it for me
the fields I am asking about is [Customer Info] Form

View attachment Main Software.zip

I am trying to make something like the template "Goods", but its look very hard
You can check it in the other zip file

View attachment Goods.zip

Thank you so much

 

[JHB]

A lot of stuff to change.

• Bad/wrong fields name.
• Wrong code.
• Not the right form to filter.
• Subform's data looked.

Don't use spaces and special characters in object/field names, ex. "Contacts-subform".
Database attached.

I don't know what you mean about "Template Goods".

 

[Shamass94]

Thanks a lot, I see now where was the problem....
I will work on all the issues you tell me about it and will be very appreciated if you check it for me after finishing it.

The template "Goods" is a template database from the Access Templates i use it to create my database considering the main issues.

 

[JHB]

Thanks a lot, I see now where was the problem....

You're welcome, good luck.

 

[JHB]

You can reduce the code to the below if you add a field to the table "Basic-Fields-For-Use".

Private Sub btnSearch_Click()
  Dim sqlStreng As String
  Dim filterStr As String
  
  If Not IsNull(Me.Combo52) Then
    filterStr = "[" & Me.Combo52.Column(1) & "] Like '*" & Me.Text3 & "*'"
    Me.Contacts_subform.Form.Filter = filterStr
    Me.Contacts_subform.Form.FilterOn = True
  End If
End Sub

 

[damageinc86]

Do you have any experience bringing this search code over to visual studio? I have linked the database as a data source, and got the table to populate the box under my search fields like it does when the "clear" button is pressed in the access version. I copied and pasted the search code into the search button onclick, and it fills the code area full of red squiggly lines, and an error when you run the form app.

 

[JHB]

Do you have any experience bringing this search code over to visual studio?

Sorry no!
Maybe you'll get a better response if you post you question in the "Visual Basic" forum on this site.