Some error occured when copying data to a new table

[Avvy65]

I have managed to get a filter query to work with help from Tutorial odb’s etc. I want to copy all the data from tblRecInfo to tbl_filter_RecInfo, and when I select ‘copy’, then ‘paste’, on tblRecInfo, I select Data and Defintion , then change the PK ID from integer to a Yes/No Boolean, then ‘Create’ but only the first 2 records are copied.
I get an error message box up saying :
Violation of unique constraint SYS_PK_102: duplicate value(s) for column(s) “ID1” in statement [INSERT INTO “tbl_Filter_RecInfo” ( “ID1”,“BuyDate”,“Item”,“Quantity”,“SupplierName”,“Cost”) VALUES ( ?,?,?,?,?,?)]

In the actual tutorials, only the Definition is copied, and data was added afterwards. I would rather not do it that way if it were possible. Would it mean more work or using a macro to achieve this.

If no records were copied I could understand it better, as it would prompt me to look or find out why.
I know it says ‘duplicate value(s)’, but I don’t know where to look for that. Unless it means that the duplicates are Yes and No???

[Ratslinger]

I don’t understand the purpose of the table copy or why you want to change the record id type. It may also help if you explain which tutorial you using - what document is it in?

[Avvy65]

I got the tuturiol from here: http://www.instructables.com/id/LiberOffice-Base-Tutorial-Making-a-filter-inside-a/
There were a couple of others as well which I used, and think I got one from this site.

[Ratslinger]

Basically you are trying to change a primary key to allow values of only 0 or 1 - that is what boolean is. That means the max number of records allowed in the table would be 2!

Edit:

@Avvy65 You have not clearly understood the filtering process. The filter table is meant to have only one record and that for temporary storage. The search fields on the form fill the appropriate field in the filter. When the button is pushed, the subform is refreshed with the information created by the query.

The query uses the temporary information int the filter table to find selected information from the regular table to be displayed in the subform.

That is why the sample uses a boolean value for the key - it only needs one record.

If this answers your question please click on the (upper left area of answer).

[Avvy65]

Ok so how does one get all 145 records into a table that only allows 2. In one of the said tutorials there were 5 records. I’ll post up if you like.

[Ratslinger]

You still don’t understand. With that method you can display thousands of records. The filter table only needs ONE record. It holds the information from the text boxes then uses it in the query. The sample from the tutorial actually demonstrates this. The query returns the records selected to the subform directly, not to the filter table.

[Ratslinger]

Another point. As you mentioned, the tutorial only copied the definition. This was a quick method in creating the filter table. In all actuality, no data was added later. It is just a holding area for the search data. The filter table never has any more that one record. Form puts search data in first & only record of Filter table. SubForm displays data based upon query results. Query generates results by using data in Filter record to select records from your table which match criteria.

[Ratslinger]

Here is something else which may help you get this. Use the sample from the site you provided. Open the form. Enter an s in the first name text box. Click search button. Four records are displayed in the table grid. Now, open the filter table. All you will see is s in the first name field of the one and only record in the table.

[Avvy65]

Yes and yes, that is how it works. I have got it now, as it is how you say and the subform holds all the records. Well it does on an example, so now I have to get a subform added to a search form. Does that sound right.

[Ratslinger]

@Avvy65 Yet another hurdle in the SQL battle (did you get the book yet?). Not to mis-lead you, but the query actually creates a result set which holds ALL the selected records and the subform retrieves what is needed to fill the control (display). Just answered a similar subform question - see my answer here. This may give you additional insight & more samples.

[Avvy65]

Yes I did get the book, sql for dummies, and have been looking through it page by page. I’ll get there one day!

[Ratslinger]

That makes me happy!

I don’t mind answering your questions, but you having down some of the basics makes getting the concepts across much easier.