By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,046 Members | 2,105 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,046 IT Pros & Developers. It's quick & easy.

Adding data in combo boxes

P: 10
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!
Sep 5 '06 #1
Share this Question
Share on Google+
4 Replies


100+
P: 179
There is a property for the combo box called "Limit to List". Set it to "No".

comteck
Sep 5 '06 #2

PEB
Expert 100+
P: 1,418
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 :)
Sep 6 '06 #3

P: 10
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?
Sep 6 '06 #4

PEB
Expert 100+
P: 1,418
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
Sep 7 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.