Database Form Query

[mogul0212]

RuralGuy,

Thanks, I'll take a look at that now and see what I can find out.

 

[KeithG]

Is this what you want?

Open frmExample and select the criteria and hit view form. Is this what you wanted?

 

[mogul0212]

weird thing with the prompts...

there is a prompt with just a "T"

I don't see a line in the code that should just ask for "T" value though

 

[mogul0212]

KeithG...

What I'm looking for is a form that has drop down boxes that you can select a set from a list of information entered into a table, that way you don't have to know the exact wording/spelling or any variable of something in that column from a table. If I say have 20 drop down boxes of info, I want to be able to select 1 of the 20 and get results that use a boolean type search if the drop down box on that search form is left empty. Or if want 19 of the 20 selected or any any combination of entries selected in the drop down boxes returns results. IF that makes sense?

 

[KeithG]

What I'm looking for is a form that has drop down boxes that you can select a set from a list of information entered into a table

A set of what? Can you elaborate. Does frmExample not fit your needs?

 

[mogul0212]

KeithG...

Your example is it in a nutshell I believe, Yes.

You make that look easy.

Could you please explain what you did to do that?

My brain is mush right now and even the simplest task right now is getting my blood pressure up.

 

[KeithG]

Basically I wrote a procedure to modify the Where Clasue in the SQL statement behind the the form that shows the results. If the the comblo box on frmExample is not null then its value gets added to the where clause.

 

[mogul0212]

Basically I wrote a procedure to modify the Where Clasue in the SQL statement behind the the form that shows the results. If the the comblo box on frmExample is not null then its value gets added to the where clause.

Ok, so what you did in like 10 minutes is what I've been trying to get to work for days now. Thank you very much for that effort. You even applied it to the form I want it in. That rocks. Now I what is the easiest way to add the rest of the items in the Tooling Information form as drop down boxes for criteria?

 

[selenau837]

Ok, so what you did in like 10 minutes is what I've been trying to get to work for days now. Thank you very much for that effort. You even applied it to the form I want it in. That rocks. Now I what is the easiest way to add the rest of the items in the Tooling Information form as drop down boxes for criteria?

Yeah, I know...he's good like that. I will be one day......

 

[KeithG]

Just add two new if statements for each combo box you add. The first if statement checking if the combo box is null. If it is not null a second if statement checking to see if strWhere is null. If it is not null add the value of the combo box to the existing Where clause. Good Luck!!

 

[mogul0212]

Yeah, I know...he's good like that. I will be one day......

Well, I can't say thank you enough. You really pulled through for me. I truly appreciate the effort. I don't know how long it would have taken me to get this done with your help.

And I'll try pay it forward as best I can.

 

[selenau837]

Well, I can't say thank you enough. You really pulled through for me. I truly appreciate the effort. I don't know how long it would have taken me to get this done with your help.

And I'll try pay it forward as best I can.

What you can do it complete it and then post a copy of the finished copy, or at elast the code so that others in your situation can benefit..

Good luck and I'm glad he came about.

 

[mogul0212]

Just add two new if statements for each combo box you add. The first if statement checking if the combo box is null. If it is not null a second if statement checking to see if strWhere is null. If it is not null add the value of the combo box to the existing Where clause. Good Luck!!

Ok, I'll try my best to add the info like you said. Would you mind a few more questions if I run into them as I go further into this? Should I post them in this thread or a private message ok?

Thanks so much for all your help.

 

[KeithG]

Feel free to post all your questions. If I can't answer them I sure someone can.

 

[mogul0212]

KeithG

Just curious, to add the drop down boxes to the form you created, do I need my orginal tables "SearchCriteria-Tooling" Table, and the "Result-Tooling" Query that I created?

 

[KeithG]

No I don't believe you will need those.

 

[mogul0212]

KeithG

Actually, do need to have that separate table. I noticed if I deleted the info in one of those drop down menus, it deleted that info in the table it was pull from. So I do need a separate search criteria table to have as a pull down menu to make sure no data is deleted.

I'm struggling with how to add a new line. I tried adding one more drop down box as a criteria, and I get all records back as a result. I changed the names in the onclick criteria to match accordingly. But no luck.

Any ideas where I may have gone wrong?

 

[KeithG]

you should post your code and I will tell you.

 

[mogul0212]

you should post your code and I will tell you.

Here's my code...
Private Sub Command15_Click()

Dim strSelect, strWhere, strFrom As String
Dim strNcTool, strNcDiv, strMaterial As String
Dim defQuery As QueryDef



strSelect = "SELECT [Tooling Information].[NC Tool #], [Tooling Information].[NC Division], [Tooling Information].[Profile # or Name], [Tooling Information].[Profile Type], [Tooling Information].[Tool Type], [Tooling Information].Notes, [Tooling Information].[Tool Material], [Tooling Information].[Shank Diameter], [Tooling Information].[Minor Diameter], [Tooling Information].[Max Cut Depth], [Tooling Information].[Board Thickness], [Tooling Information].[Tool Description], [Tooling Information].[Tool used in Fergus Falls - Veneer], [Tooling Information].[Tool used in Corbin - Thermofoil], [Tooling Information].[Tool used in Vanceburg, KY - Veneer], [Tooling Information].[Tool used in Vanceburg, KY - Wood], [Tooling Information].[Tool used in Arkansas City, KS - Veneer], [Tooling Information].[Tool used in Fergus Falls - Thermofoil], [Tooling Information].[AutoCAD File], [Tooling Information].[PDF File]"
strFrom = " FROM [Tooling Information]"

If IsNull(Me.cmbNCTool) = False Then
strWhere = " Where ([NC Tool #]='" & Me.cmbNCTool & "')"
End If

If IsNull(Me.cmbNCDivision) = False Then
If IsNull(strWhere) = False And Len(strWhere) > 2 Then
strWhere = strWhere & " AND ([NC Division]='" & Me.cmbNCDivision & "')"
Else
strWhere = " Where ([NC Division]='" & Me.cmbNCDivision & "')"
End If
End If

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

If IsNull(Me.Profile#) = False Then
If IsNull(strWhere) = False And Len(strWhere) > 2 Then
strWhere = strWhere & " AND ([Profile # or Name]='" & Me.Profile# & "')"
Else
strWhere = " Where ([Profile # or Name]='" & Me.Profile# & "')"
End If
End If

Set defQuery = CurrentDb.QueryDefs("Query1")

defQuery.SQL = strSelect & strFrom & strWhere
defQuery.Close

DoCmd.OpenForm "Tooling Information"



End Sub

I've also attached my file so you can look if you want to see what I did wrong.

 

[mogul0212]

KeithG

I do have to leave the office for the day @ 4:30pm CST If you have a solution to what I did wrong, or list of step by step instructions to what I did wrong. Post it here, I'll check on it Friday, 6/9/2006. If possible, as I go forward in with this to have you keep checking this thread over the next few days. I would appreciate it. Thanks again for all your help to this point and in the future.