Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Forms _ Not Sure How To Pass Data From A Selection Criteria Form

Posted by: Brandi Dec 7 2017, 06:58 PM

I have used a template for a selection criteria form given to me some time ago by a UA member.
I am trying to use this type of form to open another form which would use the data selected from my criteria form.

Users select a Division, Department, Location, or Position (or any combination of those fields from listboxes on my selection form.
Any employees who match the criteria will then appear in a listbox on the selection criteria form.

I want to use those selected employee records as the data to open another form.

I think the problem is that the EmplID in my other db is numeric as it comes from another system where it is numberic.

In my new db, the EmplID is Text.

Can someone tell me what I need to change to make EmplID text in the following event code?
Thank you.
Brandi

I have two Subs ( Sub SelectEmployee_AfterUpdate() and (Sub FilterEmployeeList(). I am copying both Subs below.
I am not sure which one needs to be changed but qrySelectEmployee is where I need EmplID to be Text.

Private Sub SelectEmployee_AfterUpdate()

Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef


strSQL = "SELECT qryEmployeeList.[EmpID], qryEmployeeList.Last, qryEmployeeList.First, qryEmployeeList.Division, qryEmployeeList.Department,qryEmployeeList.Location, qryEmployeeList.Position FROM qryEmployeeList"

For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & "((tblEmployee.[EmpID]) = " & Me.ActiveControl.ItemData(varItem) & ") Or "
Next
strSQL = strSQL & " WHERE (" & Left(strTemp, Len(strTemp) - 4) & ");"

Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployeeSelect"
Set qry = .CreateQueryDef("qryEmployeeSelect", strSQL)
End With
End Sub

Private Sub FilterEmployeeList()
Dim strSQL As String
Dim strWhere As String
Dim strListCriteria As String
Dim varItem As Variant

strSQL = "SELECT qryEmployeeList.[EmpID], qryEmployeeList.Last, qryEmployeeList.First, qryEmployeeList.Division, qryEmployeeList.Department,qryEmployeeList.Location, qryEmployeeList.Position FROM qryEmployeeList"
'strWhere = " WHERE 1=1 "
strWhere = " WHERE [Status] <> 'T' "
'Process Pharmacies
strListCriteria = ""
For Each varItem In Me.SelectDivision.ItemsSelected
strListCriteria = strListCriteria & "'" & Me.SelectDivision.ItemData(varItem) & "',"
Next varItem

If strListCriteria > "" Then
strListCriteria = Left(strListCriteria, Len(strListCriteria) - 1)
strWhere = strWhere & " And Division In(" & strListCriteria & ")"
End If

Me.SelectEmployee.RowSource = strSQL & strWhere

'Process Department
Dim strSQLDepartment As String
Dim strWhereDepartment As String
Dim strListCriteriaDepartment As String
Dim varItemDepartment As Variant
strListCriteriaDepartment = ""
For Each varItemDepartment In Me.SelectDepartment.ItemsSelected
strListCriteriaDepartment = strListCriteriaDepartment & "'" & Me.SelectDepartment.ItemData(varItemDepartment) & "',"
Next varItemDepartment

If strListCriteriaDepartment > "" Then
strListCriteriaDepartment = Left(strListCriteriaDepartment, Len(strListCriteriaDepartment) - 1)
strWhereDepartment = strWhereDepartment & " And Department In(" & strListCriteriaDepartment & ")"
End If

Me.SelectEmployee.RowSource = strSQL & strWhere & strWhereDepartment
'Process Location
Dim strSQLLocation As String
Dim strWhereLocation As String
Dim strListCriteriaLocation As String
Dim varItemLocation As Variant
strListCriteriaLocation = ""
For Each varItemLocation In Me.SelectLocation.ItemsSelected
strListCriteriaLocation = strListCriteriaLocation & "'" & Me.SelectLocation.ItemData(varItemLocation) & "',"
Next varItemLocation

If strListCriteriaLocation > "" Then
strListCriteriaLocation = Left(strListCriteriaLocation, Len(strListCriteriaLocation) - 1)
strWhereLocation = strWhereLocation & " And Location In(" & strListCriteriaLocation & ")"
End If
Me.SelectEmployee.RowSource = strSQL & strWhere & strWhereDepartment & strWhereLocation

'Process Position
Dim strSQLPosition As String
Dim strWherePosition As String
Dim strListCriteriaPosition As String
Dim varItemPosition As Variant
strListCriteriaPosition = ""
For Each varItemPosition In Me.SelectPosition.ItemsSelected
strListCriteriaPosition = strListCriteriaPosition & "'" & Me.SelectPosition.ItemData(varItemPosition) & "',"
Next varItemPosition

If strListCriteriaPosition > "" Then
strListCriteriaPosition = Left(strListCriteriaPosition, Len(strListCriteriaPosition) - 1)
strWherePosition = strWherePosition & " And Position In(" & strListCriteriaPosition & ")"
End If

Me.SelectEmployee.RowSource = strSQL & strWhere & strWhereDepartment & strWhereLocation & strWherePosition

Me.SelectEmployee.Requery
End Sub

Posted by: MadPiet Dec 7 2017, 07:03 PM

What's with all that code? I thought you wanted this:

"I want to use those selected employee records as the data to open another form."

What if you grab the form's filter or the list of selected values from the listbox and pass those?

One of the arguments for the DoCmd.OpenForm command is a filter, so you'd pass the filter you created to that ...

Posted by: Brandi Dec 7 2017, 07:17 PM

The user selects the criteria they want from 4 different Listboxes for Division, Department, Location, and Position. Then the code populates the Employee list with the appropriate employees.
I believe the code is creating a query called qrySelectEmployees. I think The query doesn't actually exist until the code creates it.
So I am trying to figure out where in the code it creates the EmplID field as numeric and change it to Text.

Thanks.
Brandi

Posted by: MadPiet Dec 7 2017, 07:28 PM

Why create the whole query when all you really need is the WHERE clause (minus the "WHERE" keyword)?

If it were me, I'd forget all about the query nonsense. It's a huge red herring. If the form/report recordsource you want to filter has all the fields in it, you don't need any of the fields in the SELECT statement at all.

You can create the filter and pass it to either a form or a report in the Open command...

Posted by: Brandi Dec 7 2017, 08:53 PM

I'm sorry. I'm not very good at creating code.

How would I know what the WHERE clause would be?
It would be coming from a query created by the code?

Posted by: MadPiet Dec 7 2017, 09:37 PM

<snip>The user selects the criteria they want from 4 different Listboxes for Division, Department, Location, and Position.</snip>

If they're multi-select listboxes, you can loop over the SelectedItems collection and append them to a string/create an IN() filter... then you'd end up with something like:

strFilter1 = "[field1] IN ('A','C','D')"
strFilter2 ="[field2] IN (1,3,5)"
etc

then you just AND that stuff together.

Here's the first part:

CODE
Private Sub Command10_Click()
    Dim varItem As Variant
    Dim strList As String
    
    For Each varItem In Me.lbxCrew.ItemsSelected
        'MsgBox Me.lbxCrew.ItemData(varItem), vbOKOnly
        strList = strList & ",'" & Me.lbxCrew.ItemData(varItem) & "'"
    Next varItem
    
    strList = Right(strList, Len(strList) - 1)
    strList = "[FieldName] IN (" & strList & ")"
End Sub


Say you have several of those... (good place for a function... pass in the listbox, then output the string.)

CODE
strFilter = strList1 " AND " & strList2 & " AND " & strList3
DoCmd.OpenForm "MyForm", acNormal, , strFilter

Posted by: BruceM Dec 8 2017, 08:00 AM

http://allenbrowne.com/ser-50.html describes using a multi-select list box to filter a report. Although that isn't quite what you are trying to do, it may help you to assemble an appropriate Where condition. It is essentially what MadPiet suggested in the most recent posting, but with a little more detail.

I agree that creating queries is likely to be more than you need in this case, but I am having trouble following the flow of the process just by reading the code.

If you are using the selections in several controls as the criteria for the Employee listing in the list box (that is, to set the list box Row Source), best IMO would be to make the selections, then click a command button, rather than resetting the Row Source after each selection. Again, I'm not quite sure how you are processing Division, etc. into the Row Source criteria.

In any case, you will need to make provisions for any or all of the controls not having entries or selections.

Posted by: Brandi Dec 8 2017, 09:24 AM

OK. maybe I should start over.
This is a form with multiple select listboxes. users can choose one or more Divisions, one or more Departments, one or more Locations, and one or more Positions.
Based on their selections, there is another listbox on the form that displays Employee names and their Division, Department, Location, and Position.

They can further select on the Employee Listbox to eliminate other employees from the List,

Once they have the employees they want in the Employee listbox, they press a button to select those records.
Then the following code runs to create a query with those records only. These are the records I need as the data for my form.

So it all works, except the query that is created is considering the EmplID to be numeric instead of text. I think it is just a matter of putting quotes somewhere around EmplID but not sure where exactly to put them.

Here is the event code that creates the dynamic query.
Thank you.
Brandi

Private Sub SelectEmployee_AfterUpdate()

Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef


strSQL = "SELECT qryEmployeeList.[EmpID], qryEmployeeList.Last, qryEmployeeList.First, qryEmployeeList.Division, qryEmployeeList.Department,qryEmployeeList.Location, qryEmployeeList.Position FROM qryEmployeeList"

For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & "((tblEmployee.[EmpID]) = " & Me.ActiveControl.ItemData(varItem) & ") Or "
Next
strSQL = strSQL & " WHERE (" & Left(strTemp, Len(strTemp) - 4) & ");"

Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployeeSelect"
Set qry = .CreateQueryDef("qryEmployeeSelect", strSQL)
End With

Posted by: BruceM Dec 8 2017, 11:39 AM

QUOTE
the query that is created is considering the EmplID to be numeric instead of text

Do you mean in qryEmployeeSelect? You are selecting from qryEmployeeList, so all I can guess is that it is numeric there. Without seeing the database, or even any sample data, there is really no way to know from here what is happening.

As for the Where condition for the query you are building, you are selecting from qryEmployeeList, but your criteria use tblEmployee, although it is not part of the SELECT statement. I don't see how that can work at all. A Debug.Print strSQL line of code would help evaluate the query. It appears you have something like the following abbreviated construction:

SELECT qryEmployeeList.[EmpID], qryEmployeeList.Last
FROM qryEmployeeList
WHERE tblEmployee.EmpID = 15

I would use IN rather than a string of OR statements, as shown in the link I provided. Where you would have:

WHERE tblEmployee.EmpID = 15 OR tblEmployee.EmpID = 22 OR tblEmployee.EmpID = 38 OR tblEmployee.EmpID = 44

you could have instead:

WHERE tblEmployee.EmpID IN (15,22,38,44)

One advantage is that if you want to add other criteria you don't need to include those in each OR statement. Instead of this:

WHERE (tblEmployee.EmpID = 15 AND tblEmployee.Active = True) OR (tblEmployee.EmpID = 22 AND tblEmployee.Active = True) OR (tblEmployee.EmpID = 38 AND tblEmployee.Active = True) OR (tblEmployee.EmpID = 44 AND tblEmployee.Active = True)

This:

WHERE tblEmployee.EmpID IN (15,22,38,44) AND tblEmployee.Active = True

That is not really the point at the moment, but it is worth considering.

Again, selecting from one domain (qryEmployeeList) and applying criteria from another domain (tblEmployee) that is not included in the SELECT is very likely to cause problems.

Other points: Why ActiveControl? It seems the code is being applied to a specific control, so I would just name it.

I would just assign the SQL as the Record Source at run time, or else open the form and use the Where condition (without the word WHERE) as the OpenForm WhereCondition value. I don't think the QueryDef is causing the problem you describe, but it looks like you are taking the long way around.

Posted by: Brandi Dec 8 2017, 01:33 PM

EmpID's come from another system where they are Text.
An EmpID would be something like 0000117, 0000119, 0000127
They are definitely text in qryEmployeeList

I see what you mean about the select statement referring to tblEmployee and I have changed that to qryEmployeeList.
I also like the idea of using In instead of OR but not sure how to change that in the SQL below.

When my dynamic query runs, it changes those to 117, 119, and 127. Code for the dynamic query is currently as follows: I am certain this is where I need to designate EmpID as Text but I don't know how to do that. I am not good at writing code and for me it is easier to have this query to use as the control source for another form and also for reports. Would you be able to show me how to change the Or to In? I think it will require some brackets somewhere?
This code is just telling me which Employees are currently selected from a full list of employees that comes from qryEmployeeList. I am also including the SQL for qryEmployeeSelect where the records listed above are selected on my criteria form.

SO THE FOLLOWING CREATES THE DYNAMIC QUERY.
Private Sub SelectEmployee_AfterUpdate()
Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef

strSQL = "SELECT qryEmployeeList.EmpID, qryEmployeeList.Last, qryEmployeeList.First, qryEmployeeList.Division, qryEmployeeList.Department,qryEmployeeList.Location, qryEmployeeList.Position FROM qryEmployeeList"

For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & "((qryEmployeeList.EmpID) = " & Me.ActiveControl.ItemData(varItem) & ") Or "
Next
strSQL = strSQL & " WHERE (" & Left(strTemp, Len(strTemp) - 4) & ");"
Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployeeSelect"
Set qry = .CreateQueryDef("qryEmployeeSelect", strSQL)
End With
End Sub

AND THIS IS THE QUERY THAT IS GENERATED FROM THE CODE ABOVE. Here is the SQL for qryEmployeeSelect where it has changed the text EmplId's from qryEmployeeList to numbers. It is stripping the leading zeroes. And some EmpID's start with the word Temp if it is an acquisition. IN THAT CASE THE QUERY PROMPTS FOR A FIELD NAMED [Temp01] as an example vs. using Temp01 as the value in Criteria.

SELECT qryEmployeeList.EmpID, qryEmployeeList.Last, qryEmployeeList.First, qryEmployeeList.Division, qryEmployeeList.Department, qryEmployeeList.Location, qryEmployeeList.Position
FROM qryEmployeeList
WHERE (((qryEmployeeList.EmpID)="117" Or (qryEmployeeList.EmpID)="119" Or (qryEmployeeList.EmpID)="127"));

Posted by: Brandi Dec 8 2017, 02:08 PM

I am attaching a sample database. For purposes of the sample only 4. Budget Merit matrix will work.
this is the area in question. You can select employees by filtering on Division, Department, Location, Position and even further select out employees from the employee list.
Once you have the employees you want, press the Select all button above the employee list. This creates the dynamic query, qrySelectEmployee.

If you look at the query, you will see that it is changing text to numbers and I can't figure out why.

Thank you.
Brandi

 MeritCriteriaForm.zip ( 464.54K ): 7
 

Posted by: Brandi Dec 9 2017, 10:10 AM

Thank you for the link. I have read it and it seems to be very similar to the code I am trying to modify. I sort of understand code when I read it but the syntax is always difficult for me.

I am trying to modify the code to use the In statement but so far I don't have the right syntax. Does anyone see my mistake?

Ultimately I want the dynamic query to create a criteria such as In("001","002","Temp01")

The old code which is commented out in the following was giving me 1 or 2 or [Temp01] where it thought Temp01 was a field and was prompting me for it's value.
My new code underneath the old code is attempting to change the old code to give me the In string but it is not working. I added a line above the loop that I think would start the strTemp with In(

Thank you.
Brandi

Private Sub SelectEmployee_AfterUpdate()

Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef


strSQL = "SELECT qryEmployeeList.EmpID, qryEmployeeList.Last, qryEmployeeList.First, qryEmployeeList.Division, qryEmployeeList.Department,qryEmployeeList.Location, qryEmployeeList.Position FROM qryEmployeeList"
strTemp = "In("
For Each varItem In Me.ActiveControl.ItemsSelected
'strTemp = strTemp & "((qryEmployeeList.EmpID) = " & Me.ActiveControl.ItemData(varItem) & ") Or "

strTemp = strTemp & "((qryEmployeeList.EmpID) = " & Me.ActiveControl.ItemData(varItem) & ","
Next
'strSQL = strSQL & " WHERE (" & Left(strTemp, Len(strTemp) - 4) & ");"
strSQL = strSQL & " )"
Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployeeSelect"
Set qry = .CreateQueryDef("qryEmployeeSelect", strSQL)
End With
End Sub

Posted by: MadPiet Dec 9 2017, 11:31 AM

Note the modifications - I wrapped the result of
For Each varItem In Me.ActiveControl.ItemsSelected
a little bit.

You need to wrap each of those values returned in single quotes, because EmpID is defined as a string, not as some kind of number. You many need to clean up some of that at the end of your code so that your query is right (the trimming off excess characters part may be off a little). You can use Debug.Print strSQL and then copy & Paste that into a query and see if it works.

CODE
Private Sub SelectEmployee_AfterUpdate()

Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef

strSQL = "SELECT qryEmployeeList.EmpID, qryEmployeeList.Last, qryEmployeeList.First, qryEmployeeList.Division, qryEmployeeList.Department,qryEmployeeList.Location, qryEmployeeList.Position FROM qryEmployeeList"
strTemp = "In ("
For Each varItem In Me.ActiveControl.ItemsSelected
   '-- modified the following line... Note the single quotes around the Me.ActiveControl.ItemData(varItem)
   strTemp = strTemp & "((qryEmployeeList.EmpID) = '" & Me.ActiveControl.ItemData(varItem) & "',"
Next

'-- this part may well be wrong, because you have to snip off the last single quote in the string LEFT$(strTemp, LEN(strTemp) - 1)
'strSQL = strSQL & " WHERE (" & Left(strTemp, Len(strTemp) - 4) & ");"
strSQL = strSQL & " )"
Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployeeSelect"
Set qry = .CreateQueryDef("qryEmployeeSelect", strSQL)
End With
End Sub

Posted by: Brandi Dec 10 2017, 03:32 PM

Thank you so much for your help. I think I am getting close. So far I am getting an error. I changed the last part that used to drop off the last 4 characters to strip off the last 1 character.
Here is the error I get. It looks like it is getting the right EmpID's 009 and 018 but I'm not sure what it doesn't like.

Syntax error (missing operator) in query expression
‘(In(((qryEmployeeList.EmpID) = ‘009’,((qryEmployeeList.EmpID) = ‘018’)’

I think I have copied your statement correctly. Here is what I have now.

Private Sub SelectEmployee_AfterUpdate()

Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef


strSQL = "SELECT qryEmployeeList.EmpID, qryEmployeeList.Last, qryEmployeeList.First, qryEmployeeList.Division, qryEmployeeList.Department,qryEmployeeList.Location, qryEmployeeList.Position FROM qryEmployeeList"
strTemp = "In("
For Each varItem In Me.ActiveControl.ItemsSelected
'strTemp = strTemp & "((qryEmployeeList.EmpID) = " & Me.ActiveControl.ItemData(varItem) & ") Or "

strTemp = strTemp & "((qryEmployeeList.EmpID) = '" & Me.ActiveControl.ItemData(varItem) & "',"
Next
strSQL = strSQL & " WHERE (" & Left(strTemp, Len(strTemp) - 1) & ")"
Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployeeSelect"
Set qry = .CreateQueryDef("qryEmployeeSelect", strSQL)
End With
End Sub

Posted by: MadPiet Dec 10 2017, 04:35 PM

Your syntax for IN is wrong. =(

It's like this for strings:

SELECT...
FROM ...
WHERE MyTextField IN ('abc', 'def', 'ghi');

for numeric values it's:

SELECT ...
FROM...
WHERE MyNumericField IN (1,2,3);

Posted by: Brandi Dec 10 2017, 05:09 PM

Do you mean there is something wrong with this line? strTemp = "In("

Posted by: MadPiet Dec 10 2017, 05:56 PM

What does your code do when you run it?

Not totally sure, but I think you need a space between IN and (.

Posted by: Brandi Dec 10 2017, 11:27 PM

I get the same error with or without a space after In. So close! I just don't understand very well where the parentheses and double quotes and single quotes need to be.
Thank you for your help.

Syntax error (missing operator) in query expression
‘(In(((qryEmployeeList.EmpID) = ‘009’,((qryEmployeeList.EmpID) = ‘018’)’

Posted by: MadPiet Dec 11 2017, 10:41 AM

Your syntax is still incorrect. The syntax for IN () goes like this:

IN ('string1', 'string2','string3') (strings are going to be in single quotes.)
or
IN (1,3,4) -- numbers have no delimiters.

take a step backward and play with IN() for a few minutes and get that part working. Once you get it working on a simpler example, come back and fight with this one. The big thing is to get your head around how IN() does and does not work first. Then you can apply that to what you're doing.

Here's a really simple example. (I have a form with a multi-select listbox and a button that builds the filter string using IN ()

CODE
Private Sub Command4_Click()
    Dim varItem As Variant
    Dim strFilter As String
    ' the Constant declaration is a bit of overkill... just makes the code really obvious!
    Const cQUOTE As String = "'"
    
    For Each varItem In Me.List0.ItemsSelected
        If Len(strFilter) = 0 Then
            strFilter = cQUOTE & Me.List0.ItemData(varItem) & cQUOTE
        Else
            strFilter = strFilter & "," & cQUOTE & Me.List0.ItemData(varItem) & cQUOTE
        End If
    Next varItem
    
    strFilter = "IN (" & strFilter & ")"
    MsgBox strFilter
    
End Sub


Posted by: Brandi Dec 11 2017, 03:19 PM

I like your idea of making the quote a constant. That really helps me understand. I am trying to make the criteria that is dynamically created say something like In ("001","002","Temp07")
When I just type in the criteria myself it does select the correct records.
Thank you for your help. I am so sorry that I am not getting this!

I also made a CQuoteComa constant to put the comma after each empID.

Here is what I have now. (I just kept my dim names from before but tried to follow your logic. It now give me an error 424 Object Required and highlights this line in the event procedure.
strTemp = strTemp & cQUOTE & (qryEmployeeList.EmpID) = cQUOTE & Me.ActiveControl.ItemData(varItem) & cQuoteComma

Private Sub SelectEmployee_AfterUpdate()
Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef
' the Constant declaration is a bit of overkill... just makes the code really obvious!
Const cQUOTE As String = "'"
Const cQuoteComma As String = "',"
strSQL = "SELECT qryEmployeeList.EmpID, qryEmployeeList.Last, qryEmployeeList.First, qryEmployeeList.Division, qryEmployeeList.Department,qryEmployeeList.Location, qryEmployeeList.Position FROM qryEmployeeList"
For Each varItem In Me.ActiveControl.ItemsSelected
'strTemp = strTemp & "((qryEmployeeList.EmpID) = '" & Me.ActiveControl.ItemData(varItem) & "',"
strTemp = strTemp & cQUOTE & (qryEmployeeList.EmpID) = cQUOTE & Me.ActiveControl.ItemData(varItem) & cQuoteComma
Next varItem
strTemp = strTemp & " WHERE (" & Left(strTemp, Len(strTemp) - 1) & ")"
strSQL = "IN (" & strTemp & ")"
Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployeeSelect"
Set qry = .CreateQueryDef("qryEmployeeSelect", strSQL)
End With
End Sub

Posted by: MadPiet Dec 11 2017, 03:51 PM

Instead of assigning the value of the SQL string directly to the querydef's SQL property, why not just print out the value of the variable to the immediate window so you can see what it's building?

I think it would be easier to create a function that just built the SQL statement (and nothing else!) and then just use that. Then at least the pieces are separate and you can test them independently.

Posted by: MadPiet Dec 11 2017, 06:01 PM

How about something like this? (Still working on it, but hopefully you get the idea)... I got tired of repeating all the code for the various mult-select listboxes, so I created a function to handle it.

The only hard part is the ANDing stuff... the rest is trivial.

 MeritCriteriaForm_v2.zip ( 497.68K ): 4
 

Posted by: Brandi Dec 11 2017, 08:00 PM

Thank you. I just tried your sample.
It does work except the criteria in the select query is numeric. It says 6 or 7.

I will need to change it to In("006","007")

Thank you for taking so much time on this. I really appreciate it. I've got to get this working hopefully by tomorrow.

Brandi

Posted by: MadPiet Dec 11 2017, 10:50 PM

Like this maybe?

CODE
Private Sub Command16_Click()
    Dim varItem As Variant
    Dim strList As String
    
    For Each varItem In Me.lbxEmployee.ItemsSelected
        strList = strList & ", '" & Me.lbxEmployee.ItemData(varItem) & "'"
    Next varItem
    
    strList = Right$(strList, Len(strList) - 2)
    Me.Text14 = strList
End Sub

Posted by: Brandi Dec 12 2017, 11:25 AM

I am not having any luck. I am not understanding this last code posted. What is lbxEmployee?
Where is the "In"?
What is Me.Text14? Does StrList put a comma' before each Item? Would the first item then have a comma before it?
I can't get this to run , so I cannot really say what I need to change. I am at a loss right now but thank you for your help.
Brandi

Private Sub Command16_Click()
    Dim varItem As Variant
    Dim strList As String
    
    For Each varItem In Me.lbxEmployee.ItemsSelected
        strList = strList & ", '" & Me.lbxEmployee.ItemData(varItem) & "'"
    Next varItem
    
    strList = Right$(strList, Len(strList) - 2)
    Me.Text14 = strList
End Sub

Posted by: MadPiet Dec 12 2017, 11:45 AM

Sorry, this is what happens to me when I do things late at night or do too many things at once!

lbxEmployee is a listbox of employees. It's basically looping through the .SelectedItems collection and then wrapping each item in single quotes (because EmployeeID is a text string).

That chunk is just going to return a string of comma-separated values, like this:

'Emp001','Emp002','Emp003'

In a word, I should break this up into different functions and then call/test them one at a time. (Just helps to isolate each bit and make sure that bit works before combining them!)

--- Okay, let me back up a step.... Where are you calling all of this from? Which form? "frmReportCriteria"?

In a nutshell, when I have problems like this, the easiest way for me to deal with them is one issue at a time. So generally, I'll try to isolate a problem, and then write a function that solves one piece of the problem at a time. Then I can call a bunch of those functions (or have one function call another one) so that the outermost function returns what I want. At least that way, I can test each piece individually and figure out where things are going haywire.

Post back where I should be looking/adding/modifying code to fix this.

Sorry about that!
Pieter

Posted by: Brandi Dec 12 2017, 02:45 PM

This is on frmSelect in the AfterUpdate event for the listbox named SelectEmployee.
right now the SQL is looking for numbers instead of text.
You had suggested taking out the "OR' and instead using IN("001",'002") which I think is a good idea but I am just not getting the syntax right.

Thank you.
Brandi

Posted by: MadPiet Dec 12 2017, 03:57 PM

Change the double-quotes around each value to single quotes

Instead of this...
IN ("003","004")

You want this...
IN('003','004')

Posted by: Brandi Dec 13 2017, 10:19 AM

That actually works either way. The samples I have seen had double quotes, but I see that single quote can also work after looking at yours.

BUT that is if I type in manually just to see how it works.

The issue is I need the dynamic query to come up with the In('EmpID1', 'EmpID2', 'EmpID3') which are the EmpIDs from the listbox on frmSelect named SelectEmployee.

The line in question is in frmSelect in the AfterUpdate event for the listbox named SelectEmployee.
right now the SQL is looking for numbers instead of text.
You had suggested taking out the "OR' and instead using IN("001",'002") which I think is a good idea but I am just not getting the syntax right.

Here is the current code which I modified according to your suggestions. I have spent hours trying to figure out the right syntax. I think the line in question is this: strTemp = strTemp & cQUOTE & (qryEmployeeList.EmpID) = cQUOTE & Me.ActiveControl.ItemData(varItem) & cQuoteComma
I GET ERROR 424 OBJECT REQUIRED and it highlights this line.

Entire event listed below.
Private Sub SelectEmployee_AfterUpdate()
Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef
' the Constant declaration is a bit of overkill... just makes the code really obvious!
Const cQUOTE As String = "'"
Const cQuoteComma As String = "',"
strSQL = "SELECT qryEmployeeList.EmpID, qryEmployeeList.Last, qryEmployeeList.First, qryEmployeeList.Division, qryEmployeeList.Department,qryEmployeeList.Location, qryEmployeeList.Position FROM qryEmployeeList"
For Each varItem In Me.ActiveControl.ItemsSelected
'strTemp = strTemp & "((qryEmployeeList.EmpID) = '" & Me.ActiveControl.ItemData(varItem) & "',"
strTemp = strTemp & cQUOTE & (qryEmployeeList.EmpID) = cQUOTE & Me.ActiveControl.ItemData(varItem) & cQuoteComma
Next varItem
strTemp = strTemp & " WHERE (" & Left(strTemp, Len(strTemp) - 1) & ")"
strSQL = "IN (" & strTemp & ")"
Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployeeSelect"
Set qry = .CreateQueryDef("qryEmployeeSelect", strSQL)
End With

End Sub

Thank you,
Brandi


Posted by: MadPiet Dec 13 2017, 10:35 AM

Simple answer - Don't use Me.ActiveControl. Just refer to the listbox.

dim lbx as listbox

set lbx = me.lbxEmployee
...

Then if you wanted, you could create a function that got a reference to the listbox and returned a properly delimited list of values... then you'd have ONE function to process ALL the listboxes on your form that you'd just call multiple times.

Posted by: Brandi Dec 13 2017, 10:50 AM

Thank you. Sounds very logical but for me is very difficult. I wouldn't know where to begin to write a function.

I'm sorry for bothering you. For me it is easier to understand queries. Should I try and post this another way?

Thank you.
Brandi

Posted by: MadPiet Dec 13 2017, 11:09 AM

Ha! Found it! Here's the code to process a listbox. As is, it's incomplete, because I don't know what field the list of values belongs to.

CODE
Private Function GetSelectedItems(ByVal lbx As ListBox) As String
    Const cQUOTE = "'"
    Dim varItem As Variant
    Dim strList As String
    
    If lbx.ItemsSelected.Count > 0 Then
        For Each varItem In lbx.ItemsSelected
            strList = strList & "," & cQUOTE & lbx.ItemData(varItem) & cQUOTE
        Next varItem
        
        strList = Right$(strList, Len(strList) - 1)
    End If
    
    GetSelectedItems = strList
End Function


If you changed this a little, then you could build the filters easily:


CODE
Private Function GetSelectedItems(ByVal lbx As ListBox, ByVal strFieldName As String) As String
    Const cQUOTE = "'"
    Dim varItem As Variant
    Dim strList As String
    
    If lbx.ItemsSelected.Count > 0 Then
        For Each varItem In lbx.ItemsSelected
            strList = strList & "," & cQUOTE & lbx.ItemData(varItem) & cQUOTE
        Next varItem
        
        strList = Right$(strList, Len(strList) - 1)
    End If
    
    GetSelectedItems = "[" & strFieldName &"] IN (" & strList & ")"
End Function


Then you'd call it like this:
CODE
strFilterString = GetSelectedItems(me.lbxEmployees, "EmployeeID")


and it would return

"[EmployeeID] IN (1,2,3)"

then if you got the field type of the values in the listbox, you could determine if a delimiter was required (like for dates and strings), and you're golden. Building your filter would be a series of calls to GetSelectedItems() and then you could just AND them together.

Posted by: MadPiet Dec 13 2017, 11:55 AM

Oh, that explains the playing with QueryDefs stuff... as I asked elsewhere, where are you going with all this? What's the end goal, besides filtering the data on your form? Are you filtering down a recordset or something to pass to a report?

Posted by: Brandi Dec 13 2017, 01:30 PM

Yes. I am filtering employee records to use in a form and also in reports.
I can create a query manually but I don't quite know how to get the criteria to be correct with a dynamic query.
I'm not good with just writing in SQL on the fly, so that is why I like queries.

I'll take a look at your function above.

Thank you.
Brandi

Posted by: MadPiet Dec 13 2017, 03:06 PM

Now it makes more sense! You can filter both forms and reports in the Open event or when you use DoCmd.OpenReport or DoCmd.OpenForm...

Since both Reports and Forms have a RecordSource property (the query or table the object is based on), you can pass a filter to them at runtime. (The filter would be just a valid WHERE clause without the "WHERE" keyword). So, you don't need to create the whole query definition at all. You could just build the WHERE clause and pass that in the open event of the object you're opening. So you could build the filter in pieces using the form "frmSelect" and then have a button on there that opens a selected form/report (from a dropdown, maybe) and pass the filter in the Open event.

Posted by: Brandi Dec 13 2017, 03:28 PM

Yes. that is my intent but either way, I need to make the SQL statement work or the dynamic query of it work and that is my problem. I'll have to sit down again and see what I can figure out from your recent posts.
Thank you.
Brandi

Posted by: MadPiet Dec 13 2017, 03:44 PM

If your intention is just to filter a report or form when you open it, then you don't need any of the QueryDef stuff at all. Both forms and reports have a RecordSource property that can be any of the following:
1. a table name
2. a query name (as long as it's a select query!)
3. a SQL SELECT statement (one you build on the fly)

But since you're most likely binding fields to controls in your form/report, the field list in your query will be fixed 99% of the time. That's basically a QueryDef right there without the WHERE part. But you can build that filter (the stuff after the WHERE keyword) dynamically and pass it to a form or report when you open it.

Both DoCmd.OpenForm and DoCmd.OpenReport have a filter argument where you can pass a valid WHERE clause when you call either one. They will both apply the passed filter to the form or report's underlying recordset for you - so you can AND your filter "chunks" together and just pass that when you open your form/report.

What this means to you is that the only part of the query you need to worry about is the stuff after the WHERE keyword. (So you could have one form that builds the filter you want, and then have a dropdown to select the object you want to open and pass the filter to and do the whole thing with only a few lines of code.