Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Mandody is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    18

    Update record in Unbound combo box according to selection

    Hi everyone, I've got an unbound combo box in my form header which I use to quickly select records, but I sometimes also use the normal navigation buttons. When I use the navigation buttons, the record in the combo box doesn't change. Can I get this to update automatically?

    Many Thanks

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    There is always a way to get it done. However, it is not clear to me what "using the navigation buttons" is. If I take this literally, it means that you are navigating to another record via the arrows at the bottom of the form and this, somehow, is bypassing code that would, otherwise, fire.

    If this is what is happening, you can hide the "Navigation Buttons" via the Form's Property Sheet.

  3. #3
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try updating the combo from the form current event - something like

    me.cbobox=me.ID

    change cbobox to the name of your unbound combo and ID to the name of whatever field you search on.

    you will need to add in error control, if the record you have navigated to is a new record (therefore ID is null or empty)

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Ajax View Post
    try updating the combo from the form current event...
    How can we be sure the OP desires this?

  5. #5
    Mandody is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    18
    Click image for larger version. 

Name:	screen_shot_01.JPG 
Views:	22 
Size:	77.1 KB 
ID:	20450Click image for larger version. 

Name:	screen_shot_02.JPG 
Views:	22 
Size:	75.7 KB 
ID:	20451Hi ItsMe, Thanks for the reply. Apologies for the lack of clarity in my original post, but your assumption is correct. I use both the combo box and the arrows at the bottom of the form to navigate. I've attached two screen shots - in the first one all is fine, but from there, if I click the arrow at the bottom of the form to take to the next record, the combo box still shows the previous record as shown in the second screen shot. I'd like this to update when I use the arrow keys. I tried adding a new navigation button to the form for going to the next record and it did the same thing ie. the combo box stayed on the current record. Thanks again or your reply.

    And thanks also to you Ajax. Your solution sounds very credible but you'll have to forgive my ignorance, I don't even know where I'm supposed to add "me.cbobox=me.ID". Can you point me in the right direction?

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Ajax, my be understanding better than I am, what the function of the combo is. You said it is for selecting records. What do you want the combo to display as you navigate to another record?

    Ajax's suggestion is to have it display the current record you navigated to. You would do this from design view. VBA code behind the form will go in the Current Event of the form.

    You can get there by clicking the ellipses to the right of the On Current property in the Property Sheet and then select Code Builder.

    Set focus on the form by clicking the upper left corner of the form or using the pulldown from within the property sheet. The On Current event is under the Event tab.

  7. #7
    Mandody is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    18
    Thanks!!! That worked a treat. Exactly what I wanted - haven't come across any problems so far so no need for error control, yet. I'm sure I'll be back. Thanks to both of you once again. Much appreciated.

  8. #8
    Mandody is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    18
    OK. Still no error messages :-) but just one slight annoyance. When I open the database, the title in the combo box doesn't match the current record. Any ideas why this is happening? Always opens up displaying "37" as the current record (the first one in my table) but the combo box shows "Broken Glass". As soon as I make a change either using the navigation buttons at the bottom of the form or the combo box, they sync up fine.

  9. #9
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what record is displaying as the current record when you first open the form? i.e. the equivalent of '37' for 'broken glass'

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe duplicate the code that is in the On Current in the On Load event handler.

  11. #11
    Mandody is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    18
    Hi Ajax & ItsMe,

    Ajax : "37" is the title of one of my songs. It's top of the list when my songs are listed alphabetically, and it's the record that displays in the main window when I start up. The combo box however was displaying the song title "Broken Glass".

    Strangely I noticed that 37 is actually also the SongID for the track Broken Glass.

    I tried the code in the On Load event handler but it didn't help. My workaround : I just renamed the song from "37" to "Thirty Seven". The combo box and the current record now match when I start up, but Thirty Seven still appears at the top of the combo list. I'd like it to automatically sort alphabetically. How do I do this? Renaming songs is something I do on a regular basis so it'd great to have this functionality. Sorry for the continued questions - I've opened a can of worms!

    But thanks for all your help so far :-)

  12. #12
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'd like it to automatically sort alphabetically
    you do this with your rowsource which should be something like

    Code:
    SELECT ID, SongName 
    FROM myTable
    ORDER BY SongName

  13. #13
    Mandody is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    18
    Thanks Ajax,

    Rowsource currently reads : SELECT [Songs].[SongID], [Songs].[Title] FROM Songs;

    I changed it to : SELECT [Songs].[SongID], [Songs].[Title] FROM Songs; ORDER BY Title

    ...but now there is nothing at all in the combo box. What did I do wrong?

  14. #14
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    semi colon not required here

    Songs; ORDER

  15. #15
    Mandody is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    18
    Thank you Ajax! You're a star. That worked perfectly. It's almost there - just one final tweak. I'd like the form to display the first alphabetical record by "title" when I open it up. It's showing "Thirty Seven" which is still the first record by SongID.

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

Similar Threads

  1. Replies: 7
    Last Post: 03-30-2015, 10:04 AM
  2. Replies: 2
    Last Post: 01-05-2015, 04:18 PM
  3. Replies: 1
    Last Post: 04-10-2013, 12:47 PM
  4. combo box selection does not update correctly
    By mrtinmalaysia in forum Forms
    Replies: 2
    Last Post: 01-14-2013, 06:37 AM
  5. combo box selection to update form
    By sartan2002 in forum Forms
    Replies: 6
    Last Post: 02-03-2012, 01:47 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