Solved Append Query for Inserting Records in the Same Table (1 Viewer)

[Pac-Man]

Hello,

I'm trying to use following query to insert records in the same table with different ClassID but it's giving error similar to "xx records fail due to Validation rule". Can someone please see where I'm doing wrong?

INSERT INTO tblStudentClass ( ClassID, StudentID )
SELECT [Forms]![frmPromoteToNewClass]![ClassID] AS expClassID, tblStudentClass.StudentID
FROM tblStudentClass
WHERE (((tblStudentClass.ClassID)=[Forms]![frmClasses]![ClassID]));

Thanking you in anticipation.
Abdullah

 

[theDBguy]

Is ClassID a primary key in the table?

 

[bastanu]

I'm trying to use following query to insert records in the same table with different ClassID

But in your SQL Where clause you return the same ClassID. If you want to add a new classid for the same student (the value of the [Forms]![frmPromoteToNewClass]![ClassID] control) you need to replace the where clause to WHERE (((tblStudentClass.StudentID)=[Forms]![frmClasses]![StudentID]));

Cheers,

 

[June7]

@bastanu, maybe not. Two different forms are referenced. Using StudentID as criteria restricts insert to single student, does not promote multiple students.

@abdullahkhan, exactly what values are pulled by the control references? If you want to provide db for analysis, follow instructions at bottom of my post.

 

[arnelgp]

your criteria does not make sense.
remove your criteria.
edit your table and delete the index on ClassID and StudentID.
create a Composite index (no duplicate) on ClassID + StudentID.

 

[Pac-Man]

Thanks everybody for reply.

Is ClassID a primary key in the table?

@theDBguy, No ClassID is not PK of the table.

But in your SQL Where clause you return the same ClassID. If you want to add a new classid for the same student (the value of the [Forms]![frmPromoteToNewClass]![ClassID] control) you need to replace the where clause to WHERE (((tblStudentClass.StudentID)=[Forms]![frmClasses]![StudentID]));

Cheers,

I cannot add studentID into where claus because sole purpose of this query is to promote students into new class no matter who the student is (provided he passed the exam) in a specific class.

exactly what values are pulled by the control references? If you want to provide db for analysis, follow instructions at bottom of my post.

Values are pulled correctly when I view the query in datasheet view before Inserting but Inserting not done. I'll provide the db if required later on because I'm away from PC right now.

This query is being used to promote students of a class (say ClassID = 1 which os equal to or provided by [Forms]![frmClasses]![ClassID]) into a mew class (say ClassID = 2 which is selected in a form [Forms]![frmPromoteToNewClass]![ClassID], the very form used to run the query). Only ClassID of the records being appended is being changed.

 

[Gasman]

Hello,

I'm trying to use following query to insert records in the same table with different ClassID but it's giving error similar to "xx records fail due to Validation rule". Can someone please see where I'm doing wrong?

INSERT INTO tblStudentClass ( ClassID, StudentID )
SELECT [Forms]![frmPromoteToNewClass]![ClassID] AS expClassID, tblStudentClass.StudentID
FROM tblStudentClass
WHERE (((tblStudentClass.ClassID)=[Forms]![frmClasses]![ClassID]));

Thanking you in anticipation.
Abdullah

Show the properties of those two fields in your destination table.?

 

[Pac-Man]

Show the properties of those two fields in your destination table.?

Fields are number but while seeing the properties, I noticed that PK field is a number too (not autonumber, I changed it to number to get rid of some other error few months back) and I guess it is being left blank while Inserting the records and and that's why insertion of records is not successful. I cannot return back to autonumber because I had reasons to convert to number. If that is the reason of failed execution of query then I might switch to recordset method instead of query.

 

[Gasman]

If that was me, I'd fix the autonumber problem?
Is that number set as unique?

 

[Pac-Man]

If that was me, I'd fix the autonumber problem?
Is that number set as unique?

Yes, it is PK indexed with no dups. I removed the autonumber and used another function to get unique next available number. It didn't give any issue (atleast not until today if source of failed query is this PK field).

 

[June7]

Certainly field set as required that is not given a value would cause failure. Function to generate next number would also not work with INSERT action. I expect every record would get same calculated PID resulting in duplicate PID - another validation rule error.

So either go back to autonumber or use looping recordset approach.

What issue would cause you to eliminate autonumber as PK?

 

[Pac-Man]

Function to generate next number would also not work with INSERT action. I expect every record would get same calculated PID resulting in duplicate PID - another validation rule error.

That is exactly what happened when I tried to replicate and fix the issue in another table. I'll use recordset method for this purpose.

What issue would cause you to eliminate autonumber as PK?

I am using function to undo subforms which copies the whole recordsource table on form load and when user click undo button, put back the copied table. Now if next available auto no is, say, 101 and data that had been copied is has id 10 then on undo autonumber is getting reset to 10. So when new record is added, it assigns 11 to id which is actually already present in the table (as my autonumber ID field has reached to 101 before undo) and record could not be created due to duplicate pk id.

 

[June7]

Right, would have to run Compact & Repair to reset autonumber seed so it will pick up from the maximum value.

 

[Pac-Man]

Right, would have to run Compact & Repair to reset autonumber seed so it will pick up from the maximum value.

That is right but how could a user run compact and repair of encrypted backend every time he undo a form.

 

[Gasman]

Can you not reset the seed yourself?
I have used the Alter Table to reset a table (to zero in my case) just so that it started with decent numbers after any testing.
Just the way I liked to do things.

Sub ResetTableNumber(pstrTable As String, pstrID As String)
Dim strCmd As String, strSQL As String
strSQL = "DELETE * FROM " & pstrTable
strCmd = "ALTER TABLE " & pstrTable & " ALTER COLUMN " & pstrID & " COUNTER(1,1)"
CurrentDb.Execute strSQL
CurrentDb.Execute strCmd
End Sub

NB, you do not want the DELETE SQL

 

[June7]

Trying ALTER TABLE. Just get 'invalid field data type' error. What are the Counter() arguments? If I want to set numbering to continue with ID 9, how would that be specified?

I never encountered a process like you described. Might want to rethink it.

 

[Gasman]

I believe they are start number and increment.
I obviously passed in the table and autonumber field names when I used it.

 

[June7]

I guessed that was what the arguments are for, just didn't know which was which.

I am running from immediate window with my table and field names. Just get error.

Everything I've read says this cannot be done if data already in table. Tried on empty table and still errors.

Are you executing on SQLServer tables?

 

[Gasman]

Ah OK, sorry, that will likely not work then.
I happened to delete all my data, as I wanted to start from scatch, but was not aware that was a requirement. It was sometime ago I created that as well. I have only ever used Access tables with Access as well.

Sorry for leading you in the wrong direction.

Edit. I just tried it on a table with data and it worked fine?, next record had the ID of the parameter passed. However it was a local table?
Tried with a linked table and get error 3611 Cannot execute DD statements on linked sources, which makes sense, you have to go to the BE to modify anything normally.

 

[Pac-Man]

@Gasman thanks for reply. I didn't try resetting the seed value. I use looping through recordset method now as suggested by @June7 to achieve the purpose. Thanks all for reply and suggestions.