Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002
    Thanked 0 Times in 0 Posts

    Selecting Displayed Sub Form Records (Access 2007)

    Hi All
    I am trying to update records that are displayed on a bound subform. On occassions I i filter the records displayed on the subform and i would like to only update these records using a query. This is where i am stuck. The filter value and field i filter on is variable and i would like to only update those visible records.

    Thanks in advance as i am sure there must be a solution.



  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Mt Macedon, Victoria, Australia
    Thanked 45 Times in 44 Posts

    Re: Selecting Displayed Sub Form Records (Access 2007)

    In this case, you will need to perform the update using an SQL string generated via code. Are you familiar with (and comfortable with) SQL?

    Create an update query that would update all Records in the subform. (I imagine this is not all records in the table, and the query will have a criterion that references the main form.)
    Switch to SQL view and copy all the SQL.

    In the event that you want to trigger the update...perhaps you click a command button.
    Go to the code behind the button and write this.

    Dim SQL as string

    SQL = ... then past in the SQL you copied earier. Remove any line breaks so it is all one one line, and put double quotes around it all.

    What was a Criterion in the query grid becomes a Where clause in SQL.

    Before we go any further, can you post back with the SQL string you have at this point, as it will need a little bit of modification before it can be used.

    Where this is heading is that the filter you applied to the subform can be added to the SQL as a second where clause, then the SQL can be executed.

Posting Permissions

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