Error 3075-Run Query after multi select listbox is utilized

[tangerine]

the db is huge and is work related. I am going to copy, paste and delete all proprietary information. one sec.

thank you for your help!

 

[tangerine]

I just dummed the database down. It still seems to be too big so i put it in google drive here

 

[sneuberg]

The major problem was the FROM clause was missing in the SQL. I guess I couldn't see the forest for the tree.

The code below has the FROM clause, but it still has problems. The fields QuarterID, LongDesc, and NumDes are not in the [z_Basis_QSReport5_Proposal Details] query and so you get prompted for them as parameters when you run the code. I took these fields out of the commented out strSQL and used that to test this code which seems to work fine with the missing fields taken out.

Of course there no way I'd know why these field are missing. You will have to run that down.

Private Sub Command56_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
   Set qdf = db.QueryDefs("z_Basis_QSReport5_Proposal Details_For_Report")
For Each varItem In Me!SelectTime.ItemsSelected
      strCriteria = strCriteria & ",'" & Me!SelectTime.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
      MsgBox "You did not select anything from the list" _
             , vbExclamation, "Nothing to find!"
      Exit Sub
   End If
   strCriteria = Right(strCriteria, Len(strCriteria) - 1)
   strSQL = "SELECT [z_Basis_QSReport5_Proposal Details].SchoolName, [z_Basis_QSReport5_Proposal Details].SchoolAcronym, [z_Basis_QSReport5_Proposal Details].PIFullName, [z_Basis_QSReport5_Proposal Details].PIGWID, [z_Basis_QSReport5_Proposal Details].AppID, [z_Basis_QSReport5_Proposal Details].App_Type, [z_Basis_QSReport5_Proposal Details].Outcome, [z_Basis_QSReport5_Proposal Details].DeptCode, [z_Basis_QSReport5_Proposal Details].SponsorName, [z_Basis_QSReport5_Proposal Details].Title_Long, [z_Basis_QSReport5_Proposal Details].ProjTotal, [z_Basis_QSReport5_Proposal Details].SubmissionDate, [z_Basis_QSReport5_Proposal Details].QuarterID, [z_Basis_QSReport5_Proposal Details].LongDesc, [z_Basis_QSReport5_Proposal Details].NumDes, [z_Basis_QSReport5_Proposal Details].FYLabel, [z_Basis_QSReport5_Proposal Details].CriteriaFY, [z_Basis_QSReport5_Proposal Details].FY " & _
            " FROM [z_Basis_QSReport5_Proposal Details] " & _
            "WHERE [z_Basis_QSReport5_Proposal Details].CriteriaFY IN (" & strCriteria & ");"
'    strSQL = "SELECT [z_Basis_QSReport5_Proposal Details].SchoolName, [z_Basis_QSReport5_Proposal Details].SchoolAcronym, [z_Basis_QSReport5_Proposal Details].PIFullName, [z_Basis_QSReport5_Proposal Details].PIGWID, [z_Basis_QSReport5_Proposal Details].AppID, [z_Basis_QSReport5_Proposal Details].App_Type, [z_Basis_QSReport5_Proposal Details].Outcome, [z_Basis_QSReport5_Proposal Details].DeptCode, [z_Basis_QSReport5_Proposal Details].SponsorName, [z_Basis_QSReport5_Proposal Details].Title_Long, [z_Basis_QSReport5_Proposal Details].ProjTotal, [z_Basis_QSReport5_Proposal Details].SubmissionDate, [z_Basis_QSReport5_Proposal Details].FYLabel, [z_Basis_QSReport5_Proposal Details].CriteriaFY, [z_Basis_QSReport5_Proposal Details].FY " & _
            " FROM [z_Basis_QSReport5_Proposal Details] " & _
            "WHERE [z_Basis_QSReport5_Proposal Details].CriteriaFY IN (" & strCriteria & ");"
   qdf.SQL = strSQL
   DoCmd.OpenQuery "z_Basis_QSReport5_Proposal Details_For_Report"
   Set db = Nothing
   Set qdf = Nothing
End Sub

 

[sneuberg]

I just notice something else. If you run the code more than once without closing the query, the query doesn't change. I suggest you put some code in to check if the query is open and closed it if it is before a new query is created by the code.

 

[tangerine]

Snueberg,

Thank you so very much! I so appreciate this. This works. I've never wanted to hug a stranger so much as I do now.

Question:
If I were to add another button to run a report based off this query how would the code change? That's the eventual goal.

 

[tangerine]

OK In retrospect I can see how this was naive of me.

I wanted the user to select a school within my university, then be able to select multiple time frames. I went into the query and tried to add

[forms]![QS Report 5: Proposal Details]![Rep5SelectSchool]

