Creating a new record

[access345]

I have an initial number which I must combine with a type of unit to create a new unique serial number. The problem comes when I try and enter a new serial number from a form that I have constructed for this purpose.
On the form I have the code type in a list box, based on another table. The next box is a text box which requires that you type in 4 numbers. That I have a button that you click that runs a macro in the background.The macro :
1.turns the warning messages off
2. Runs a query to combine the code name with the serial number.
3.Deletes any record which dont have a unique serial number
4. turns the warning messages on

The error I am getting is "The field cannot contain a null value because the required property for this field is set to true."

The unique serial number field is set to required and no duplicates. So I don't understand why my query is not being executed.

 

[JustineB]

I had a similar issue to this a couple of days ago. I tried everything and eventually re-booted my pc which fixed the issue.

Maybe try it before you get too bogged down? you never know!

 

[access345]

I tried to reboot and I still came up with the same problem

 

[JustineB]

Is the query working? Have you checked to see that the field that is the concatenation of the code name and serial number is populated?
Are the records deleting correctly too?

 

[access345]

No, the fields are not being updated or deleted. I almost think that the records are not getting into the table to begin with. If I disable the null function on the table. Than type in the record through the form. The Code name and the serial numbers do enter into the table. Now if I run the update query :

UPDATE TblUnitInformation2 SET TblUnitInformation2.UniqueSerialNumber = [CodeName] & [SerialNumber], TblUnitInformation2.ShippingStatus = "Test"
WHERE (((TblUnitInformation2.UniqueSerialNumber) Is Null) AND ((TblUnitInformation2.ShippingStatus) Is Null));

The query does update the table.
It seems that my problem is I can't do the update all in one step, which is what I need to do.

 

[Golom]

With that message

... The field cannot contain a null value because the required property for this field is set to true ...

It may be telling you that one of the fields

[CodeName] or
[SerialNumber]

is NULL and NULL propogation is causing the concatenated value to be NULL so UniqueSerialNumber can't be updated to that value.

The other thing that I'm wondering about is, if UniqueSerialNumber is set to required, it would seem that the condition

WHERE (((TblUnitInformation2.UniqueSerialNumber) Is Null)

should never return any records because the field can never be NULL.

 

[access345]

So I guess I have a logic problem with the query. When I have my form open and I click on the list box to designate which code I want. I than go to the next step which is to enter the serial number of the unit into the text box. I am assuming that this information is already put in the table. So the next step is to press the button which runs the macro that runs the query that updates the unique serial number field by combining the code name and the serial number. The reason why I have the statement WHERE (((TblUnitInformation2.UniqueSerialNumber) Is Null)
is because I only want to update the new records that were just added not the ones that already exist.

I took out the null statment and the query wants to update all my records. not just the new one that was entered. I have thought of using an append query but I have not been able to figure out how.

 

[Golom]

So ... reading between the lines ... if you have added a new record and, in that record, UniqueSerialNumber is NULL, then the message must not be referring to that field or you wouldn't have been able to add it.

It must be some other field that's blowing up on the "required" constraint.

Another possibility is that UniqueSerialNumber has a default value of an empty string so testing for NULL will not succeed (i.e. it isn't NULL). Instead try

UPDATE TblUnitInformation2 

SET UniqueSerialNumber = [CodeName] & [SerialNumber],
    ShippingStatus     = "Test"

WHERE (UniqueSerialNumber Is Null OR Len(Trim(UniqueSerialNumber))=0) 
  AND ShippingStatus Is Null

 

[access345]

I get no error messages anymore. But the records don't update unless I close the form and open the form up again. Than hit the button which executes the query.