Filtered data produces error on form

[Avvy65]

I have a weather database with almost 6000 records copied over from MS Access. I then copied all the tables into Libre Office Base tables, then created the queries. After all that I set the required filters in the relevant query to give me data from 2 dates.
I received help from here on how to do that, and I had it working as it should yesterday, but earlier today as I was entering data in the form I received the error message below

The filter that is in the qryWXdataLeeds is this:
BETWEEN {d ‘2017-04-01’ } AND {d ‘2017-04-30’ }

I now get an ID1 conflict. or more precisley:
Error inserting the new record.Violation of unique constraint SYS_PK_260 : duplicate values for columns “ID1” in statement [INSERT INTO “tblWXdataLeeds” all data entered into the form.

This seems like I can’t have filtered data in a query, unless there is more to it.

I don’t know why this has just happened when it was ok before, except that the actual record without the filter would be 5993 as an example but with filtered data the record number is 28. So as the date filter is filtering all data including the ID numbers, is there a way of not selecting the ID number, so it would be the same number in the filtered results.

[Ratslinger]

Using the data provided in your last question (Date parameters problem), the largest ‘ID1’ value (this is the key field not ‘ID’) is 5899, and using that field to insert a record with a value of 5900 I have no problem. The error message you display also shows this as being the key field with the problem. If you use the correct field you should have no problem.

It seems to me that once upon a time this WAS a dBase file as originally stated in previous question. However, it was probably copied to a Base .odb and when that happened, it didn’t have the necessary key info and the system asked if a key should be created. Answering YES would then create field ID1 which is now the actual key field and not field ID. It is now no longer a dBase table but rather an embedded HSQLDB table.

This is probably why you are having a problem now with new entries.

[Avvy65]

After more investigating etc why this problem occured, I synchronised the ID1 and ID numbers in the table as the ID was upto 14 numbers higher than ID1. This amazingly seemed to solved the error as when again entered new data and saved it there was no error message, and the filtered dates are shown on the form after new data was added.

Actually I realised after I posted the initial message that the ID key could be disabled or not selected in the query, which is the case now.

[Ratslinger]

From what I can see, the ID field has no value within the table. Syncing had no effect on anything. In fact, I don’t see any need for that field in the table.