Conference bump::msaccess

1630.0. "update subform with 'pass-through' query" by HYLNDR::DUFAULT () Thu Feb 06 1997 16:02

    Hi,
    
    I'm trying to redisplay a subform that has a recordsource =
    'pass-through' query. Details:
    
    SQL-SERVER V6.5 contains 'upsized' tables and stored procedures.
    
    MSAccess V2.0 Client with 'pass-through' queries.
    
    Went to the Knowledge Base and found that in order to use an SQL
    pass-through query as a subforms record source you have to dynamically 
    set the subforms recordsource in the main form's Load event. Did this
    and it worked fine...records come back from the server and load into
    the subform
    
    HOWEVER,
    
    On the main form is a list box that acts as a filter. If you click on
    the list box again, it essentially executes the 'pass-through' query
    again only this time with a parameter which filters down the number
    of records returned. 
    
    PROBLEM,
    
    The subform never refreshes with the new records returned after the
    'click' from the list box. So all you see are the original records that
    the query loaded on the initial 'Load Event'. I wonder what I'm doing
    wrong?
    
             mark 
    
    
      

 Have you checked if your code does work on a local main/subform combination
 for a local Access table?

 Did you do a form.Requery?

 Theo

    I think I know what you mean, and it worked when I had the LinkChild
    Fields and Link Master Fields correctly filled for a local table, but 
    here's the kicker...Knowledge base says that these fields must be blank 
    if you are using a 'pass-through' query as the recordsource for the
    subform. So I had to delete them, in fact, if I leave them filled, the 
    original load will NOT work. I can't figure this out! Here's the KB # if 
    you want to read it (Q112746). Thanks for the input!
    
     mark

   You may perhaps be better off to load a combobox with the selection
   value for the subform and then use the AfterUpdate event to find out
   what value is used and then use that to do a pass through query for the
   one or few records that satisfy this selection and display those.

   You can dynamically decide what the subform should show by setting its

   [subform control].Form.RecordSource="query string" (or query name)

   This way you can play for master/child link field yourself.

   Theo