HELP !!! Updating Records in MSAccess fromVB

[Viji]

Hi Guys,

I am trying to update a field in a MS Access DB with the input from a ListView Control. But the problem is field in the MS Access DB has a releationship to a similar field in a different table (in the same DB) so its not letting me update this record. Any thoughts on getting around the problem??
Thanks for your help ...
Some of the code looks like as follows:

Viji


Public Sub AddRightsToDB()
Dim SQL2 As String


If Not cnn.State = adStateOpen Then
OpenConnection
End If

If cnn.State = adStateOpen Then

SQL2 = "update USER_RIGHTS set Access_Level = ('" & AccessLvl & "')where User_Name='" & Uname_Rights & "'"

rstNewRights.Open SQL2, cnn, adOpenForwardOnly, adLockOptimistic

CloseConnection


End If
End Sub

[Cakkie]

You're trying to open a recordset which contains a command to execute. You must do this in 2 steps: First execute the command, then reselect the record:

public Sub AddRightsToDB()
Dim SQL1 as string 'will hold statement to execute
Dim SQL2 as string 'will hold statement to select

If Not cnn.State = adStateOpen then
OpenConnection
End If

If cnn.State = adStateOpen then

SQL1 = "update USER_RIGHTS set Access_Level = ('" & AccessLvl & "')where User_Name='" & Uname_Rights & "'"
SQL2 = "select * FROM USER_RIGHTS WHERE User_Name='" & Uname_Rights & "'"

cnn.execute SQL1 ' do update
rstNewRights.Open SQL2, cnn, adOpenForwardOnly, adLockOptimistic

CloseConnection

End If

End Sub





Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Viji]

Hi Tom,

Thanks a lot...I tried but it still comes up with the following error message!!!

"You can't add or change a Record because a releted record is required in table USER_GROUPS"

This is because the field Access_Level cntains in both tables (USER_RIGHTS and USER_GROUPS) and are related ( defined using Relationship command in Access)
Any thoughts are greatly appreciated.

Viji