Number of query values and destination fields are not the same.
The PENDING table that I'm selecting from has more fields than the destination table called FILE.
I've checked to make sure that all the fields in my select statement exist in the FILE table.
related to this problem I believe is the fact that both tables have a PK called FileID, which is an auto number.
The problem is that my FILE table will always have more records than the PENDING Table. And so I need a way to select a pending record (with a pk value of 15 for example...) and insert it into the FILE table which is already beyond 15 records.
I think my error is related to the fact that I didn't include FileID in my select statement. When I do include it, I get the following error:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship.
Try changing the FileID field in the File table to a regular Number field, then add a separate Autonumber field that has nothing to with PENDING and use that as the Primary Key. The FileID in the File table would then be a Foreign Key.
Thanks a bunch for the response. I use the same code for both tables...
Is there any other way?
Ultimtely, what happens is if the insertion to the FILE table is successful, i delete it from pending.
What about this....
When a user submits a new file request, thus writing a record to the pending table, I select * from FILE, calculate the next availalbe FILE ID number, and then create a record in PENDING using that value?....
that means, I'd take your suggestion but do it to the pending table instead of the file table, right?
Let me see if I understand correctly: you want to track whether a file request is pending or successful, right? Instead of using two tables, why not use one and have an extra Yes/No field called Pending that indicates whether the request is pending or not. Make it Yes the default value for the field so that when a new record is added, it will always be "Pending" until you say otherwise. That way you won't have to worry the FileID being the same on two separate tables, and you won't even have to deal with two tables. You can get all the info from the same source. When a request is successful, just change the Yes/No field "No" for that FileID. In that case, you would only need an update query that does this:
"UPDATE FILE SET Pending=No WHERE FileID=" & lngFileID & ";"
instead of a gigantic SQL string to move it from one table to another...it will already be there.
Actually, that thought just occurred to me. but I'm entering into the official testing phase for this release...
I don't want to make any big changes at this time... too risky.
I guess I'll have to incorporate that into the next release....
For now, I think I'll do the following...
Select record from Pending
Build a new SQL statement to insert into file (field1, field2) values (value1, value2). But I will exclude FILEID and have Access generate a number for me.
I guess if you're deleting the record from PENDING when its successful, then the FileID doesn't really matter anyway, so that would work. Or you can leave the Autonumber on the PENDING table and change FileID to a regular Number on FILE and append its value in your SQL statement. At least that way you can keep the same FileID.