1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Populating a subform from combo box entries

Discussion in 'Business Applications' started by Charmian, Feb 6, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    I have a form that has a few combo boxes. The list presented in the 2nd combo box is determined by the input in the first combo box. This works. Now I need to have a subform that is populated when all the combo boxes are filled in.
    Eg: They choose a line - depending on the line only teams in that line appear in the 2nd combo box drop down. Then they may type in a part number in a text box - but this could be blank - but that's a problem for later.


    1. I can get the subform to populate from the entries of the combo box, but only on the NEXT attempt. The first time I go into the form and populate the combo boxes nothing happens in the subform. If I close the form and go back into it, the subform is populated with the information that was previously in the combo boxes - the combo boxes are blank on going into the form a second time. I have this subform set up as the source object being a query. There is an event setting - On enter and on exit. Should I be using one of those to get it to populate only when the current info is entered?
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You should use the Combo's After Update Event to "requery" the subform.
    the Syntax is
    me.subfromname.Requery
    where subfromname is the actual name of your subfrom, which the VBA Editor will provide.
     
  3. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    Will try, another combo problem that I've just seen. When the drop down occurs on the form the user picks one and that populates the block - how do you get other columns to also show. EG I have the partnumber in the box that they have picked, but would also like to display the description while they are on that screen
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I am just about to describe that for another poster.
    Basically you either use the Combo's SQL query or an actual query to concatenate the 2 columns using a new Column Heading to replace the other 2 columns ie.

    ProdDesc: [Product] & " - " & [Description]

    where you use your proper field names in place of the Product & Description.
     
  5. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    HI,
    The error I'm getting now is
    Me.Q_Emergency2.Requery is method or data member not foundd

    I've tried it with a few variations but he subform doesn't get updated. The query that is used to create the subform is:
    SELECT OPERATIONCG.opsname AS Operation, WorkstationCG.wsname AS [Works Station], asset.assetno, Asset.EQSerialNo, Asset.Torque AS [Asset Torque], Asset.Setting AS [Asset Setting], Operationcg.OPSTorque AS [Operation Torque], Operationcg.OPSSet AS [Operation Setting]
    FROM ((Asset INNER JOIN WorkstationCg ON WorkstationCG.wsid=Asset.CurrentWSID) INNER JOIN operationcg ON Operationcg.opswsid=workstationcg.wsid) INNER JOIN TEAMCG ON TeamCG.teamid=WorkstationCG.WSteam
    WHERE Asset.Assetno=Forms!frm_TechnicianLineAssist!Text35;


    And the VB code is:

    Private Sub Text35_AfterUpdate()
    MsgBox "in Text35_afterupdate" This happens
    Me.[Q_Emergency2 subform].Requery
    MsgBox "in Text35_afterupdate after the gap subform command" This happens
    Me.Q_Emergency2.Requery This gives the error
    MsgBox "in Text35_afterupdate after the no gap subform command"
    End Sub
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, try

    Me.Q_Emergency2.Form.Requery

    Did the VBA Editor provide the name of the Subform?
     
  7. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    This is not appearing in the form even though I have made the Bound Column the concatenated one; if I run the query stand alone - it works.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The bound column should normally be the Key field.
    What column number is the new column and what "Width" is it set to?
     
  9. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    The Editor did give me the form when I decided to use it's prompts.
    Its working now - thank you
     
  10. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    Changing the bound column didn't work - so the new column is 5cm and it does appear in the drop down. But when you click/move to the next combo box the one you chose in the previous combo box , is just the asset number not the asset & description one - or must I put that FIRST in my SELECT?

    Don't worry, OBP - I put it first in my Selection statement and whoopsie - its there - sorry, some days the blonde isn't just out of the bottle!
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    A nice bit of learning today, subform requerying, field concatenating and combo manipulation.
    Not bad Ha.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1088400

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice