Need Unbound Textbox fed from crosstab query to insert/delete table data

[Omar Awan]

I have an unbound control "CC_Security_1_ TextBox" that gets its data from a crosstab query tied to the form. What i need is code that would update the table "CC Security" which has two columns: User and CC Security Rule when someone changes the data on the form.


I'm using an AfterUpdate event that i haven't completed, but can't find a related topic on bytes to help me finish it, so i thought i'd ask.

I think i need the code to remember the original value so it can find and delete it because what i have so far uses the new value to find and delete, which doesn't exist. The insert part seems to be working and the requery part works as well.

Private Sub CC_Security_1_TextBox_AfterUpdate()

If Me.CC_Security_1_TextBox = "" Then

CurrentDb.Execute " DELETE * FROM [CC Security] WHERE User = '" & Me.User_ID & "' AND [CC Security] = '" & Me.CC_Security_1_TextBox & "';"

Else

CurrentDb.Execute " INSERT INTO [CC Security] ( [User], [CC Security Rule] ) VALUES ('" & Me.User_ID & "', '" & Me.CC_Security_1_TextBox & "');"

End If

Dim holdID As String
holdID = Me.User_ID.Value
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "[User ID] ='" & holdID & "'"
Me.Bookmark = rs.Bookmark

End Sub

Any help would be greatly appreciated.

Thanks,
Omar

[Omar Awan]

I've got the delete insert part working for the second half of the If statement. now i just need it to delete the current record if someone deletes the text in the box. I created a hidden field on there for the original value of CC_Security_1 from the crosstab.

How can i get it to delete if the textbox = Null or ""?

Private Sub CC_Security_1_TextBox_AfterUpdate()

If Me.CC_Security_1_TextBox = "" Then

CurrentDb.Execute " DELETE * FROM [CC Security] WHERE User = '" & Me.User_ID & "' AND [CC Security Rule] = '" & Me.CC_Security_1 & "';"
Else
CurrentDb.Execute "DELETE * FROM [CC Security] WHERE User = '" & Me.User_ID & "' AND [CC Security Rule] = '" & Me.CC_Security_1 & "';"
CurrentDb.Execute "INSERT INTO [CC Security] (User, [CC Security Rule]) VALUES ('" & Me.User_ID & "', '" & Me.CC_Security_1_TextBox & "');"
End If

Dim holdID As String
holdID = Me.User_ID.Value
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "[User ID] ='" & holdID & "'"
Me.Bookmark = rs.Bookmark

End Sub

Grateful for any help...

thanks,
Omar

[pod]

Thsi should be simple unless I am not understanding your re, as you get your data from your crosstab query, capture the primary key of that record(PK) then create an update query according to that PK and values in the fields

[Omar Awan]

Changed the first part to an IsNull statement. now it works great!

Thanks:

Private Sub CC_Security_1_TextBox_AfterUpdate()

If IsNull(Me.CC_Security_1_TextBox) Then
CurrentDb.Execute " DELETE * FROM [CC Security] WHERE User = '" & Me.User_ID & "' AND [CC Security Rule] = '" & Me.CC_Security_1 & "';"
Else
CurrentDb.Execute "DELETE * FROM [CC Security] WHERE User = '" & Me.User_ID & "' AND [CC Security Rule] = '" & Me.CC_Security_1 & "';"
CurrentDb.Execute "INSERT INTO [CC Security] (User, [CC Security Rule]) VALUES ('" & Me.User_ID & "', '" & Me.CC_Security_1_TextBox & "');"
End If

Dim holdID As String
holdID = Me.User_ID.Value
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "[User ID] ='" & holdID & "'"
Me.Bookmark = rs.Bookmark

End Sub