Listbox runtime additions

[twychopen22]

I want to have a dropdown box in a form but allow additions to be made, but after they are made, instead of them not showing up in the box like usual, I want them to become a part of the list. Plz help! Thanks

 

[Matt Greatorex]

Set the listbox's Limit To List property to No, then, in the After Update event of the listbox, add

[Forms]![formname]![listbox name].Requery

 

[twychopen22]

[vb]Private Sub MetricName_AfterUpdate ()
[Forms]![EmployeeMetricRecordsForm]![MetricName].Requery
End Sub[/vb]

I have entered this into the form's code but it does not update it? the next record still has an empty list

 

[twychopen22]

Please Help!

 

[Matt Greatorex]

If you close and reopen the form after adding the new entry, is it visible then?

If not, is the Limit to List property set to False?

 

[twychopen22]

Do I need it to lookup a table first or something or can I have it unbound in my form (the form would be the first instance of the list entries)?

 

[Banana]

Instea d of doing a requery, re-set the rowsource. That is assuming of course, that when you add new values, it gets added to the table or query used for listbox's rowsource.

 

[twychopen22]

I have it look up a value in a table but there are no entries in the table. The limit to list is "no". After Update I have
[vb]Me.MetricName.Requery[/vb]

 

[twychopen22]

Private Sub MetricName_NotInList(NewData As String, Response As Integer)
'suppress the default error message.
Response = acDataErrContinue
'Prompt user to verify if they wsh to add a new value
If MsgBox("The Metric " & NewData & " is not in the list. Add it?", vbYesNo) = vbYes Then
'Set Response argument to indicate that data is being added.
'Open a recordset of the Metrics table.
Dim db As database
Dim rsMetricName As Recordset
Dim sqlMetricNames As String
Set db = CurrentDb()
sqlMetricNames = "Select * From MetricNames"
Set rstMetricName = db.OpenRecordset(sqlMetricNames, dbOpenDynaset)
'Add a new Metric with the value that is stored in the variable NewData
rst.MetricName.AddNew
rstMetricName![MetricName] = NewData
rstMetricName.Update
'Inform the combo box tht the desired item has been added to the list.
Response = acDataErrAdded
rstMetricName.Close 'Close the recordset
End If

End Sub

Here is the code I have so far but it is throwing a compile error at the
Dim db as database. The list it automatically brings up for me to chose from does not contain database...but only dataAccess page and others similar. Any idea what I am doing wrong?

 

[twychopen22]

Private Sub MetricName_NotInList(NewData As String, Response As Integer)
  On Error GoTo MetricName_NotInList_Err
  Dim intAnswer As Integer
  Dim strSQL As String
  intAnswer = MsgBox("The metric " & Chr(34) & NewData & Chr(34) & " is not currently listed." & vbCrLf & "Would you like to add it to the list now?", vbYesNo, "Metric Name")
  If intAnswer = vbYes Then
    strSQL = "INSERT INTO MetricNametbl([MetricName]) " & "VALUES ('" & NewData & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "The new metric name has been added to the list.: "
    Response = acDataErrAdded
  Else
    MsgBox "Please choose a job title from the list."
    Response = acDataErrContinue
  End If
MetricName_NotInList_Err:
  Exit Sub
MetrinName_NotInlist_Err:
  MsgBox Err.Description, vbCritical, "Error"
  Resume MetricName_NotInList_Exit
End Sub

I found code that works finally.
WWW.fontstuff.com/access/acctut20.htm