Hi I have a data base with 30 fields and over 2000 entries. What I would like to do is create a drop down box that has one of each entry in the current field.

EG... Field name could be car manufacturer. Then in the field would be Ford, GM, Honda etc. When adding a new entry I want to use a drop down box with all the possipilities I have in the field.

I have used an append query to write distinct values to another table and then I use SELECT DISTINCT in the drop down combo box. The problem with this is that if I add a new car manufacturer say BMW. I have to run the Append quary before it will show up in my drop down box.

Is there a better way of doing this ?

Thanks for your help.

Wayne

You are saying your combo is built off of a table? Where are you allowing the new "BMW" to be entered?

If you have to run an append query, you can do it behind the scenes automatically with Code in the event it is "added".(whereever that may be)

Well, I gave it some thought and what I am thinking is that you are adding the new "BMW" in a table. Though the field may use another table for lookup, you are able to add a different value here.

To make the lookup current. You need to add the "BMW" in the lookup table. I guess you are doing that manually using an append query.

What you SHOULD do...

Well, do your data entry on a form. You can build it in Tabular View to look like a table.

Put a combo on the Form Header that selects the Manufactur. Use Option 3. It will find all records you have that have your choice. You should set the control to Limit To List: Yes also.
You WILL NOT be able to add any new "BMW" here if it is not in your table the combo is based off of. You can have another button that opens that table (or a tiny form) to add the "BMW" to that table, while still in your data entry form. Once that form is closed you will need to refresh your combo. Now if you goto a new record and select your drop down, you will have "BMW" in it. No need to append query to find unique values, which you have to initiate when you need them. Edit your data as you go!

Hope that makes some sense. It's pretty late and I'm dead tired, but working on a problem of my own!



[This message has been edited by jwindon (edited 08-31-2001).]

hi jwindon,

Thanks for all your work,

I am currently working through the Osborne complete reference on Access 2000. (Great book at some +1300 pages) I have not got to the Forms area but I am a little suprised that you can not do this in an atomatic way. I did try to great a query pulling unique values from the table i was working with and then point the combo box to the query but Access does not let you do this either....

The way I have currently set my DB up is for ALL drop down lists I made a small table all begining with ZZZ. Eg "ZZZ car manufactures".

Then I have created a form call update drop down menus. Here it just runs an append SELECT DISTINCT values from my main Table INTO my table "ZZZ car manufactures".

Doing this way means the end user can still enter what he likes. The only down side about this is that when I append the records it doubles up entries. I have got around this by selecting DISTINCT in the table combo box but i would like a way to append just the data that is not in the table. I.E. copy over data that is already there or not append it. I tried to use create table for this but as it is in a relationship because of the combo box it is a hassel.

Any ideas would be greatful and thanks for your time so far.

Waynef

I was able to create a combo using the query. During the wizard you have to tell it where to get your values. There are three choices on the second screen - tables, queries or both.

If you like using the append option, I could offer this. Before running the append query, run a DELETE query to clear the records in your distinct table. That will stop your "duplicates".

I think you are getting closer. I'm glad you have a book. You will find yourself addicted to Access very soon! And when you start finding out how Access can work with other applications, you'll really fall in love!

hi again,

I just wanted to clarify something...

You wrote - I was able to create a combo using the query. During the wizard you have to tell it where to get your values. There are three choices on the second screen - tables, queries or both.

I have no problem creating the query for the SELECT DISTINCT values in my table. I.E. the query returns "BMW" etc... but then when you are specifing the source for your combo box you can not point to the query. Access will not allow this and comes back with an error. pitty because this would be a great function to have.

Cheers,

Wayne
that creating

Sorry. Trying to help, but I really don't get it. It's tough when you don't see the db in front of you. There ARE others on the forum with this "gift" of vision. Perhaps they can help.

The combo SHOULD allow you to choose a query for its recordsource.

[This message has been edited by jwindon (edited 09-01-2001).]

Hi jwindon

Don't be sorry mate, I appreciate all the help I can get and other stuff you have told me in these posts may not have help me solve the current problem BUT i have learnt other interesting things so no harm done.

You are right to say that the combo SHOULD allow you to choose a query for its recordsource but i think the problem is because the query is using the same table and same field as the field i am filling in using the drop down box.

Anyway i just wanted to say thanks for all your help.

Cheers,

Waynef

Two things if you already have the car manufacturer listed in one table why do you need to store it in another?
To use the query as the row source for your comb simply switch the query to SQL view, copy the entire statement, paste it in to the row source for your combo, delete the query if necc.
HTH