I would like to be able to edit the contents of data coming back from a
resultset and not just display it. I have the impression that if a select
query is used, it only is displayed in a form it cannot be edited any
longer-it only display what is on the database. I would like to be able to
change the data in the screen also and save the changes back to the
database.

Any suggestions from the Access gurus are more than welcome.

Joseph

Hi Joseph,

Here are a few ideas you might find useful. Three subs are included:

1.  Form_Open opens a read-writeable recordset and stores it in a
    module level variable. A Select query named qryClients pulls records
    from tblClients to fill the recordset. Notice the connection Mode
    and recordset LockType and CursorType properties used to make the
    recordset updateable (though mine isn't the only configuration of
    these properties that will return a r/w recordset. There are
    others)

2.  DisplayRecords()is then called to populate the unbound controls on
    the form with the data from the underlying recordset(unbound just
    means that the form's record source property and the controls'
    control source properties are set at run-time via code)

3.  cmdUpdate_Click() is the code behind an Update command button on
    the form that lests you commit edits you've made to the form's
    data back to the datasource. The recordset's Update method is used,
    then the DisplayRecords sub is called again to display the updated
    records in the form.

The form also has Add, Delete, Find and Close buttons on it, as well as
First, Last, Next and Previous navigation buttons. Clicking the Add
button opens a second form to add a new record to the data source. This
second form has a Save and Cancel button on it. Find opens a third form for 
multi-parameter searches. Thats the app in a nut shell.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim mrstClients As Recordset

Private Sub Form_Open(Cancel As Integer)
    Dim cnnClient As New ADODB.Connection
    Dim strProvider As String
    Dim strDataSource As String

    ' Create a connection
    strProvider = "Microsoft.Jet.OLEDB.4.0"
    strDataSource = CurrentProject.Path _
        & "\dbOutpatient.mdb."

    ' Enable read and write
    cnnClient.Mode = adModeReadWrite

    ' Open connection
    cnnClient.Open "Provider = " & strProvider & _
        "; Data Source = " & strDataSource

    ' Create and open recordset
    Set mrstClients = New ADODB.Recordset
    mrstClients.Open "qryClients", cnnClient, adOpenDynamic, _
        adLockOptimistic, adCmdTable

    ' Enable and disable command buttons
    txtCaseNumber.SetFocus
    cmdFirst.Enabled = False
    cmdNext.Enabled = True
    cmdPrevious.Enabled = False
    cmdLast.Enabled = True


    Call DisplayRecords

End Sub

Private Sub DisplayRecords()

    txtCaseNumber = mrstClients!CaseNumber
    txtClientLastName = mrstClients!ClientLastName
    txtClientFirstName = mrstClients!ClientFirstName
    txtDOB = mrstClients!DOB
    txtSSN = mrstClients!SSN
    cboCounselor = mrstClients!CounselorID
    txtDOA = mrstClients!DOA
    cboReferralSource = mrstClients!ReferralSourceID
    cboClientType = mrstClients!ClientTypeID
    cboProgram = mrstClients!ProgramID
    txtDOD = mrstClients!DoD
    cboDischargeStatus = mrstClients!DischargeStatusID

End Sub

Private Sub cmdUpdate_Click()
    Dim intMsg As Integer
    Dim strMsg As String
    Dim intIcon As Integer
    Dim strTitle As String

    strMsg = "Are you sure you want to save this information" _
	& vbCrLf
    strMsg = strMsg & "for a new client?"
    intIcon = vbYesNo
    strTitle = "Outpatient Database"

    intMsg = MsgBox(strMsg, intIcon, strTitle)

    If intMsg = vbNo Then
        Exit Sub
    End If

    With mrstClients
        !CaseNumber = txtCaseNumber
        !ClientLastName = UCase(txtClientLastName)
        !ClientFirstName = UCase(txtClientFirstName)
        !DOB = txtDOB
        !SSN = txtSSN
        !CounselorID = UCase(cboCounselor)
        !DOA = txtDOA
        !ReferralSourceID = UCase(cboReferralSource)
        !ClientTypeID = UCase(cboClientType)
        !ProgramID = UCase(cboProgram)
        !DoD = txtDOD
        !DischargeStatusID = UCase(cboDischargeStatus)
        .Update
    End With

    Call DisplayRecords

End Sub

If you ever stumble across a book by Gordan Padwick called "Creating
Microsoft Access 2000 Solutions: A Power User's Guide", it has one
of the best samples of an unbound ADO app in it I've ever run across.
I didn't even read much of the book. Learned most of what I wanted to
know just from playing with the app code, which contains all the
functionality I've described above and then some.

Best,

Bob


>From: "Joseph Valdez" <e-valdez@a...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Resultset that can be edited from a select statement
>Date: Sat, 15 Mar 2003 10:24:49 -0800
>
>I would like to be able to edit the contents of data coming back from a
>resultset and not just display it. I have the impression that if a select
>query is used, it only is displayed in a form it cannot be edited any
>longer-it only display what is on the database. I would like to be able to
>change the data in the screen also and save the changes back to the
>database.
>
>Any suggestions from the Access gurus are more than welcome.
>
>Joseph
>
>







>From: "Joseph Valdez" <e-valdez@a...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Resultset that can be edited from a select statement
>Date: Sat, 15 Mar 2003 10:24:49 -0800
>
>I would like to be able to edit the contents of data coming back from a
>resultset and not just display it. I have the impression that if a select
>query is used, it only is displayed in a form it cannot be edited any
>longer-it only display what is on the database. I would like to be able to
>change the data in the screen also and save the changes back to the
>database.
>
>Any suggestions from the Access gurus are more than welcome.
>
>Joseph
>
>


_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online  
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

This was an excellent reply as well as comprehensive in content!
What if the control I am using is a listbox? I'd like to be able to double
click the row so the detail form would show? Also, is it possible to return
the result in a subform with listbox so that right and there I will be able
to edit the data directly to the table or database.

Thanks for your reply!

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Saturday, March 15, 2003 9:17 AM
To: Access
Subject: [access] Re: Resultset that can be edited from a select
statement


Hi Joseph,

Here are a few ideas you might find useful. Three subs are included:

1.  Form_Open opens a read-writeable recordset and stores it in a
    module level variable. A Select query named qryClients pulls records
    from tblClients to fill the recordset. Notice the connection Mode
    and recordset LockType and CursorType properties used to make the
    recordset updateable (though mine isn't the only configuration of
    these properties that will return a r/w recordset. There are
    others)

2.  DisplayRecords()is then called to populate the unbound controls on
    the form with the data from the underlying recordset(unbound just
    means that the form's record source property and the controls'
    control source properties are set at run-time via code)

3.  cmdUpdate_Click() is the code behind an Update command button on
    the form that lests you commit edits you've made to the form's
    data back to the datasource. The recordset's Update method is used,
    then the DisplayRecords sub is called again to display the updated
    records in the form.

The form also has Add, Delete, Find and Close buttons on it, as well as
First, Last, Next and Previous navigation buttons. Clicking the Add
button opens a second form to add a new record to the data source. This
second form has a Save and Cancel button on it. Find opens a third form for
multi-parameter searches. Thats the app in a nut shell.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim mrstClients As Recordset

Private Sub Form_Open(Cancel As Integer)
    Dim cnnClient As New ADODB.Connection
    Dim strProvider As String
    Dim strDataSource As String

    ' Create a connection
    strProvider = "Microsoft.Jet.OLEDB.4.0"
    strDataSource = CurrentProject.Path _
        & "\dbOutpatient.mdb."

    ' Enable read and write
    cnnClient.Mode = adModeReadWrite

    ' Open connection
    cnnClient.Open "Provider = " & strProvider & _
        "; Data Source = " & strDataSource

    ' Create and open recordset
    Set mrstClients = New ADODB.Recordset
    mrstClients.Open "qryClients", cnnClient, adOpenDynamic, _
        adLockOptimistic, adCmdTable

    ' Enable and disable command buttons
    txtCaseNumber.SetFocus
    cmdFirst.Enabled = False
    cmdNext.Enabled = True
    cmdPrevious.Enabled = False
    cmdLast.Enabled = True


    Call DisplayRecords

End Sub

Private Sub DisplayRecords()

    txtCaseNumber = mrstClients!CaseNumber
    txtClientLastName = mrstClients!ClientLastName
    txtClientFirstName = mrstClients!ClientFirstName
    txtDOB = mrstClients!DOB
    txtSSN = mrstClients!SSN
    cboCounselor = mrstClients!CounselorID
    txtDOA = mrstClients!DOA
    cboReferralSource = mrstClients!ReferralSourceID
    cboClientType = mrstClients!ClientTypeID
    cboProgram = mrstClients!ProgramID
    txtDOD = mrstClients!DoD
    cboDischargeStatus = mrstClients!DischargeStatusID

End Sub

Private Sub cmdUpdate_Click()
    Dim intMsg As Integer
    Dim strMsg As String
    Dim intIcon As Integer
    Dim strTitle As String

    strMsg = "Are you sure you want to save this information" _
	& vbCrLf
    strMsg = strMsg & "for a new client?"
    intIcon = vbYesNo
    strTitle = "Outpatient Database"

    intMsg = MsgBox(strMsg, intIcon, strTitle)

    If intMsg = vbNo Then
        Exit Sub
    End If

    With mrstClients
        !CaseNumber = txtCaseNumber
        !ClientLastName = UCase(txtClientLastName)
        !ClientFirstName = UCase(txtClientFirstName)
        !DOB = txtDOB
        !SSN = txtSSN
        !CounselorID = UCase(cboCounselor)
        !DOA = txtDOA
        !ReferralSourceID = UCase(cboReferralSource)
        !ClientTypeID = UCase(cboClientType)
        !ProgramID = UCase(cboProgram)
        !DoD = txtDOD
        !DischargeStatusID = UCase(cboDischargeStatus)
        .Update
    End With

    Call DisplayRecords

End Sub

