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.Connectio n and uses two ADODB.Recordset s: 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.Connectio n
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=Micro soft.Jet.OLEDB. 4.0;"
strDataSource = "Data Source=\\Comput er\Path\Invento ry.mdb"
'The local or network share path to the Access Database
strConnect = strProvider & strDataSource
Set cnMorris = New ADODB.Connectio n
cnMorris.Cursor Location = adUseClient
cnMorris.Open strConnect
'Connect to the database
Set rsEmployees = New ADODB.Recordset
rsEmployees.Cur sorType = adOpenStatic
rsEmployees.Cur sorLocation = adUseClient
rsEmployees.Loc kType = adLockPessimist ic
rsEmployees.Act iveConnection = cnMorris
rsEmployees.Sou rce = "SELECT * FROM Employees WHERE Username = '" &
Environ$("Usern ame") & "'"
'SQL to select all the employees that match the windows username of
the logged in user
rsEmployees.Ope n
'Opens the database and pulls all records from the SQL statement
into the recordset rsEmployees
Set rsItems = New ADODB.Recordset
rsItems.CursorT ype = adOpenStatic
rsItems.CursorL ocation = adUseClient
rsItems.LockTyp e = adLockPessimist ic
rsItems.ActiveC onnection = cnMorris
rsItems.Source = "SELECT * FROM Items WHERE Employee = '" &
rsEmployees!Emp loyee & "' 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.Cap tion = " "
Call LoadDataInContr ols
'Loads data from Items table into form to view
End Sub
_______________ _______________ _
Private Sub LoadDataInContr ols()
If rsItems.BOF = True Or rsItems.EOF = True Then
Exit Sub
End If
LblEmployee.Cap tion = rsEmployees!Emp loyee
TxtLast.Text = rsEmployees!Las tName
If rsEmployees!See Me = True Then
ChkSeeMe.Value = 1
Else
ChkSeeMe.Value = 0
End If
LblSIU.Caption = rsItems!SIU
LblSerial.Capti on = rsItems!Serial
LblDesc.Caption = rsItems!Descrip tion
LblItemNum.Capt ion = "Item " & rsItems.Absolut ePosition & " of " &
rsItems.RecordC ount
LblRoom.Caption = rsItems!room
OptHas.Value = rsItems!Has
OptHasNot.Value = rsItems!HasNot
End Sub
_______________ _______________ _
Private Sub rsItems_MoveCom plete(ByVal adReason As
ADODB.EventReas onEnum, ByVal pError As ADODB.Error, adStatus As
ADODB.EventStat usEnum, ByVal pRecordset As ADODB.Recordset )
If mblnAddMode = False Then
'If we are not in the add mode, load data in the controls.
Call LoadDataInContr ols
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.Cap tion = " "
BtnNext.Enabled = True
If rsItems.BOF = False Then
rsItems.MovePre vious
If rsItems.BOF = True Then
rsItems.MoveFir st
BtnPrev.Enabled = False
LblPosition.Cap tion = "You are at the first item"
End If
Else
If rsItems.EOF Then
MsgBox ("There are no items for " & rsEmployees!Emp loyee), ,
"Oops!"
Else
rsItems.MoveFir st
End If
End If
End Sub
_______________ _______________ _
Private Sub BtnNext_Click()
rsEmployees!Las tName = 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.Cap tion = " "
BtnPrev.Enabled = True
If rsItems.EOF = False Then
rsItems.MoveNex t
If rsItems.EOF Then
rsItems.MoveLas t
LblPosition.Cap tion = "You are at the last item"
BtnNext.Enabled = False
End If
Else
If rsItems.BOF Then
MsgBox ("There are no items for " & rsEmployees!Emp loyee), ,
"Oops!"
Else
rsItems.MoveLas t
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!See Me = True
Else
rsEmployees!See Me = 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_GotFo cus()
'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>
