UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Not Sure How To Pass Data From A Selection Criteria Form, Access 2010    
 
   
Brandi
post Dec 7 2017, 06:58 PM
Post#1



Posts: 1,573
Joined: 24-June 04



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
Go to the top of the page
 
MadPiet
post Dec 7 2017, 07:03 PM
Post#2



Posts: 2,264
Joined: 27-February 09



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 ...
Go to the top of the page
 
Brandi
post Dec 7 2017, 07:17 PM
Post#3



Posts: 1,573
Joined: 24-June 04



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
This post has been edited by Brandi: Dec 7 2017, 07:19 PM
Go to the top of the page
 
MadPiet
post Dec 7 2017, 07:28 PM
Post#4



Posts: 2,264
Joined: 27-February 09



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...
This post has been edited by MadPiet: Dec 7 2017, 07:30 PM
Go to the top of the page
 
Brandi
post Dec 7 2017, 08:53 PM
Post#5



Posts: 1,573
Joined: 24-June 04



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?
Go to the top of the page
 
MadPiet
post Dec 7 2017, 09:37 PM
Post#6



Posts: 2,264
Joined: 27-February 09



<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
Go to the top of the page
 
BruceM
post Dec 8 2017, 08:00 AM
Post#7


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


This link 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.
Go to the top of the page
 
Brandi
post Dec 8 2017, 09:24 AM
Post#8



Posts: 1,573
Joined: 24-June 04



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
Go to the top of the page
 
BruceM
post Dec 8 2017, 11:39 AM
Post#9


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


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.
Go to the top of the page
 
Brandi
post Dec 8 2017, 01:33 PM
Post#10



Posts: 1,573
Joined: 24-June 04



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"));
This post has been edited by Brandi: Dec 8 2017, 01:39 PM
Go to the top of the page
 
Brandi
post Dec 8 2017, 02:08 PM
Post#11



Posts: 1,573
Joined: 24-June 04



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
This post has been edited by Brandi: Dec 8 2017, 02:08 PM
Attached File(s)
Attached File  MeritCriteriaForm.zip ( 464.54K )Number of downloads: 7
 
Go to the top of the page
 
Brandi
post Dec 9 2017, 10:10 AM
Post#12



Posts: 1,573
Joined: 24-June 04



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
This post has been edited by Brandi: Dec 9 2017, 10:10 AM
Go to the top of the page
 
MadPiet
post Dec 9 2017, 11:31 AM
Post#13



Posts: 2,264
Joined: 27-February 09



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

This post has been edited by MadPiet: Dec 9 2017, 11:35 AM
Go to the top of the page
 
Brandi
post Dec 10 2017, 03:32 PM
Post#14



Posts: 1,573
Joined: 24-June 04



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
Go to the top of the page
 
MadPiet
post Dec 10 2017, 04:35 PM
Post#15



Posts: 2,264
Joined: 27-February 09



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);
Go to the top of the page
 
Brandi
post Dec 10 2017, 05:09 PM
Post#16



Posts: 1,573
Joined: 24-June 04



Do you mean there is something wrong with this line? strTemp = "In("
Go to the top of the page
 
MadPiet
post Dec 10 2017, 05:56 PM
Post#17



Posts: 2,264
Joined: 27-February 09



What does your code do when you run it?

Not totally sure, but I think you need a space between IN and (.
This post has been edited by MadPiet: Dec 10 2017, 05:57 PM
Go to the top of the page
 
Brandi
post Dec 10 2017, 11:27 PM
Post#18



Posts: 1,573
Joined: 24-June 04



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’)’
Go to the top of the page
 
MadPiet
post Dec 11 2017, 10:41 AM
Post#19



Posts: 2,264
Joined: 27-February 09



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


This post has been edited by MadPiet: Dec 11 2017, 10:57 AM
Go to the top of the page
 
Brandi
post Dec 11 2017, 03:19 PM
Post#20



Posts: 1,573
Joined: 24-June 04



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
This post has been edited by Brandi: Dec 11 2017, 03:21 PM
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 03:16 AM