Populate Value List at runtime

[KraljevicMarko]

Hello, community.

I have a combo box that is supposed to build its own value list from values that the user enters at runtime. I used NotInList event to add the following code:

Response = acDataErrContinue
[myfield].RowSource = [myfield].RowSource & ";" & NewData
Response = acDataErrAdded

However, the additions aren't permanent; the value list is wiped clean each time I restart the database.

How do I fix it?

Many thanks!

Kraljevic Marko

 

[IfButOnly]

You will need to write the values to a table if you want them to be available next time you restart the database.

Set up the rowsource for the combo to the table.

Add something like this to your NotInList module, you might want to include some error handling -

Dim db As Database, rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblName", dbOpenDynaset)
rs.AddNew
rs!fldName = NewData
rs.Update

Response = acDataErrAdded

me.myfield.requery ' requery the combo box

Peter

 

[krunalprajapati]

create a table and add the value to it.
for a new value, write notinlist event and add the NewData to it.

Add the below code to ur NotInList event:
Private Sub Text0_NotInList(NewData As String, Response As Integer)
msgbox("give a msg for the data not in list.")
Response = acDataErrContinue
Dim con As Object
Dim c As Command
Dim s As String
Set con = Application.CurrentProject.Connection
Set c = CreateObject("ADODB.Command")
c.ActiveConnection = con
s = "insert into URTableName values ('" & NewData & "')"

c.CommandText = s
c.Execute
Response = acDataErrAdded
msgbox("give a msg for the data added in list.")End Sub