What? Combo dropdown group headers? What voodoo is this?

I have a combo box on a continuous form in an Item Description field. Each row on the continuous form can contain a line item that can be populated from different sources.

There is a job estimate source where items are defined for the job.

There is a vendor pricing source where items are defined for the vendor.

There are actually 3 different categories of items for the vendor.

I decided to solve this with “UNION ALL” queries. A standard UNION query automatically de-duplicates the results and re-orders them and I want them in the order in which they were retrieved, hence I’m using a UNION ALL query.

It looks something like this (in a very simplified format):

SELECT IDescription as [Description], Unit_Price as [Unit $] FROM [job_estimate_items]
UNION ALL
SELECT VDescription as [Description], Item_Price as [Unit $] FROM [vendor_items]

The customer wants to add headers into the rows. The form should ignore the column header selection.

Let’s say the headers should be JOB ITEMS and VENDOR ITEMS respectively. The first thing to do here is to add the headers via their own queries:

SELECT TOP 1 'JOB ITEMS' as [Description], Null as [Unit $] FROM [job_estimate_items]
UNION ALL
SELECT IDescription as [Description], Unit_Price as [Unit $] FROM [job_estimate_items]
UNION ALL
SELECT TOP 1 'VENDOR ITEMS' as [Description], Null as [Unit $] FROM [vendor_items]
UNION ALL
SELECT VDescription as [Description], Item_Price as [Unit $] FROM [vendor_items]

This actually can have problems because we are not matching column types. The NULL value in the second column can prevent Access from detecting the correct version of the column. The way I am choosing to get around this in this case is to change them all to strings:

SELECT TOP 1 'JOB ITEMS' as [Description], '' as [Unit $] FROM [job_estimate_items]
UNION ALL
SELECT IDescription as [Description], CStr(Unit_Price) as [Unit $] FROM [job_estimate_items]
UNION ALL
SELECT TOP 1 'VENDOR ITEMS' as [Description], '' as [Unit $] FROM [vendor_items]
UNION ALL
SELECT VDescription as [Description], CStr(Item_Price) as [Unit $] FROM [vendor_items]

So far so good. I’ll let you know if I run into any other speedbumps and how I deflect the user from selecting a group title in another message.