Help on self-lookup combo box. 1

[Neowynds]

I have created a database in Access 2000 for filing security incident reports. My manager wants me to have some of the fields be drop down combo boxes, ones that any new data that is entered, if the new data is not on the list it will automatically be added to the drop down list. I have tried visual basic scripting (newbie) and SQL (newbie) and have not had any luck. Self-lookup combo boxes seem to be the way to go and I tried to build a query for this and it failed. I am a complete newbie to Access so please explain in detail and dont assume I know anything, what might be common knowledge to you might be totally new to me. The field I am most concerned with is the "Subject:" field. Subject is where Larceny, Fire Alarm, etc. would go. Please help, any advice would be appreciated.

Thanks in Advance,
Neowynds

 

[JeffAlton]

One way to accomplish this is to only use the subject field in your query. Now use the group by property. If you don't know how to do this, click on the view option on the toolbar and select "Totals". Now use this query as the row source for your drop down box. As new subjects come in that don't match what's already in your table they will display in the combo box.

 

[Neowynds]

Sorry, but I am a low-end newbie to database design. I am only using the subject field in my query. I pulled up the totals option and "Group By" drop down box did not contain property. The SQL of my query is:

SELECT DISTINCT Table1.[Subject:]
FROM Table1;

I assigned the query (titled Subject)to the row source in my table. When I go to enter data into my form the drop down box is available but if I try to type in anything other than data it already has it says "The text you entered isnt an item in the list"

 

[JeffAlton]

Okay lets see if we can get this done this way.

In design view of the subject query change to SQL view and paste the following statement:

SELECT Table1.subject
FROM Table1
GROUP BY Table1.subject;

Save and run the query. If it displays the results that you were expecting then

Create a new form and in the drop down box in the wizard select Table1 and design view. Now create a new combobox on your form leave the option for I want the combobox to look up the values in a table or query. Click Next. Click the center radio button Queries and select the subject query from the list, click next. Click the > button and click next. From here you can click finish, unless you need to store the values in the subject field for new records?

 

[JeffAlton]

I just reread your post, finish out the combobox wizard by going to the next page and selecting the store value in this field option and then selecting the subject field from the drop down list. To prevent the error you are getting you need to click once on the combobox in design view and on the property sheet click on the data tab and set the "limit to list property" to no

 

[JeffAlton]

One more thing I forgot to mention is if you want the new values to show in the dropdown immediately after entering a new record you need to past the following into the forms After Update Event (bring up the property sheet and make sure that in the upper left of the design view that their is a small square showing just under the form name header. click in the after update option and then click on the ... to bring up the code builder then past the following between "Private Sub Form_AfterUpdate()" and "End Sub":

On Error Resume Next
Combo1.Requery

click save and you should be done.

 

[Neowynds]

OK did everything as said. However after getting rid of old code and pasting:

SELECT Table1.subject
FROM Table1
GROUP BY Table1.subject;

into the query. It now asks me to "Enter Parameter Value"

What does this mean? Didnt get this box before. I put the After Update code you suggested in the form.

 

[Neowynds]

OK I got it to successfully add the data but I had to change the SQL to this:

SELECT [Table1].[Subject of Report:]
FROM Table1
GROUP BY [Table1].[Subject of Report:];

I had renamed the field "Subject of Report:"

It will now add data to the list but not until I save the form exit it and open it again. I put the code you said to use in the After Update under properties of the form.

If I can get it to do the instant update then I can move on past this problem to the rest of the database.

 

[JeffAlton]

The name of the combo box is the problem now. In design view of the form click once on the combo box. Look at the Other tab in the property box. Use the name in the top name field in the after update code for the form. I assumed their were no other comboboxes. For example where it says "combo1" change this to the name in the properties box.

 

[Neowynds]

Success!!! Thank you very much it now instantly updates and I dont think Ill ever have a problem remembering how to do this if the need arises again. Your patience was much appreciated.

 

[JeffAlton]

I am glad that you could understand my explanations. I have difficulty explaining things without visual references.