If you ever stumble across a book by Gordan Padwick called "Creating
Microsoft Access 2000 Solutions: A Power User's Guide", it has one
of the best samples of an unbound ADO app in it I've ever run across.
I didn't even read much of the book. Learned most of what I wanted to
know just from playing with the app code, which contains all the
functionality I've described above and then some.

Best,

Bob


>From: "Joseph Valdez" <e-valdez@a...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Resultset that can be edited from a select statement
>Date: Sat, 15 Mar 2003 10:24:49 -0800
>
>I would like to be able to edit the contents of data coming back from a
>resultset and not just display it. I have the impression that if a select
>query is used, it only is displayed in a form it cannot be edited any
>longer-it only display what is on the database. I would like to be able to
>change the data in the screen also and save the changes back to the
>database.
>
>Any suggestions from the Access gurus are more than welcome.
>
>Joseph
>
>







>From: "Joseph Valdez" <e-valdez@a...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Resultset that can be edited from a select statement
>Date: Sat, 15 Mar 2003 10:24:49 -0800
>
>I would like to be able to edit the contents of data coming back from a
>resultset and not just display it. I have the impression that if a select
>query is used, it only is displayed in a form it cannot be edited any
>longer-it only display what is on the database. I would like to be able to
>change the data in the screen also and save the changes back to the
>database.
>
>Any suggestions from the Access gurus are more than welcome.
>
>Joseph
>
>


_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

Hi Joseph,

The listbox is workable, but I'd shy away from subforms using ADO.
There simply isn't any way to assign unbound control values from
an ADO Recodset if the form is displayed in continuous or datasheet
view mode. You could use a delete/append query combo to fill a local
data cache, and bind the subform to the local table, but thats a lot
of work.

To do the kind of thing you're describing with a listbox, I made a
simple form with a listbox and a cmdFillList button on it. The code
behind cmdFillList uses the GetRows method of a recordset to fill an
array that gets assigned to the listboxes RowSource property. Once the
listbox is populated, double-clicking it opens frmClients, sending
along the listbox value as the OpenArgs argument. frmClients uses
the OpenArgs value to open an updatable ADO recordset which populates
the form. The recordset uses a Static cursor (even though I specified
adOpenDynamic, ADO's funny that way) and BatchOptimistic locking, even
though the lock property value isn't specified (leave it implied or
you'll generate a FROM clause error).

You could also just use the recordsets Find method and an Inputbox
instead of the ListBox:

  strCaseNumber = InputBox("Enter Case Number of Client")
  mrstClients.Find "CaseNumber = '" & strCaseNumber & "'", _
     Start:=1
  Call DisplayRecords

Here's the listbox approach:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
frmSearch
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub cmdFillList_Click()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strList As String
    Dim strSQL As String

    strSQL = "SELECT CaseNumber FROM tblClients " _
        & "ORDER BY Casenumber"

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnn

    strList = BuildString(rst)
    lstSearch.RowSource = strList
    rst.Close
End Sub

Private Function BuildString(rst As ADODB.Recordset) As String
    Dim strReturn As String
    Dim intCount As Integer
    Dim avarItems As Variant
    Dim x As Integer
    Dim y As Integer

    rst.MoveFirst
    intCount = rst.RecordCount

    avarItems = rst.GetRows(intCount)
    For x = LBound(avarItems, 2) To UBound(avarItems, 2)
        For y = LBound(avarItems, 1) To UBound(avarItems, 1)
            strReturn = strReturn & avarItems(y, x) & ";"
        Next y
    Next x
    BuildString = strReturn
End Function

Private Sub Form_Open(Cancel As Integer)
    lstSearch.RowSource = ""
End Sub

Private Sub lstSearch_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmClients", , , , , , Me![lstSearch]
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
frmClients
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private mrstClients As ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)
    Dim cnnClient As New ADODB.Connection
    Dim strProvider As String
    Dim strDataSource As String
    Dim strSQL As String
    Dim strCriteria As String

    ' Create a connection
    strProvider = "Microsoft.Jet.OLEDB.4.0"
    strDataSource = CurrentProject.Connection

    ' Enable read and write
    cnnClient.Mode = adModeReadWrite

    ' Open connection
    cnnClient.Open "Provider = " & strProvider & _
        "; Data Source = " & strDataSource

    ' Build SQL string
    If Not IsNull(Me.OpenArgs) Then
        strCriteria = Me.OpenArgs
    End If

    strSQL = "SELECT * FROM tblClients " _
                    & "WHERE CaseNumber = '" & strCriteria & "'"

    '    Use Find method instead of the WHERE clause if you want
    '    mrstClients.Find "CaseNumber = '" & strCriteria & "'"

    ' Create and open recordset
    Set mrstClients = New ADODB.Recordset
    mrstClients.CursorLocation = adUseClient
    mrstClients.Open strSQL, cnnClient, adOpenDynamic, adCmdTable

    Call DisplayRecords

  End Sub

