Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    6

    Unanswered: Newbie. Help with updating query on two subforms

    Hello.

    I have been banging my head on this for too many days now. Decided to see if I could get some assistance with MS Access 2003.

    I have a main form called DeliveryCatalog
    DeliveryCatalog pulls from Table DeliveryCatalog.

    The main form DeliveryCatalog has two List boxes.
    It has also uses the "On Current" event to do the following.
    Me.List127.Requery
    Me.List134.Requery

    To summarize the main form DeliveryCatalog is used to iterate
    over the records in the Table DeliveryCatalog. Each time a new record is loaded I use On Current to refresh the data in the two list boxes. List127 shows all records similiar the current record that are marked "active". List134 shows all records similiar the current record that are marked "obsolete".


    Now here is my problem. I have also added in a subform called "DeliveryCatalog_ReadOnly". What I want to do but have been unable to figure it out is to do the following. If a record is "Clicked" in List127 I want the subform fields to update based on the record selected. I also want If a record is "Clicked" in List134 I want the subform fields to update based on the record selected.

    Can someone please tell me what I need to do?
    Thanks!!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think you would have to employ some code for your AfterUpdate events for those listboxes. They would need to set the subform's recordsource to some SQL that interrogates your status (obsolete or active) field.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    An example of the code "similar" to ST's mentioned is:

    1. Make your listboxes so the first column is the ID column (ie. the autonumber field) or field that joins the tables. You can set the width to 0 for this column if you don't want to show it. This is the field which would join the records in the listbox to the DeliveryCatalog_ReadOnly table.

    2. In the Afterupdate event of the listbox, add code such as the following (note: this is ADO and I typed it from memory)....
    if isnull(me!List127) then exit sub
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from DeliveryCatalog_ReadOnly where IDField = " & me!List127 & ""
    rs.open strSQL,currentproject.connection,adopendynamic, adlockoptimistic
    rs!FieldToUpdate = "XXXXX" (where "XXXXX" is the value or variable you want to update it with
    rs.update
    rs.close
    set rs = nothing
    me.listDeliveryCatalog_ReadOnly.requery (<-- note that I made the DeliveryCatalog_ReadOnly into a listbox on the form. If it's not a listbox, but instead the recordsource for the entire form, you'll need to go the form's record (that matches the ID field selected in the listbox), and then update the value on the form.) So steps 2.... would look like this:

    2. In the Afterupdate event of the listbox, add code such as the following....
    if isnull(me!List127) then exit sub
    me.IDField.setfocus
    docmd.findrecord me!List127
    me.myfieldtoupdate.setfocus
    <- Now just update that field with the value you want. You can do this automatically by....
    me!MyFieldToUpdate = "XXXXX" (or again, a variable which holds the value.

    or
    2. In the Afterupdate event of the listbox, add code such as the following....
    you can set the recordsource of the form to show just the data in the DeliveryCatalog_ReadOnly table based on the IDField criteria.
    if isnull(me!list127) then exit sub
    me.recordsource = "Select * from DeliveryCatalog_ReadOnly where IDField = " & me!List127 & ""
    <- and then your code to update the field you need to update with whatever value.
    Last edited by pkstormy; 03-30-09 at 23:23.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Mar 2009
    Posts
    6
    Hi pkstormy and StarTrekker.

    I think I may not have presented my information clearly.

    I have a mainform called DeliveryCatalog. DeliveryCatalog has
    many fields, but in particular has a List127 and a List134. The
    main form also has a subform called DeliveryCatalog_ReadOnly.

    What I want to do is the following.

    (1) If a user clicks a record in List127, I want the "On Click" event to
    fire. This event will access the primary key of the record clicked in List127 and then display this information in the subform DeliveryCatalog_ReadOnly

    (2) If a user clicks a record in List134, I want the "On Click" event to
    fire. This event will access the primary key of the record clicked in List134 and then display this information in the subform DeliveryCatalog_ReadOnly



    After reading into your replies I tried putting this in this code, which of course did NOT work.

    Private Sub List127_Click()
    Me.DeliveryCatalog_ReadOnly.Form.Filter = "Select * from DeliveryCatalog WHERE PK = " & List127
    Me.DeliveryCatalog_ReadOnly.Form.FilterOn = True
    End Sub

    Thanks
    Last edited by subformer; 03-31-09 at 12:22.

  5. #5
    Join Date
    Mar 2009
    Posts
    6
    Well I ended up making the subform become a pop up form.
    Not exactly what I wanted but I can get it to work this way.

    Thanks for the help..

    bg

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Me.DeliveryCatalog_ReadOnly.Form.Filter = "Select * from DeliveryCatalog WHERE PK = " & List127

    Should be...

    Forms!MyMainFormName!DeliveryCatalog_ReadOnly.Reco rdSource = "Select * from DeliveryCatalog WHERE PK = " & Forms!MyMainFormName!List127 & ""

    (Just make sure you're returning the right List127 value and matching it to the right field (ie. PK).
    Last edited by pkstormy; 03-31-09 at 22:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, don't apply a filter, reset the Record Source property of your subform.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •