Thank you for the reply, is a variable I use for a multiple search routine. The code is as follows now:
Code:
Option Compare DatabaseOption Explicit
Dim strSearchCompany As String
Dim strSearchFirst As String
Dim strSearchLast As String
Dim strSearchMissionDept As String
Dim boolFullSearch As Boolean
Dim ArgCount As Integer
Dim MyCriteria As String
Dim strSQLCommand As String
Dim strSearchSelect As String
Dim strSearchCriteria As String
Dim MyRecordSource As String
Dim varItm As Variant
Const MySQL = "SELECT tbl_contacts.contact_ID, tbl_contacts.[First] & ' ' & tbl_contacts.[Middle] & ' ' & tbl_contacts.[Last] AS [Name], tbl_contacts.jobtitle AS [Professional Title], tbl_company.company_name AS [Company], tbl_contacts.[Department], tbl_company.company_address1 AS [Address] "
'Const MySQL = "SELECT [contact_ID], [Name], [jobtitle] AS [Professional Title], [company_name] AS [Company], [department_initials] AS [Department], [contact_address1] AS [Address] "
'Const strSearchFrom = "FROM tbl_categories INNER JOIN ((((tbl_contacts Left JOIN tbl_company ON tbl_contacts.Company = tbl_company.company_id) INNER JOIN tbl_contact_dept ON tbl_contacts.contact_ID = tbl_contact_dept.contact_ID) INNER JOIN tbl_category_contact ON tbl_contacts.contact_ID = tbl_category_contact.contact_id) INNER JOIN tbl_mission_dept ON tbl_contact_dept.department_id = tbl_mission_dept.department_id) ON tbl_categories.catergory_ID = tbl_category_contact.category_id; "
Const strSearchFrom = "FROM tbl_contacts Left JOIN tbl_company ON tbl_contacts.Company = tbl_company.company_id "
'Const strSearchFrom = "FROM qry_ContactList "
Const strSearchOrder = " ORDER BY tbl_company.company_name, tbl_contacts.Last"
Code:
Private Sub Form_Current()
MyCriteria = "(((True)<>False) AND ((tbl_contacts.archive)=No)) "
' Create SELECT statement.
MyRecordSource = MySQL & strSearchFrom & "WHERE " & MyCriteria & strSearchOrder & ";"
' set record source to Subform
Me!lstContacts.RowSourceType = "Table/Query"
Me!lstContacts.RowSource = MyRecordSource
MyCriteria = ""
ArgCount = 0
Me!txt_SearchFirst.SetFocus
Me!lstContacts.Selected(0) = True
Me!txtContactID = lstContacts.Column(0, varItm)
Me!txtContactCount.Value = lstContacts.ListCount
End Sub