Access + VB6 recordset troubles

I have a database in Access 2003 (Access2000 file format). There are
two tables that are being used: Employees and Items. It is linked by
the Employee field so that one employee can have many items.

I have a VB6 application that is tied into this database using an
ADODB.Connection and uses two ADODB.Recordsets: rsEmployees and rsItems.
I can pull data from the DB into the app with both recordsets. But I
can only change data in rsItems and have the changes be made to the
database. rsEmployees loads data, but doesn't save any of the data I
change back to the database. Mainly I just want to use the Checkbox:
ChkSeeMe to save a check onto the checkbox in the Access Database. I
have included the code so that hopefully someone can check it out and
help me with this problem.
Thanks Much,
Ray

<BEGIN CODE>
Option Explicit
Private WithEvents cnMorris As ADODB.Connection
Private WithEvents rsEmployees As ADODB.Recordset
Private WithEvents rsItems As ADODB.Recordset
Dim mblnAddMode As Boolean
______________________
Private Sub Form_Load()

Dim strConnect As String
Dim strProvider As String
Dim strDataSource As String
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
strDataSource = "Data Source=\\Computer\Path\Inventory.mdb"
'The local or network share path to the Access Database
strConnect = strProvider & strDataSource

Set cnMorris = New ADODB.Connection
cnMorris.CursorLocation = adUseClient
cnMorris.Open strConnect
'Connect to the database

Set rsEmployees = New ADODB.Recordset
rsEmployees.CursorType = adOpenStatic
rsEmployees.CursorLocation = adUseClient
rsEmployees.LockType = adLockPessimistic
rsEmployees.ActiveConnection = cnMorris
rsEmployees.Source = "SELECT * FROM Employees WHERE Username = '" &
Environ$("Username") & "'"
'SQL to select all the employees that match the windows username of
the logged in user
rsEmployees.Open
'Opens the database and pulls all records from the SQL statement
into the recordset rsEmployees

Set rsItems = New ADODB.Recordset
rsItems.CursorType = adOpenStatic
rsItems.CursorLocation = adUseClient
rsItems.LockType = adLockPessimistic
rsItems.ActiveConnection = cnMorris
rsItems.Source = "SELECT * FROM Items WHERE Employee = '" &
rsEmployees!Employee & "' ORDER BY SIU"
'SQL to select all the Items that have the employee's name in that
record and order them by the SIU Tag #
rsItems.Open
'Opens the database and pulls all records from the SQL statement
into the recordset rsItems

LblPosition.Caption = " "

Call LoadDataInControls
'Loads data from Items table into form to view

End Sub
_______________________________
Private Sub LoadDataInControls()

If rsItems.BOF = True Or rsItems.EOF = True Then
Exit Sub
End If

LblEmployee.Caption = rsEmployees!Employee
TxtLast.Text = rsEmployees!LastName
If rsEmployees!SeeMe = True Then
ChkSeeMe.Value = 1
Else
ChkSeeMe.Value = 0
End If


LblSIU.Caption = rsItems!SIU
LblSerial.Caption = rsItems!Serial
LblDesc.Caption = rsItems!Description
LblItemNum.Caption = "Item " & rsItems.AbsolutePosition & " of " &
rsItems.RecordCount
LblRoom.Caption = rsItems!room
OptHas.Value = rsItems!Has
OptHasNot.Value = rsItems!HasNot

End Sub
_______________________________
Private Sub rsItems_MoveComplete(ByVal adReason As
ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As
ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

If mblnAddMode = False Then
'If we are not in the add mode, load data in the controls.

Call LoadDataInControls
End If
End Sub
_______________________________
Private Sub BtnPrev_Click()

'Checks to see if OptHas or OptHasNot has been selected
If OptHas.Value = False And OptHasNot.Value = False Then
MsgBox ("You must select whether you Have or Do NOT Have this item")
Exit Sub
End If

Call RadioButton
Call ChkBox
LblPosition.Caption = " "
BtnNext.Enabled = True
If rsItems.BOF = False Then
rsItems.MovePrevious
If rsItems.BOF = True Then
rsItems.MoveFirst
BtnPrev.Enabled = False
LblPosition.Caption = "You are at the first item"
End If
Else
If rsItems.EOF Then
MsgBox ("There are no items for " & rsEmployees!Employee), ,
"Oops!"
Else
rsItems.MoveFirst
End If
End If

End Sub
_______________________________
Private Sub BtnNext_Click()

rsEmployees!LastName = TxtLast.Text

'Checks to see if OptHas or OptHasNot has been selected
If OptHas.Value = False And OptHasNot.Value = False Then
MsgBox ("You must select whether you Have or Do NOT Have this item")
Exit Sub
End If

Call RadioButton
Call ChkBox
LblPosition.Caption = " "
BtnPrev.Enabled = True
If rsItems.EOF = False Then
rsItems.MoveNext
If rsItems.EOF Then
rsItems.MoveLast
LblPosition.Caption = "You are at the last item"
BtnNext.Enabled = False
End If
Else
If rsItems.BOF Then
MsgBox ("There are no items for " & rsEmployees!Employee), ,
"Oops!"
Else
rsItems.MoveLast
End If
End If

End Sub
_______________________________
Private Sub ChkBox()

'Writes the SeeMe value to the database in the Employee table
If ChkSeeMe.Value = 1 Then
rsEmployees!SeeMe = True
Else
rsEmployees!SeeMe = False
End If

End Sub
_______________________________
Private Sub RadioButton()

'Writes the Has and HasNot values to the database in the Items table
If OptHas.Value = True Then
rsItems!Has = "True"
rsItems!HasNot = "False"
ElseIf OptHasNot.Value = True Then
rsItems!HasNot = "True"
rsItems!Has = "False"
End If

End Sub
_______________________________
Private Sub OptHasNot_GotFocus()
'When the Option Button for "I do NOT have this item." is clicked, this
box pops up and asks if they are sure.
Dim Sure
Sure = MsgBox("Are you sure that you do not have this item? Have you
looked everywhere?", vbYesNo, "Missing Item")
If Sure = vbNo Then
OptHasNot.Value = False
Else
OptHasNot.Value = True
End If

End Sub
_______________________________
Private Sub BtnExit_Click()

cnMorris.Close
Set cnMorris = Nothing
'Close the connection to the database
Unload Me
'Unload the form which exits the application
End Sub


<END CODE>

re: Access + VB6 recordset troubles

Your problem is easily resolved by using the ADODB command object
instead of a recordset object for performing Updates, Deletes, and
Inserts. These are all action items. For Action items you want to
think in terms of the command object. For reads you can use the
recordset objects. With ADO.Net you can use a DataReader object for
reading; much nicer and easier to work with.

Here is a sample using the command object:

Dim cmd As New ADODB.Command
cmd.ActiveConnection =
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID =;Data
Source=C:\somedir\yourmdb.mdb"

cmd.CommandType = adCmdText
cmd.CommandText = "Update tbl1 Set fld1 = 'test'"
cmd.Execute

cmd.CommandText = "Insert Into tbl1 (fld1, fld2, fld3) " _
& "Values('test1', 'test2', 'test3')
cmd.Execute

cmd.ActiveConnection.Close

Here I am assuming tbl1 contains all text fields. Thus, delimit with
single quotes "'". For numeric - no delimeters, for dates, use "#" the
pound sign.

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***