Results 1 to 8 of 8
  1. #1
    madams is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    15

    displaying long description of a lookup value to view on a subform

    I have a form that I would like to display the long description of a lookup value. For example I have a form that has a procedure code field as a combo box where the drop-down box displays records from another table to choose from. The combo box has 2 columns the procedure code and it's long description. After selecting the procedure code (which is then saved in the field) I want to display the long description on the form for viewing purposes only. I created a text box on the form where AfterUpdate I make that field "Me.ProcDesc = Me.txtProc.Column(1)" which works but as you move through existing records I also had to place the same code "Me.ProcDesc = Me.txtProc.Column(1)" on the Form_Current so it keeps refreshing to what the current records long description should be as you move through the records.



    My issue is that it's on a subform and causes the code to repeatedly be accessed when the main form is loading and makes the form longer to load or even move through the records. The main form is not specifically tied to one data source. The data source is created from the user selecting certain criteria so it is filtered to just the specific records they choose.

    Is there any way to have that field refresh with the associated long description without having to reference it from Column(1) of the combo box?

    Hope this makes sense. I am maintaining an old application and it's been a while since I've actually had to program any changes to it. Any suggestions would be appreciated

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why are you using VBA to save description? This is duplicating data in multiple tables. Could just have expression in textbox ControlSource: =Me.txtProc.Column(1) or use DLookup() which can also slow performance or include lookup table in form RecordSource and bind textbox to descriptive field. Set textbox Locked Yes and TabStop No.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    madams is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    15
    I am not trying to save the description I just want to display it on the form for the user to see. In checking the forms it looks like they had the right idea but put it on the wrong control as 'default value'. I will move it to the Control source and remove all other programming that was trying to update it and see if this works.

    Thanks

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If your first column is bound it's typical to not show that (as an autonumber value at least) and show the 2nd column instead. I'm trying to understand why you don't just show the value in of the 2nd column in the combo.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    madams is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    15
    The combo box actually does show both columns in the drop-down box so when they are selecting it they see the long description. When they exit off just the Procedure code displays in the combo box. The reason we want to display the long description on the form is so that when someone comes back to that screen at a later time they may not necessarily know what a "B1.1" stands for so to have the long description displayed as well helps the user understand what procedure is being performed "Bone marrow transplant".

    Changing the control source to equal what was in column(1) of the combo worked. It didn't resolve the slowness in which the form launches but it did fix my initial issue. Apparently there are some unrelated code (me.refresh and me.requery) that's happening that is causing the slowness.
    Thanks

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry, I'm a bit under the weather but why can't you get what you want with the first (bound) column width being set to 0" and the next one to a value sufficient to show what it is you want to see? The list can have as many columns as you want but only the first visible column is displayed in the combo.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sounds like columns are a procedure code like B1.1 and a description like Bone Marrow Transplant. Since B1.1 has meaning to users, it is displayed. Also sounds like B1.1 is saved instead of autonumber record ID.

    Another alternative is a RowSource like: SELECT ProcCode, ProcCode & " : " & ProcDesc FROM Procedures ORDER BY ProcCode;
    Set ColumnWidths to: 0";2"
    Now textbox is not needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    madams is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    15
    I'm a little confused....do you mean by setting the RowSource to be like "SELECT ProcCode, ProcCode & " : " & ProcDesc FROM Procedures ORDER BY ProcCode" that once they select the procedure code they want that the procedure code (B1.1) is saved in the table (because it's the hidden field with a width of 0") but for displaying purposes the combo box would display "B1.1 : Bone Marrow Transplant"

    Yes - The procedure code is the key field in the look up table not an autonumber record ID

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

Similar Threads

  1. Replies: 1
    Last Post: 11-27-2018, 04:54 PM
  2. Replies: 1
    Last Post: 09-03-2015, 09:25 AM
  3. Replies: 1
    Last Post: 11-18-2014, 02:16 PM
  4. Replies: 13
    Last Post: 07-01-2014, 06:16 AM
  5. Replies: 3
    Last Post: 04-03-2014, 08:13 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