Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2014
    Posts
    7

    Unanswered: Suggestions for code to populate a Listbox with data from a link table

    In an Access 2007 database I have a form where each record/page displays PRODUCT details and the supplying COMPANY. Both PRODUCT and COMPANY are Primary Keys in their respective tables. I have a link table (COMPETITORS) that links PRODUCT type and other COMPANY(S) competing to supply that PRODUCT. The link table contains multiple entries for PRODUCT where more than one COMPANY is competing to supply that PRODUCT.

    Each PRODUCT is displayed on the form in its own record page and I would like a Listbox for data from the (COMPETITORS) link table to display the other COMPANY(S) competing to supply the PRODUCT.

    Can someone suggest or point me toward the code which would populate the Listbox with the COMPANY entry(s) from the (COMPETITORS) link table for the particular PRODUCT displayed on the form?

    Finally, would code or a query be the best/easiest way to do meet the requirement described above?

    Any help much appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Basically, you change the RowSource property of the Listbox control when the page changes in the form (Form_Current event).

    You do not supply many details about the objects, their names and the settings of the Listbox object, so what follows is only a template (aircode) that you'll need to adapt.
    Code:
    Private Sub Form_Current()
    
        Dim strSQL As String
        
        strSQL = "SELECT Company.CompanyName " & _
                 "FROM (Competitors INNER JOIN Company ON Competitors.CompanyForeignKey = Company.PrimaryKey) " & _
                 "INNER JOIN Product ON Competitors.ProductForeignKey = Product.PrimaryKey " & _
                 "WHERE Product.PrimaryKey = " & Me.ProductIDName.Value & _
                 " ORDER BY Company.CompanyName;"
        Me.ListboxName.RowSource = strSQL
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Mar 2014
    Posts
    7
    Sinndho, many thanks for your time in replying and providing the suggested code. I've included more details below, hopefully this helps.

    The COMPANY table contains a small number of fields about the company the relevant field here being (also) called COMPANY which contains the company names i.e. IKEA, CRATEANDBARREL etc.

    The PRODUCT table is very simple and has only one field listing the PRODUCT type i.e. CHAIR, TABLE etc.

    The link table COMPETITORS uses the PRODUCT field from the PRODUCT table and the COMPANY field from the COMPANY table, in the link table the COMPANY names populate a field called COMPETITORCOMPANY. Each link table entry has a UniqueID/Primary Key autonumber but I don't see this having a role in this problem.

    The listbox is called "listboxCOMPETITORS". I have not adjusted any of the settings for the listbox as I am content with a simple box which contains the COMPANY names from the COMPETITORCOMPANY field for that particular PRODUCT. If necessary I can adjust the settings for the listbox.

    I understand the concept that I need the properties for the RowSource of the listbox to be variable and change for each form Record entry, however I have some questions on the code as the terminology and commands are not obvious to me.

    1. When I open the build code window for the listbox it automatically begins:
    Private sub listboxCOMPETITORS_BeforeUpdate (Cancel As Integer)

    If I replace the above with the code from the start of your suggested code:
    Private sub Form_Current()

    it is highlighted as an error.

    Should the code you suggest be applied to the listbox or to the form as a whole (if this is possible)?

    2. Again my lack of knowledge comes through here, but I don't understand the role/function of the: & _ at the end of some of the lines code. Is this where I need to provide further input or is there a specific meaning here?

    3. When I enter the code you provided what words should be highlighted in blue font (as SQL key words/commands)? At present when I enter the code (adjusting the table and fields names as appropriate) only Dim and As String become blue SQL key words and none of the rest of the text.

    Apologies for the list of questions and as soon as I have time I will endeavour to enrol of a SQL course.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Codenovice View Post
    1. When I open the build code window for the listbox it automatically begins:
    Private sub listboxCOMPETITORS_BeforeUpdate (Cancel As Integer)

    If I replace the above with the code from the start of your suggested code:
    Private sub Form_Current()

    it is highlighted as an error.

    Should the code you suggest be applied to the listbox or to the form as a whole (if this is possible)?
    a) Which line (or lines) is (or are) rejected as error(s) (please post the code)?
    b) The Form_Current event happens every time the current row changes in the Form (i.e. each time you go to the previous or to the next record). It is then a Form event but you can use code in it concerning other controls (such as the Combo).

    Quote Originally Posted by Codenovice View Post
    2. Again my lack of knowledge comes through here, but I don't understand the role/function of the: & _ at the end of some of the lines code. Is this where I need to provide further input or is there a specific meaning here?
    '&' is the string concatenation operator, i.e. it merges 2 strings together. '_' is the line continuation symbol. It indicates that the current instruction does not stop at the end of the line but continues on the next.
    Quote Originally Posted by Codenovice View Post
    3. When I enter the code you provided what words should be highlighted in blue font (as SQL key words/commands)? At present when I enter the code (adjusting the table and fields names as appropriate) only Dim and As String become blue SQL key words and none of the rest of the text.
    Only reserved words that are part of the VBA dialect of the BASIC language are printed in blue, as well as the directives (more generally: every word that the VBA Interpretor/Compiler recognizes as built-in).
    Have a nice day!

  5. #5
    Join Date
    Mar 2014
    Posts
    7
    Sinndho, thanks for the reply and details, helps with my understanding.

    This is kind of a sideline for me but I'm working through it slowly bit by bit. However I've resolved one issue as I didn't spot you could select 'Form' and then OnCurrent as an option from the Form Properties Sheet drop down list. I had been trying to enter 'Form_Current' manually (which didn't seem to work) as an event listing, so this sorts out the first obstacle. Some of the other problems I was encountering might now work themselves out.

    I'll work on the other points and post separately; I find trying to cover two many points in one posts tends to get a bit mesy/confusing to follow and maintain a dialogue.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

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
  •