Combo Box with Blank Entry

[Cat129]

Hi All

I have created a form that searches through one of my tables, via a query, it then automatically creates a report from that query.

I want to add an option into the combo box's that is blank.

Currently if you wanted to choose by 'fault category' but accidently click on 'failure analysis', it will take the top value of the combo as the entry, you can't then blank 'failure analysis'.

Each combo box gets its information from an independent table.

The only think I figured out what adding the refresh button which just closes then reopens the form so all the fields go blank.

Thank You, Cat

 

[pr2-eugin]

Hello Cat129, How about code the 'Reset' button to clear all category? Something like..

Private Sub clearFiltBtn_Click()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then ctrl.Value = ""
    Next
End Sub

 

[Cat129]

Hello Cat129, How about code the 'Reset' button to clear all category? Something like..

Private Sub clearFiltBtn_Click()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then ctrl.Value = ""
    Next
End Sub

Hi, Sorry to sound silly, i'm not very good with VBA in access, only just grasp it in excel lol.

Will this clear just the field in question or all fields? Currently my refresh button clears all fields. I want to add in a blank entry.

I used * but this didn't pick up blank fields within a record, it only picked up fields within the record that had been completed.

The reson being not all fields that can be searched will have information in them.

 

[pr2-eugin]

The code I gave would reset all controls (i.e. Text and ComboBoxes) empty.. Is your trouble Refreshing the controls or something to do with search.. If it is the former then the code I gave should be suffice.. If it is the latter.. More information on how the Query is generated is needed..

Is the Query built in or are you generating it in VBA.. If you are using * as a search variable make sure you use LIKE operator not = (equals)

 

[Cat129]

The query runs by looking at every field within the record and determining its results by using

Like "*" & [Forms etc....] & "*" - This is used on all text fields,

When I use this on the combo box it will work, however you can't go back and clear the combo box without refreshing the whole form.

I created a new record on each table where the combo box gets its information with *, this half works, it will pick up every record with an entry, but it wont pick up the records with no entry in that specified field.

Every field on this search form is returned in the query as all the information asked for will be within 1 record on the main table.

Sorry if I'm over complicating things.

 

[RainLover]

Use

Me.ComboBoxName = ""

For each Combo that you wish to clear.

Which Event you use is up to you. You could add a command button, or use the Double Click on the combo.

 

[Cat129]

Thanks for all the info but I want to just add a blank option in the combo box, is this not possible?

This is going to be used by loads of different people so needs to be as simple as possible

 

[RainLover]

Thanks for all the info but I want to just add a blank option in the combo box, is this not possible?

This is going to be used by loads of different people so needs to be as simple as possible

The Code I gave you will clear the combo box and replace the previous data with a ZLS. Zero Length String.

So when you say it is not possible, this is not correct.

BTW Could you please check the definition of the words "Field" and "Record". Records are found within a Field. HTH.

 

[Cat129]

Thanks for the info, but this didn't do what I wanted, I have solved it now, thanks for all your help

 

[Mihail]

You say that your combo is based on a table.
Add one more record to that table where to have a null value in the field that is displayed on the combo.
If you don't wish to allow nulls then you can add something like "_Show All" in that field.
The underscore ensure you that always will be the first record in a AZ sort order.

 

[RainLover]

Thanks for the info, but this didn't do what I wanted, I have solved it now, thanks for all your help

I don't know what the problem is here. You asked for your Combo to show Blank.

I gave you the information that works, yet you say that is not what you were looking for and that you solved the problem.

For the curiosity of those who tried to help and other members could you please tell us what you did to solve the problem.

We all want to learn. That is why we joined the forum.

Thanking You.

 

[Cat129]

It was ones of thoese things that is so simple you dont think of it.

I had to add an entry in the tables that the combo box's got their information from that was blank. admittedly I did feel a bit silly when it worked

 

[RainLover]

Thanks for that.

And don't worry you will do a lot more silly things.

Thanks for posting your solution. A lot of people search this forum who have the same or similar problem.

The posting of your solution will help some one some day.

Cheers.

 

[thaonguyen]

Hello Cat129, How about code the 'Reset' button to clear all category? Something like..

Private Sub clearFiltBtn_Click()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then ctrl.Value = ""
    Next
End Sub

Hi Paul,

Glad I found this post. I used your code and it worked, but it does refresh all the combo boxes and text boxes but it also appears a 'Run time error 331' saying I need to save my record. I have a Save button to save my records and I implemented this code in the 'New record' button. I am not sure how to work around this. I hope you can help me. Thanks so much.

 

[pr2-eugin]

Hello thaonguyen, I am not completely sure how you have implemented this. What you could do is start a New thread, explaining what you have done and where/what exactly is the problem I (and many other valuable members) would be glad to look into that.

 

[thaonguyen]

Oops, mistake. I mean listbox. Ok, so I have a database that helps me input and search for lessons/books. In the data entry form, I have some textboxes, combo boxes and listboxes. I also have 2 command buttons, one is to save records (Command Save in macro) and one is to refresh record (command New). To tell the truth, I got the sample from Microsoft and changed it. So when I want to add a new record, I click the New button but only the text boxes and combo boxes are refreshed and go blank. The text boxes still have the previous records chosen. I want them to refresh as well. So I did some research and I tried using the Null VB but it didnt work. Below is how I wrote the code:

Private Sub cmdNewRecord_Click()
Me.Combo163 = Null
Me.Location = "" (This is a text box)
Me.Page = ""
Me.[Work title] = ""
Me.List128 = Null
Me.List130 = Null
Me.Vocabulary = ""
Me.Activities = ""
End Sub