Need to add static entry to a SQL populated Combo Box (2 Viewers)

[mdlueck]

In the application I am developing, I have a Quotes form. On that form is a Combo Box which is populated by a SQL query statically entered into the control.

SELECT t.id & ' / ' & t.vendortitle AS [ID / Vendor:],t.id,t.rev AS [Rev:],t.ver AS [Ver:],t.poprice AS [Price:] FROM tmptblqry_quotes AS t ORDER BY t.rev,t.ver,t.vendortitle

I have the Combo Box control bound to Col #2 which is the unique ID of the record, and that field is not displayed in the Combo Box list.

The quotes form also displays the records in FE temp table tmptblqry_quotes.

Which ever the selected quote is in the combo box, that record is located when the form opens / refreshes and is the selected record.

That is all working very well. And thus "one more requirement" has crept in...

Not all parts in the application I am building will have quotes on file... such as all of the current production parts. So I need to statically add as the top entry of the Combo Box 'Production Part - Refers to JDE' and have the ID which means that value be some impossible value, such as -1.

I do not want that choice to show up in the multiple records grid... just in the Combo Box. Temporarily adding a special record would end up showing up as a Quote record in the Multiple Records grid view!

I came across this page:

"How do I... Add items to an Access combo box on the fly?"
http://www.techrepublic.com/blog/howdoi/how-do-i-add-items-to-an-access-combo-box-on-the-fly/201

and it appears their example actually adds the record to the table which populates the pick list. (Which is what I do NOT want to do.)

How do I add an additional entry to a Combo Box which is being populated by a SQL Query without needing to really add it to the table?

I have tried already UNION ALL, and that will not work as UNION ALL in Access / Jet requires a FROM for each SELECT. So while:

SELECT 'Production Part - Refers to JDE' AS [ID / Vendor:],-1 AS [id],-1 AS [Rev:],-1 AS [Ver:],-1 AS [Price:]

works by itself, it will not work with UNION ALL.

 

[mdlueck]

This looks promising...

  'Manually add an entry to allow JDE to be the selected source for this part
  Me.fldcomboselectedquoteid.AddItem "Production Part - Refers to JDE", -1

An error states that:

Date: 20111219 Time: 16:50:20 UserID: c_mlueck
AppErrorMsg: Form: Form_products, Subroutine: Form_Load()
Error Source: Fandango_FE
Error Number: 6014
Error Description: The RowSourceType property must be set to 'Value List' to use this method.

So perhaps I would need to manually run the query and add the results set to:
1) Set the control to "Value List"
2) Add my static entry - along the lines of the code I tried
3) Then add the query results - looping through the result set which the query returned?

 

[spikepl]

You make a table with one record (or more) containing whatever you want to have permanently in your combo, and do a union query on that table and your query

 

[mdlueck]

aaahhh, I like your idea, spikepl! I will try it out next.

 

[mdlueck]

Well I am making progress with your suggestion. I have the "static" entry added in for a production part. I have it successfully merging that static entry with the dynamic quote list.

I however can not find a way to re-enable sorting of the quotes by Revision / Version. It was doing that fine before I added the UNION ALL. Any suggestions how to get that working as well?

This one at least works, does not sort by revver
SELECT z.[id_vendor] AS [ID / Vendor:],z.[id],z.[rev],z.[revdisp] AS [Rev:],z.[ver] AS [Ver:],z.[price] AS [Price:] FROM zStaticQuotes AS z
UNION ALL
SELECT t.id & ' / ' & t.vendortitle AS [ID / Vendor:],t.id,t.rev,t.revdisp AS [Rev:],t.ver AS [Ver:],t.poprice AS [Price:] FROM tmptblqry_quotes AS t

Attempt to sort by revver
SELECT z.[id_vendor] AS [ID / Vendor:],z.[id],z.[rev],z.[revdisp] AS [Rev:],z.[ver] AS [Ver:],z.[price] AS [Price:] FROM zStaticQuotes AS z
UNION ALL
SELECT t.id & ' / ' & t.vendortitle AS [ID / Vendor:],t.id,t.rev,t.revdisp AS [Rev:],t.ver AS [Ver:],t.poprice AS [Price:] FROM tmptblqry_quotes AS t ORDER BY t.rev,t.ver

