This is driving me absolutely bonkers trying to understand how different things (combo box, list box, forms, subforms, tabbed pages) are tied together; as in tying them together to work in unison.

Now I'm trying to get a combo box or listbox to call up the values in another table on the same form. Like I select an option from the list box and the form below it displays the values of a related table.

I have one database that is doing this perfectly and am trying to duplicate it in a different database. The two databases are very, very, similar, nearly the same, yet I CANNOT get the new one to work.

Why is it that in the one that works, the subform is a big white rectangle in design view? Other times, in my wallowing around, the form shows up in a more normal way. What is going on here?

If I had a million dollars to pay someone that could break through my stony head and make me understand how this works I would but I have only imaginary money.

Thank you for every little bit of insight into this. I'm sure it is extremely simple and fun once you "get it".

I don't know about 2007 exactly, but I'd bet that defaults for listboxes and comboboxes in 2003 and earlier were all wrong. I had to manually set the following properties before it will work correctly:

1) ColumnCount: 2 (Most of time, it's 2 but there are exceptions)
2) ColumnWidth: 0";
3) RowSource: Generally in this form:



That said, design view don't actually display any records, so white rectange (usually with a text noting the ControlSource) is displayed and thus is expected behavior. It's normal to toggle between views to verify that the control is working as expected.

Did that help?

Thanks for every little potential clue BananaRepublic but I'm still very confused.

It does seem that changing the Source Object from a table to a form makes the actual object, the form, show up in Design View instead of the blank rectangle, but I am groping for straws. Both good database and bad one seems like the the form I want to operate from the lookup are subforms asking for "Source Object" and Master/Child fields to link.

Okay, I should point out that the subform control and listbox/combobox control are two different beasts. The SourceObject property you spoke of is specific to Subform control only, and you've already figured out that if it's referencing a form name, it will display the form in design view, whereas table's name is blank white rectangle, which is also expected behavior since you can't display table in design view (at least not meaningfully in form design view).

The Master/Child fields are what you need to tell to Access how subform and parent form are to be related. Just click on the "..." button and select the common field (usually a primary key to foreign key) and you've linked the subform and it will work. Without the links, you'll get all rows in the subforms whether they relate to the parent record or not.

Did that help?

Maybe helped. I'll play around with it and think about it. Thank you.

Have you figured this out yet?

I have been struggling with the same problem. Though others here have offered insight, it tends to be over my head.

I can easily get the combo to work if it uses a field from the same Table or query, but it does not work properly when I try to connect a select list table to a query I'm using for the selection. After the last attempt I'm finding myself rewriting a lot of the data base.

Yes, that helped BananaRepublic. Everything looks ok, but I'm finding something odd and it still does not work.

I have two databases in this case I have two tables linked. The tables in the two databases are identical. The combobox works in one and not in the other.

For some reason in the combobox that is not working properly with the form, in the relationships window, I am not getting the 1 to many (infinity) symbols showing, just a line. I've checked the join type and it is the same as the working arrangement.

Any clues?

Edited by: endl on Tue Jan 6 11:58:04 EST 2009.

If you click twice on the relationship line in the relationship windows, what does Access say? Does it say 'Indeterminate' instead of usual 'One-Many'?

What is the Combobox's rowsource? Form's recorsource?

It says One-To-Many but the symbols for that are not showing on the diagram. The customers table has the + and expands like it should showing the data from the purchases table.

The Combobox's rowsource says "SELECT [customers].[ID], [customers].[LastName], [customers].[FirstName], [customers].[Address], [customers].[City] FROM customers ORDER BY [LastName];"

The Form's recorsource is "frmPurchases" and I've tried creating and using two other forms to make sure everything is current with same results.

That's first time I've heard of 1-Many symbol not showing up at all... (Is either tables in the relationship linked?)

Rowsource for combobox seems to be fine.

Form's recordsource... can you double check on that? Is there really a table named "frmPurchases?" Recordsource should be either table name, a query name or SQL statement.