Private Sub DisplayRecords()

    txtCaseNumber = mrstClients!CaseNumber
    txtClientLastName = mrstClients!ClientLastName
    txtClientFirstName = mrstClients!ClientFirstName
    txtDOB = mrstClients!DOB
    txtSSN = mrstClients!SSN
    cboCounselor = mrstClients!CounselorID
    txtDOA = mrstClients!DOA
    cboReferralSource = mrstClients!ReferralSourceID
    cboClientType = mrstClients!ClientTypeID
    cboProgram = mrstClients!ProgramID
    txtDOD = mrstClients!DOD
    cboDischargeStatus = mrstClients!DischargeStatusID

End Sub

Private Sub cmdUpdate_Click()
    Dim intMsg As Integer
    Dim strMsg As String
    Dim intIcon As Integer
    Dim strTitle As String

    strMsg = "Are you sure you want to save this information" & vbCrLf
    strMsg = strMsg & "for a new client?"
    intIcon = vbYesNo
    strTitle = "Outpatient Database"

    intMsg = MsgBox(strMsg, intIcon, strTitle)

    If intMsg = vbNo Then
        Exit Sub
    End If

    With mrstClients
        !CaseNumber = txtCaseNumber
        !ClientLastName = UCase(txtClientLastName)
        !ClientFirstName = UCase(txtClientFirstName)
        !DOB = txtDOB
        !SSN = txtSSN
        !CounselorID = UCase(cboCounselor)
        !DOA = txtDOA
        !ReferralSourceID = UCase(cboReferralSource)
        !ClientTypeID = UCase(cboClientType)
        !ProgramID = UCase(cboProgram)
        !DOD = txtDOD
        !DischargeStatusID = UCase(cboDischargeStatus)
        .Update
    End With

    mrstClients.MovePrevious
    If mrstClients.BOF Then
        cmdFirst.Enabled = False
        cmdPrevious.Enabled = False
        mrstClients.MoveFirst
    Else
        cmdFirst.Enabled = True
        cmdPrevious.Enabled = True
        mrstClients.MoveNext
    End If

    mrstClients.MoveNext
    If mrstClients.EOF Then
        cmdLast.Enabled = False
        cmdNext.Enabled = False
        mrstClients.MoveLast
    Else
        cmdLast.Enabled = True
        cmdNext.Enabled = True
        mrstClients.MovePrevious
    End If

    Call DisplayRecords

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

