combo box w/ NotInList

[benjammin]

Hello,

You all have been so helpful i'm just going to continue to post questions as searching seems to turn up things that are close but not exactly what I'm after...

I have a combo box that is for equipment models. In the combo boxI would like the first or last entry to be Add New Model...



Add new model option won't be an actual model or value pulled from my table like the others but added by code or something for that specific combo box. Essentially it would trigger a NotInList event in which a form to add a model pops up. I'm a little unsure of how to do this.

Thanks

[June7]

Use a Union query as the row source for the combobox:
SELECT "Add New Model" As fieldname FROM tablename
UNION SELECT fieldname FROM tablename;

There is no wizard or designer for UNION queries, must type them out, either right on the RowSource property line or in the SQL View of query designer.

[benjammin]

thanks, that makes sense but i want it to pop up the Add Model form when that Add New Model... option is selected, that's what i'm having problems with now.

[June7]

Show the code for procedure to open form(s) based on the combobox selection.

[benjammin]

I'm actually doing it a different way other than the SQL way but the SQL way makes more sense but I can't do a UNION of one field with a SELECT that has two fields (VendorID, VendorName)...

My rowsource for the combobox is

SELECT [tblVendor].[VendorID], [tblVendor].[VendorName] FROM tblVendor ORDER BY [VendorName];

My AfterUpdate() for VendorName is

If Me.[cboVendorName].Value = "13" Then
DoCmd.OpenForm "frmAddVendor"
Else
DoCmd.GoToControl "cboVendorName"
End If

Where Value 13 is VendorID

I don't like this because I have an actual record Add New Vendor... in the tblVendor, I don't want to have that record in there, I like the UNION better. So when you drop down to Add New Vendor... it pops up the frmAddVendor, when you save that it closes and you go back to the previous form and on that same combo box it has a GotFocus() which runs a requery showing your new vendor. I want to be able to use Add New ____... for more than just vendor, model, type, etc...

This might some convoluted but thanks for the help.

[June7]

Not following all that, will have to look at more later.

Meanwhile, can you work with this:

SELECT 0 as ID, "Add New Model" As fieldname FROM tablename
UNION SELECT ID, fieldname FROM tablename;

[benjammin]

That's exactly it! All works well, thank you.

Hah now i have another question directly related to this same issue. I have some cascading combo boxes that produces a SQL statement for the rowsource of another combo box, when I try to use the UNION it does not like it... Here's the currect AfterUpdate for combo box one to set combo box 2 rowsource

Private Sub cboEquipmentType_AfterUpdate()
Me.cboModelName.RowSource = "SELECT ModelID, ModelName FROM tblModel " & _
"WHERE tblModel.TypeID = '" & cboEquipmentType.Value & "' " & _
"ORDER BY tblModel.ModelName"
Me.cboModelName.Requery
End Sub

If I add the UNION code to that it doesn't like it

[June7]

This is a new twist to me and not tested but try:
SELECT 0 as ID, "Add New Model" As fieldname FROM tablename
UNION SELECT ModelID, ModelName FROM tblModel WHERE TypeID=cboEquipmentType;

Put this in the RowSource property of the combobox instead of in the VBA.

[benjammin]

Very nice thank you! All works great now!