none
Pulling data into a combo box on a form RRS feed

  • Question

  • Hi MS Access gurus,

    I am relatively new to MS Access so this may be a newbie question.

    I have been given some mdb files to take a look at.  The files are Access 2003 compatible.  On one of the forms in the access project there is a combo box.  When I open the form in normal viewing mode, this combo box retrieves its data from a table called, say, "table1".  

    When I view the form in design mode, the properties of the form show, on the Data tab, that the Record Source is "table2".

    When I view the properties of the combo box, on the Data tab, the Control Source is VendorName.  VendorName is a field in table2 ... there is no VendorName field in table1.

    Where is the code that pulls the table1 data into the combo box?  I can't seem to find this code or relationship anywhere.

    Thanks,
    D

    Tuesday, August 23, 2011 6:23 PM

Answers

  • Dencolp:

    One more thing to check.... see if there are any events assigned to the combo box.  Perhaps there is some VBA code that is manipulating the data.

    Regards,


    Rich Locus Logicwurks, LLC www.logicwurks.com
    • Marked as answer by dencolp Tuesday, August 23, 2011 11:29 PM
    Tuesday, August 23, 2011 9:14 PM

All replies

  • Hello dencolp:

    If I understand your issue correctly, the form and the combo box have different data sources.  In design mode, click the little square box in the left upper corner of the form and check the properties, especially the Record Source property.  It is the table or query that supplies the data to the form.

    For the combo box, activate it in design mode, and then check the control source properties of the combo box.  The control source usually refers to a field in a table or query.

    However.... the combo box also has a Row Source Type and Row Source property.  Keep in mind that when the user clicks the down arrow on a combo box, it activates the "Row Source" property.  So, as you can see, the same combo box has two different sources:  (1) The control source and (2) The Row Source.

    Regards,


    Rich Locus Logicwurks, LLC www.logicwurks.com




    Tuesday, August 23, 2011 6:29 PM
  • Hi Rich,

    Thanks for your response.

    Yes you are correct in that the form and the combo box seem to have different data sources.

    Like I said in my original post, the form -> Record Source property is: "table2" and the control source property of the combo box is "VendorName".  "VendorName" is the name of a field in "table2".

    The "Row Source Type" property of the combo box is "Table/Query".

    The "Row Source" property of the combo box is empty.

    As an experiment, I set the "Row Source" of the combo box to another table called "table3", now the combo box is populated with data from "table3", as you would expect.

    But the mystery remains as to why the combo box retrieves its data from "table1" when the "Row Source" is set to an empty property.

    Incidently, when I change the combo box "Row Source", I get the following warning:

    "Do you want to save the changes to the SQL statement and update the property?  The RecordSource or RowSource property contained an SQL statement when you invoked the Query Builder, so the original SQL statement was modified.  To close the Query Builder without changing the original SQL statement, click No."


    Question 1
    ------------
    Do you know how I might be able to view and edit the SQL statement that is refered to in the warning message above?


    Question 2
    ------------
    Do you have any other guesses as to how and/or why the combo box might be retrieving it's data from table1 when the "Row Source" property of the combo box is empty?


    Thank you in advance for your help,
    D.

    Tuesday, August 23, 2011 8:33 PM
  • Question 2
    ------------
    Do you have any other guesses as to how and/or why the combo box might be retrieving it's data from table1 when the "Row Source" property of the combo box is empty?

    I would hazard a guess that you have the combo bound to a field in Table1.

    Row Source for combo is where it picks from and bound field is where that pick is stored.

    Tuesday, August 23, 2011 8:43 PM
  •  

    Hi Karl,

    Thanks for your response.

    The combo box's "bound column" property is set to "1".

    The combo box's "Row Source" property is empty.

    So I still don't see how the combo box is retrieving data from Table1.

    Thanks,
    Dennis.

    Tuesday, August 23, 2011 9:03 PM
  • Dencolp:

    One more thing to check.... see if there are any events assigned to the combo box.  Perhaps there is some VBA code that is manipulating the data.

    Regards,


    Rich Locus Logicwurks, LLC www.logicwurks.com
    • Marked as answer by dencolp Tuesday, August 23, 2011 11:29 PM
    Tuesday, August 23, 2011 9:14 PM
  • The Control Source would be the Table1 field.
    Tuesday, August 23, 2011 9:16 PM
  • @Karl Dewey:

    Karl:  If the form's Record Source property is Table 2, how could the control source for the combo box be a field in table 1?  Table 1's content is not being bound to the form?

    Regards,


    Rich Locus Logicwurks, LLC www.logicwurks.com
    Tuesday, August 23, 2011 10:27 PM
  • Dencolp:

    Are you absolutely certain that the Form's Record Source is a table and not a query?  If the form is getting information from both table 1 and table 2 then I would suspect that the form's record source could be a query that uses fields from both tables.  Could you check?

    Regards,


    Rich Locus Logicwurks, LLC www.logicwurks.com
    Tuesday, August 23, 2011 10:30 PM
  •  

    Hi Karl,

    As per my original post, the combo box's Control Source is a field in table2, which is why I am mystified as to how the combo box's data is being pulled from table1.

    D.

    Tuesday, August 23, 2011 11:26 PM
  • Hi Rich and Karl,

    OK I have now figured out what is going on. 

    Previously, I had scanned all the VBA code and I didn't find any references to Table1.  Also I scanned all the properties of the form and all the properties of the combo box for any references to Table1. 

    The "On Got Focus" Event in the Combo Box is set up to call a function where an SQL statment is the input parameter.  The function call and SQL statement is the event property value.  The SQL statement within the event property contains the reference to Table1.  In all my years of programming I have never seen SQL statements stored in the value of an Event property.  I consider this horrible programming style by the person/people who originally wrote this access application.  The only way I could see the table1 reference was if I scrolled over about 50 characters in the event property value.  This is why it took me so long to find the reference to table1.  

    Thanks for your help.  I am marking the "see if there are any events assigned to the combo box" post as the answer.

    D.

    Tuesday, August 23, 2011 11:28 PM
  • dencolp:

    Glad you figured it out!!

    Regards,


    Rich Locus Logicwurks, LLC www.logicwurks.com
    Tuesday, August 23, 2011 11:42 PM