Database Form Query

[mogul0212]

So the user can select 3 tool# and the query will search every tool field and bring back matching results? Is this what you want?

Yes, up to 8 tool combo boxes to select as search criteria.

 

[mogul0212]

The way I had it set up before, was that you'd have to know what column you put the tool in to know where to put it to search. That would completely suck and you'd never get any results.

 

[mogul0212]

well, maybe not that bad, since the drop down boxes only bring back values that are in that column.

 

[KeithG]

Is this what you are wanting? I added a listbox and the user can use as many Tool numbers as they please and the query will search every tool field for the Tool Number. Don't fill in any of the Tool Number combo boxes as they would need to be deleted.

 

[mogul0212]

The only problem with a list box is that over time, I could end up with 100's of tools, and to have to scroll throw them and select each one could become a problem. The items I have in the database right now are just junk filler pieces to have something to fill the tables to test searches with.

 

[mogul0212]

I am getting myself confused now again. I think I'm creating a monster. On one hand sometimes I may want to search for all the drawings that use a particular tool number, while other times, I may want to search for drawings that use varying quantities of different tools, up to a max of 8 tools in a search. I don't know if there is a way to be able to differentiate these two types of searches easily. If I select more then one tool at a time, I guess to me that should mean both sets of tools should be used in a single drawing, and the resultant of the search should show all of the drawings that use all the specified tools in each drawing, not all drawings that use just one of the particular tools in them.

That make sense?

 

[KeithG]

The best thing to do is stop and come up with your requirments of what you want the form to do. Then you it is much easier to design and takes alot less time.

 

[mogul0212]

The best thing to do is stop and come up with your requirments of what you want the form to do. Then you it is much easier to design and takes alot less time.

I put the cart before the horse on this one for sure. What makes the most sense to me is having multiple drop down boxes so a user can either select an entry from them or type in the tool # in each one of them. If there are more then one entry in the drop down boxes, then the search considers them to be grouped and only returns records with both tools used in any of the Tool # fields.

Because the Outside/Inside Tool # field isn't an important relationship between fields, it's just a place holder for a particular tool used in a drawing.

 

[mogul0212]

KeithG...

I have my Drawing Information search criteria nailed down for sure now. I want to have 8 drop down boxes that pulls its drop down menu from "Tooling Information" table, NC Tool # field. That way any tool # shows up as a possibility for the search and can use any amount of the boxes to do a search that pulls results that contain each tool # selected. The search criteria should look in the "Outside Tool #1, Outside Tool #2, Inside Tool #1" etc columns.

Any ideas on the coding to do that?

 

[KeithG]

Isn't that how you originally had it?

 

[mogul0212]

nope, the way it originally was had a drop down menu that pulled info from a specific column and searched only that column. The only way you would know if that tool was used in that column was to scroll through the drop down menu and locate the tool # in the list. I guess I think it would make more sense to be able to search for tools in any of the Outside/Inside tool columns using the NC Tool # field from the "tooling information" table as a drop down menu that then looks at any of the Outside/Inside tool clumns. If you search for more then one tool #, then it only gives results of drawings with both numbers in it. That way you aren't manually searching each column for the tool #.

The way it is currently set up you would have to scroll through each drop down box and locate the tool # to make sure it was/wasn't used in that column before you searched. The columns don't cross reference themselves to see if that particular tool number is used in another column. If that makes any sense.

 

[KeithG]

So basically the example with the list box would work exept for if the user picked more than one tool number it would search for a record with a combination of the tool numbers?

 

[mogul0212]

Yes, the list box you have would work if in only returned results of tools that had that combination. The problem is that with only one list box, I will have hundreds of tools and to have to scroll from top to bottom and select from it may be a bit tough to remember what tools you have selected since you can't see them all when you would hit search.

 

[KeithG]

Won't all of your tools be listed in the combo box also? You can do it the way you want to though. You will have to grab each value from the combo boxes and seperate them with a comma and put them into a string and then use the In(strComboList) in your where clause.

 

[mogul0212]

Won't all of your tools be listed in the combo box also? You can do it the way you want to though. You will have to grab each value from the combo boxes and seperate them with a comma and put them into a string and then use the In(strComboList) in your where clause.

Yes, all the tools would show up in the combo boxes yes and yes you'd have to scroll through them as well, but when you select it in that box, you'd be able to see each selection you made, in the list box you wouldn't be able to see each search item you selected, which could be confusing for some of my users.

 

[mogul0212]

If I create an unbound combo box with a record source of "Tooling Information" NC Tool #, and name each combo box like cmbToolSearch1, cmbToolSearch2, etc...

How does that line of code work you talked about?

 

[mogul0212]

So far I have this line of code

    If IsNull(Me.cmbToolSearch1) = False And Len(Me.[cmbToolSearch1]) > 1 Then
        If IsNull(strWhere) = False And Len(strWhere) > 2 Then
            strWhere = strWhere & " AND ([Outside Tool #1]='" & Me.cmbToolSearch1 & "')"
        Else
            strWhere = " Where  ([Outside Tool #1]='" & Me.cmbToolSearch1 & "')"
        End If
    End If

How do I modify it to search the other Outside/Inside tool fields?

And then is it then copying that code for each cmbToolSearch# then?

 

[KeithG]

First you are going to have to check each combo it see it was data (like you currently are). If it has data assign the value to a string variable with a comma at the end. Then once you have checked all you combo boxes you will have a variable with all the tool numbers seperated by a ,

Something like below

 If IsNull(Me.cmbToolSearch1) = False And Len(Me.cmbtoolsearch) >= 1 Then
        strToolSearch = strToolSearch & "'" & strToolSearch & "'" & ","
    End If
    
    If IsNull(strToolSearch) = False Then
        strWhere= " Where [InsideToolNo1]In(" & strToolSearch & ")" And .....
    End If

 

[mogul0212]

hmmmmmmm I'm not quite getting that...

 

[mogul0212]

I'm updated the form to have the criteria I want, I haven't deleted the older tool combo boxes yet the code for their search yet, but I'm confused about that line of code you gave me.

I see the new string name you gave, strToolSearch

Does that mean I need a line at the begining like

Dim strToolSearch As String

But I don't see the whole Comma thing and how to apply it.