I'm trying to enhance the technique to add a "New"/"All" value to a list.

Starting point - this works:
SELECT MfrRls FROM SCH_Forms
WHERE JobNum=[txtJobNum] AND Form=[cmbForm]
UNION Select
"New" AS New From SCH_Forms ORDER BY MfrRls;

When there are records in the table the value list includes the MfrRls's plus New.
When there are no records in the table the value list is New.

The goal is for that 'new' value to be the next MfrRls number.

Table
JobNum / Form / MfrRls
1234 / 1 / 1
1234 / 1 / 2

The value list should be 1,2,3

The SQL Statement is now to this point and works when there is a record in the table, I need it to create a value list: 1 when there are no records in the table.
- I put in the Nz hoping that would do what was needed.

SELECT MfrRls FROM SCH_Forms
WHERE JobNum=[txtJobNum] AND Form=[cmbForm]
UNION Select
( SELECT Nz(Max(MfrRls),0)+1 AS New
FROM SCH_Forms
WHERE SCH_Forms.[JobNum]=[txtJobNum] AND SCH_Forms.[Form]=[cmbForm]
GROUP BY SCH_Forms.MfrRls) as MfrRls
From SCH_Forms ORDER BY MfrRls;

I realize that another approach is to construct the value list in VBA code. It would be great to 'keep it simple', if it is possible - and I would add another technique to my repertoire.

Just by way of terminology, a value list is something like "Yes","No","Maybe","In your dreams", where the combo box Row Source Type is Value List. You seem to be describing the Row Source when Table/Query is selected as the Row Source Type.

I'm curious about a table that has records one day, but may not the next day. What happens to the records?

That said, I think you will need to use DMax rather than Max, as Max can only work if there are records:


This assumes txtJobNum and Form are text fields that will never contain apostrophes. If txtJobNum and cmbForm are fields on a form, reference them like this:


Substitute the actual form name for FormName.

BTW, Form is a reserved word, and should not be used for names. For more about reserved words:
http://www.allenbrowne.com/AppIssueBadWord.html

There are also posts about reserved words in the FAQ section of UA, I believe.

Thanks Bruce! It's working!

Thanks for the other tips...
I changed the Form in (2) tables to FormID, and fixed a couple queries.
I first created the tables on SQL Server and had the sense to check its reserved words. Didn't think about checking Access - Duh! Silly Me! FORM - it doesn't get any more obvious than this! (ha, ha) (Of course, "report" may tie it).

The SQL statement now reads:

As for your curiosity about "records there one day and not the next"...
You misunderstood what I said (I could have said it better now that I read it over).
There will be no SCH_Form records until the Scheduler runs this form and creates them. Once they are created he can retrieve them for editing, or he may add a new MfrRls. The sample data was to illustrate/test when there are existing records for a Job/Form.

Thanks again for your help! I appreciate your taking the time.

I think I see. It's not so much the table that won't have records as that the query parameters could result in no records. Something like that.

Anyhow, glad to help and to hear you got it working