Results 1 to 7 of 7
  1. #1
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182

    Using a Value from a Combobox to Autofill remaining fields.

    Hello.



    I have a form (frmFood) that displays "IncidentNumber" "FoodName" "Ingredients" "FoodCategory" "UtensilNeeded" and "NumberServed"

    I populate frmFood with different foods and connect them to an "Incident Number" which is also connected to a frmChef and frmActivity.

    What I want to do is type in a value in "FoodName" in my frmFood. And then I want to have "Ingredients" "FoodCategory" and "UtensilNeeded" autofilled. For example, if I type in "Apple Pie (FoodName)", I want to autofill the form with "Apples(Ingredients)" "Sweet(FoodCategory)" and "Spoon(UtensilNeeded)". How do I do this?

    I have created a separate table called tblAutoFillFoodList. This table lists all of the foods known to mankind, as well as the ingredients, food category, and utensilneeded. I am at a loss on how to do this. I also want the information that is autofilled under frmFood to be represented in a table called tblFood. I know that tblFood and tblAutoFillFood will have shared data. But that does not matter in my case. I am aware that my database could be made much more efficiently, but considering it is ten years old, I do not want to go poking around in there.

    So if someone can just tell me how to make a form autofill using a combobox connected to a table, I would appreciate it.

    ------------------------------------------

    In order to get around this, I have been hard-coding to get the auto-fill results I want by using IF-THEN statements. For example, "If me.txtFoodName=ApplePie Then me.Ingredients=Apple". However, this is also inefficient as I have a lot of FoodNames that I want to be able to autofill.

    Thank you

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    There are a number of ways to do this depending on what you are going to be doing with the data once it is selected from the comboboox.

    If the fields are for display purposes only and if they all come from different tables, then create a subform with those fields on it, link it to the combobox and in the AfterUpdate of the combobox requery the subform.

    If the fields are part of your record source for the form, then all you need is to set the filter on the form.

    Otherwise you can manually populate them in the AfterUpdate of the combobox, such as
    Me!field1=Me!combobox.Column(1)
    etc

  3. #3
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Sorry... But I am confused.

    Also, I shouldve mentioned that frmFood is actually already a subform. subfrmFood.

    I have tried to do the manual population via the AfterUpdate of the combobox. But this has not been working. I receive an error.

    Currently, the code I have is:

    Private Sub cmbFood_AfterUpdate ()

    If Me.cmbFood = tblAutoFillFoodList.FoodName Then
    Me.Ingredients = tblAutoFillFoodList.Ingredients
    End If.

    I have also tried

    If Me.cmbFood = tblAutoFillFoodList.Column(1) Then
    Me.Ingredients = tblAutoFillFoodList.Column(2)
    End If.

    What am I doing wrong here?

    I would prefer to use the manual population via AfterUpdate as opposed to the other suggestions you made as this is the easiest for me.

    Im still new to access

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What is "tblAutoFillFoodList"? When you are on a form and need to refer to a field, either that field is already on the form (Me) or else you have to go to a table/query and get it.

    What is the IF for?

    This is what I posted above: Me!field1=Me!combobox.Column(1)

    If "me!field1" is on a subform then it must be "Me!subformname!field1"
    Column count starts at zero so Column(1) is the second column

  5. #5
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    tblAutoFillFoodList is the table that I want to create that will list the 100-150 different foods for my database. I want the combobox in my subform to be linked to tblAutoFillFoodList. So when I select a value in my combobox, my subform autofills the rest of the fields from data taken from tblAutoFIllFoodList

    I thought I needed the IF. For example, IF subform.FoodName = tblautofilfoodlist.foodname, THEN the remaining fields in my subform will equal the records in tblAutoFIllFoodlist

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you have fields that need to be on the main form, fine. If not, you don't need the subform. They key would be that those main form fields are not repeated on the subform.

    Base the subform on a query that references the combo, which you will have on the main form. Use the combo AfterUpdate event to requery the subform to populate those controls with the data from the query. At least, that's how I see it, based on my interpretation of your posts.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I want the combobox in my subform to be linked to tblAutoFillFoodList
    The combobox can be "linked" to any table(s), that is its Row Source property.

    tblAutoFillFoodList is the table that I want to create
    How/where are you creating this table? Does it have anything to do with this combobox or the subform?

    I thought I needed the IF
    It is the sequence of events that you aren't getting, I think (I am quite confused by your posts!).
    1 - select value list in combobox
    2 - populate fields on subform as described above
    Where would it be different? I presume the subform doesn't have a record source (otherwise you could requery it based on the combobox).

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

Similar Threads

  1. Replies: 7
    Last Post: 08-29-2016, 09:34 AM
  2. AutoFill Based on ComboBox Selection
    By chelseagardens in forum Forms
    Replies: 3
    Last Post: 08-08-2013, 01:14 PM
  3. Replies: 7
    Last Post: 10-31-2011, 02:21 PM
  4. Autofill Text Box from Cascading Combobox
    By desibabu90 in forum Forms
    Replies: 14
    Last Post: 06-28-2011, 11:31 AM
  5. ComboBox Autofill
    By t_dot in forum Forms
    Replies: 2
    Last Post: 08-19-2010, 06:18 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