New Item in the field

[Gaufres]

Hi

I have a field that contains some kinds of the student status, such as "Current", "Alumni" and "Expire". The table has already got a lot of records. How can I make a new item available to the field, for example, "Other", without entering a new status?

Like, "Student A" is "Current" but he is now "Other" status. When the user see his record, he/she needs to change the student status from "Current" to "Other". I don't want the user to enter "Other" manually. I want it to be available from a comb box at the first place. How can I add a new item into a field without entering a new student record?

Sorry, I am unsure that I explain enough ..

Thank you.
Gaufres

 

[WayneRyan]

Gaufres,

Use the Search Facility here and look for: "NotInList". Don't type the quotes.
You'll find some examples.

Wayne

 

[neileg]

I guess you are populating the combo from your existing data. I would always prefer to hold the list of valid values for the combo in a separate table.

I agree with Wayne that the NotInList approach would solve this particular problem, but I like to avoid users being able to create new classifications.

 

[Gaufres]

I am trying to use "NotInList" and debugger stuck in the middle of the code in the following. (The yellow shadow is over ".Update".)

Private Sub cmbProgramme_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Student")

If MsgBox(NewData & " is not in the selection provided." & vbCrLf & vbCrLf & "Would you like to add it?", vbQuestion + vbYesNo, "Unknown data") = vbYes Then
With rs
.AddNew
.Fields("programme") = StrConv(NewData, vbProperCase)
.Update
.Close
End With
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub


What does "LocalID As Long" mean? This info came up when I was re-typing ".Fields" line.

StrConv(String, Conversion As VbStrConv, [LocalID As Long])

Gaufres