Results 1 to 12 of 12
  1. #1
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453

    form display with different query calls - via form properties and VBA

    Hi



    thanks for the advice I'm now trimming down my tables.

    If I use my existing sequence a button opens a form based on a query

    frm_bannsBrideForeNameSearchResults based on query

    qry_BannsBrideForenameSearch

    because I'm using a temporary variable when I run the form it opens and shows all records with matching criteria ie 5 elizabeths.

    So I copied the frm_bannsBrideForeNameSearchResults and renamed it BannsResults intending to use the form for all search results.

    I modified by button code to

    Code:
    Private Sub btn_Search_Click()
    
        TempVars.Add "varBrideForenames", Me.txtBrideForenames.Value
        DoCmd.Close acForm, "frm_BannsBrideForenameSearch"
        DoCmd.OpenForm "frm_BannsResults", acNormal, "", "", , acNormal
        Forms!frm_BannsResults.RecordSource = "qry_BannsBrideForenameSearch"
    End Sub
    It seems to work but visually it's not what I want.

    With my original method I would see perhaps 15 or 20 records on the page with a scroll bar whereas using this method I only see one record with a scroll bar to see the others.

    But the two forms have the same properties apart from the original has a record source listed and the new one doesn't

    Where have I gone wrong?

    thanks

    Ian

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is the detail section too large to display multiple records on one page?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi

    not sure what you mean but the properties for both forms are below one is a copy ans paste of the other in navigation panel

    thanks

    Ian

    Detail proporties form 1

    height 1.825

    auto height yes

    can grow no

    can shrink no

    Detail proporties form 2:

    height 1.825

    auto height yes

    can grow no

    can shrink no

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here, or post a picture? I'm not clear on what you mean by "I only see one record with a scroll bar to see the others".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Good point, sorry

    OK I've cut down the database and attatched it as a zip.

    you will see 4 forms

    frm_BannsBrideForenameSearch_1

    frm_BannsBrideForenameSearchResults_1a


    and

    frm_BannsBrideForenameSearch_2

    frm_BannsResults


    if you open frm_BannsBrideForenameSearch_1

    and type in E followed by search you will see a list of records

    If you do the same with

    frm_BannsBrideForenameSearch_2


    THen you will see only one record. you can view the other records via the scroll bar.

    I would like the second Form to show result like the first form. ie more than one record visible.

    The forms as far as I can see are identical as is the query

    The only difference is that in

    frm_BannsBrideForenameSearchResults_1a the form contains a source query

    and in the second example the recordset query is loaded via the search button VBA.

    Thanks for the assist

    Ian
    Attached Files Attached Files

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Haven't tried to download or open your db, but if you set the recordsource for an object at run time, you usually have to requery it, otherwise it doesn't reflect the change. Did you try that?
    Private Sub btn_Search_Click()

    Code:
    TempVars.Add "varBrideForenames", Me.txtBrideForenames.Value
          DoCmd.Close acForm, "frm_BannsBrideForenameSearch"
          DoCmd.OpenForm "frm_BannsResults", acNormal, "", "", , acNormal
          Forms!frm_BannsResults.RecordSource = "qry_BannsBrideForenameSearch"
      Me.Requery
    End Sub
    If it's possible that the first form is not open when this runs (maybe a user closes it manually) trying to close it will generate an error. In those cases, I include

    Code:
    If CurrentProject.Allforms("frm_BannsBrideForenameSearch").IsLoaded Then
     DoCmd.Close acForm, "frm_BannsBrideForenameSearch"
    End If
    If memory serves, you seem bent on using the wrong window open argument (acNormal instead of acWindowNormal)
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi

    tried the requery, it threw up an error an didn't change the display.

    As to acNormal and acWindowNormal I wasn't aware of any difference but it seems I should be using acWindowNormal

    Another lesson learnt

    cheers

    Ian

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Curious. I don't like tabbed view, so I normally set the sizes of my forms in design view. If you change the Border Style to Sizable at least you can stretch the form and see more records. One option I've used (with a client who likes tabbed view) is MoveSize in the open event of the form, to set the sizes I want.

    I don't think I've ever included a requery when setting a record source or row source property. In my experience, setting the source is enough.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't think I've ever included a requery when setting a record source or row source property. In my experience, setting the source is enough.
    Correct me if I'm wrong, but it can depend on when you set it and if it's set to "" on opening or the property setting has no value. If it is set on the form opening, then I agree. If after an event like OnChange, then I believe you have to, otherwise what else causes the new source to be applied? The OP is using a button click event to assign a source, but I have no idea what it was before that. Not to mention that I don't see the value in this unless all of the controls on the form are bound to the same fields regardless of the source or else their bound fields all have to change in the same event to match the fields in the new source. Unless of course the new source is basically a modification of the same query and the control sources still match the fields. In that case, it might be simpler to apply a filter rather than have a bunch of queries that you apply to the form recordsource via code. And we're not talking about control source here, right?

    Jen0dorf:
    it threw up an error
    is of no help (per my signature). It could be that this is about a subform, in which case my syntax is not correct, but I'm reluctant to comment further unless you want to elaborate. Also, I think I might have you confused with someone else who seemed bent on using the incorrect Window mode parameter after I pointed that out and provided a link to the MS website detailing all the parameters for the OpenForm method. Sorry about that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi

    I would never say doesn't work-Doesn't help. Every suggestion hint I get is helpful regardless of if it works or not! I'm rapidly realising that there are many different ways to get to the same endpoint and there is no correct or incorrect. It is always nice to read how someone else approaches a problem because in my case it's always the opposite of my solution.

    IN this case I knew about the Move Size option but it did not occur to me as I thought I had an error.

    However I take great satisfaction in the phrase "Curious" At last I posed a problem that is not a simple solution ;-)

    thanks again
    Ian

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Micron View Post
    If after an event like OnChange, then I believe you have to, otherwise what else causes the new source to be applied?
    I can only say that in my experience it's not necessary. I've created a number of dynamic search form/subforms, where the user can enter search criteria in any number of optional fields in the form, and the search results appear in the subform. The after update event of the controls call a function, which would look like:

    'code setting strSQL as appropriate to the selections made
    Me.Whatever.RecordSource = strSQL

    Setting the source applies it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Been busy with out of town company so no chance to follow up.
    You are correct - setting the record source of a form automatically causes the form to be requeried (per Microsoft):
    Changing the record source of an open form or report causes an automatic requery of the underlying data. If a form's Recordset property is set at runtime, the form's RecordSource property is updated.
    so you don't actually have to invoke the requery method of the docmd object if the form is open. If memory serves, my past usage of applying record sources after a control update always involved subforms where the master field was a combo (which has to be unbound, otherwise it causes issues). Since you cannot create master/child relationships between a subform and an unbound parent, I always requeried the subform as a habit.

    However, if I were to call a function to do this, I'd make it usable by any form and pass the form to the function, which would negate the use of Me. Otherwise I think it just ought to be a sub. So it seems that when applying a sql statement or filter query to a form, I've always had to use the requery method rather than simply apply the filter to the source property. Your way works. Thanks for the edumacation.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating Form that Calls on Query
    By dbalilti in forum Access
    Replies: 1
    Last Post: 06-21-2012, 09:27 PM
  2. Replies: 1
    Last Post: 03-01-2012, 09:06 AM
  3. Replies: 2
    Last Post: 02-15-2012, 04:04 PM
  4. Help with Form & Report that calls a Query
    By bobfin in forum Reports
    Replies: 7
    Last Post: 08-14-2010, 12:05 PM
  5. Displaying Query Properties in a Form
    By Bjar in forum Programming
    Replies: 1
    Last Post: 07-16-2008, 07:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums