Results 1 to 7 of 7
  1. #1
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150

    Not selecting a record upon opening a form


    I have a continuous form with subforms. The subforms are tied by CustomerID of main form. Upon opening the form, the form will show all customers, however, the subform shows the data that is tied to the very first record. I want to have the subform to show data that sums of all the customers if no record is selected, so I'll insert another subform on top of the other and set visible to true if no record is selected, otherwise it'll be false. The question is, how do I not select a record at all? Is there a form property or I have to do it via VBA?

  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
    Options include moving the main form to a new record when it opens and setting the record source to not display a record. When the user selects a record from a combo or whatever, change the record source to that record.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Do you mean add something like this to On Open event?
    DoCmd.GoToRecord , "", acNewRec

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If I understand you right your subform in question is summing data for either all customers on open or one specific customer if you have a record selected in the main form. To achieve this you simply need to remove the linking between the main form and this subform (the master/child link on CustomerID) and base the subform on a totals query that includes all customers. In the OnCurrent event of the main form you can then switch the subform's recordsource to include the selected customer (either by changing it to a new query that uses the CustomerID as a parameter of by adding a Where clause to the existing one). Alternatively you have to subforms with the right record source (one for all customers and one for selected customer) and switch them by using the SourceObject property of the subform control.

    Cheers,
    Vlad

  5. #5
    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 LonghronJ View Post
    Do you mean add something like this to On Open event?
    Yes, or the load event (open may be too soon). I'm more likely to use the other option, as I don't normally load a form with all records.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Paul, what's the other option? I can't get
    Code:
    DoCmd.GoToRecord , "", acNewRec
    to work On Load event. I have Allow Additions set to Yes, even though, it's not type of form that allows the user to enter additional data. Is there not a simple VBA code that would deselect a record if one is selected?

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, the form has to display something. I can't think of any way to display nothing on a bound form, other than a new record. Easy on an unbound form, which is of course a lot more work. I assume the underlying record source won't allow a new record? I might have the user select a record on a form preceding this one, and open this one to the desired record:

    http://www.baldyweb.com/wherecondition.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Selecting a record before opening a form
    By ManuelLavesa in forum Access
    Replies: 7
    Last Post: 08-09-2017, 04:52 PM
  2. Replies: 5
    Last Post: 01-24-2015, 12:59 AM
  3. Selecting a record from a sub form
    By pfales in forum Programming
    Replies: 19
    Last Post: 06-10-2013, 04:32 AM
  4. Replies: 9
    Last Post: 09-16-2011, 03:52 PM
  5. Replies: 3
    Last Post: 05-02-2011, 07:34 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