I have a main form that has a check

[DomFino]

I have a main form that has a check box. If the check box is checked, the on_click event is to determine if the current record ID exists in the Lost_LKU table. If the current record ID does not exist, then insert data into a Lost_LKU table. If the check box is unchecked, the on_click event is to delete data from the Lost_LKU table where the ID matches the ID on the current record.

PROBLEM

If I put a check in the box (add data to the Lost_LKU table) I receive an error. When I check the actual table the record is there.

Error Received:
Error No 3022 Description: The change you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

If I remove the check (delete data from the Lost_LKU table), I receive this error.

Error No 3129 Invalid SQL statement expected ‘Delete’, ‘Insert’, ‘Procedure’, ‘Select’, or ‘Update’.

Here is the code I am running in the on_click event of the check box. Any and all help for this newbee is appriciated.

Private Sub Check158_Click() 
On Error GoTo ErrorHandler 

Dim db As Database 
Dim strSQL As String 
Dim curr_rec As String 
Dim MyDate As String 

MyDate = Date 

'Sets the current record so that after DoCmd.Requery the same record appears 
curr_rec = Me.CurrentRecord 

If (IsNull(DLookup("[ID]", "Lost_LKU", "[ID] = " & Me!ID & ""))) Then 

' Code if ID is not found - Insert recor
Set db = CurrentDb 
 
DoCmd.RunSQL "INSERT INTO [Lost_LKU](ID, Last, Lost, DateAdded)" & _ 
"VALUES(" & Me.ID & ", '" & Me.LAST & "', -1, #" & Date & #);"

DoCmd.Requery 
DoCmd.GoToRecord , , acGoTo, curr_rec 
Exit Sub 
Else 

'Code if ID is found - Delete record 
DoCmd.RunSQL strSQL 
DoCmd.Requery 
DoCmd.GoToRecord , , acGoTo, curr_rec 
strSQL = ("DELETE * FROM Lost_LKU WHERE [ID] = " & Forms!MasterForm!ID & ";") 
End If 

Exit Sub 

ErrorHandlerExit: 
Exit Sub 

ErrorHandler: 
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description 
Resume ErrorHandlerExit 

End Sub

 

[vbajock]

Error 3129:
strSQL = ("DELETE * FROM Lost_LKU WHERE [ID] = " & Forms!MasterForm!ID & ";&quot would generate an error if Forms!MasterForm!ID is an alphnumeric value. If it is, then place single quotes in your SQL string:

strSQL = ("DELETE * FROM Lost_LKU WHERE [ID] = '" & Forms!MasterForm!ID & "';&quot


Error 3022:
DoCmd.RunSQL "INSERT INTO [Lost_LKU](ID, Last, Lost, DateAdded)" & _
"VALUES(" & Me.ID & ", '" & Me.LAST & "', -1, #" & Date & #);"
If this is entering data into a keyed table your going to get a key violation if you already have the same record in your table, which is what the error is trying to tell you.
You need to either get rid of the duplicate keyed record before you try the insert or modify your error handler like this if you want to ignore key violations:

ErrorHandler:

IF err=3022 then 'key viol ignore it
resume next
end if

MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

 

[DomFino]

vbajock,
Thanks for your reply. The ID is the primary key on the Master Table that joins to table Lost_LKU. In the Lost_LKU table the ID is a number. However, I did try your suggestion regarding the enclosed ' but it had no affect.

As for the error Error 3022 there are no duplicates in the Lost_LKU table. There is a one to one relationship between the Master table and the Lost_LKU table. The Master table ID being the primary key with no duplicates allowed.

This is very confusing to me. Any other ideas are certainly welcome.
Dom

 

[vbajock]

Error No 3129 Invalid SQL statement expected ‘Delete’, ‘Insert’, ‘Procedure’, ‘Select’, or ‘Update’.
When this error occurs, open the immediate window, and type

?strSql

Cut and paste the return value into a thread window and lets look at how VBA is looking at your variable.

On the other one, if ID is a number, then don't use quotes.
Is Lost_LKU a table or a query? If it is a table, do you have a formal relationship established with another table that has cascades enabled? Can you check the properties of the table and check to see that "Index Unique" was not accidently selected on a field that should allow duplicates?

 

[DomFino]

vbajock,

1) Sorry, but I do not know what you mean by open the immediate window. How do I do that?

2) Lost_LKU is a table. There is a one-to-one relationship between the Master table and the Lost_LKU table. ID is the key that joins the tables. Also there are no cascades on any of the tables in the database I checked properties of the Lost_LKU table and found all the fields to be correct. The ID field is Indexed No Duplicates. The last filed is indexed allow duplicates.

 