>From: "Joseph Valdez" <e-valdez@a...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Resultset that can be edited from a select statement
>Date: Sat, 15 Mar 2003 16:10:59 -0800
>
>This was an excellent reply as well as comprehensive in content!
>What if the control I am using is a listbox? I'd like to be able to double
>click the row so the detail form would show? Also, is it possible to return
>the result in a subform with listbox so that right and there I will be able
>to edit the data directly to the table or database.
>
>Thanks for your reply!
>
>-----Original Message-----
>From: Bob Bedell [mailto:bobbedell15@m...]
>Sent: Saturday, March 15, 2003 9:17 AM
>To: Access
>Subject: [access] Re: Resultset that can be edited from a select
>statement
>
>
>Hi Joseph,
>
>Here are a few ideas you might find useful. Three subs are included:
>
>1.  Form_Open opens a read-writeable recordset and stores it in a
>     module level variable. A Select query named qryClients pulls records
>     from tblClients to fill the recordset. Notice the connection Mode
>     and recordset LockType and CursorType properties used to make the
>     recordset updateable (though mine isn't the only configuration of
>     these properties that will return a r/w recordset. There are
>     others)
>
>2.  DisplayRecords()is then called to populate the unbound controls on
>     the form with the data from the underlying recordset(unbound just
>     means that the form's record source property and the controls'
>     control source properties are set at run-time via code)
>
>3.  cmdUpdate_Click() is the code behind an Update command button on
>     the form that lests you commit edits you've made to the form's
>     data back to the datasource. The recordset's Update method is used,
>     then the DisplayRecords sub is called again to display the updated
>     records in the form.
>
>The form also has Add, Delete, Find and Close buttons on it, as well as
>First, Last, Next and Previous navigation buttons. Clicking the Add
>button opens a second form to add a new record to the data source. This
>second form has a Save and Cancel button on it. Find opens a third form for
>multi-parameter searches. Thats the app in a nut shell.
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>Dim mrstClients As Recordset
>
>Private Sub Form_Open(Cancel As Integer)
>     Dim cnnClient As New ADODB.Connection
>     Dim strProvider As String
>     Dim strDataSource As String
>
>     ' Create a connection
>     strProvider = "Microsoft.Jet.OLEDB.4.0"
>     strDataSource = CurrentProject.Path _
>         & "\dbOutpatient.mdb."
>
>     ' Enable read and write
>     cnnClient.Mode = adModeReadWrite
>
>     ' Open connection
>     cnnClient.Open "Provider = " & strProvider & _
>         "; Data Source = " & strDataSource
>
>     ' Create and open recordset
>     Set mrstClients = New ADODB.Recordset
>     mrstClients.Open "qryClients", cnnClient, adOpenDynamic, _
>         adLockOptimistic, adCmdTable
>
>     ' Enable and disable command buttons
>     txtCaseNumber.SetFocus
>     cmdFirst.Enabled = False
>     cmdNext.Enabled = True
>     cmdPrevious.Enabled = False
>     cmdLast.Enabled = True
>
>
>     Call DisplayRecords
>
>End Sub
>
>Private Sub DisplayRecords()
>
>     txtCaseNumber = mrstClients!CaseNumber
>     txtClientLastName = mrstClients!ClientLastName
>     txtClientFirstName = mrstClients!ClientFirstName
>     txtDOB = mrstClients!DOB
>     txtSSN = mrstClients!SSN
>     cboCounselor = mrstClients!CounselorID
>     txtDOA = mrstClients!DOA
>     cboReferralSource = mrstClients!ReferralSourceID
>     cboClientType = mrstClients!ClientTypeID
>     cboProgram = mrstClients!ProgramID
>     txtDOD = mrstClients!DoD
>     cboDischargeStatus = mrstClients!DischargeStatusID
>
>End Sub
>
>Private Sub cmdUpdate_Click()
>     Dim intMsg As Integer
>     Dim strMsg As String
>     Dim intIcon As Integer
>     Dim strTitle As String
>
>     strMsg = "Are you sure you want to save this information" _
>	& vbCrLf
>     strMsg = strMsg & "for a new client?"
>     intIcon = vbYesNo
>     strTitle = "Outpatient Database"
>
>     intMsg = MsgBox(strMsg, intIcon, strTitle)
>
>     If intMsg = vbNo Then
>         Exit Sub
>     End If
>
>     With mrstClients
>         !CaseNumber = txtCaseNumber
>         !ClientLastName = UCase(txtClientLastName)
>         !ClientFirstName = UCase(txtClientFirstName)
>         !DOB = txtDOB
>         !SSN = txtSSN
>         !CounselorID = UCase(cboCounselor)
>         !DOA = txtDOA
>         !ReferralSourceID = UCase(cboReferralSource)
>         !ClientTypeID = UCase(cboClientType)
>         !ProgramID = UCase(cboProgram)
>         !DoD = txtDOD
>         !DischargeStatusID = UCase(cboDischargeStatus)
>         .Update
>     End With
>
>     Call DisplayRecords
>
>End Sub
>
>If you ever stumble across a book by Gordan Padwick called "Creating
>Microsoft Access 2000 Solutions: A Power User's Guide", it has one
>of the best samples of an unbound ADO app in it I've ever run across.
>I didn't even read much of the book. Learned most of what I wanted to
>know just from playing with the app code, which contains all the
>functionality I've described above and then some.
>
>Best,
>
>Bob
>
>
> >From: "Joseph Valdez" <e-valdez@a...>
> >Reply-To: "Access" <access@p...>
> >To: "Access" <access@p...>
> >Subject: [access] Resultset that can be edited from a select statement
> >Date: Sat, 15 Mar 2003 10:24:49 -0800
> >
> >I would like to be able to edit the contents of data coming back from a
> >resultset and not just display it. I have the impression that if a select
> >query is used, it only is displayed in a form it cannot be edited any
> >longer-it only display what is on the database. I would like to be able 
>to
> >change the data in the screen also and save the changes back to the
> >database.
> >
> >Any suggestions from the Access gurus are more than welcome.
> >
> >Joseph
> >
> >
>
>
>
>
>
>
>
> >From: "Joseph Valdez" <e-valdez@a...>
> >Reply-To: "Access" <access@p...>
> >To: "Access" <access@p...>
> >Subject: [access] Resultset that can be edited from a select statement
> >Date: Sat, 15 Mar 2003 10:24:49 -0800
> >
> >I would like to be able to edit the contents of data coming back from a
> >resultset and not just display it. I have the impression that if a select
> >query is used, it only is displayed in a form it cannot be edited any
> >longer-it only display what is on the database. I would like to be able 
>to
> >change the data in the screen also and save the changes back to the
> >database.
> >
> >Any suggestions from the Access gurus are more than welcome.
> >
> >Joseph
> >
> >
>
>
>_________________________________________________________________
>Protect your PC - get McAfee.com VirusScan Online
>http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
>
>
>
>
>


_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail

Hi Joseph,

Ignore the navigation button code in the cmdUpdate sub of my last post.
I should have cut it out. If you try out the listbox code you'll see
that the recordset returned for the detail form consists of one record
that meets the criteria in the SELECT statment. So navigation isn't an
issue, obviously. One "advantage" of using the Find method is that it
lets you work with a larger recordset, and then locate the single
record you're interested in within that recordset. In that case you
might want some navigation ability.

Bob



>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Resultset that can be edited from a select statement
>Date: Sun, 16 Mar 2003 05:33:53 +0000
>
>Hi Joseph,
>
>The listbox is workable, but I'd shy away from subforms using ADO.
>There simply isn't any way to assign unbound control values from
>an ADO Recodset if the form is displayed in continuous or datasheet
>view mode. You could use a delete/append query combo to fill a local
>data cache, and bind the subform to the local table, but thats a lot
>of work.
>
>To do the kind of thing you're describing with a listbox, I made a
>simple form with a listbox and a cmdFillList button on it. The code
>behind cmdFillList uses the GetRows method of a recordset to fill an
>array that gets assigned to the listboxes RowSource property. Once the
>listbox is populated, double-clicking it opens frmClients, sending
>along the listbox value as the OpenArgs argument. frmClients uses
>the OpenArgs value to open an updatable ADO recordset which populates
>the form. The recordset uses a Static cursor (even though I specified
>adOpenDynamic, ADO's funny that way) and BatchOptimistic locking, even
>though the lock property value isn't specified (leave it implied or
>you'll generate a FROM clause error).
>
>You could also just use the recordsets Find method and an Inputbox
>instead of the ListBox:
>
>  strCaseNumber = InputBox("Enter Case Number of Client")
>  mrstClients.Find "CaseNumber = '" & strCaseNumber & "'", _
>     Start:=1
>  Call DisplayRecords
>
>Here's the listbox approach:
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>frmSearch
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>Private Sub cmdFillList_Click()
>    Dim cnn As ADODB.Connection
>    Dim rst As ADODB.Recordset
>    Dim strList As String
>    Dim strSQL As String
>
>    strSQL = "SELECT CaseNumber FROM tblClients " _
>        & "ORDER BY Casenumber"
>
>    Set cnn = CurrentProject.Connection
>    Set rst = New ADODB.Recordset
>    rst.Open strSQL, cnn
>
>    strList = BuildString(rst)
>    lstSearch.RowSource = strList
>    rst.Close
>End Sub
>
>Private Function BuildString(rst As ADODB.Recordset) As String
>    Dim strReturn As String
>    Dim intCount As Integer
>    Dim avarItems As Variant
>    Dim x As Integer
>    Dim y As Integer
>
>    rst.MoveFirst
>    intCount = rst.RecordCount
>
>    avarItems = rst.GetRows(intCount)
>    For x = LBound(avarItems, 2) To UBound(avarItems, 2)
>        For y = LBound(avarItems, 1) To UBound(avarItems, 1)
>            strReturn = strReturn & avarItems(y, x) & ";"
>        Next y
>    Next x
>    BuildString = strReturn
>End Function
>
>Private Sub Form_Open(Cancel As Integer)
>    lstSearch.RowSource = ""
>End Sub
>
>Private Sub lstSearch_DblClick(Cancel As Integer)
>    DoCmd.OpenForm "frmClients", , , , , , Me![lstSearch]
>End Sub
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>frmClients
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>Private mrstClients As ADODB.Recordset
>
>Private Sub Form_Open(Cancel As Integer)
>    Dim cnnClient As New ADODB.Connection
>    Dim strProvider As String
>    Dim strDataSource As String
>    Dim strSQL As String
>    Dim strCriteria As String
>
>    ' Create a connection
>    strProvider = "Microsoft.Jet.OLEDB.4.0"
>    strDataSource = CurrentProject.Connection
>
>    ' Enable read and write
>    cnnClient.Mode = adModeReadWrite
>
>    ' Open connection
>    cnnClient.Open "Provider = " & strProvider & _
>        "; Data Source = " & strDataSource
>
>    ' Build SQL string
>    If Not IsNull(Me.OpenArgs) Then
>        strCriteria = Me.OpenArgs
>    End If
>
>    strSQL = "SELECT * FROM tblClients " _
>                    & "WHERE CaseNumber = '" & strCriteria & "'"
>
>    '    Use Find method instead of the WHERE clause if you want
>    '    mrstClients.Find "CaseNumber = '" & strCriteria & "'"
>
>    ' Create and open recordset
>    Set mrstClients = New ADODB.Recordset
>    mrstClients.CursorLocation = adUseClient
>    mrstClients.Open strSQL, cnnClient, adOpenDynamic, adCmdTable
>
>    Call DisplayRecords
>
>  End Sub
>
>Private Sub DisplayRecords()
>
>    txtCaseNumber = mrstClients!CaseNumber
>    txtClientLastName = mrstClients!ClientLastName
>    txtClientFirstName = mrstClients!ClientFirstName
>    txtDOB = mrstClients!DOB
>    txtSSN = mrstClients!SSN
>    cboCounselor = mrstClients!CounselorID
>    txtDOA = mrstClients!DOA
>    cboReferralSource = mrstClients!ReferralSourceID
>    cboClientType = mrstClients!ClientTypeID
>    cboProgram = mrstClients!ProgramID
>    txtDOD = mrstClients!DOD
>    cboDischargeStatus = mrstClients!DischargeStatusID
>
>End Sub
>
>Private Sub cmdUpdate_Click()
>    Dim intMsg As Integer
>    Dim strMsg As String
>    Dim intIcon As Integer
>    Dim strTitle As String
>
>    strMsg = "Are you sure you want to save this information" & vbCrLf
>    strMsg = strMsg & "for a new client?"
>    intIcon = vbYesNo
>    strTitle = "Outpatient Database"
>
>    intMsg = MsgBox(strMsg, intIcon, strTitle)
>
>    If intMsg = vbNo Then
>        Exit Sub
>    End If
>
>    With mrstClients
>        !CaseNumber = txtCaseNumber
>        !ClientLastName = UCase(txtClientLastName)
>        !ClientFirstName = UCase(txtClientFirstName)
>        !DOB = txtDOB
>        !SSN = txtSSN
>        !CounselorID = UCase(cboCounselor)
>        !DOA = txtDOA
>        !ReferralSourceID = UCase(cboReferralSource)
>        !ClientTypeID = UCase(cboClientType)
>        !ProgramID = UCase(cboProgram)
>        !DOD = txtDOD
>        !DischargeStatusID = UCase(cboDischargeStatus)
>        .Update
>    End With
>
>    mrstClients.MovePrevious
>    If mrstClients.BOF Then
>        cmdFirst.Enabled = False
>        cmdPrevious.Enabled = False
>        mrstClients.MoveFirst
>    Else
>        cmdFirst.Enabled = True
>        cmdPrevious.Enabled = True
>        mrstClients.MoveNext
>    End If
>
>    mrstClients.MoveNext
>    If mrstClients.EOF Then
>        cmdLast.Enabled = False
>        cmdNext.Enabled = False
>        mrstClients.MoveLast
>    Else
>        cmdLast.Enabled = True
>        cmdNext.Enabled = True
>        mrstClients.MovePrevious
>    End If
>
>    Call DisplayRecords
>
>End Sub
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
>
>>From: "Joseph Valdez" <e-valdez@a...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] Re: Resultset that can be edited from a select statement
>>Date: Sat, 15 Mar 2003 16:10:59 -0800
>>
>>This was an excellent reply as well as comprehensive in content!
>>What if the control I am using is a listbox? I'd like to be able to double
>>click the row so the detail form would show? Also, is it possible to 
>>return
>>the result in a subform with listbox so that right and there I will be 
>>able
>>to edit the data directly to the table or database.
>>
>>Thanks for your reply!
>>
>>-----Original Message-----
>>From: Bob Bedell [mailto:bobbedell15@m...]
>>Sent: Saturday, March 15, 2003 9:17 AM
>>To: Access
>>Subject: [access] Re: Resultset that can be edited from a select
>>statement
>>
>>
>>Hi Joseph,
>>
>>Here are a few ideas you might find useful. Three subs are included:
>>
>>1.  Form_Open opens a read-writeable recordset and stores it in a
>>     module level variable. A Select query named qryClients pulls records
>>     from tblClients to fill the recordset. Notice the connection Mode
>>     and recordset LockType and CursorType properties used to make the
>>     recordset updateable (though mine isn't the only configuration of
>>     these properties that will return a r/w recordset. There are
>>     others)
>>
>>2.  DisplayRecords()is then called to populate the unbound controls on
>>     the form with the data from the underlying recordset(unbound just
>>     means that the form's record source property and the controls'
>>     control source properties are set at run-time via code)
>>
>>3.  cmdUpdate_Click() is the code behind an Update command button on
>>     the form that lests you commit edits you've made to the form's
>>     data back to the datasource. The recordset's Update method is used,
>>     then the DisplayRecords sub is called again to display the updated
>>     records in the form.
>>
>>The form also has Add, Delete, Find and Close buttons on it, as well as
>>First, Last, Next and Previous navigation buttons. Clicking the Add
>>button opens a second form to add a new record to the data source. This
>>second form has a Save and Cancel button on it. Find opens a third form 
>>for
>>multi-parameter searches. Thats the app in a nut shell.
>>
>>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>
>>Dim mrstClients As Recordset
>>
>>Private Sub Form_Open(Cancel As Integer)
>>     Dim cnnClient As New ADODB.Connection
>>     Dim strProvider As String
>>     Dim strDataSource As String
>>
>>     ' Create a connection
>>     strProvider = "Microsoft.Jet.OLEDB.4.0"
>>     strDataSource = CurrentProject.Path _
>>         & "\dbOutpatient.mdb."
>>
>>     ' Enable read and write
>>     cnnClient.Mode = adModeReadWrite
>>
>>     ' Open connection
>>     cnnClient.Open "Provider = " & strProvider & _
>>         "; Data Source = " & strDataSource
>>
>>     ' Create and open recordset
>>     Set mrstClients = New ADODB.Recordset
>>     mrstClients.Open "qryClients", cnnClient, adOpenDynamic, _
>>         adLockOptimistic, adCmdTable
>>
>>     ' Enable and disable command buttons
>>     txtCaseNumber.SetFocus
>>     cmdFirst.Enabled = False
>>     cmdNext.Enabled = True
>>     cmdPrevious.Enabled = False
>>     cmdLast.Enabled = True
>>
>>
>>     Call DisplayRecords
>>
>>End Sub
>>
>>Private Sub DisplayRecords()
>>
>>     txtCaseNumber = mrstClients!CaseNumber
>>     txtClientLastName = mrstClients!ClientLastName
>>     txtClientFirstName = mrstClients!ClientFirstName
>>     txtDOB = mrstClients!DOB
>>     txtSSN = mrstClients!SSN
>>     cboCounselor = mrstClients!CounselorID
>>     txtDOA = mrstClients!DOA
>>     cboReferralSource = mrstClients!ReferralSourceID
>>     cboClientType = mrstClients!ClientTypeID
>>     cboProgram = mrstClients!ProgramID
>>     txtDOD = mrstClients!DoD
>>     cboDischargeStatus = mrstClients!DischargeStatusID
>>
>>End Sub
>>
>>Private Sub cmdUpdate_Click()
>>     Dim intMsg As Integer
>>     Dim strMsg As String
>>     Dim intIcon As Integer
>>     Dim strTitle As String
>>
>>     strMsg = "Are you sure you want to save this information" _
>>	& vbCrLf
>>     strMsg = strMsg & "for a new client?"
>>     intIcon = vbYesNo
>>     strTitle = "Outpatient Database"
>>
>>     intMsg = MsgBox(strMsg, intIcon, strTitle)
>>
>>     If intMsg = vbNo Then
>>         Exit Sub
>>     End If
>>
>>     With mrstClients
>>         !CaseNumber = txtCaseNumber
>>         !ClientLastName = UCase(txtClientLastName)
>>         !ClientFirstName = UCase(txtClientFirstName)
>>         !DOB = txtDOB
>>         !SSN = txtSSN
>>         !CounselorID = UCase(cboCounselor)
>>         !DOA = txtDOA
>>         !ReferralSourceID = UCase(cboReferralSource)
>>         !ClientTypeID = UCase(cboClientType)
>>         !ProgramID = UCase(cboProgram)
>>         !DoD = txtDOD
>>         !DischargeStatusID = UCase(cboDischargeStatus)
>>         .Update
>>     End With
>>
>>     Call DisplayRecords
>>
>>End Sub
>>
>>If you ever stumble across a book by Gordan Padwick called "Creating
>>Microsoft Access 2000 Solutions: A Power User's Guide", it has one
>>of the best samples of an unbound ADO app in it I've ever run across.
>>I didn't even read much of the book. Learned most of what I wanted to
>>know just from playing with the app code, which contains all the
>>functionality I've described above and then some.
>>
>>Best,
>>
>>Bob
>>
>>
>> >From: "Joseph Valdez" <e-valdez@a...>
>> >Reply-To: "Access" <access@p...>
>> >To: "Access" <access@p...>
>> >Subject: [access] Resultset that can be edited from a select statement
>> >Date: Sat, 15 Mar 2003 10:24:49 -0800
>> >
>> >I would like to be able to edit the contents of data coming back from a
>> >resultset and not just display it. I have the impression that if a 
>>select
>> >query is used, it only is displayed in a form it cannot be edited any
>> >longer-it only display what is on the database. I would like to be able 
>>to
>> >change the data in the screen also and save the changes back to the
>> >database.
>> >
>> >Any suggestions from the Access gurus are more than welcome.
>> >
>> >Joseph
>> >
>> >
>>
>>
>>
>>
>>
>>
>>
>> >From: "Joseph Valdez" <e-valdez@a...>
>> >Reply-To: "Access" <access@p...>
>> >To: "Access" <access@p...>
>> >Subject: [access] Resultset that can be edited from a select statement
>> >Date: Sat, 15 Mar 2003 10:24:49 -0800
>> >
>> >I would like to be able to edit the contents of data coming back from a
>> >resultset and not just display it. I have the impression that if a 
>>select
>> >query is used, it only is displayed in a form it cannot be edited any
>> >longer-it only display what is on the database. I would like to be able 
>>to
>> >change the data in the screen also and save the changes back to the
>> >database.
>> >
>> >Any suggestions from the Access gurus are more than welcome.
>> >
>> >Joseph
>> >
>> >
>>
>>
>>_________________________________________________________________
>>Protect your PC - get McAfee.com VirusScan Online
>>http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
>>
>>
>>
>>
>>
>
>
>_________________________________________________________________
>The new MSN 8: advanced junk mail protection and 2 months FREE*  
>http://join.msn.com/?page=features/junkmail
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to 
>unsubscribe send a blank email to 


_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8. 
http://join.msn.com/?page=features/junkmail

Hi!

Access queries are "live", that's to say you can edit data. PROVIDED THAT, the 
query returns an updatable recordset. Usually, recordsets are updatable if all 
tables have PKs and are joined using an inner join (not an outer (left or 
right) join). Sometimes you can come up with a recordset that SHOULD be 
updatable, but it is not: in this case, it means that Access engine assembled 
data in a way that makes the whole result not updatable.
But, concerning the possibility of updating data, that's easy, just include 
your query result into a (sub)form with the datasheet look and you'd be OK.

HTH

    Claudio de Biasio
      Team 97 S.r.l.


Quoting Joseph Valdez <e-valdez@a...>:

> I would like to be able to edit the contents of data coming back from a
> resultset and not just display it. I have the impression that if a
> select
> query is used, it only is displayed in a form it cannot be edited any
> longer-it only display what is on the database. I would like to be able
> to
> change the data in the screen also and save the changes back to the
> database.
> 
> Any suggestions from the Access gurus are more than welcome.
> 
> Joseph
> 
> 
>