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>
