Form With Combo Boxes


R

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

Advertisements

K

Klatuu

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

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

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)


Klatuu said:
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


Ron said:
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.
 
Ad

Advertisements

R

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


Klatuu said:
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


Ron said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top