empty combo box

[markjkubicki]

A form has a combo box where the user can pick entries from a table; it also gives the opportunity to add to the control's row source table.


The code for the row source is:

SELECT tbeInstallationNotes_EOS.PrintNoteYN, tbeInstallationNotes_EOS.InstallationNote_ID, tbeInstallationNotes_EOS.InstallationNoteTitle  
FROM tbeInstallationNotes_EOS   
ORDER BY tbeInstallationNotes_EOS.[InstallationNote_ID]  
UNION Select FALSE, 999, "<   ADD A NEW NOTE TO THE END OF THE SCHEDULE   >"  
FROM [tbeInstallationNotes_EOS]
ORDER BY tbeInstallationNotes_EOS.[InstallationNoteTitle];

If the row source table is empty (and that is a real possibility), I want " < ADD A NEW NOTE TO THE END OF THE SCHEDULE > " to still be an option.
(selection this opens a form that allows the user to add a record to the that table and a record the references it the control source table; however, when the row source table is empty, the control does not function at all.

My work around is to check if a record exists (in the row source table), and if not, then a command button is visible in lieu of the combo box, which when clicked, opens the same form as "< ADD A NEW NOTE TO THE END OF THE SCHEDULE >" would....

This seems to be a clunky way of handling this; is there better away?

with thanks in advance,
m.

[Micron]

There is a method of adding data to a combo row source but it seems you'd want more control of the data entered, and I see that you want 3 pieces of data (you have 3 combo columns).
So, if you have a table that you can guarantee that will have at least one record, I think this would work

UNION Select DISTINCT FALSE, 999, "< ADD A NEW NOTE TO THE END OF THE SCHEDULE >"
FROM [tbeInstallationNotes_EOS]...

If you can't guarantee that, then there are other possibilities that I've never tried but aren't worth adding to the confusion if the above will work. The first method of adding data to a combo row source is the NotInList event, but it can result in a lot more code than what you appear to be using. I don't find your current approach clunky in the least. However, when I see lonnnnggg table and field names like yours, I might suggest you use aliases:

EOS.PrintNoteYN, EOS.fld2, EOS.fld3... FROM tbeInstallationNotes_EOS AS EOS...

EDIT 2: in the FROM portion above, that would be the other table that would have at least one record.

[ssanfu]

This seems to be a clunky way of handling this; is there better away?

@markjkubicki, You don't have to use the same table in the UNION query. Try this:

In a COPY of the dB,
1) create another table:

Table2 (catchy name... No??)

Field Name	Field Type	Comment
Msg_ID	NUMBER LONG	(PK)
PrintNoteYN	BOOLEAN	(or whatever your other table field type is)
InstallationNoteTitle	TEXT

Add 1 record

Msg_ID	PrintNoteYN	InstallationNoteTitle
0	False	< ADD A NEW NOTE TO THE END OF THE SCHEDULE >

(Note: the Msg_ID is the number zero)


2) Next, the union query would be

SELECT EOS.PrintNoteYN, EOS.InstallationNote_ID, EOS.InstallationNoteTitle  
FROM tbeInstallationNotes_EOS As EOS  
ORDER BY 2  
UNION SELECT T2.PrintNoteYN, T2.Msg_ID, T2.InstallationNoteTitle
FROM Table2 T2
ORDER BY 3;

(** Notice that the table name "tbeInstallationNotes_EOS" is aliased as "EOS".)


3) Delete all records from the table "tbeInstallationNotes_EOS".

4) Try selecting a option from the combo box.

In the after update event of the combo box, code would open the form to add new data. When the form closes, you can clear/reset the combo box to Null/Empty/Nothing or to the new data.



----------------------------
NOTE: This is what Micron was saying. I just gave a concrete example.......

[Micron]

You don't have to use the same table in the UNION query.

Umm, I wasn't
By

if you have a table that you can guarantee that will have at least one record

I meant any table. You could even use a system table for that matter.

[ssanfu]

Uhhh... @ Micron, that "have" comment was for the OP (...... corrected that slip up)

And I did say it was your idea.... kinda like a "Pinky and the Brain" thing (I am not the brain! )
https://www.youtube.com/watch?v=GBkT19uH2RQ
https://www.youtube.com/watch?v=n9aYrURLHh0

[Micron]

Sorry if I jumped to convulsions

[markjkubicki]

what
i had I know know is good
but this is what I was looking for
...perfect;
thnx !

[markjkubicki]

BTW

you do know that in a very good way, the two (ssanfu & Micron) of you crack me up!!
(and Pinky and the Brain is one of my go-to when your about to explode...)
=:-)

[Micron]

ssanfu is obviously much younger than me, based on that reference.
My kids wouldn't let me watch Pinky and the Brain, but I was allowed to watch PeeWee Herman.

[ssanfu]

ssanfu is obviously much younger than me, based on that reference.

Sadly, not so....... it was the only recent cartoon that I could think of (remember?)... otherwise it was Snagglepuss or Deputy Dawg.