Adding data in combo boxes

[ChristianC]

I have a combo box that is pulling data out of table that I created with possible selections for the users of the database. I also have another table where the data is saved after the user has completed the form. My question is...I cannot restrict the users to the options I came up with in the table, so how do I set up the combo box so that users can either select from the existing list, or add an item and have it saved to the list for subsequent users?

I created an append query that does this, but I don't know how to make the query run automatically, every time a new item is added. I've seen some postings on the AddItem property, but I haven't seen how the code works.

Any help would be greatly appreciated!

[comteck]

There is a property for the combo box called "Limit to List". Set it to "No".

comteck

[PEB]

Hi,

If you want to Save every introduced value in the combo box in the after update property you have to assign a Macro that runs your Query!

For your Macro You can use RunsSQL Macro and copy your SQL from your query to your Macro!

So in this case you don't have to write a code and you will use only Macros and SQL :)

[ChristianC]

Well the SQL did what I wanted it to do, but the Append query is not the way to go. First of all, it adds duplicate entries to my table. I know there's a way to prevent this, but the major issue, is that I want the new option to appear in the combo box as well as in the table when it is saved, and the append query only saves the options to my table.

Is the AddItem the way to go? and if so how does the code work?

[PEB]

To escape from the repetitive add of items you have to change your Append query. I'll give an exemple how to do it!

1. You have to create a table for exemple Connection
2. In this table yu insert only one record! The information for this record doesn't matter! The columns of this table also doesn't matter!
3. Imagine that you save the New Values in a table COMMUNES in the filed COMMUNE and the new value is "ARBIGNY"
So your SQL should seems like:

INSERT INTO COMMUNES ( COMMUNE )
SELECT "ARBIGNY" AS Expr1
FROM [Connection]
WHERE ((("ARBIGNY") Not In (SELECT COMMUNE FROM COMMUNES)));

In this SQL you have to change "ARBIGNY" with your Form Control and change the table Names and fields

So you shouldn't have a dublicates in your table!

When you want that the new value appears in the combo box you should refresh or requery the combo box or the form!

So to do this you have to replace the macro in after update with event procedure

your procedure is:

DoCmd.RunMacro "Your macro name"
Me!ComboBox.Requery