Subform Woes

[PBISMaryland]

Hi All.

I am using Access 2007 and have a combo box on a form with a list of
students. When a student is selected related suspension data is displayed in
a subform. That part works fine. When I click inside the subform to enter
data the names in the combo box end up changing.

For example...if my combo box contains the names:

Jerry
Hannah
Molly

and I select Jerry for argument sake and begin clicking in the the subform
somewhere is the process the dropdown box changes to something like:

Jerry
Molly
Molly

When I go to the source table for the students the data is changed there as
well....obviously.

How can I make it that the items in the dropdown list will display
associated information in the subform but not change the original list?

Clearly I am missing something rather simple.

Thanks for your time.

 

[John W. Vinson]

How can I make it that the items in the dropdown list will display
associated information in the subform but not change the original list?

Make it an Unbound combo box - put nothing at all in its Control Source
property. A combo can be used for two purposes: you can use it to update data
in the table (for which you would use an unbound combo); or it can be used to
find records, in which case it should be unbound.

John W. Vinson [MVP]

 

[PBISMaryland]

Hi John.

Thanks so much for your quick and helpful response. I now have another
related issue.

The dropdown box actually contains items that look like this:

Smith, Jerry
Jones, Brian
Stevens, Joe

These names come from a query I had to create to combine first and last
name. The source table also contains a unique ID number for each person. I
want to use names as they are more user friendly for those entering data. I
took your advice for my last post but was hoping to tweak it a bit more. If
Jerry Smith has an ID number of 1234 how can I get Access to display both the
name and the ID number in the next row on the subform? When I change the
Link Master Fields and Link Child Fields property boxes to either
"studentname" or "ID" the subform populate with the correct person but only
one of the two fields populates. Another post I read said I could enter
something like studentname;ID but it does not seem to work.

Sorry for the long explanation but I am having trouble expressing my issue
succinctly.

Again, thanks for your time.

 

[John W. Vinson]

Hi John.

Thanks so much for your quick and helpful response. I now have another
related issue.

The dropdown box actually contains items that look like this:

Smith, Jerry
Jones, Brian
Stevens, Joe

These names come from a query I had to create to combine first and last
name. The source table also contains a unique ID number for each person. I
want to use names as they are more user friendly for those entering data. I
took your advice for my last post but was hoping to tweak it a bit more. If
Jerry Smith has an ID number of 1234 how can I get Access to display both the
name and the ID number in the next row on the subform? When I change the
Link Master Fields and Link Child Fields property boxes to either
"studentname" or "ID" the subform populate with the correct person but only
one of the two fields populates. Another post I read said I could enter
something like studentname;ID but it does not seem to work.

Well, maybe a somewhat *longer* description is needed here.

Generally ID numbers - particularly Access-assigned autonumbers - should not
be displayed AT ALL; they're generally meaningless to users. Instead the combo
should display enough information to unambiguously identify the person.

You can put a textbox on a form or subform with a Control Source like

=comboboxname.Column(0)

to display the (otherwise concealed) first column of the combo's query. Use
(4) to display the fifth column - zero based.

The subform's Recordsource should certainly NOT contain the student's name -
only the unique ID.

What are the Recordsources of the mainform and subform? What is the Rowsource
of the combo? Perhaps you could open them in SQL view and post.

John W. Vinson [MVP]

 

[PBISMaryland]

Hi John.

Thank you very much for your reply. I read your suggestions and things
started to fall in place from there.

Thanks again.