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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> 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
 

Posts in this topic
- Brandi   Not Sure How To Pass Data From A Selection Criteria Form   Dec 7 2017, 06:58 PM
- - MadPiet   What's with all that code? I thought you wante...   Dec 7 2017, 07:03 PM
- - Brandi   The user selects the criteria they want from 4 dif...   Dec 7 2017, 07:17 PM
- - MadPiet   Why create the whole query when all you really nee...   Dec 7 2017, 07:28 PM
- - Brandi   I'm sorry. I'm not very good at creating ...   Dec 7 2017, 08:53 PM
- - MadPiet   <snip>The user selects the criteria they wan...   Dec 7 2017, 09:37 PM
- - BruceM   This link describes using a multi-select list box ...   Dec 8 2017, 08:00 AM
- - Brandi   OK. maybe I should start over. This is a form wit...   Dec 8 2017, 09:24 AM
- - BruceM   QUOTE the query that is created is considering the...   Dec 8 2017, 11:39 AM
|- - Brandi   EmpID's come from another system where they ar...   Dec 8 2017, 01:33 PM
|- - Brandi   I am attaching a sample database. For purposes of...   Dec 8 2017, 02:08 PM
- - Brandi   Thank you for the link. I have read it and it see...   Dec 9 2017, 10:10 AM
- - MadPiet   Note the modifications - I wrapped the result of ...   Dec 9 2017, 11:31 AM
- - Brandi   Thank you so much for your help. I think I am get...   Dec 10 2017, 03:32 PM
- - MadPiet   Your syntax for IN is wrong. =( It's like thi...   Dec 10 2017, 04:35 PM
- - Brandi   Do you mean there is something wrong with this lin...   Dec 10 2017, 05:09 PM
- - MadPiet   What does your code do when you run it? Not total...   Dec 10 2017, 05:56 PM
- - Brandi   I get the same error with or without a space afte...   Dec 10 2017, 11:27 PM
- - MadPiet   Your syntax is still incorrect. The syntax for IN ...   Dec 11 2017, 10:41 AM
- - Brandi   I like your idea of making the quote a constant. ...   Dec 11 2017, 03:19 PM
- - MadPiet   Instead of assigning the value of the SQL string d...   Dec 11 2017, 03:51 PM
- - MadPiet   How about something like this? (Still working on i...   Dec 11 2017, 06:01 PM
- - Brandi   Thank you. I just tried your sample. It does work...   Dec 11 2017, 08:00 PM
- - MadPiet   Like this maybe? CODEPrivate Sub Command16_Click...   Dec 11 2017, 10:50 PM
- - Brandi   I am not having any luck. I am not understanding ...   Dec 12 2017, 11:25 AM
- - MadPiet   Sorry, this is what happens to me when I do things...   Dec 12 2017, 11:45 AM
- - Brandi   This is on frmSelect in the AfterUpdate event for ...   Dec 12 2017, 02:45 PM
- - MadPiet   Change the double-quotes around each value to sing...   Dec 12 2017, 03:57 PM
- - Brandi   That actually works either way. The samples I hav...   Dec 13 2017, 10:19 AM
- - MadPiet   Simple answer - Don't use Me.ActiveControl. J...   Dec 13 2017, 10:35 AM
- - Brandi   Thank you. Sounds very logical but for me is very...   Dec 13 2017, 10:50 AM
- - MadPiet   Ha! Found it! Here's the code to proc...   Dec 13 2017, 11:09 AM
- - MadPiet   Oh, that explains the playing with QueryDefs stuff...   Dec 13 2017, 11:55 AM
- - Brandi   Yes. I am filtering employee records to use in a ...   Dec 13 2017, 01:30 PM
- - MadPiet   Now it makes more sense! You can filter both ...   Dec 13 2017, 03:06 PM
- - Brandi   Yes. that is my intent but either way, I need to ...   Dec 13 2017, 03:28 PM
- - MadPiet   If your intention is just to filter a report or fo...   Dec 13 2017, 03:44 PM



Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 08:42 PM