List Box will not populate the field in the Control Source table

[RayN]

I have a form where I am trying to populate FirstName and
Title list box controls by selecting a LastName value
from a combo box and having them fill out automatically.
There is only one FirstName and Title for each LastName
value. The Row Source for each is a query which displays
the appropriate values.

The problem is that when I select a LastName and requery
the other two controls with an Event Procedure,the values
appear in the form but if I go back to the Control Source
table, the fields are blank. I go back to the form and
the values are still there. If I change the two list
boxes to combo boxes, the fields in the table populate
just fine. I want to avoid having to select the down
arrow in the FirstName and Title cntrols if I can avoid
it.

Can someone point me in the right direction??

 

[Steve Schapel]

Ray,

Will there always only ever be "one FirstName and Title for each
LastName value"? What happens if at some point in the future you end up
with two people in the system with the same LastName?

There is no need to use either a combobox or a listbox for this purpose
anyway. It would be a lot better to use a textbox for the FirstName and
Title.

It sounds like the Listboxes you are using are bound, i.e. there are
fields in the form's underlying table for FirstName and Title. If I
understand you correctly, this would generally be regarded as an invalid
design. You should remove these fields from the table altogether.
Instead, replace the listboxes with unbound textboxes. Set your
LastName combobox up with 3 columns (don't have to actually see them all
in the combobox list, if you don't want to). Then, set the Control
Source property of the textboxes to...
=Me.LastName.Column(1)
=Me.LastName.Column(2)

If I am worng, and there is a sound reason to store the FirstName and
Title in the table, then the code on the After Update event of the
combobox could be like this...
Me.FirstName = Me.LastName.Column(1)
Me.Title = Me.LastName.Column(2)

 

[Guest]

Thanks for the prompt reply.

The listboxes are bound. I have a master table of client
names, last, first and titles and I am using that data to
populate a table of transactions for the clients. Is
there a better way to do this? I have buily databases
before but I'm stretching a bit with this one.

I was not able to get your suggestion to work. What would
I put as the Row Source Type and Row Source for the
FirstName and Title Controls?

I hope my reply doesn't sound too disjointed. With my
original configuration, I am able to get the two list
boxes to populate with the correct data but the only way
to write that data to the underlying table is to click on
the respective boxes with my mouse. I havent been able to
duplicate this action in an Event Procedure. My skill is
very limited here. Thanks in advance.

 

[Steve Schapel]

Ray,

My suggestion is to use a textbox instead of a listbox or combobox for
the FirstName and Title controls, so they therefore do not have a Row
Source property.

The Row Source property of the LastName combobox, assuming it is the
"Master Table", remains the same of course, but you need to change the
Column Count property to 3.

You are in effect using the LastName as a key field. In other words,
once you know the LastName value, you have identified the person. Once
you have identified the person, you know what their FirstName and Title
are. If you already know what their FirstName and Title are, it is
incorrect and invalid practice to store these values in yet another
table. This is the #1 reason for using a relational database programme
like Access. That's why I am suggesting you do not do it like this, and
why I suggested your FirstName and Title should be unbound controls,
which allows you to show them on the form, but not write them to the
table. Hope that makes sense. The other thing I questioned was the
wisdom of using LastName as the key. In the absence of an already
existing unique identifier in the "Master Table" (is that really what
it's called?), I would be adding an Autonumber Primary Key field to the
table, and using this as the linking field to your other table.