Avoid "Null Error" when No value entered in and don't want to enter in value.

[enquiries]

Hi there,



I'm new at VBA coding and the programmer for this database has other priorities as we're a small group so I'm a bit stuck at the moment and would really appreciate any help, as it's a small error but the database is near completion. Basically, I have different sub-forms in tabs and what happens is, if I click on a drop-down menu in a new row or enter in a value and than backspace it because I don't want to enter in a value and then click somewhere else I get the "No Null Error" and then have to select a "pretend value" and then delete that in order to move on. So I know the solution would be to have VBA code to:
* check whether all the rows are blank except for the applicantID and the other IDs and then ignore the null error"
* otherwise if there's another field that is not blank (i.e. I'm actually forgetting to put a value in one of the fields not trying to backspace) then prompt the error.

Thanks for any help - much appreciated
Enquiries

[June7]

Why does it generate "No Null Error"? Something somewhere is performing some validation and requiring entry. But deleting the "pretend value" should still result in a null in the field. I have no problem backspacing to remove an input unless I have some validation on the control that enforces an input.

[enquiries]

Well, one of the forms is using a combo box (3 columns) and I have VBA code that uses that to populate 2 other fields......combo box is qualification ID and it has a list of qualifications and their programmes .........e.g. Diploma Health Sciences or Bachelor Science and then that will populate the Qualification field: Diploma/Bachelor and Programme field: Health Sciences/Science. What happens if I select the combo box and then backspace a value that i selected and want to go somewhere else it prompts with "You tried to assign the Null value to a variable that is not a Variant data type" and then I have to select a pretend value and then delete that to get past the error.

2nd Problem is I have a drop down list and if I accidentally click on the drop down and don't select a value then it will prompt "The Microsoft Office Access database engine cannot find a record in the table "Nominated Public Body" with Key matching field(s) PublicBodyID"

Sorry was a bit confused when explaining it in the beginning and I've realized that there's 2 different problems.

Thanks for your help

[June7]

These problems are specific to your db structure and code. Since I am not familiar with either, can't offer suggestion for fix.

You will have to get familiar with the db structure and the code and debug. Modify as required. Refer to link at bottom of my post for debugging guidelines.

For the second problem, sounds like some event of the combobox is doing a search with value of the combobox but since nothing is actually selected the value is Null and code errors.

Is the programmer an employee? Who sets their priorities? What does the Boss say?

[enquiries]

Hey, thanks for your time & input anyways - yeah my work place dynamics are a bit complicated and I know I won't get anywhere with my boss lol (there's priorities, then politics and stunting). I think I can take it from here though and try debugging and then make the questions broader. Thanks again.

[enquiries]

For the second problem, I've figured that it's because I'm trying to set in the form which I've based on a query that takes the values from 2 tables - the value of an ID that is a primary key and foreign key (many side) of one table and that it depends on the value of the field I select which is stored in the other table (one side). So when I select a value and then back-space then it'll search for an empty value in the table for the ID and it'll return that error.
This isn't an unresolvable problem however and I can as I said before just select any value from the drop down menu and then delete it, and it'll go pass the error but it's inconvenient and I'm thinking that if a new user who isn't as familiar with access tries to use the database it might stump them.
What I think I need is maybe an "On Not in List" event that checks if the cell is blank and then just "undo" (not delete) the record.
Any suggestions please

[June7]

Why is the form based on a query with 2 tables? A form can edit/enter data to only one table.

Why is there a search if the box is empty?

Still don't know the structure and code to offer specific suggestions.

[enquiries]

The reason why the form is based on a query with 2 tables is because I need to have a form that can enter data relevant to those two tables (I need fields from both tables to keep the relationship) - it works with some code like having a combo box auto-populate two other fields and sometimes selecting both primary & foreign key ids to show in form so that the value can be set by the form.......it's a bit complicated because the form needs to be a "data-entry" form and I have multiple tables - I can't have a subform for each table as that will be a bit untidy and hard to follow. But I think this will work fine, will just have to explain to users what to do if prompted with error.