Results 1 to 10 of 10
  1. #1
    USMCTL is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    12

    How to create subform that will input data in main form?


    I am making a user friendly database for some coworkers who never used access. So forms would be much more simpler and easier for them if they just have to click what they need. I have a main form that is used to change criteria in and run query. I want my users to be able to choose between different manufacturers, brands, families, package sizes, and product types based on report they need. There are so many different types. So far, I have text boxes on my main form. I want to be able to select that box and another form pops up listing the several options to choose from. then the user be able to click ok and it refresh the main form with the option chosen on the subform. How do I do this? Thanks for any help!

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The easiest way to restrict your entries without much VBA code or macros is to replace your text boxes with (cascading) combo boxes.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    USMCTL is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    12
    Quote Originally Posted by Gicu View Post
    The easiest way to restrict your entries without much VBA code or macros is to replace your text boxes with (cascading) combo boxes.

    Cheers,
    I would do a combo box but id like it to be on a separate form , or a sub form since the combo boxes will display about 50-1000 different choices, for each question (manufacturer, brand, family..ect). Then when they select from that combo box, I would like it to repopulate automatically into the first main form.

    On main form it will have a separate place on the side that has the criteria options. I have one for manufacturer, brand, family, package size, flavor, and item. Since the combo boxes have so many choices, I would like them to be able to click on either option, it opens a subform for each, and will list all options, then when they select the criteria (like code 0010 and code 0020 for manufacturer) I would like it to automatically copy to the main form under manufacturer.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    That is exactly how cascading combo-boxes are usually set up in order to limit the entries progresively as the user makes choices or sets criteria. WHat is the benefit of opening another form if you can limit the combo on the present form the same way you would do it on the second. In any case lookup Docmd.OpenForm as that is what you need to open another form, examine the arguments as you can go to the record you want in at least three ways (apply filter, using the Where condition or using the OpenArgs).

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So if I pick a parent thing (manufacturer) there will still be maybe 1000 sub items?
    You can do it with forms if you prefer though. If you have a mix of sources (different tables/queries) that you're getting list values from then perhaps one approach would be to use a staging table (temporary data, not temporary table). Perhaps a double click event on the textbox (so that it doesn't open the list form just because you click/enter on it) and open the list form based on the query you need to populate the form. Not clear if you want to select multiple values/records or just one. If the latter, you'll have to loop over the selections. Regardless, my thinking is that you append the first selection to the temp table and update the rest of the fields as you go. I don't really see that working with multiple selections though as you'd have say 3 records. Where do you put the other selections if there are only 2?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    USMCTL is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    12
    I did what you said, I made a combo box, but cannot select multiple as a list box would. A list box would work with multiselect. Is there a way then to make the box smaller in main form view to show one record, but when I click on it, it opens up like a pop up? I see I can make a macro for 'on click' 'open table' but that will not let me select items from the table. Just opens it. Cannot find an option to make the data im selecting from like a popup.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Docmd.OpenForm is your friend. For example in the DoubleClick event of a text box on the main form you add the Docmd.OpenForm ......... to open a popup form (you can use acDialog) populated by your query of choice;in the Unload event of the popup add your code to use that Forms!frmMainForm!txtCallingPopup=Me.txtSelection.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    I am making a user friendly database
    Having a user select 0010 or 0020 is hardly user friendly I would have thought?
    I'd be displaying the manufacturer and storing the ID for that manufacturer record?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    USMCTL is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    12
    Quote Originally Posted by Welshgasman View Post
    Having a user select 0010 or 0020 is hardly user friendly I would have thought?
    I'd be displaying the manufacturer and storing the ID for that manufacturer record?
    Yes! I agree I have so many manufacturers, so just displaying codes is hard on the eyes. I want it to display the manufacturer name like you said, but in my pre-made table, it only has codes. I was wondering how to do that. Im thinking i need to make a new table that has the names attached and connect the relationship to the pre-made table and my new one and just pull in the names. Havent got there yet, but i do agree! I still have so many though that displaying them all on the main form is too much. I did get the list box on a separate form, now trying to pass data from that table to my text box. I am looking at what he said above and going to try it.

  10. #10
    USMCTL is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    12
    I did end up doing a on click, open form that has the list box for manufacturer. So I will put Forms!frmMainForm!txtCallingPopup=Me.txtSelection. in the criteria for my main form box?

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

Similar Threads

  1. Replies: 5
    Last Post: 03-29-2020, 11:23 PM
  2. Replies: 11
    Last Post: 04-19-2019, 08:16 AM
  3. Replies: 13
    Last Post: 03-02-2018, 12:04 PM
  4. Replies: 2
    Last Post: 06-17-2010, 04:15 PM
  5. Replies: 1
    Last Post: 06-14-2010, 02:31 AM

Tags for this Thread

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