Inadvertently creating null values in a combo box

[Webby]

Hi

Can someone help with the following? If the solution involves code can you please be explicit as my coding skills are not great!

Basically I have a form which creates events based on two other tables – locations and service provider, the table creates a service event.

The combo box which allows the user to select the location works fine until the following scenario occurs:

Where I search for a location – which I do by typing in the address into the combo box (this is a good way to search for the record I need). But if the address is not in the combo box (locations table) I then delete the text that I have input into the combo (as I am then going to have to go back to the locations table to create the record). Then when I try to move the cursor off of the combo box I am told that I cannot have a null value.

I can understand why this is happening : I have effectively created a null value due to typing in the combo box when searching, but I don’t know how to get round this. There is a help message box which tells me about creating a DIM variant but as I say, my VBA is not the best and I really need some guidance.

Any help would be greatly appreciated.

 

[CEH]

IS your "limit to List" property on the combo set to "YES"?

 

[Kempes]

My guess is that the combo box is bound, so when you are selecting your location, what you are effectively doing is creating a new record in the table.
I imagine that your table is set up to not allow nulls in this field.

As a quick workaround, you could add a new unbound combo that will not insert a new record. You can then check it exists and decide if you want to continue.

There are other ways of doing this too.

This at least should give you the reason for the message you receive.

 

[Webby]

workaround?

Hi Kempes,

i would really like to know more about how you propose this work around, can you please explain further?

Many Thanks

 

[Kempes]

If you just want a combo box to check if a location exists in your table before actually creating a new record for it, you can simply do this.

Insert a new combo box into the form header. Don't go through the wizard. (Make sure it in unbound. ie, there is no control source for this particular field. This way it is in no way linked with any field or record and can be manipulated without effecting data within your tables).

Go to the properties of this new combo box and click on the ... for row source. (under the data tab)
It will then open up what looks like a blank query (which will drive your combo box values).

Depending on what you want to see within the combo box, add the location table in, and select the column(s) you wish it to display. (I would sort it from here too).
If you choose to show more than 1 column, make sure your properties reflect this.

Close this down and test it out. You should now be able to insert null into this box at any stage without any issues.

From here you could get more intricate but having it submit the chosen value into a new record if it exists automatically, but best to start from here and see if you can get it to work first.