Database Form Query

[mogul0212]

No problem, your boss should be happy with the finished project because you are putting togther some pretty advanced forms.

yeah, I think so. When I first started here, told people what I was going to be doing. Basically getting all their CAD drawings to more professional looking set of standards and creating a database of all the drawings and tooling needed to create our product line. When I would tell them I was one the one hired to do that I would hear "oh man, you Poor bastard." It's been a fun challenge to get this up and running. Now the task of gathering all of the tooling information and plugging it in. We have 6 manufacturing plants each with 100's, maybe 1000's of tools. So that part will take some time. Then there is the process of taking all of our CAD Drawings and updating them into my standards I developed and then teaching everyone who uses CAD around here to adopt them.

I think I've got a bit of job security because of all the drawings and tools we have. Drawings estimated at over 3000.

Again, thanks for all your help. Truly appreciate it.

 

[selenau837]

Keith is great, I've seen him all around the forum answering questions.

I'm going to school to be a DBA. I love database, becuase it is like a huge jigsaw puzzle.

I'm glad everything is up and running for you. I'm really glad Keith jumped in.

 

[KeithG]

Thanks for the compliment Selenua, I am like you I love to design databases. How long have you been working with databases?

 

[mogul0212]

The both of you were a great help. You both got me farther then I could have imagined so quickly.

I did notice one thing as I've tested my database a bit. On the "Drawing Search" form. The "# of Passes" criteria doesn't return the correct results, it always gives every record as a result. Any ideas?

 

[mogul0212]

One other thing I've noticed I may want is a search function on the form to give me all the records of drawings that use a particular tool no matter what place it is on the form. If you notice I have 3 outside profile areas and 5 inside profile areas. As I think about it, that could be important for tracking purposes.

How would I set this up for a search to look at all 8 Tool # fields and provide a resultant? Basically have a drop down box that would have all the Tool #'s in it and could select the correct one and then look at the Too # fields and if used in any one of them, results are submitted. But would you get the same record twice if the tool was used in the Outside Tool # and Inside Tool # fields?

Hope that rambling made sense.

 

[selenau837]

Thanks for the compliment Selenua, I am like you I love to design databases. How long have you been working with databases?

I've been working with Access for 2 yrs now. I was tossed into and forced to learn it. I bucked it at first, but now I love it.

I can't wait to start working with 'HUGE' databases. That have thousands of tables and millions of records. I've had a DB2 class, but hadn't had the ability to work with it much. I can create queries, but that is all I'm allowed to do. The DBA's keep tight reign over that.

That is what I want to be when I grow up. That is if I don't fall in love with my new job as a main frame programmer and change my mind.

 

[KeithG]

Here is your coding for cmbPasses

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

This test's the combo box to see if there is a value entered and checks to see if the value is longer than one digit. If so the criteria will get added to the Where clause. We had to add the check to make sure the value was greater than one because your clear button assigns a zero length string to the combo box.

Below is the solution

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

 

[KeithG]

One other thing I've noticed I may want is a search function on the form to give me all the records of drawings that use a particular tool no matter what place it is on the form. If you notice I have 3 outside profile areas and 5 inside profile areas. As I think about it, that could be important for tracking purposes.

How would I set this up for a search to look at all 8 Tool # fields and provide a resultant? Basically have a drop down box that would have all the Tool #'s in it and could select the correct one and then look at the Too # fields and if used in any one of them, results are submitted. But would you get the same record twice if the tool was used in the Outside Tool # and Inside Tool # fields?

Hope that rambling made sense.

Are you saying if there is a tool number entered have the query search all the tool # fields for the tool#?

 

[mogul0212]

Are you saying if there is a tool number entered have the query search all the tool # fields for the tool#?

Yes, exactly. Preferrably on the same form as the one I've created.

 

[KeithG]

I will post an example for you when I return from a meetimg I must goto.

 

[mogul0212]

I will post an example for you when I return from a meetimg I must goto.

Thanks. Appreciate that.

 

[mogul0212]

The other search I'm looking at, is one using the "Individual Tool Information" subform in "Tooling Information".

I'm not sure how to set that up since it has a different record source. Do I need to create a different search form? Because Ideally I'd like it to open in the same type of form that my other tooling search form does.

 

[KeithG]

here is an example of the Drawing Search. If you check the check box the form will search every Tool # field for the tool # in cmbOutsideNo1

 

[KeithG]

I forgot to attach the database

 

[KeithG]

You will have to modify the SQL statement to the query behind you sub-form

 

[mogul0212]

here is an example of the Drawing Search. If you check the check box the form will search every Tool # field for the tool # in cmbOutsideNo1

Is this the only thing I would really need to add to create that check box search feature?

Private Sub chkSearch_AfterUpdate()

    If Me.chkSearch = -1 Then
        Me.cmbOutsideNo2.Enabled = False
        Me.cmbOutsideNo3.Enabled = False
        Me.cmbInsideNo1.Enabled = False
        Me.cmbInsideNo2.Enabled = False
        Me.cmbInsideNo4.Enabled = False
        Me.cmbInsideNo5.Enabled = False
        Me.cmbInsideNo3.Enabled = False
    Else
        Me.cmbOutsideNo2.Enabled = True
        Me.cmbOutsideNo3.Enabled = True
        Me.cmbInsideNo1.Enabled = True
        Me.cmbInsideNo2.Enabled = True
        Me.cmbInsideNo4.Enabled = True
        Me.cmbInsideNo5.Enabled = True
        Me.cmbInsideNo3.Enabled = True
    End If
End Sub

 

[KeithG]

No I also modified this code

If IsNull(Me.cmbOutsideNo1) = False And Len(Me.[cmbOutsideNo1]) > 1 Then
        If Me.chkSearch = 0 Then
            If IsNull(strWhere) = False And Len(strWhere) > 2 Then
                strWhere = strWhere & " AND ([Outside Tool #1]='" & Me.cmbOutsideNo1 & "')"
            Else
                strWhere = " Where  ([Outside Tool #1]='" & Me.cmbOutsideNo1 & "')"
            End If
        Else
            strWhere = " Where  ([Outside Tool #1]='" & Me.cmbOutsideNo1 & "'" & "or [Outside Tool #2]='" & Me.cmbOutsideNo1 & "'" & " Or [Outside Tool #3]='" & Me.cmbOutsideNo1 & "'" & " Or [Inside Tool #1]='" & Me.cmbOutsideNo1 & "'" & " OR [Inside Tool #2]='" & Me.cmbOutsideNo1 & "'" & " Or [Inside Tool #3]='" & Me.cmbOutsideNo1 & "'" & " Or [Inside Tool #4]='" & Me.cmbOutsideNo1 & "'" & " Or [Inside Tool #5]='" & Me.cmbOutsideNo1 & "')"
        End If
    End If

 

[mogul0212]

As I look at that function you just made for the search to look for a specific tool # in any of the columns, I wonder if that shouldn't be the way it does it in the first place. That way could look up multiple tool #'s no matter what Outside/Inside Tool # place they were in.

Does that make sense?

 

[mogul0212]

What I'm trying to say, is have multiple combo boxes to select different Tool #'s from, and then look in each of the Outside/Inside Tool # fields to see if that tool is used in any of them, and if it is, results are returned.

 

[KeithG]

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?