append queryviolates validation rules but only 2nd time

[Happy YN]

A append query appends values the user has typed in textboxes into a table and works fine- the first time. when the user changes these values and attempts to append another record, the validation violation comes up. the table has no rules other than that the id key is indexed for no duplicates but that is created automatically for each record added anyway
What could this be
thanks!
Happy Yn

 

[Graham T]

If the append query is appending the same records again (i.e. with the same Primary Key) but slight alterations to other fields, then you will receive the error as you are violating the no duplicates rule.

HTH

graham

 

[Happy YN]

thanks but surely each time I run the append query or sql statement, a new record is created with a new primary key?
Thanks
Happy YN

 

[Graham T]

If you are running the append query to append all records from Table 1 (EmployeeID (Primary Key), EmployeeFName, EmployeeLName) into a new Table 2 (with exactly the same structure) then this will run fine the first time as it will append all records.

If you then change a record in table 1 (i.e. change an employees last name in record number 2) then attempt to append this data to Table 2 once again, you will, if you include all fields (EmployeeID (Primary Key), EmployeeFName, EmployeeLName) get the violation error.

This is because Record Number 2 (including the Primary Key) is trying to write to the table, but also trying to write the EmployeeID number again which will be a duplicate.

I hope this helps and does not confuse too much!

Graham

 

[Happy YN]

What you are saying makes absolute sense BUt as i said i am not trying to append from a table I am appending from textboxes which are uaser filled and the appended to the table. This automatically creates a new record and this is automatically numbered so why is this a violation?
Here is the SQL
INSERT INTO Parents ( Surname, Telephone, Title, Initial, road, Town, Postcode, Fax, Mobile )
SELECT [Forms]![Parents]![txtParentSurname] AS Expr2, [Forms]![Parents]![txtParentTelephone] AS Expr3, [Forms]![Parents]![comboParentsTitle] AS Expr4, [Forms]![Parents]![txtParentInitial] AS Expr5, [Forms]![Parents]![txtParentRoad] AS Expr1, [Forms]![Parents]![txtparentTown] AS Expr6, [Forms]![Parents]![txtParentPostcode] AS Expr7, [Forms]![Parents]![txtParentfax] AS Expr8, [Forms]![Parents]![txtParentMobile] AS Expr9;

thanks
Happy YN

 

[Graham T]

Happy YN

I have atempted to re-create what you are doing and created my Parents table and form and used the SQL that you have provided.

Unfortunately, I am not getting the same error message and can append all new records to the table.

Have you included the Primary Key in your SQL statement or have you just got a separate PK in the Parents table (i.e. Autonumber ID)?

Is your form bound to the table or using unbound controls?

Does the User scroll through records and make the changes or is the form cleared after each use?

Graham

 

[Happy YN]

Wow you work fast!!
No this form is not bound to the table at all just empty text boxes
The Pk is not included in sql since it should and is automatically created. The user cannot scroll thru records at all. I tried clearing the form but this does not seem to help. Perhaps my method in clearing it is wrong? I coded each textbox=""
Thanx for your support and interest
happy Yn

 

[Graham T]

Happy YN

I have added the following code to the command button which runs the query (using your SQL) and clears all of the text boxes on the form.

There is an autonumber PK in the table, which updates with each append:

Private Sub Command21_Click()

'Run the append query
    DoCmd.OpenQuery "Query2"

'Clear all text boxes on the form
    Me.txtParentSurname = ""
    Me.txtParentTelephone = ""
    Me.txtParentTitle = ""
    Me.txtParentInitial = ""
    Me.txtParentRoad = ""
    Me.txtParentTown = ""
    Me.txtParentpostcode = ""
    Me.txtParentfax = ""
    Me.txtParentmobile = ""
   
End Sub

Not sure why you are having the errors if this does not work. If you wish you can send the database over if possible and I'll investigate.

Graham

 

[Happy YN]

Thanks for your continued interest. That code is exactly what I had to clear the boxes and the PK is an autonumber. So I am back to square one why does it not work?tried compacting to no avail. It seems that the record is not moving up by one number each time i append/ is there anyway i can do this/ Can I set the PK to increment by one in the code/ but why should I have to - it was never necessary in all other append queries till now! Or is there a difference because I am not appending any records from any tables?
The db is far to big to send over. maybe i will try using recordset and update method

 

[Graham T]

Happy YN

Sorry, but all of my ideas have ran out.

I was not appending any records from any tables, only new records added through an unbound form.

My PK was just an autonumber and this was not causing any problems.

Hopefully someone else may come to the rescue!

Graham

 

[Happy YN]

Thanks Graham for your time and support -will post any success here!
Happy Yn

 

[pcs]

happy,

a long shot...

could AllowZeroLength:No be causing the problem???


hth,
al

 

[Happy YN]

Yes! That was it!! Although how was graham to know that when I tried to add the second record I did not bother to fill out all the fields and since for some reason allow zero length was set to no for most fields that violated the apeend query.
Still don't know why it let me add the first record though even if it had a few blank fields?
Thanks very much again
Happy Yn

 

[Jon K]

Happy,

The blank fields for the first record use Access's default, which is Null, not a zero-length string.

I think you should keep the default AllowZeroLength:No and clear the text boxes and combo box with Null instead of "", otherwise you will not be able to use Null or IsNull() in your queries.

Jon

 

[pcs]

happy,

i agree with jon k's explanation and advice...

and yes, graham couldn't have known that you didn't fill out all the fields.

i sussed it from 2 fields that are probably optional in your table:

Me.txtParentfax = ""
Me.txtParentmobile = ""

all's well that ends well

al

 

[Happy YN]

Once again this prove that this forum is really the best!!!
So especially in this case I can say "al"s well that ends well!!
Happy Yn