Add to list

[ddrew]

I have a list box that is limited to list. I want to be able to add to the list via a message box. this is what I'm using at present, and it works OK. But this opens up a second form, 'ExerciseName' where the user has to press 'Enter'. What I want to do is cut that form out of the equation, but Im unsure what to change in my code. Any ideas anyone? Thanks!

Response = MsgBox(Me.Exercise_Name & "is not recognised in this database." & vbCrLf & vbClRf & "Would you like to add it?", vbQuestion + vbYesNo, "Unrecognised Sata")
If Response = vbYes Then
DoCmd.OpenForm "ExerciseName", acNormal, , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Me.Exercise_Name.Undo
Response = acDataErrContinue
End If

 

[cuttsy]

You could put an insert query in the IF statement

If Response = vbYes Then

        Dim sInsertExercise As String
        sInsertExercise = "INSERT INTO tblExercise(ExerciseName) VALUES(""" & Me.Exercise_Name & """);" 
        CurrentDb.Execute sInsertExercise
Else

You will need to requery the combo box. But the value in the combo box will probs cause problems as it will have a value that is not in the list untill you requery and I don't think it will let you requery while it contains that value.

 

[ddrew]

OK, based on that, it would appear that this won't work. Any other ideas?

 

[ansentry]

I have a list box that is limited to list

Do you mean Combo Box?

 

[ddrew]

Doh! Yes my appologies, I mean ComboBox not list.

 

[ansentry]

Have a look at the attached sample (Not my work).