OK, the following is not in any way meant to disparage you. A lot of this will be my "style" of programming.
The NZ() function is Null to Zero. It is a way to convert a null to a value. A control that is Null looks the same as a control with an empty string. If you want to test for a non Null value, most of the time there will be a test for Null and a test for an empty string.
Example:
Code:
If MyControl = "" Or IsNull(MyControl) Then ...
I convert any Nulls to an empty string, trim any spaces, then check the length. To me, that is the most fool proof method.
We had a numeric field bound to a control. The field was a required field, so if a value was not entered, other calculations would fail. We used
NZ(MyRequiredField,-1111) to convert the Null to negative quad 1 (-1111), since there would never/could never have that value. We knew which records need valid values by looking for -1111 values.
Naming objects:
You should never accept Microsoft's naming of an object, whether it is a form name or a button or .......
What does "Private Sub Command8_Click()" tell you about what the procedure is for/does?
How about "Private Sub btnCloseForm_Click()"?
Other suggestions for naming objects:
Object names should be letters and numbers.
NO spaces, punctuation or special characters (exception is the underscore) in object names
Do not begin an object name with a number.
Also:
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
See this about primary key fields:
Microsoft Access Tables: Primary Key Tips and Techniques
Autonumbers--What they are NOT and What They Are
You have spaces in field names and special characters ( dash, hash mark and quotes). These need to be fixed.
AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.
"ID" is a very poor name for a field. For a table, I would use "tblCGBoxStatusIn" for the table name and "CGBoxStatusInID_PK" for the PK field. Foreign key fields would have a "_FK" suffix.
Now for the code.
and I tried different things but when a box isn't in the table it will add it however it will still give me a message that the "Box is already In-Process"
It does that because that is what you told it to do.
In the code, "str_SQL" should be declared as a String, not a Variant: "Dim str_SQL As String".
Attached is a zip file with 2 dBs.
"CutterG Fixed" is your dB with modified code.
"BetterCutterG_Mod" is where I renamed the fields in the table and modified the code for the form "frm_CGBoxStatusIn".