Yes I was trying to use a form so I redid it using a table directly. When I dragged the table in and answered the questions the recordsource (you do mean "Source Object" don't you because I can't find anything that says recordsource) showed as a form ( purchases subform1) that Access created apparently on its own.

I renamed Source Object from "purchases subform1" to "Table.purchases" and the form turned into a blank rectangle like it should? and like the working database is, but still it doesn't update from the combobox.

The working database and this non-working one seem to be identical. I did copy some things over from the working one.

When I click the box "Enforce Referential Integrity" the one to many symbols show up but no help.

I have created another duplicate database this time totally by hand and with these two tables only and still cannot make it work. Close, but it's not working.

Is there a certain order that you need to put this all together. Do you start with a blank form and then add the combobox and table/subform or are there other certain steps?

The order shouldn't really matter as long they are logical (e.g. you do need have to a table first before you can bind form to it, but there is nothing stopping you from creating a blank form and filling it with controls and designing the layout then binding it afterward)

I'm pretty sure we're missing a step at some point, so let's try from the top.

1. Create a new blank database. If you know what it is, turn off AutoCorrect.
2. Create a bogus table. Fill it with bogus. You only need at least two column, one for AutoNumber and other for junk data.
3. Create a form. Bind it to the bogus table. Make sure you have two textbox with their controlsource set to the respective column.
4. Create a new combobox, but leave it unbound. Create its rowsource to reference the same table. Set the combobox properties as I indicated earlier so it will display only bogus data not the autonumber.


See if this process helps highlights the step you may have been missing.

This is where I get lost...

How do I bind it?


Like ID would show up in the controlsource as ID and LastName as LastName?


I'm using two different tables trying to look up names in one table(using combobox) and call up values in a linked table. My working one uses a query on the one table only. The combobox wizard lets me choose table and fields and creates the sql query and puts it in the combobox Row Source.


I must not understand that either.


Also I'm wondering why the combobox Control Source is blank.

I've spent two solid weeks reading tutorials and have books and still don't get it but I do think I'm getting closer.

Thanks again

[quote][quote] 3. Create a form. Bind it to the bogus table. [/quote] How do I bind it?[/quote]

By setting the form's recordsource. For starters, we can just select the name of the bogus table from the droplist in the recordsource. (Recordsource is a property of Form- to access that, use Properties Form, and make sure the topmost combobox says "Form", and you're on Data tab).


[quote] Make sure you have two textbox with their controlsource set to the respective column. [/quote]Like ID would show up in the controlsource as ID and LastName as LastName? [/quote]

What we are actually doing here is have two columns, but hide one column for key and displaying only the value instead of keys so it's human-readable but keys are used instead.

Those properties need to be set:

ColumnCount: 2
ColumnWidth: 0;
RowSource: SELECT key, value FROM BogusTable;

Setting ControlSource to "" will make it unbound. It's just a simple test to make sure we covered all steps and all you need to verify is that you're only seeing values, not keys in the combobox.

Trying and trying. Still missing something. I'm uploading a file with two examples which are done different ways, perhaps you can explain where I am going wrong.

Example 1 uses tables: MainTable and SecondaryTable and frmSecondaryTable

Example 2 uses tables customers and purchases and "purchases subform"

Thank you very much for your patience.

Because Jane's legs are twice as long as Jill's?







Edited by: endl on Tue Jan 6 23:09:41 EST 2009.

Hi itzallgreek

Well, I did figure out something that works. Don't know if it will work in your situation but it might help in some way.

I created a blank form, then created a combobox, then, using the subform control wizard, I pulled in a subform.

Then, in the property sheet in the "Link Master Fields" box I put the NAME OF THE COMBOBOX which I got from the "Other" tab of the property sheet in this case Combo59. Then I put the usual linking field (in this case CustomerID) in the "Link Child Fields".

There may be other ways or better ways but for now this works.

Thanks again to BananaRepublic for his assistance.