Referencing a dropdown list box on a form

[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

 

[Stefan Hoffmann]

hi,

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. Aha.

& " where ((qry_idCritical_keyRoles.business_unit) = " &
Forms!frm_MainMenu!cmbBU & ");" _
& " ORDER BY qry_idCritical_keyRoles.incumbent;"

First problem: is [business_unit] a number or a string? If it is a
string, you need to enclose it in quotation marks:

" WHERE business_unit = '" & _
Replace(Forms!frm_MainMenu!cmbBU, "'", "''") & "') "

The Replace() function handles quotation marks in the content.

The real problem is the semi-colon at the wrong place:

& " where ((qry_idCritical_keyRoles.business_unit) = " &
Forms!frm_MainMenu!cmbBU & ");" _
& " ORDER BY qry_idCritical_keyRoles.incumbent;"

The first one in the second line needs to be removed, otherwise the
ORDER BY makes no sense.


mfG
--> stefan <--

 

[Douglas J. Steele]

Get rid of the semi-colon at the end of this line:

& " where ((qry_idCritical_keyRoles.business_unit) = " &
Forms!frm_MainMenu!cmbBU & ");" _

 

[Miskacee]

Get rid of the semi-colon at the end of this line:

& " where ((qry_idCritical_keyRoles.business_unit) = " &
Forms!frm_MainMenu!cmbBU & ");" _

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

Thanks to both of you. I will try this tomorrow.

 

[Miskacee]

I tried both suggestions and I'm still gett the same missing operator error.
Any more suggestions?

Thanks in advance for your help!