Form With Combo Boxes

[Ron]

I have a simple form with two combo boxes. The first combo box allows
selection of an employee; AfterUpdate the second should requery for only that
employees clients. Once I select the employee, I get an error - basically
typed incorrectly or too complex. Here are the queries for the combo boxes:

SELECT NullID As EmployeeID, "<All Sales Reps>" As EmployeeName
FROM tblDummy
UNION ALL SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName
FROM tblEmployees
ORDER BY EmployeeName;

PARAMETERS Forms!fpopCaseSelectV2!cmbEmployeeName Long;
SELECT NullID As CliCode, "< All Customers >" As CliReportingName
FROM tblDummy
UNION ALL SELECT tblBillingMeetingData.CliCode,
tblBillingMeetingData.CliReportingName
FROM tblBillingMeetingData
WHERE (tblBillingMeetingData.BTKName =
[Forms]![fpopCaseSelectV2]![cmbEmployeeName]) OR
([Forms]![fpopCaseSelectV2]![cmbEmployeeName] Is Null)
ORDER BY CliReportingName;

I haven't done a lot of this but I'm guessing (hoping) it's something
simple. Can someone help me out? Thank you.

 

[Klatuu]

Try your where clause like this:
WHERE tblBillingMeetingData.BTKName Like
IIf([Forms]![fpopCaseSelectV2]![cmbEmployeeName] Is Null, "*",
([Forms]![fpopCaseSelectV2]![cmbEmployeeName] ) ORDER BY CliReportingName;

 

[Douglas J. Steele]

Assuming you're trying to have the row <All Sales Reps> return a Null value,
your SQL should be

SELECT Null As EmployeeID, "<All Sales Reps>" As EmployeeName
FROM tblDummy
UNION
SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName
FROM tblEmployees
ORDER BY EmployeeName;

Note that I removed the ALL from UNION: I'm assuming you don't want
duplicate rows returned should there be any.

 

[Douglas J. Steele]

I've never seen the point of using Like when you don't need to, Dave. Ron's
SQL is fine.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Try your where clause like this:
WHERE tblBillingMeetingData.BTKName Like
IIf([Forms]![fpopCaseSelectV2]![cmbEmployeeName] Is Null, "*",
([Forms]![fpopCaseSelectV2]![cmbEmployeeName] ) ORDER BY CliReportingName;

--
Dave Hargis, Microsoft Access MVP

I have a simple form with two combo boxes. The first combo box allows
selection of an employee; AfterUpdate the second should requery for only
that
employees clients. Once I select the employee, I get an error -
basically
typed incorrectly or too complex. Here are the queries for the combo
boxes:

SELECT NullID As EmployeeID, "<All Sales Reps>" As EmployeeName
FROM tblDummy
UNION ALL SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName
FROM tblEmployees
ORDER BY EmployeeName;

PARAMETERS Forms!fpopCaseSelectV2!cmbEmployeeName Long;
SELECT NullID As CliCode, "< All Customers >" As CliReportingName
FROM tblDummy
UNION ALL SELECT tblBillingMeetingData.CliCode,
tblBillingMeetingData.CliReportingName
FROM tblBillingMeetingData
WHERE (tblBillingMeetingData.BTKName =
[Forms]![fpopCaseSelectV2]![cmbEmployeeName]) OR
([Forms]![fpopCaseSelectV2]![cmbEmployeeName] Is Null)
ORDER BY CliReportingName;

I haven't done a lot of this but I'm guessing (hoping) it's something
simple. Can someone help me out? Thank you.

 

[Ron]

That works for getting the list of all clients but when you select a value in
cmbEmployeeName and Tab or click out, it doesn't requery the client combo box
and it continues to list all clients. Here's the action attached to
cmbEmployeeName:

Private Sub cmbEmployeeName_AfterUpdate()
' Requery the customer list when changing Employee Name
Me.cmbCustomerID.Requery
End Sub

Try your where clause like this:
WHERE tblBillingMeetingData.BTKName Like
IIf([Forms]![fpopCaseSelectV2]![cmbEmployeeName] Is Null, "*",
([Forms]![fpopCaseSelectV2]![cmbEmployeeName] ) ORDER BY CliReportingName;

--
Dave Hargis, Microsoft Access MVP

I have a simple form with two combo boxes. The first combo box allows
selection of an employee; AfterUpdate the second should requery for only that
employees clients. Once I select the employee, I get an error - basically
typed incorrectly or too complex. Here are the queries for the combo boxes:

SELECT NullID As EmployeeID, "<All Sales Reps>" As EmployeeName
FROM tblDummy
UNION ALL SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName
FROM tblEmployees
ORDER BY EmployeeName;

PARAMETERS Forms!fpopCaseSelectV2!cmbEmployeeName Long;
SELECT NullID As CliCode, "< All Customers >" As CliReportingName
FROM tblDummy
UNION ALL SELECT tblBillingMeetingData.CliCode,
tblBillingMeetingData.CliReportingName
FROM tblBillingMeetingData
WHERE (tblBillingMeetingData.BTKName =
[Forms]![fpopCaseSelectV2]![cmbEmployeeName]) OR
([Forms]![fpopCaseSelectV2]![cmbEmployeeName] Is Null)
ORDER BY CliReportingName;

I haven't done a lot of this but I'm guessing (hoping) it's something
simple. Can someone help me out? Thank you.