in the criteria and it didn't work. How could I incorporate this feature into the code?

 

[sneuberg]

I'd just concatenate it into the code as I show below in red

Private Sub Command56_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
   Set qdf = db.QueryDefs("z_Basis_QSReport5_Proposal Details_For_Report")
For Each varItem In Me!SelectTime.ItemsSelected
      strCriteria = strCriteria & ",'" & Me!SelectTime.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
      MsgBox "You did not select anything from the list" _
             , vbExclamation, "Nothing to find!"
      Exit Sub
   End If
   strCriteria = Right(strCriteria, Len(strCriteria) - 1)
'   strSQL = "SELECT [z_Basis_QSReport5_Proposal Details].SchoolName, [z_Basis_QSReport5_Proposal Details].SchoolAcronym, [z_Basis_QSReport5_Proposal Details].PIFullName, [z_Basis_QSReport5_Proposal Details].PIGWID, [z_Basis_QSReport5_Proposal Details].AppID, [z_Basis_QSReport5_Proposal Details].App_Type, [z_Basis_QSReport5_Proposal Details].Outcome, [z_Basis_QSReport5_Proposal Details].DeptCode, [z_Basis_QSReport5_Proposal Details].SponsorName, [z_Basis_QSReport5_Proposal Details].Title_Long, [z_Basis_QSReport5_Proposal Details].ProjTotal, [z_Basis_QSReport5_Proposal Details].SubmissionDate, [z_Basis_QSReport5_Proposal Details].QuarterID, [z_Basis_QSReport5_Proposal Details].LongDesc, [z_Basis_QSReport5_Proposal Details].NumDes, [z_Basis_QSReport5_Proposal Details].FYLabel, [z_Basis_QSReport5_Proposal Details].CriteriaFY, [z_Basis_QSReport5_Proposal Details].FY " & _
            " FROM [z_Basis_QSReport5_Proposal Details] " & _
            "WHERE [z_Basis_QSReport5_Proposal Details].CriteriaFY IN (" & strCriteria & ");"
    strSQL = "SELECT [z_Basis_QSReport5_Proposal Details].SchoolName, [z_Basis_QSReport5_Proposal Details].SchoolAcronym, [z_Basis_QSReport5_Proposal Details].PIFullName, [z_Basis_QSReport5_Proposal Details].PIGWID, [z_Basis_QSReport5_Proposal Details].AppID, [z_Basis_QSReport5_Proposal Details].App_Type, [z_Basis_QSReport5_Proposal Details].Outcome, [z_Basis_QSReport5_Proposal Details].DeptCode, [z_Basis_QSReport5_Proposal Details].SponsorName, [z_Basis_QSReport5_Proposal Details].Title_Long, [z_Basis_QSReport5_Proposal Details].ProjTotal, [z_Basis_QSReport5_Proposal Details].SubmissionDate, [z_Basis_QSReport5_Proposal Details].FYLabel, [z_Basis_QSReport5_Proposal Details].CriteriaFY, [z_Basis_QSReport5_Proposal Details].FY " & _
            " FROM [z_Basis_QSReport5_Proposal Details] " & _
            " WHERE [COLOR="Red"][SchoolAcronym] = '" & Me.Rep5SelectSchool & "' And [/COLOR][z_Basis_QSReport5_Proposal Details].CriteriaFY IN (" & strCriteria & ");"
   qdf.SQL = strSQL
   DoCmd.OpenQuery "z_Basis_QSReport5_Proposal Details_For_Report"
   Set db = Nothing
   Set qdf = Nothing
End Sub

You might consider putting something in the code to check if a user has selected a school and if not give them a message.

 

[sneuberg]

Snueberg,

Thank you so very much! I so appreciate this. This works. I've never wanted to hug a stranger so much as I do now.

The thanks from the Thumbs Up Button are like hugs. I think when we accumulate 1000 of them we get some sort of prize. Maybe a set of steak knifes.

Question:
If I were to add another button to run a report based off this query how would the code change? That's the eventual goal.

I believe all you need to do is base the report off the resulting query "z_Basis_QSReport5_Proposal Details_For_Report" and just open the report in instead of the DoCmd.OpenQuery

 

[sneuberg]

Concerning my post with the addition to the WHERE clause. It's not necessary but if you want the [SchoolAcronym] field like the others you can prefix it with [z_Basis_QSReport5_Proposal Details] so the addition to the WHERE clause would be :

[z_Basis_QSReport5_Proposal Details].[SchoolAcronym] = '" & Me.Rep5SelectSchool & "' And

Of course the other way to make them consistent would be to take the [z_Basis_QSReport5_Proposal Details] prefix off the other fields. In general it's not necessary to prefix a field name with the table or query name unless two fields have the same name which is never the case if you are dealing with a single table or query.