Software

Clear an Access combo box for entering the next record

You can have Access clear the selection from a combo box in a data entry form so that the user starts fresh with each new record. See how to make this user-friendly tweak.
You've created a data entry form that uses a combo box, as shown in Figure A. However, when the user clicks the next record button, the previous record's combo box selection is still visible.

Figure A

To clear the entry for entering the next record, follow these steps:

  1. Open the form in Design view.
  2. Click the Form properties button at the top left of the form.
  3. In the Event tab, click the On Current property box and select Event Procedure (Figure B).

Figure B

  1. Click the Build button.
  2. Enter the following code at the prompt:

Private Sub Form_Current()

On Error Go to ErrorHandler

Me![cboname of box]= Null

ErrorHandlerExit

Exit Sub

ErrorHandler:

MsgBox "Error No: " & Err.Number &": Description: " & Err.Description

Resume ErrorHandlerExit

End Sub

  1. Press [Alt]+Q

Now when the user moves to the next record, the previous selection will not appear.


Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

7 comments
bronx72
bronx72

i usually put the Me![cboname of box]= Null at the end of the combo box afterupdate event. Sub Combobox_AfterUpdate() ' Find the record that matches the control. Me.RecordsetClone.FindFirst "[record id] = " & Me![Combobox] Me.Bookmark = Me.RecordsetClone.Bookmark Me![Combobox] = Null End Sub works like a charm, you can even do it if access makes an embedded macro, just add this action. have always wondered why they don't make that a default part of the code. must confuse a lot of people.

jwoolman
jwoolman

It worked for me but the information does not save. I can get the combo boxes to cascade and clear when I go to the next form but when I go back to the record the information in the boxes is empty. The rest of the data in the regular fields saves fine.

ccook13
ccook13

I tried this and find it flat out doesn't work.

bronx72
bronx72

oh, wait, i see, you're trying to do that with the same box that holds the name. i usually put a "find" combobox at the top of the form, then when it finds the desired record, the name populates automatically, of course, and the combobox goes blank again. i would rather users only use comboboxes on main fields in the report for data entry, and not ever expect it to take them to another record. if they sometimes do, they might think the record has changed, and start overwriting data, without realizing their mistake. pretty sure that has happened with one of my users...

DKL-M
DKL-M

I couldn't get the command to work at first either. It's missing the colon (:) at the end of two of the ErrorHandlerExit command. Add these and it should work.

ssharkins
ssharkins

I ran a quick test and it worked fine for me. Describe your problem and maybe we can work through it. Setting the combo to Null using the form's Current event should work just fine.

DKL-M
DKL-M

The command worked for me, but my problem is that when I use the next record button I created for my form, it won't take me to the beginnig of the next form as I am entering new data into the form. The blank form just appears without a cursor in any of my fields. Do you know how I can fix this?

Editor's Picks