Note: Col [rev] has the Base 10 representation of the Part Revision. Col [revdisp] has the actual Base 26 alpha representation of the Revision, that does not sort correctly so I translate to a Base 10 number which will always sort. Otherwise should be straight forward enough what I am doing.

 

[spikepl]

Why do you use ALL?

Google - and you can find eg this: http://stackoverflow.com/questions/213851/sql-query-using-order-by-in-union

 

[vbaInet]

I'm not sure what you're requirement is. Can you tell us in one sentence what is it you're trying to do?

After a quick peruse through your thread it appears that you want to add items to a combo box on the fly. If this is the case, you want to use Value List and add using ComboBox.AddItem or setting the RowSource to the string of the items separated by a semicolon (.

 

[mdlueck]

Why do you use ALL?

I wanted to be sure that records in the pick list were not getting missed. So, success at last!

Query inside the Combo Box control is as follows:

SELECT [id_vendor] AS [ID / Vendor:],[id],[rev],[revdisp] AS [Rev:],[ver] AS [Ver:],[price] AS [Price:]
  FROM (SELECT z.[id_vendor],z.[id],z.[rev],z.[revdisp],z.[ver],z.[price] FROM [zStaticQuotes] AS z
  UNION
  SELECT t.[id] & ' / ' & t.[vendortitle] AS [id_vendor],t.[id],t.[rev],t.[revdisp],t.[ver],t.[poprice] AS [price] FROM [tmptblqry_quotes] AS t) AS cb
ORDER BY [rev],[ver];

And attached is the results.

Thanks much for the assistance, spikepl!

 

[mdlueck]

I just noticed... the quoted price column is not showing up in the Combo Box list.

The column widths are as follows:

4";0";0";0.5";0.5";1"

So there should be 1" for the price field where the blank spot is at the right of the pick list.

Any thoughts why that might not be showing up?

P.S. And the entire control is 6.5", so 0.5" more than the sum of the requested widths.

 

[mdlueck]

After a quick peruse through your thread it appears that you want to add items to a combo box on the fly.

Yes, I suddenly needed to manually add a static entry to the list to be a special token if Fandango should refer to production JDE for part price information rather than run with internal Fandango pricing. Quite a nasty omission in the design I caught only late last week. "What do you mean you do not want to hand set up pricing for 44,000+ production parts in Fandango, and have to maintain that which JDE is able to provide?!"

 

[mdlueck]

I just noticed... the quoted price column is not showing up in the Combo Box list.

Solved! I added one column to the query, and forgot to bump the Column Count up by one.

I did so and the display is now as expected. Thus reattaching the captured results.

 

[mdlueck]

I thought to try to come back to this query and knock out the need for the static FE temp table by simply selecting the correct strings. I seem to have a syntax error somewhere that I can not see...

Syntax error (missing operator) in query expression ': [id_vendor]'.

Query is as follows:

SELECT [id_vendor] AS [ID / Vendor:],[id],[rev],[revdisp] AS [Rev:],[ver] AS [Ver:],[price] AS [Price:]
FROM ([COLOR=Red][B]SELECT "Production Part - Refers to JDE" AS [id_vendor],-1 AS [id],-1 AS[rev],NULL AS [revdisp],NULL AS [ver],NULL AS [price][/B][/COLOR]
UNION
SELECT t.[id] & ' / ' & t.[vendortitle] AS [id_vendor],t.[id],t.[rev],t.[revdisp],t.[ver],t.[poprice] AS [price] FROM [tmptblqry_quotes] AS t) AS cb
ORDER BY [rev],[ver];

And I marked in RED the part of the query I updated to select from strings instead of the static FE temp table. Looks fine to me... what is Access's problem?

Oh, and the RED query does run properly / as expected in an Access QueryDef window.

 

[jdraw]

How does the red query run with no FROM ??

 

[mdlueck]

How does the red query run with no FROM ??

Because it is "SELECTing" static text to be added to the dynamic other SELECT via the UNION.

So, it performs flawlessly as-is in its own QueryDef.

 

[jdraw]

OK - got it.

 

[Galaxiom]

Here is an alternative that I have found extremely versatile.

Use a disconnected ADO recordset as the Combo's recordset.They are easily populated with anything you desire. Entries can be added at any time and the recordset is can be ordered and filtered without reloading it.

 

[mdlueck]

Use a disconnected ADO recordset as the Combo's recordset.

Hua?! I had no idea that ADO.Recordset objects could be the data source of Form objects!

So would I define an ADO.Recordset object at the Form level so that the object would remain in-scope for as long as the Form is open?

Perhaps do you have some sample code?

So, nothing obvious was jumping out at you as to why Access complains of my alteration to the SQL query? It is certainly baffling to me since the changed part by itself runs without error, and the rest of the query I left unchanged. Perhaps the UNION just is not joining the two separate pieces well.

 

[LPurvis]

Once assigned as the combo's recordset object, there's no need to persist the original object variable (there remains a pointer to it from the control).

Your earlier example of a SELECT statement without a FROM clause does indeed work... but not when in a UNION query in Jet/ACE. (It would be lovely if it did - more consistent anyway, and convenient.)
You need some dummy table as the source in selecting even a fixed value row.

Usual method is to take a settings table or just the top row of another table you know will be there.
For example

SELECT [id_vendor] AS [ID / Vendor:],[id],[rev],[revdisp] AS [Rev:],[ver] AS [Ver:],[price] AS [Price:]
FROM (SELECT TOP 1 "Production Part - Refers to JDE" AS [id_vendor],-1 AS [id],-1 AS[rev],NULL AS [revdisp],NULL AS [ver],NULL AS [price] FROM msysObjects
UNION
SELECT t.[id] & ' / ' & t.[vendortitle] AS [id_vendor],t.[id],t.[rev],t.[revdisp],t.[ver],t.[poprice] AS [price] FROM [tmptblqry_quotes] AS t) AS cb
ORDER BY [rev],[ver];

You can SELECT a value without a source when used otherwise.
I'd tend to go for the ADO recordset ordinarily - in a controlled environment anyway.
It's a leaner hit.

Cheers.

 

[LPurvis]

Oh... Examples of assigning recordsets to list controls... Yoinks. Several files do that in the examples page linked to in my sig.
Combo Filter is one example and more involved are the last two Generated and Shaped recordsets (which contain a lot more going on that just the assignment to a control.)

 

[mdlueck]

Thank you, LPurvis! Indeed with faking the [MSysObjects] table it is working much better. Neat trick!

One new weird observation I see all of a sudden with this updated query... the Ver column ended up with an odd square box character aka "[]" looking... so I copied that character and deoded it in the Immediate window: (I have needed to represent the character with square brackets.)

? Val("[]
")
 0

Yet the FE Temp Table has a "1" (one) as the Ver value. I have no idea why this fancier / updated query would suddenly be mess'n with my data. hhhhmmmm....

Query is as follows:

SELECT [id_vendor] AS [ID / Vendor:],[id],[rev],[revdisp] AS [Rev:],[ver] AS [Ver:],[price] AS [Price:]
FROM (SELECT TOP 1 "Production Part - Refers to JDE" AS [id_vendor],-1 AS [id],-1 AS[rev],NULL AS [revdisp],NULL AS [ver],NULL AS [price] FROM [MSysObjects]
UNION
SELECT t.[id] & ' / ' & t.[vendortitle] AS [id_vendor],t.[id],t.[rev],t.[revdisp],t.[ver],t.[poprice] AS [price] FROM [tmptblqry_quotes] AS t) AS cb
ORDER BY [rev],[ver];

Any ideas why between the FE temp table being correct the Ver value would suddenly be getting scrambled? Oh, I suppose I should try my decomplile / compact / compile quick a sec to see if that clears up the final nonsense. BRB...