Update VBA code triggered by check box

[Dom Fino]

I have a main form that has a check box. If the check box is checked, the on_click option is to determine if the current record ID exists. 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 option is to delete data from the table where the ID matches the ID on the current record.

Problem one is the insert adds one record into the table with correct data, and one record with just the ID. I discover this by taking off the allow duplicates in the Lost_LKU table ID field. If I leave the no duplicates allowed on the Lost_LKU table ID field, I receive an error saying record or ID already exists. Very confusing. The second problem is the records that are unchecked all show as gray rather than white.

I am new to this VBA stuff so please offer any suggestions that you think might lead me in the right direction. Here is the code I am running in the on_click event of the check box.


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 record
Set db = CurrentDb
DoCmd.RunSQL "INSERT INTO [Lost_LKU](ID, Last, Lost, DateAdded)" _
& "VALUES([ID], [Last], -1, 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

 

[WayneRyan]

Dom,

You couldn't have Inserted, or Deleted any recordsd.

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 record 
   Set db = CurrentDb 
   '
   ' This command will not work
   '
   DoCmd.RunSQL "INSERT INTO [Lost_LKU](ID, Last, Lost, DateAdded)" & _ 
                "VALUES([ID], [Last], -1, Date());" 
   '
   ' This command will work
   '
   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 
   '
   ' This won't work
   '
   strSQL = ("DELETE * FROM Lost_LKU WHERE [ID] = Forms!MasterForm!ID;") 
   '
   ' This will ...
   '
   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

Wayne

 

[Dom Fino]

Hi Wayne,
Thanks for your reply and code example. Unfortunately, when I run the code I get a pop up asking me to Enter Parameter Value - (it shows the actual last name).

Example: Enter Parameter Value 'Smith'

When I first start the application and enter a check in the box I get error 3075 syntax Error (Missing Operator) in query expression 'Smith'

The behavior is very strange. However, I am not able to insert a row or delete a row without receiving an error.

Any ideas? Below is the revised code as you suggested.

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 record
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

 

[WayneRyan]

Dom,

If Last is a name (text) then you need to add the single quotes
around it as shown below.

By asking you for the value, Access can't resolve the name
ID or Last. Double-check that that is what their names really
are on your form (Could be Text5?)

DoCmd.RunSQL "INSERT INTO [Lost_LKU] (ID, Last, Lost, DateAdded) " & _ 
"VALUES(" & Me.ID & ", '" & Me.LAST & "', -1, #" & Date & "#);"

Wayne

 

[Dom Fino]

Wayne, Your suggestion fixed the problem regarding (Enter Parameter Value). Thank you.

However,
If I put a check in the box (add to the table) I receive this error. When I check the actual table the record is there.

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 from the table) I receive this error.

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

Sorry to be a pain but this really has me stumped.