Avatar of yoducati
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.
Microsoft Access

Avatar of undefined
Last Comment
yoducati

8/22/2022 - Mon
blaadje

me.cboOffice=DISABLED? or me.cboOffice!=CHECKED?
wiswalld

me.combobox = ""

ASKER
yoducati

I dont understand your comment???
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
blaadje

SelectedIndex = -1
wiswalld

me.cboOffice = ""

will clear the combobox
blaadje

i read the question wrong
 SelectedIndex = -1 should work i think
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
yoducati

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.
wiswalld

Sounds like maybe you should upload the database.
ASKER
yoducati

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
blaadje

can you attach the file to the post? or a part of the code?
ASKER
yoducati

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.  
wiswalld

Can you post the code?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
yoducati

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.  
Brendamage

just simple return the content of your database after clicking the close button to avoid the error to null.
Arji

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


Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Arji

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
Hamed Nasr

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.
Lambert Heenan

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
yoducati

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question