none
[Access 2003 + VBA] ListBox showing persons's PK_ID instead of their name RRS feed

  • Question

  • Hi,

    I have:

     - search text box and a list box

     - table1 and table2

     - in table1 I have a bound combobox with table2

     - agents combobox in the form shows agents name, but in the list box, it shows their primary keys...

    How do I fix that, please?

    listBox query is:

    SELECT PK_ID, Customer, DateSend, PhoneNumber, agents FROM Table1 ORDER BY DateSend DESC;


    and the search text box:

    Private Sub txtSearch_Change()
        On Error GoTo Err_txtCredSpec_Change
        
        Dim strSource As String
        
        strSource = "SELECT PK_ID, Customer, DateSend, PhoneNumber, agents " & _
        "FROM Table1 WHERE agents LIKE '*" & Me.txtSearch.Text & "*' ORDER BY DateSend DESC;"
    
        Me.ListBox.RowSource = strSource
        
    Exit_txtSearch_Change:
        Exit Sub
    Err_txtSearch_Change:         MsgBox Err.Number & " " & Err.Description
        Resume Exit_txtSearch_Change
    End Sub
    
    

    Regards






    • Edited by iboumiza Friday, October 14, 2011 6:07 PM
    Friday, October 14, 2011 5:58 PM

Answers

  • Hi again,

    In VBA its something like this, in the OnCurrent event of your Form:

     

    Private Form_Current()
    
        Me.Listbox.ColumnCount = 5
        Me.Listbox.ColumnWidths = "0;0;0;0;2"
    
    End Sub
    
    

     

    By the way if its the Customer you want to show in the Listbox, change it into:

    Private Form_Current()
    
        Me.Listbox.ColumnCount = 5
        Me.Listbox.ColumnWidths = "0;2;0;0;0"
    
    End Sub
    

    Hope that helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Friday, October 14, 2011 8:43 PM
    Moderator

All replies

  • If you want to show only the field agents, you need to make sure you use the right amount of columns and you need to set the width in Tab Format of your Listbox on your Form, see below screenshot:

     

    So you have five fields selected in your SQL, so agents is the last field. So you set the Column Count to 5, and set the width for the first four fields to 0 (you hide them), and the last one to 2" for example.

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, October 14, 2011 6:07 PM
    Moderator
  • hi,

    it's not what I'm looking for:

    [Access 2003 + VBA] ListBox showing persons's PK_ID instead of their name

    Friday, October 14, 2011 8:35 PM
  • Hi again,

    In VBA its something like this, in the OnCurrent event of your Form:

     

    Private Form_Current()
    
        Me.Listbox.ColumnCount = 5
        Me.Listbox.ColumnWidths = "0;0;0;0;2"
    
    End Sub
    
    

     

    By the way if its the Customer you want to show in the Listbox, change it into:

    Private Form_Current()
    
        Me.Listbox.ColumnCount = 5
        Me.Listbox.ColumnWidths = "0;2;0;0;0"
    
    End Sub
    

    Hope that helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Friday, October 14, 2011 8:43 PM
    Moderator