Combo box - multiple tables

[axw]

I have
tblProducts - Where is my inventory list and
tblServices - where are my services

Also I have made an invoice form and a datasheet subform (orderDetails) where I enter/choose (using combo box) said services/products but I know only how to do it for one table ex. tblProducts. My question is can I make a drop down list for two tables? One column - ServiceName / ProductName
So I can choose a service or a product in the same order line.
Or do I have to join my services and products in the same table? Which would be really unpractical for my business.

Thanks in advance.

 

[BlueIshDan]

You can make a query that goes something like this:

 SELECT service_product_name 
 FROM (SELECT DISTINCT (servicename) As service_product_name FROM tblServicecs)
 INNER JOIN (SELECT DISTINCT (productname) As service_product_name FROM tblProducts)

Something like that maybe?

 

[axw]

I get this: Syntax error in FROM clause

 

[BlueIshDan]

SELECT service_product_name   FROM (SELECT DISTINCT (servicename) As service_product_name FROM tblServices)  UNION (SELECT DISTINCT (productname) As service_product_name FROM tblProducts);

 

[axw]

Great! Works, thanks a lot.