Updating drop down menus

[spectraflame]

I have created a form with several drop down menus. The data in the drop down menus changes on a monthly basis. I created a Maint. Menu that will allow me to modify each drop down menu, but that really takes a great deal of time.

Is there a way to update the selections in the drop down list from my entry form? EX: When I get to a drop down list only has RED, GREEN, & BLUE as choices, I would like to be able to key in YELLOW and have that added as a choice next time.

I have tried the NOT IN LIST trigger, but I cannot seem to figure it out.

 

[Joe4]

Matthew,

I have had this occassion arise. The way I get around it is to add a button next to the drop down box that says "Add New Item". This button is linked to an entry form which is created from the table the list is created from.

Keep in mind that you will have to requery to get the drop down box to immediately reflect the new entry after you enter it. Do this by entering the following VB code to your form:

Sub Form_Current()
   Me![drop down box name].Requery
End Sub

Hope this helps.
This message was edited by jmiskey on 2002-12-30 10:35

 

[dmckinney]

Alternatively....

... you were on the right track with the notinlist event. Off the top of my head can't remember exactly how it works but you should find examples on the web without too much trouble. It will give you the flexibility of asking e.g. "Entry not in list. Would you like to add it?" etc.

 

[spectraflame]

Thanks for getting me back on the right track. I thought that the notinlist feature was one of the ways to go. I will do some looking around for some examples.

Thanks again,
Matthew

 

[dmckinney]

well since you asked nicely....

Create a table 'tblNames' with one field 'name'.

Create a form 'form1' with a combobox 'cboNames' which has a recordsource of SELECT [tblNames].[name] FROM tblNames;

Set the Limit to list property of the combo to Yes. Then in the limittolist event put the following code.

Option Compare Database
Option Explicit

Private Sub cboNames_NotInList(NewData As String, Response As Integer)
    ' You need a reference to ADO (ActiveX Data Objects) to run this code.
    
    ' You've set Limit To List = Yes which all things being equal
    ' will trigger an error message when you enter something which is not in the list
    ' By putting code here, you're getting the chance to step in before the error, and
    ' ask the user what (s)he's trying to achieve.
    '
    ' If the user says that they'd like to add it to the list then
    ' you've got to write the code to add it.
    ' Then you pass the 'Response' parameter value=acDataErrAdded
    ' which lets Access know to requery the combo etc..
    '
    ' If it was a genuine error then you pass one of the other constants (see below)
    ' The only difference between them is that one displays the original error message
    ' and the other one doesn't.
    ' DMCKinney.
    '
    ' The relevant constants.
    'acDataErrAdded
    'acDataErrContinue
    'acDataErrDisplay
    
    Dim rst As ADODB.Recordset
    
    Dim MyResponse As VbMsgBoxResult
    MyResponse = MsgBox(NewData & " is not in the list." & vbCrLf & "Do you want to add it?", vbYesNo, "Not in List")
    Select Case MyResponse
        Case vbYes
            Set rst = New ADODB.Recordset
            rst.Open "Select * from tblNames", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
            rst.AddNew
                rst.Fields("Name").Value = NewData
            rst.Update
            rst.Close
            
            Set rst = Nothing
            Response = acDataErrAdded
            
        Case vbNo
            Response = acDataErrContinue
    End Select

    
End Sub

 

[spectraflame]

DMC,

That is really cool! You have just made many of my drop downs so much easier to update.

Thank you again,

Matthew

 

[spectraflame]

DMC,

I modified your code to reflect the names of my combo boxes and tables. Now I am getting the following error when something is not in the list:

Compile Error: User defined type not defined

Then when I click OK, it highlights in yellow

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

and stops at

rst As ADODB.Recordset

I compared it to the way I set it up from your original example, and everything looks to be the same. Can you tell me some things that I may be missing??

Thanks,
Matthew

 

[dmckinney]

you need a reference to ado. From the VBA window...
Tools...References...Microsoft ActiveX Data Objects 2.x

 

[spectraflame]

DMC,

Thanks. I should have read your comments in the macro more closely.

Thanks again,
Matthew