yoducati
asked on
How do I clear the value of a combobox?
I have a data entry form with a tabbed form control for tracking mail our office processes. The first tab is for sending new mail(data entry), second tab is for viewing past mail, third is for printing labels etc. Here's the problem. On my tab for sending mail I have two comboboxes, cboOffice and cboRecipient. From these two comboboxes the user selects which of our offices is to receive the mail, and then the individual. These two combo boxes are part of an option group that from which the user can choose OptionA (select one of our offices) or optionB (manually type an address). I have code in VBA that checks to see if the user has selected optionA and if so it checks to make sure the user has selected both an office and recipient before it will let them continue. The problem is that even though the combo boxes dont have anything in them the database still thinks something is selected and therefore skips over the code checking to see if they are blank. I ve tried adding me.cboOffice=Null and me.cboRecipient=null in several different events and have eliminated the problem of the database thinking the combo boxes have something in them. The problem now is when I try to close the form I get the error "index or primary key cannot be null". neither of the combo boxes are the primary key and I havent entered anything in the form at this point. I just open and close it and get the error. If I remove the me.cboOffice=Null and Me.cboRecipient = null the "index or primary key cannot be null" message goes away but then the database goes back to thinking the combo boxes have a value selected when they do not and therefore skips over the code I have checking to see if they are blank.
blaadje
me.cboOffice=DISABLED? or me.cboOffice!=CHECKED?
me.combobox = ""
ASKER
I dont understand your comment???
SelectedIndex = -1
me.cboOffice = ""
will clear the combobox
will clear the combobox
i read the question wrong
SelectedIndex = -1 should work i think
SelectedIndex = -1 should work i think
ASKER
Ok im glad you have posted these responses so I know Im not crazy. Ive tried all of those and I think they all work. The problem is with any of those in place, I get the "index or primary key cannot be null" message when I try to close the form. So even though I can get the combo boxes to be clear I cant do it without getting the null error message. Also to clarify... when I open the form for data entry, and havent typed anything in the form, the combo boxes are empty but the database still thinks something is selected. So when I added the code you have both suggested above the database prompts the user to select the office and recipient as it should but I then get the other error message above. If I take out the code you suggested above the database no longer prompts the user to select the office and recipient because it thinks there is something selected, but I also dont get the other error message anymore either.
Sounds like maybe you should upload the database.
ASKER
wiswalld my "i dont understand" comment was actually directed towards blaadje's first post. I hadnt seen yours yet. Ive tried me.cboOffice="", null, etc. I tried the list count, and list index property. Like I said I can clear the value but then I get the other error message.
can you attach the file to the post? or a part of the code?
ASKER
Unfortunately its classified stuff so I cant upload it. I cant even get it off the secure server. Could it be something to do with closing the recordset and setting it =nothing at some point programatically. Sorry I cant just upload it for you. Believe me I wish I could.
Can you post the code?
ASKER
Id have to retype it. Basically right now the code with which Im haveing the problem is in the on click and on current events of the form. Ive narrowed it down to these two places because when I comment out the code that clears the comboboxes I dont get the error message but the checking for blanks doesnt work. When I put it back in vise versa. I have a "finished" button on the form that the user can press when they are done entering one item it checks to see if all the necessary fields have been filled in correctly. So in the on click event of that what I have is essentially
If me.frame21.value=1 then
if IsNull(me.cboOffice) or isnull(me.cboRecipient) then
msgBox("You must select both an office and recipient to continue.",vbokonly
end if
end if
This is the part that doesnt run because it thinks they arent null.
After that the code take the user to a new record which should theroretically blanks out all the fields and lets the user start to enter a new record. When I add any combination of me.cboOffice="" or me.cboOffice=Null or me.cboOffice.value=Null or pretty much any of the suggestions you made to the on current event of the form, then the "index or primary key cannot be null" appears as soon as I do anything on the form aside from entering a record. If I type inthe primary key and continue then the system checks for the blanks as it should.
If me.frame21.value=1 then
if IsNull(me.cboOffice) or isnull(me.cboRecipient) then
msgBox("You must select both an office and recipient to continue.",vbokonly
end if
end if
This is the part that doesnt run because it thinks they arent null.
After that the code take the user to a new record which should theroretically blanks out all the fields and lets the user start to enter a new record. When I add any combination of me.cboOffice="" or me.cboOffice=Null or me.cboOffice.value=Null or pretty much any of the suggestions you made to the on current event of the form, then the "index or primary key cannot be null" appears as soon as I do anything on the form aside from entering a record. If I type inthe primary key and continue then the system checks for the blanks as it should.
just simple return the content of your database after clicking the close button to avoid the error to null.
Since column(0) is an index/primary key then if you provide a value that is not one of the indexes/primay keys, it should blank the combo. Assuming none of the indexes are 0 then 0 should give you nothing. You could even use a very high number like 9999999 assuming none of the items has an index of that value.
me.cboOffice=0
me.cboOffice=0
An alternate is to reverse your logic:
if Not IsNull(me.cboOffice) or Not isnull(me.cboRecipient) then
'Do your processing here
else
msgBox("You must select both an office and recipient to continue.",vbokonly
end if
if Not IsNull(me.cboOffice) or Not isnull(me.cboRecipient) then
'Do your processing here
else
msgBox("You must select both an office and recipient to continue.",vbokonly
end if
Long thread, I searched for bound, with no results, so:
If comboboxes bound to table fields
If fields are Not Null
you cannot set comboboxes to null; database refuses with the indicated error message.
If comboboxes bound to table fields
If fields are Not Null
you cannot set comboboxes to null; database refuses with the indicated error message.
I was just about to chime in here, when I saw that the last comment from hnasr is probably on the money. But I'll add my two cents for emphasis.
The two combo boxes sound like they are bound to data fields, while in all likelihood they do not need to be. The should have a rowsource that selects the data for the combobox lists, but they should not be bound to any field, as that would alter the contents of the data that the containing from is bound to: which is probably not what you want to do, and is almost certainly the cause of the "index or primary key cannot be null" errors.
Just use the two unbound combos to select the data that the email generation code needs.
The two combo boxes sound like they are bound to data fields, while in all likelihood they do not need to be. The should have a rowsource that selects the data for the combobox lists, but they should not be bound to any field, as that would alter the contents of the data that the containing from is bound to: which is probably not what you want to do, and is almost certainly the cause of the "index or primary key cannot be null" errors.
Just use the two unbound combos to select the data that the email generation code needs.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just a thought...I've often copied code in a Select Case structure in another subroutine that had a combo box name that was very similar to the one I needed. In a case like this, no matter what you do, it will not affect the combo box you want to change. (Head slap)