design a combo box that automatically adds new alternatives? 1

[carolineJ]

I have designed a form for entering new projects into our project database. In one of the controlls the user should enter the name of the project manager for the new project. I have made this controll a combo box so that the user can choose a project manager from a drop list or just write the name of the project manager if it is not in the list.

However if a user writes in a new project manager, I want that project manager to be added to the drop down list, so that next time a user wants to register a new project with that project manager, he or she can choose that project manager from the list instead of having to type it in again.

This will also prevent spelling errors so that you get for example one project manager named Smith and one named Smiht when in fact it is the same project manager.

Can this be done in an easy way?

 

[UncleJack]

Sure, it's easy. Just write an append query that appends the current value of the combo box to the record source. The current value is the name just typed in and the record source is the table where the project managers are stored. You should make this automatic by placing the code in the "Not In List" event.

Uncle Jack

 

[carolineJ]

thank you Uncle jack but I'm not sure how to do this. I understand that I should right click, choose propoerties and then the not in list event. Then I guess I should choose the code builder.

However I'm not familiar to VBA programming in Access, and I have just done some basic VBA programming in Excel.

I guess i should start to Dim a variable called for example currentvalue and then set it equal to the value of the combo box...but then I'm lost.

Does anyone have a link to a good site where I can learn some basic VBA programming in Access?

 

[Mossoft]

Set the combo box property "LimitToList" to yes. In the 'On Not on List' event put the following code:

Private Sub Combo1_NotInList(NewData As String, Response As Integer)

If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
CurrentDb.Execute <and sql statement that inserts the value into the source table e.g.&quot;INSERT INTO Table1(field1) VALUES ('&quot; & NewData & &quot;')&quot;>

Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
Combo1.Undo
End If

End Sub

This code is lifted from the Acc2K Help file (search for 'NotInList Event')

HTH

M

 

[carolineJ]

Thank you Mossoft! Guess I can spend the weekend doing something else than learning VBA programming now