Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73

    Combo Boxes

    Hi all,



    I am having difficulty getting an unbound combo box to activate the selected data in a form. I believe I need to use either the On Change or After Update event but can't seem to get these to do what I want them to.

    Any pointers please?

  2. #2
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Ok I seem to have made a bit of progress since posting the above! I have got the combo box to activate the selected record in the form, however when I try to run the macro from the builder window it brings up a Type mismatch warning.

    Should I be concerned about this?

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It is not clear what you are trying to accomplish. If you are using a combo box, the better event would be the after update event.
    activate the selected data in a form
    What does this mean?

    I don't use macros, but ANY type of error you should be concerned with.



    Maybe you could explain a little better and give examples/attach images/post your dB???

  4. #4
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    When a particular field is selected using the combo box (in this case it's component type), I want the other text box field/s on the form which relate to that record to change/upadate. I then have a subform below which dispalys the records linked to the selected field in the combo box:


    Click image for larger version. 

Name:	Combo Box Update.PNG 
Views:	50 
Size:	19.7 KB 
ID:	33487

    This all seems to be working ok now but when I run the macro under the macro tools window I get this message:

    Click image for larger version. 

Name:	Macro Warning.PNG 
Views:	48 
Size:	29.1 KB 
ID:	33488

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The security warning is because you are executing the dB from a non-trusted location. This is to try and prevent malicious code/macros from causing harm.

    What I do is create a folder or two that all dBs are in (can have sub folders for different projects), then:
    1) Open Access.
    2) Click on FILE/OPTIONS/TRUST CENTER
    3) Then find and click the button "Trust Center Settings". (on the right)
    4) Click on "Trusted Locations". (2nd from top on left menu)
    5) Click on the button "Add new location".
    6) Navigate to the folder you want to be trusted. Be sure to click on the check box "Subfolders of this location are also trusted".
    6a) Add other folders if you want more trusted locations that are not sub folders of a trusted location.
    7) Click OK until you are back to the main form.



    Sometimes a dB is 2 or 3 folders down the chain, so I create a shortcut to it and place it on the desktop so it is easy to open the dB.
    Other times the shortcut is to a (trusted) folder where several dBs are located.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree with the above for sure, but not sure I see the need for a macro to populate the subform (assuming I understand that's the goal). Have you not "linked" a common field between the main form and subform so that the subform displays records where the 2 instances of the 1 value are common? I'm referring to the Link Master and Link Child fields property between the 2 forms.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Quote Originally Posted by Micron View Post
    Agree with the above for sure, but not sure I see the need for a macro to populate the subform (assuming I understand that's the goal). Have you not "linked" a common field between the main form and subform so that the subform displays records where the 2 instances of the 1 value are common? I'm referring to the Link Master and Link Child fields property between the 2 forms.
    Populating the subform as far as I can tell is working as expected using the master/child link you mentioned above.

    I have 2 tables,

    tbl_component_type which contains component_type_ID, component_prefix and component_name;

    tbl_parts which contains part_ID, component_type_ID(FK), created_by, part_number, description, date_created and vehicle

    I have created a master form using the field component_prefix as a drop down combo box and component_name just as a text box. (component_type_ID is also on the form but not visible). It is component_name which I cannot seem to get to update on change of the combo box.

    I created a 2nd form (sfrm_parts) which I dragged into the design view of the master form, thus creating the master/child link (actually, it didn't do this itself I had to go into the properties to link them). This subform works fine on change of the combo box, its just the component_name text box on the master form which won't update???

    Hopefully this makes some kind of sense!!

    Thank you kindly for your assistance

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    its just the component_name text box on the master form which won't update
    Maybe your combo AfterUpdate event needs to requery or refresh the main form, or requery the textbox itself. Suggest you try one or both of those. Requery will reload the form, reapplying any filters, bringing in changes to records made by others, etc. Refresh will simply refresh the current records (which ought to update any calculated controls) but won't reflect changes to the current record set that have been made by others. Sometimes, it's more efficient to refresh rather than go across networks retrieving large record sets if all you need to do is affect the current set you have.
    Last edited by Micron; 04-11-2018 at 12:30 PM. Reason: added info

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If it were me, I would use the combo box after update event to push the component name into the text box.
    It appears that both controls are unbound??? (which is good)

    The row source for the combo box would be something like
    Code:
    SELECT component_type_ID, component_prefix, component_name FROM tbl_component_type ORDER By component_prefix
    Combo box settings:
    Bound Column = 1
    Column Count = 3
    Column Widths = 0, 1, 0


    The code in the after update event"
    Code:
    Private Sub Combo63()
      Me.component_nameControlName = Me.Combo63.Column(2)  ' <<--columns property is zero based
    End Sub
    Change component_nameControlName to your control name.
    If it is named "component_name", I would change it to "tbComponentName". Having a control named the same as the bound field name gets confusing.
    And the "tb" prefix is for Text Box.


    So changing the combox results in the code pushing the component name into the text box..


    BTW, "Combo63" is a poor name. Maybe use "cboComponentType" or "cboSelectComponent" for the control name??

  10. #10
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Quote Originally Posted by ssanfu View Post
    The row source for the combo box would be something like
    Code:
    SELECT component_type_ID, component_prefix, component_name FROM tbl_component_type ORDER By component_prefix
    Combo box settings:
    Bound Column = 1
    Column Count = 3
    Column Widths = 0, 1, 0


    The code in the after update event"
    Code:
    Private Sub Combo63()
      Me.component_nameControlName = Me.Combo63.Column(2)  ' <<--columns property is zero based
    End Sub
    This has worked for updating the text box to the correct name. However, the subform no longer updates with the list of parts which relate to the combo box selection. I have checked the Master/Child link between the main form and subform and this is still in place.

    I have even tried to requery the subform using something like Me.sfrm_parts.Requery or Me.sfrm_parts.Form.Requery but this doesn't make any difference. I am wondering if it has something to do with the combo box being unbound and so not communicating with the subform, despite the link???

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @Beanie_d83,
    How about describing the application in general terms?
    And posting a copy of the database (zip format) with instructions showing how to get to the problem.
    What do you want a tester to do? What data? What is the "error condition"? What would you like to happen?

  12. #12
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Hi Orange,

    I have attached a copy of the database.

    If you open frm_components, this will have on it a combo box, text box and sub form. When a selection is made in the combo box, the text box returns the name of the component type and the sub form should only display the parts of that particular component type.
    Attached Files Attached Files

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Beanie_d83,

    Try this code
    Code:
    Private Sub cboSelect_Component_Type_AfterUpdate()
       'filter the form recordset with value selected from combo
        Me.Filter = "Component_Type_id =" & Me.cboSelect_Component_Type.Column(0)
        Me.FilterOn = True
        'Me.txtComponent_Name = Me.cboSelect_Component_Type.Column(2)
        Me.Requery
    End Sub
    Explanation: Your code consistently used Component_Type_Id of 1.
    You open your form with a recordset that starts at top/beginning.
    Then you select from the combo. The recordset for the form gets filtered based on the
    component_type_Id of the selected item. Then, requery the form to use the "filtered recordset".

    I made the txtComponent_Type_id visible to see what was/was not being used.

    Good luck.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I took a different path than orange. Most of the time I use the method orange provided. I have the unbound combo box in the main form header, main form details display data and the sub form is in the form footer.
    Using code and the combo box, I can filter records and/or click on a record in the details to show records in the sub form.

    In your case, I moved the combo box and text box to the form header and removed the main form record source (making the main form unbound. Then I set the main form/sub form linking fields to the combo box and the sub form field Component_Type_ID_FK.. (whoops - I also changed a couple for field names for clarity)
    I also revised your sub form

    I duplicated your forms and left the form names with "Copy of" at the beginning of the names... kinda lazy today.
    The PK/FK fields in the sub form are almost (usually I hide them) because they are of NO importance to users.

    So, first select the combo box Component Type of "FD", then "SD"...




    Other things:
    EVERY module should have these two lines at the top:
    Code:
    Option Compare Database
    Option Explicit
    Also, "Description" and "Position" are reserved words in Access and shouldn't be used as object names. I changed them to "PartDescr" and "UserPosition".
    Attached Files Attached Files

  15. #15
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    This is great folks thank you!

    It's interesting to learn different ways that can achieve the same results!

    Coding is still a bit of a challenge for me so takes a while to understand whats going on, so please bear with me!

    Other things:
    EVERY module should have these two lines at the top:
    Code:
    Option Compare Database
    Option Explicit
    Steve, you mentioned about these 2 lines of code being at the top of every module, could you explain what they do as I've not noticed them before?

    I really like the new layout of the sub form. Initially I was just going to have it in a datasheet view but this seems a lot more user friendly now. One thing I have attempted to alter is the sub forms User_ID combo box. Ideally I would like this to simply be a text box but when I tried to change it, the Clear button returned the below error:

    Click image for larger version. 

Name:	Clear Button Error.PNG 
Views:	27 
Size:	9.4 KB 
ID:	33622

    I inspected the expression for the button and there wasn't any code that linked it to the combo (now text) box. What are your thoughts?

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub btnClear_Click()
        Me.cboSelect_Component_Type = vbNullString
        Me.txtComponent_Name = vbNullString
        Me.tbComponent_Type_id = vbNullString
    End Sub
    
    
    Private Sub cboSelect_Component_Type_AfterUpdate()
        Me.tbComponent_Type_id = Me.cboSelect_Component_Type.Column(0)
        Me.txtComponent_Name = Me.cboSelect_Component_Type.Column(2)
    End Sub
    Last edited by Beanie_d83; 04-18-2018 at 04:25 AM. Reason: Pressed the wrong button

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-22-2016, 08:42 AM
  2. Changing text boxes to combo boxes
    By Lou_Reed in forum Access
    Replies: 8
    Last Post: 09-15-2015, 11:09 AM
  3. Linking Combo boxes and Text boxes
    By Nathan4477 in forum Forms
    Replies: 6
    Last Post: 07-29-2015, 08:50 AM
  4. Replies: 11
    Last Post: 08-06-2014, 09:47 AM
  5. Replies: 9
    Last Post: 06-04-2014, 10:45 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