Results 1 to 9 of 9
  1. #1
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58

    Opening a sub form set to new record.


    Hi,

    I have a main form(FrmFMInput) and 2 sub forms (SubfrmSaleDetails and SubfrmSMInput).
    There is a combobox "Title" in main form which has 2 options, "F&I Manager" and "Sales Manager".

    When I select F&I Manager in Title, SubfrmSaleDetails should open and when I select Sales Manager, SubfrmSMInput should open new record, so i can enter data.

    Can somebody help me with the code for this?

    Thanks in Advance,

    Mismag

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I would have a main form with 2 tabs for the subforms. The combobox would activate the proper tab, showing that subform. The subfrmSMInput would be opened with Data Entry=Yes.
    If this sounds right, post your DB and I (or we collectively) can take a shot at it.

  3. #3
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    Hi Dave,

    Here is the db that i created.
    I am very new to Access. So please excuse me if there are any silly mistakes.
    Thanks for taking time to help me.

    Regards,
    Mismag
    Attached Files Attached Files

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    OK, let's start with this version. I re-arranged your relationships a bit and changed some key names, like _PK suffix for primary key and _FK suffix for foreign keys. I made all relationships refer to keys instead of text files.
    I don't totally understand your business model, especially how sales manager and manager relate to the sales and dealership tables and to each other. One example, there's no name for the Sales Managers...
    I don't know what an F&I manager is vs a sales manager or how that reflects on their relationship to the business.
    Dealership Data davegri-v01.zip

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Consider-
    If you want to expose only 1 subform either way, put one subform control on the main form and load the appropriate subform according to the combo selection. You do that by setting the subform control source object property upon combo after update event.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    Micron,

    If you want to expose only 1 sub form either way, put one sub form control on the main form and load the appropriate sub form according to the combo selection. You do that by setting the sub form control source object property upon combo after update event.
    I have made the changes as you suggested but now when I select "Sales Manager" in the combo box, the sub form doesn't open a new record. It is showing the previous record I entered.
    How to open a new record in the sub form?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    When I select F&I Manager in Title, SubfrmSaleDetails should open and when I select Sales Manager, SubfrmSMInput should open new record, so i can enter data.
    I interpreted this to mean you wanted 1 sub with one selection and another sub with a different selection. That was reinforced by davegri's suggestion to use 2 tabs. I hope that at least that much is correct. As for opening to a new record, did you see the suggestion in post 2?
    The subfrmSMInput would be opened with Data Entry=Yes.
    That might be simpler if you always want that form to open to a new record. If not, then you need an alternative. Could be as simple as using new record button on navigation control, or a directive on the calling form to open either as edit OR new record, or same button on opened form. Many ways to do things.

    If you're still stuck after attempting any of these, maybe post a new db with a status update.

  8. #8
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    As for opening to a new record, did you see the suggestion in post 2?
    The subfrmSMInput would be opened with Data Entry=Yes.


    That might be simpler if you always want that form to open to a new record.
    Thanks Micron.

    At first I couldn't find the option for Data Entry. But later when i opened the subform in design mode and looked under properties, I could.

    Now my forms work as expected.

    Thanks a lot Micron and Davegri.

    Regards,
    Mismag

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    welcome; glad you solved it.

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

Similar Threads

  1. Opening a new form, with the same record ID
    By simon123 in forum Forms
    Replies: 3
    Last Post: 07-23-2018, 08:46 AM
  2. Replies: 5
    Last Post: 01-24-2015, 12:59 AM
  3. Opening a form for a particular record.
    By 86rainey in forum Forms
    Replies: 6
    Last Post: 06-24-2012, 10:14 AM
  4. opening a form with last one record.
    By cap.zadi in forum Forms
    Replies: 1
    Last Post: 01-17-2012, 12:45 AM
  5. Replies: 9
    Last Post: 09-16-2011, 03:52 PM

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