Insert multiple records into a table and get an runtime error 3167

[STMiami]

I spent a lot of time but still couldn't figure out the reason and solution. I appreciate any help.



TblStatutesFY only has records for 2017 fiscal year. I added an update button on a form in order to copy the 2017 records and create the records for 2018 in the tblStatutesFY. On the form, there is a ctrlFYSelector where I selected 2017 there before I click the btnUpdate. Below is the coding for the button. It always gives me the error message 3167 - record is deleted and it is not doing the intended job. The database is not corrupted.

Private Sub btnUpdate_Click()
IptBox = InputBox("Enter a fiscal year to update the statutes (e.g., 2018):")
IptMath = IptBox - Me.ctrlFYSelector
If Not IsNull(IptBox) Then
Call UpdateStatutes
End If
End Sub


Private Function UpdateStatutes()

Dim IptMath As String

'Copy the statutes into the specified fiscal year
strSQL = " INSERT INTO tblStatutesFY (StatuteID, FiscalYear, ComplianceRating, Probability, Actions) " _
& "SELECT StatuteID, FiscalYear + " & IptMath & ", ComplianceRating, Probability, Actions " _
& "FROM tblStatutesFY " _
& "WHERE tblStatutesFY.FiscalYear = " & ctrlFYSelector & ";"

DoCmd.RunSQL strSQL

[pbaldy]

This might help:

http://www.baldyweb.com/ImmediateWindow.htm

if StatuteID is an autonumber, it shouldn't be included.

[STMiami]

This might help:

http://www.baldyweb.com/ImmediateWindow.htm

if StatuteID is an autonumber, it shouldn't be included.

StatuteID is a text. Fiscal Year is a number. I still can't figure out the problem. If inserting only one record for a specified statuteID , the sql will work fine.

[pbaldy]

Have you tried the method posted when there would be multiple records?

[STMiami]

Yes. Multiple records will not work. That is what I want to figure out..

[aytee111]

Add a Debug.Print strSQL just before the run command. Then copy and paste the result to a new query window, get the query working correctly, then you can make the appropriate fix(es) to the syntax in the code.

[John_G]

Do you use Option Explicit in ALL your code modules? If not, you should, because when you use it a lot of errors will be caught when you compile your code.

You have Dim IptMath As String inside the sub UpdateStatutes. I don't know if you have declared it anywhere else, but that declaration, inside UpdateStatutes, means the sub will recognize ONLY that occurance - other occurances outside the sub will not be recognized. This concept is known as the scope of variables.

What this means is that when you construct your SQL, IptMath has no value, and you get invalid SQL syntax.

I think that MS Access is getting confused and giving you the wrong error message 3167 (it happens sometimes), since nothing you are doing has anything to do with deleted records.

You could try changing DoCmd.RunSQL strSQL to CurrentDB.Execute strSQL, dbfailonerror - you might get the correct error message that way.


So -

1 - Delete the line Dim IptMath As String
2 - Add Dim IptMath as Integer to sub btnUpdate_Click
3 - change Call UpdateStatutes to Call UpdateStatutes(IptMath)
4 - change Private Function UpdateStatutes() to Private Function UpdateStatutes(IptMath as integer)

to see how it goes.

One other thing: Does StatuteID have a unique index on it? If so, that might be the issue - you are attempting to add copies of records that duplicate values for a unique index.

[SierraJuliet]

If Fiscal Year is a number and IptMath is a string then that might be your problem (, FiscalYear + " & IptMath & ", ). Possibly change Fiscal Year to a string data type and/or add parenthesis , (FiscalYear + " & IptMath & "), ? Also remove the space before insert (strSQL = " INSERT INTO).

[SierraJuliet]

If your destination table contains a primary key, make sure you append unique, non-Null values to the primary key field or fields; if you do not, the Microsoft Access database engine will not append the records.

To find out which records will be appended before you run the append query, first execute and view the results of a select query that uses the same selection criteria.

Use an additional INSERT INTO statement with a VALUES clause for each additional record you want to create.

When individual columns are not designated, the SELECT table column names must match exactly those in the INSERT INTO table.  (, (FiscalYear + " & IptMath & ") As FiscalYear, )

https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

[SierraJuliet]

Also try

CurrentDb.Execute "INSERT INTO ....yata yata yata....."

CurrentDb.Close