M
Miskacee
I'm trying to reference a list box. It defaults to 'select all' or lists
various business units. I have no issue with the 'select all' and then all
records by business unit shows up; I am having an issue with the query to
reference the list box though. I know how to reference it using the query
screen, [forms]![form name]![name] but can't figure out what is missing from
my SQL statement below. I keep getting missing operator. I believe this
line is the issue:
Forms!frm_critical_keyRoles.RecordSource = strSQL
but not certain.
CODE:
Private Sub cmdCritical_Click()
Dim rs As Recordset
Dim rs2 As Recordset
Dim strSQL As String
Dim db As Database
Set db = CurrentDb
DoCmd.OpenForm "frm_critical_keyRoles", , , , , acHidden
strSQL = "SELECT qry_idCritical_keyRoles.title,
qry_idCritical_keyRoles.Incumbent" _
& "qry_idCritical_keyRoles.Level,
qry_idCritical_keyRoles.Business_Unit" _
& "qry_idCritical_keyRoles.sub_business_Unit,
qry_idCritical_keyRoles.division" _
& "qry_idCritical_keyRoles.key/critical_role" _
& " FROM qry_idCritical_keyRoles" _
& " where ((qry_idCritical_keyRoles.business_unit) = " &
Forms!frm_MainMenu!cmbBU & ");" _
& " ORDER BY qry_idCritical_keyRoles.incumbent;"
If Me.cmbBU = "Select All" Then
DoCmd.OpenForm "frm_critical_keyRoles", acFormDS
Else
Forms!frm_critical_keyRoles.RecordSource = strSQL
End If
End Sub
various business units. I have no issue with the 'select all' and then all
records by business unit shows up; I am having an issue with the query to
reference the list box though. I know how to reference it using the query
screen, [forms]![form name]![name] but can't figure out what is missing from
my SQL statement below. I keep getting missing operator. I believe this
line is the issue:
Forms!frm_critical_keyRoles.RecordSource = strSQL
but not certain.
CODE:
Private Sub cmdCritical_Click()
Dim rs As Recordset
Dim rs2 As Recordset
Dim strSQL As String
Dim db As Database
Set db = CurrentDb
DoCmd.OpenForm "frm_critical_keyRoles", , , , , acHidden
strSQL = "SELECT qry_idCritical_keyRoles.title,
qry_idCritical_keyRoles.Incumbent" _
& "qry_idCritical_keyRoles.Level,
qry_idCritical_keyRoles.Business_Unit" _
& "qry_idCritical_keyRoles.sub_business_Unit,
qry_idCritical_keyRoles.division" _
& "qry_idCritical_keyRoles.key/critical_role" _
& " FROM qry_idCritical_keyRoles" _
& " where ((qry_idCritical_keyRoles.business_unit) = " &
Forms!frm_MainMenu!cmbBU & ");" _
& " ORDER BY qry_idCritical_keyRoles.incumbent;"
If Me.cmbBU = "Select All" Then
DoCmd.OpenForm "frm_critical_keyRoles", acFormDS
Else
Forms!frm_critical_keyRoles.RecordSource = strSQL
End If
End Sub