Combo Box Hell or Why can't I get this code to work?

[DawnTreader]

Hello All

I thought i posted this once already, but it seems to have ended up in
posting limbo so here it is again.

i have this combo box which i need to get to change a text field on a form.
it all works great except that i cant get it to do 2 things. here is the code:

Private Sub cboChangeRep_BeforeUpdate(Cancel As Integer)
If MsgBox("Are you sure?", vbYesNo, "Change Service Representative?") =
vbYes Then
Me.ISServiceRepID = Me.cboChangeRep
Else
MsgBox "Change Aborted", vbOKOnly, "Aborted"
Cancel = True
End If
End Sub

there are 2 things i would like it to do. first, when the user aborts i
would like it to clear the combo box. if the user doesnt abort i would like
it to clear the combo box after it updates the other field.

everytime i put

Me.cboChangeRep = ""

in my code at the appropriate spots i get an error message saying:

run-time error '-2147352567 (80020009)':
the macro or function set to the beforeupdate or validationrule property for
this field is preventing DATABASE from saving the data in the field.

anyone know what i have done wrong? should i move the code to another event?

 

[Douglas J. Steele]

Does it work if you set it to Null?

Me.cboChangeRep = Null

If not, try

Me.cboChangeRep.Undo

 

[Dirk Goldgar]

Hello All

I thought i posted this once already, but it seems to have ended up in
posting limbo so here it is again.

i have this combo box which i need to get to change a text field on a
form.
it all works great except that i cant get it to do 2 things. here is the
code:

Private Sub cboChangeRep_BeforeUpdate(Cancel As Integer)
If MsgBox("Are you sure?", vbYesNo, "Change Service Representative?") =
vbYes Then
Me.ISServiceRepID = Me.cboChangeRep
Else
MsgBox "Change Aborted", vbOKOnly, "Aborted"
Cancel = True
End If
End Sub

there are 2 things i would like it to do. first, when the user aborts i
would like it to clear the combo box. if the user doesnt abort i would
like
it to clear the combo box after it updates the other field.

everytime i put

Me.cboChangeRep = ""

in my code at the appropriate spots i get an error message saying:

run-time error '-2147352567 (80020009)':
the macro or function set to the beforeupdate or validationrule property
for
this field is preventing DATABASE from saving the data in the field.

anyone know what i have done wrong? should i move the code to another
event?

I answered your original post, so it's here somewhere. This is what I
wrote:

You can't change the value of the combo box -- as in your line,
'Me.cboChangeRep = ""' -- in the combo box's BeforeUpdate event, because
Access is already attempting to change the value to something else. If
you're going to use the BeforeUpdate event for this, you should either just
cancel the update -- thereby leaving the bad value in the combo, ready to be
amended by the user -- or else undo the change the combo box:

MsgBox "Change Aborted", vbOKOnly, "Aborted"
Cancel = True
Me.cboChangeRep.Undo

Or you could do your verification in the combo's AfterUpdate event instead.
In that event, you *can* change the value of the control.

 

[DawnTreader]

Just tidying up a little... here was my solution:

Private Sub cbCityID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cbCityID_NotInList
Dim addCitySQL As String

If MsgBox("Add New City?", vbYesNo, "Add New City?") = vbYes Then
addCitySQL = "INSERT INTO subtblCity(City, ServiceRepID) SELECT """
& NewData & """, " & Me.cbServiceRepID & ""
' MsgBox addCitySQL
CurrentDb.Execute addCitySQL, dbFailOnError
Response = acDataErrAdded
Else
MsgBox "The City entered is not in the Database. Please add it or
choose from list.", vbOKOnly, "Not In List"
Response = acDataErrContinue
End If

Exit_cbCityID_NotInList:
Exit Sub

Err_cbCityID_NotInList:
MsgBox Err.Number & " " & Err.DESCRIPTION
Resume Exit_cbCityID_NotInList
End Sub

dont even bother with a popup form any more, just insert it into the table.
works wonderfully.