clear combo box value

[Guest]

I have a combo box I am using to delete items from a table. The code looks
like this:

If Response = vbOK Then
DoCmd.RunSQL "DELETE FROM ClassTbl WHERE Class = '" + Class.Value +
"';"
DoCmd.RunSQL "Delete from StudentTbl Where Class = '" + Class.Value
+ "';"
DoCmd.RunSQL "Delete from DataTbl Where Class = '" + Class.Value +
"';"
Class.Value = ""
Class.Requery
End If

the problem is that i am using class.value = "" to make the combox blank so
that the item that was just deleted doesn't still show up as the selected
item in the list. I have found that the requery alone will not change the
value if the item selected is not in the list. The major problem I am having
is that when the user closes the form with this combo box blank it creates a
new blank record, even though the class field is set to not accept nulls and
is even the primary key it doesn't even display an error message it just puts
a blank field right on into the table. Is there a way around this?

 

[John Vinson]

I have a combo box I am using to delete items from a table. The code looks
like this:

If Response = vbOK Then
DoCmd.RunSQL "DELETE FROM ClassTbl WHERE Class = '" + Class.Value +
"';"
DoCmd.RunSQL "Delete from StudentTbl Where Class = '" + Class.Value
+ "';"

This is a bit odd. Why does your StudentTbl have a Class field? Does
each student take one and only one class?

DoCmd.RunSQL "Delete from DataTbl Where Class = '" + Class.Value +
"';"
Class.Value = ""
Class.Requery
End If

the problem is that i am using class.value = "" to make the combox blank so
that the item that was just deleted doesn't still show up as the selected
item in the list.

Set it to NULL instead of to ""; and - for a combo box used in this
manner - make it an UNBOUND combo box. Its Control Source should be
empty; otherwise whatever the user selects (blank, or some other
class) will overwrite the value of its ControlSource field in the
form's recordsource table.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps