Query criteria set by values in an unbound textbox 3

[Jennpen1]

The following code takes the items selected in a listbox (PARTNOSELECT) on a form (START_UP) and puts them in a textbox (Text1). It places them into the textbox the following way: "PartNumber" OR "PartNumber" Or etc....


Private Sub Command19_Click()
Dim varRow As Variant
Dim strText As String

strText = ""
For Each varRow In Me!PARTNOSELECT.ItemsSelected
If strText = "" Then
strText = """" & Me!PARTNOSELECT.Column(0, varRow) & """"
Else
strText = strText & " Or """ & "" & Me!PARTNOSELECT.Column(0, varRow) & """"
End If
Next varRow

Text1.Value = strText


If strText <> "" Then
Text1.Value = strText
End If

Exit Sub
End Sub


I am then trying to run a query where the criteria is set to this textbox (Text1). This is what I have in the criteria part of the query:

=[Forms]![START-UP]![Text1]

Everytime I run it, the query comes back blank. I cannot figure out what is causing this problem. Is what I am doing even possible and the problem is due to the formatting of the data??? Any help would be appreciated!!!!!

Jennifer

 

[Jennpen1]

Again, I am super confused. With the QueryDefs function am I calling my pre-existing query or am I supposed to re-create the entire query in the code?? If the latter, I had tried this also and was getting the same error I am getting now.

Leslie, I had tried the single ticks about 5 steps ago and it did not help.

Duane, I tried both of your suggestions and continued to get the same error message.

I will continue to debug but help with what the QueryDef is supposed to be doing would be appreciated.

 

[dhookom]

Jennpen1,
This will replace the SQL of an existing query. Please post your full strSQL.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Jennpen1]

It is pretty complicted, so it might be more confusing then helpful.

strSQL =

"SELECT [A - PART NUMBER DATE TABLE].YEAR_MONTH_COMP, Sum([RELIABILITY O-LEVEL RVMAL FOR CAUSE].[RVMAL FOR CAUSE]) AS [RVMAL FOR CAUSE], Sum([A - FLIGHT HOUR TABLE].SumOfFLIGHT_HRS) AS SumOfSumOfFLIGHT_HRS

FROM ([RELIABILITY O-LEVEL RVMAL FOR CAUSE] RIGHT JOIN [A - PART NUMBER DATE TABLE] ON([RELIABILITY O-LEVEL RVMAL FOR CAUSE].PARTNO = [A - PART NUMBER DATE TABLE].PARTNO) AND ([RELIABILITY O-LEVEL RVMAL FOR CAUSE].[Month Year] = [A - PART NUMBER DATE TABLE].YEAR_MONTH_COMP))LEFT JOIN [A - FLIGHT HOUR TABLE] ON [A - PART NUMBER DATE TABLE].YEAR_MONTH_COMP = [A - FLIGHT HOUR TABLE].TRANS_DATE

Left(strSQL, InStr(strSQL, "WHERE ")) & "WHERE [A - PART NUMBER DATE TABLE].PARTNO In (" & strText & ") "

GROUP BY [A - PART NUMBER DATE TABLE].YEAR_MONTH_COMP"


I am assuming that there is no way to accomplish this task without running a query through code. Any clue why there is not any capability for a query to read its criteria from a form??

 

[dhookom]

Your strSQL variable actually contains the "LEFT(str....". You have to understand that this should not be. Do you actually have the carriage returns in the SQL?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Jennpen1]

Yes, I just fixed that right before you wrote back. Now the code is running without error, only now nothing happens when it runs.

I really am clueless as to what this line does:

CurrentDb.QueryDefs("MASTER RELIABILITY").Sql = strSQL

Can you explain it to me?? Is there anyway I can actually see teh results of the query becasue my next step is to export the results to Excel.

 

[dhookom]

Yes, you can see the results as I stated earlier. Paste the results of your debug.Print strSQL into a blank query. Then view the datasheet view.

You could also just open your query "Master Reliability".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Jennpen1]

It actually worked!!!! You are brilliant!!!

I cannot thank you enough for your help and your patience with me.

You definitely deserve a star!!!!!

 

[lespaul]

This line:

CurrentDb.QueryDefs("MASTER  RELIABILITY").Sql = strSQL

sets the SQL of your Query "MASTER RELIABILITY" to strSQL. So whatever the SQL of Query "MASTER RELIABILITY" was, it is now whatever is in strSQL.


These two lines:

strSQL = CurrentDb.QueryDefs("MASTER  RELIABILITY").Sql
strSQL = Left(strSQL, InStr(strSQL & " WHERE ", " WHERE ")) & " WHERE [A - PART NUMBER DATE TABLE].PARTNO In (" & strText & ") GROUP BY [A - PART NUMBER DATE TABLE].YEAR_MONTH_CO"

first takes what the SQL from the existing "MASTER RELIABILITY" query and adds it to strSQL. So, before the first line strSQL = "", after the first line, strSQL equals whatever the SQL is for the query.

The second line then searches strSQL for " WHERE " and replaces it with: " WHERE [A - PART NUMBER DATE TABLE].PARTNO In (" & strText & ") GROUP BY [A - PART NUMBER DATE TABLE].YEAR_MONTH_CO"

So, you have two choices.

1. You can make sure that the "MASTER RELIABILITY" query has the word WHERE in it:

WHERE [A - PART NUMBER DATE TABLE].PARTNO = '1'

so when line two searches for the WHERE clause it finds it.

Or

2. Remove the WHERE clause from "MASTER RELIABILITY" instead of your line two, use this:

strSQL = CurrentDb.QueryDefs("MASTER  RELIABILITY").Sql
strSQL = strSQL & " WHERE [A - PART NUMBER DATE TABLE].PARTNO In (" & strText & ") GROUP BY [A - PART NUMBER DATE TABLE].YEAR_MONTH_CO"

I personally find that it's easier to remember and see what a query is doing if I just replace all the SQL in the code and not depend on the QueryDefs. In other situations I have found that if I only add on the qualifing information (WHERE clauses) that the beginning of the query isn't included.

strSQL = "SELECT [A - PART NUMBER DATE TABLE].YEAR_MONTH_COMP, Sum([RELIABILITY O-LEVEL RVMAL FOR CAUSE].[RVMAL FOR CAUSE]) AS [RVMAL FOR CAUSE], Sum([A - FLIGHT HOUR TABLE].SumOfFLIGHT_HRS) AS SumOfSumOfFLIGHT_HRS 
FROM ([RELIABILITY O-LEVEL RVMAL FOR CAUSE] RIGHT JOIN [A - PART NUMBER DATE TABLE] ON([RELIABILITY O-LEVEL RVMAL FOR CAUSE].PARTNO = [A - PART NUMBER DATE TABLE].PARTNO) AND ([RELIABILITY O-LEVEL RVMAL FOR CAUSE].[Month Year] = [A - PART NUMBER DATE TABLE].YEAR_MONTH_COMP))LEFT JOIN [A - FLIGHT HOUR TABLE] ON [A - PART NUMBER DATE TABLE].YEAR_MONTH_COMP = [A - FLIGHT HOUR TABLE].TRANS_DATE WHERE [A - PART NUMBER DATE TABLE].PARTNO In (" & strText & ") GROUP BY [A - PART NUMBER DATE TABLE].YEAR_MONTH_CO"

of course you need to add the _ & (or is it +?) on each line to continue the string.

HTH

Leslie

 

[Jennpen1]

That you so much for your explanation. I finally understand what it was trying to do. I apologize for picking up on this quicker.

I appreciate all of the time you have given to help me with this problem. It is so nice to finally have this working.

You also deserve a star!!!

 

[lespaul]

So, it's working now! That's great. Glad that you know a little more than you did when you started!

Leslie