Results 1 to 4 of 4
  1. #1
    moona is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Location
    Mississauga, Ontario, Canada
    Posts
    14

    listbox on subform

    Goal is to update a list box when I scan through the records via record navigation controls.
    To list all types of accounts I have at a bank given the unique id of the bank (Bank_ID)

    so what I have is a tab control with 4 tabs on a form.

    The 4th tab is a subform that is bound to a table4 - Bank_ID is fk
    One field on the 4th tab is a primary key to table3 bound to 3rd tab - Bank_ID (is a pk)

    Now on the 4th tab as I scan the records, the bank_id changes.

    What I would like to display on 4th tab is the bank name, city and state found in table3 bound to tab3.

    To achieve that I created a bound text box with the following control source:

    Code:
    =(select bank_name from tbl_Owner_Bank_Info where Bank_ID= [Bank_ID] )
    This results in #Name? in this text box. I tried various forms of this select with no luck.

    If I use a Dlookup it works fine....

    Code:
    =DLookUp("[Bank_Name]","[tbl_Owner_Bank_Info]","[Bank_ID]=" & [Bank_ID]) & ", " & DLookUp("[Bank_Details_City]","[tbl_Owner_Bank_Info]","[Bank_ID]=" & [Bank_ID]) & ", " & DLookUp("[Bank_Details_State]","[tbl_Owner_Bank_Info]","[Bank_ID]=" & [Bank_ID])
    and it updates the values in the text box as I scan through the records of bank accounts at various banks....but it's noticably slow...so I'd prefer to use the sql option (bound box or LBox) or some other method.

    Now on to the list box....


    So how can I achieve the functionality of the DLookup but show it in a listbox.

    I have played around with LB and various forms of the sql and events and got it to display the correct data but NOT refresh automatically as I scan through the records.

    It's the refresh that is holding me up...

    I tried adding an on change event for the "Bank_ID" field - hoping that as I scan the records this will trigger a change in it's value and refresh the LB.

    Code:
    Private Sub Bank_ID_Change()
    Set List18 = [Bank_ID]
    List18.Requery
    
    End Sub
    any suggestions how I can cause the LB to refresh?

    thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use code to requery the listbox. In the form Current event try:

    Me.comboboxname.Requery
    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
    moona is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Location
    Mississauga, Ontario, Canada
    Posts
    14
    Thanks June7...that worked.

    I had tried several detail events but not the form "current" event.

    Any idea why I am not able to display the data using the =(select....) option in a text box?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    As far as I know Access 2007 (and earlier) does not allow SELECT sql statement in ControlSource. That's what domain aggregate functions are for.

    This may have changed with Access 2010.
    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.

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

Similar Threads

  1. Replies: 4
    Last Post: 11-16-2012, 02:43 AM
  2. Replies: 5
    Last Post: 03-23-2012, 10:54 AM
  3. Subform to show records of Listbox
    By gbmarlysis in forum Forms
    Replies: 5
    Last Post: 02-27-2012, 04:03 PM
  4. Replies: 3
    Last Post: 06-22-2011, 08:51 AM
  5. Replies: 1
    Last Post: 03-15-2011, 03:53 PM

Tags for this Thread

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