[jender624]

To address your "Error No 3129 Invalid SQL statement expected ‘Delete’, ‘Insert’, ‘Procedure’, ‘Select’, or ‘Update’" problem: It looks like you are setting your strSQL variable after you run the DoCmd.RunSQL statement. It should be set before. See below.

Change this:
DoCmd.RunSQL strSQL
DoCmd.Requery
DoCmd.GoToRecord , , acGoTo, curr_rec
strSQL = ("DELETE * FROM Lost_LKU WHERE [ID] = " & Forms!MasterForm!ID & ";&quot

To This:
strSQL = ("DELETE * FROM Lost_LKU WHERE [ID] = " & Forms!MasterForm!ID & ";&quot
DoCmd.RunSQL strSQL
DoCmd.Requery
DoCmd.GoToRecord , , acGoTo, curr_rec

jender624

 

[vbajock]

Lets do the 3129 error first. Put the word STOP in your error handler like so:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
STOP
Resume ErrorHandlerExit

Run your code. When the error mesage appears, click OK and then the code will break on the stop statement. The stop statement will be highlighted in yellow. From the same editor window, Click View, Immediate. Then type in

?strSQl (enter)

 

[DomFino]

I change the code to :
strSQL = ("DELETE * FROM Lost_LKU WHERE [ID] = " & Forms!MasterForm!ID & ";&quot
DoCmd.RunSQL strSQL
DoCmd.Requery
DoCmd.GoToRecord , , acGoTo, curr_rec

as jender624 suggested. No change in processing still received the same error.

I then did the ?strSQl (enter) and the response was:
?strSQl
DELETE * FROM Lost_LKU WHERE [ID] = 1005;

This looks fine to me.

 

[jender624]

Don't know if this will work, but try taking out the "*" in your sql statement, so it just says:

("DELETE FROM Lost_LKU WHERE [ID] = " & Forms!MasterForm!ID & ";&quot

Give that a try. (crosses fingers)

jender624

 

[vbajock]

DoCmd.RunSQL strSQL should be after the strSql statement. Your running an empty string, hence error 3129

 

[vbajock]

OOPS! Looks like you already caught that. Where is the table LOST_LKU located? It is an Access table, correct?

 

[jender624]

Kinda like I said? jk

jender624

 

[vbajock]

yeah- I just jumped up to his code to see what else it could be - his SQL statement is valid. He is either using the incorrect SQL syntax for an attached backend SQL server or the field [ID] is causing problems because ID might be reserved.

Try

DELETE * FROM Lost_LKU WHERE [Lost_LKU].[ID] = 1005

What version of access are you using?

 

[vbajock]

Also, try

db.execute strsql

instead of

DoCmd.RunSQL strSQL

 

[DomFino]

Everyone,
Thanks for all your help so far.

I am running Access 2000 and I have the stand alone database split into a FE and BE (linked tables)

I tried the following code:

strSQL = ("DELETE * FROM Lost_LKU WHERE [Lost_LKU].[ID]" & Forms!MasterForm!ID & ";")

But Access came back and said :This Recordset is not updatable I tried it with the "*" and without and received the same message when trying to check or uncheck the box.

Lastly, I tried the db.execute strsql rather than the DoCmd.RunSQL strSQL

No Luck

 

[vbajock]

Lost_LKU? What is it? Table? Query? The world awaits your answer.

 

[DaltonVB]

I recreated your situation as best I could with the information provided. I have had DoCmd.RunSQL barf on me as well. I decided to make a function to create a query and then delete the query after using it. Hope this helps


If you were using (

If (IsNull(DLookup("[ID]", "Lost_LKU", "[ID] = " & Me!ID & ""))) Then

) to determine weather to delete or add, why not have the code in a command button instead?

Private Sub Check1_AfterUpdate()
    Dim strSql As String
'If the check box is checked then
    If Check1 Then
    
        strSql = "INSERT INTO [Lost_LKU](ID, Last, Lost, DateAdded)" _
          & "VALUES(" & Me.ID & ", " _
                      & "'" & Me.LAST & "', " _
                      & "-1, #" _
                      & Date & "#);"
'If the check box is not checked
    Else
        strSql = "DELETE * " _
              & "  FROM Lost_LKU " _
              & " WHERE [ID] = " & Forms!MasterForm!ID & ";"
    End If
    f_TempQRY szSql
    
    
End Sub

'Creates a temporary query based on the sql passed to it,
'executes it, and then deletes it.
Private Function f_TempQRY(szSql)
    Dim db As Object
    
    Set db = CurrentDb()
    
    db.CreateQueryDef "__TEMP__", szSql
    Debug.Print szSql
    db.QueryDefs.Refresh
    db.QueryDefs("__TEMP__").Execute
    db.QueryDefs.Delete ("__TEMP__")
    db.QueryDefs.Refresh
    Set db = Nothing
    
End Function

 

[DomFino]

Hi all,

vbajock - You must have missed my post above where I wrote the following.

2) Lost_LKU is a table. There is a one-to-one relationship between the Master table and the Lost_LKU table. [ID] is the key that joins the tables. In addition, there are no cascades on any of the tables in the database I checked properties of the Lost_LKU table and found all the fields to be correct. The [ID] field is Indexed No Duplicates. The field [Last] is indexed allow duplicates.

Sorry for the confusion.

DaltonVB - I will give your solution a shot. I think I understand what you mean. Create a command button next to a check box, which simply displays whether the ID in table Lost_LKU exists. Instead of checking the box to cause the action to occur, press the command button next to the check box and the event will trigger. Like I said, I will give that a shot. I am out of options, ideas and most of my hair is on the floor.

 

[DomFino]

Success!!!!

I do not know why, but it works perfectly.

After reading DaltonVB’s post, I got to thinking. I took his idea of putting code on a command button. Not his revised code but the original code. What I did was eliminate the Check Box completely and replaced it with a text box to simply show the value in the Lost_LKU table. I put a Command Button on the form next to the text box. The Command Button handles all the code. The exact same code I had when I used the Check Box. Anyway, I also tried a Toggle Button in place of the Command Button and it too worked flawlessly. My only conclusion is the Check Box was causing all the problems. Again, I do not know why. If anyone can explain it, I am eager to listen.

I want to thank all of you who responded to this post. Your suggestions and ideas helped me learn and solve a problem that was driving me nuts. I think I will rest for a few days now!!!

For what it is worth, I am posting the final code that works like a champ. Also, it will be a cold day in hell before I use another check box

Private Sub Command158_Click()
 On Error GoTo ErrorHandler

 Dim db As Database
 Dim strSQL As String
 Dim curr_rec As String
 Dim MyDate As String

 MyDate = Date
 
  'Sets the current record so that after DoCmd.Requery the same record appears
  curr_rec = Me.CurrentRecord
      
  If (IsNull(DLookup("[ID]", "Lost_LKU", "[ID] = " & Me!ID & ""))) Then
  ' Code if ID is not found - Insert record
  Set db = CurrentDb
  DoCmd.RunSQL "INSERT INTO [Lost_LKU] (ID, Last, Lost, DateAdded) " & _
               "VALUES(" & Me.ID & ", '" & Me.LAST & "', 'Yes', #" & Date & "#);"
   DoCmd.Requery
   DoCmd.GoToRecord , , acGoTo, curr_rec
 Exit Sub
Else
   'Code if ID is found - Delete record
   strSQL = ("DELETE * FROM Lost_LKU WHERE [ID] = " & Forms!MasterForm!ID & ";")
   DoCmd.RunSQL strSQL
   DoCmd.Requery
   DoCmd.GoToRecord , , acGoTo, curr_rec
  End If
Exit Sub

ErrorHandlerExit:
 Exit Sub
 
ErrorHandler:
 MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
 Resume ErrorHandlerExit
End Sub