ListBox on Subform

Topic

New Reply

Access 97, Jet 3.51, MDAC 2.6

I have a many to many relationship that is resolved to a third table (child). Single PK for each of the Master Tables.

I have a form with a subform showing this information. The child’s fields are hidden.

I use a query to fill the subform with one master table’s records, and the child’s fields (hidden). It doesn’t matter which table since it should work either way, depending on how you want to present the data.

On the main form my Record source is the other master table and the subform link field is the PK of the controlling master table to child table.

My problem is that the listboxes loose their behavior when displayed as a subform on the master form. The lookup is defined at the table level and I even set them in the subform. All of the listboxes in the subform appear as normal fields. What am I missing?

Reply | Quote

Replies

First off, don’t define lookups at the table level. I know it seems handy to have the wizard automagically create a combobox when you drag the field onto the form, but it also creates a drag on performance, since you have built the equivalent of a query into your table and you’re doing it again on the form. The rule is that users don’t see tables anyhow, so there isn’t any need for the lookup there. It’s just a shortcut for lazy programmers.

I don’t understand this statement [indent]

---

My problem is that the listboxes loose their behavior when displayed as a subform on the master form …. All of the listboxes in the subform appear as normal fields

---

[/indent] Do you mean that you don’t see any values or what? And are these listboxes or comboboxes?

Reply | Quote

Hey, I resent being called a lazy programmer.

So when does or under what conditions does Access trigger the table level lookup? I would have thought that this occurred only during record update/change or new record. We can start another thread on this topic if you prefer.

When the subform is displayed on the form, all listboxes on the subform no longer give one a dropdown list. They are blank. They look like a text field when you enter the field. They are list boxes.

Reply | Quote

It has to populate the list or combobox when you access the field, otherwise, you couldn’t use it to do updates and changes.

Reply | Quote

I do use a select statement to populate the controls where needed. Salutation and suffix are fairly static, so I define them at the table. As to the value of where & when to use a listbox, depends on objective. No need to let the user manually enter data in these instances, so a listbox works quite fine. They only differ in that a listbox does not allow a typed entry, where a combo box does. In response to the no drop down insistence, I used the wizard to create a simple form of the data with the “person type” as a listbox. I then added “person type” as a listbox using the listbox control. I present 3 views, two as a datasheet (one with the rows expanded) and one as a continuous form. As can be seen, there is no spinner or scroll bar in the datasheet view, it is a down arrow. As the second field shows, the appearance is that of a dropdown when the field is selected. This pictoral evidence seems to fly in the face that a listbox does not drop down. I am not trying to be a smartaleck, just trying to understand. I still do not understand why the orginal form would display all of the listboxes as regular fields, when they worked as listboxes when viewed independently in the subform.

Ed: Hi, your screenshot was gigantic and distorting the layout of the board so I’ve cut it down. Could you *all* please remember to conserve bandwidth by clipping and shrinking your graphics to the minimum before you upload them to the forum. –Eileen

Reply | Quote

They aren’t really dropping down in the same sense that a combobox does. What appears to be happening is that the whole list box becomes visible when it receives the focus, giving it the effect of a dropdown. I suspect that the rest of the behavior (ie., the down arrowhead) is because listboxes were never really intended for use in datasheet view, so Access is doing the best it can to present information in an awkward format.

Reply | Quote

there is no such thing as a dropdown in a listbox. You are describing a combobox

Reply | Quote

Well, I assume it is a listbox, since I defined it in the table that way and also defined it on the form that way. In testing the first reply, I dropped the table definition and used a wizard to define a listbox. It appears to drop down when I select the button. Enclosed is a snapshot of the result of this exercise. I also threw on a combobox so you wouldn’t think I am too crazy.

Interestingly, the listbox works on when manually defined in the subform and the combobox for that matter. Why they both have the same appearance, I know not.

Reply | Quote

It probably is a listbox. However, in a continuous subform, it will look the same as a combobox, because it only has one line high to display its contents, same as the combobox. The question is why you defined it as a listbox in the first place. A combobox will work on a continuous form, but a listbox is pretty useless there. Listboxes do NOT drop down, although they may scroll, and what you’re seeing is probably the down arrow on the compressed scrollbar.

Reply | Quote