List Box

Hi

On the side of my form i have a list box and when you
click on the client names it brings there details up.

Only problem i have is that if two people have the same
surname it picks up the first person details. I can
change it to pick up the first name but that doesnt solve
my problem.

Each client has an ID Number is it possible to use that in
the list. If so can someone help me out

Thanks

Andrew

Andrew,

Having the ID number in your table is a very good start. As a general rule,
using names as a PK is bad practice (but also a very common mistake among
new users). Having said that, obviously you need to change your desing so it
utilizes the ID field, and looks up the details based on that. The trick is
to make the listbox display the surname and first name, while it actually
returns the ID field. To do that, select the listbox in form design, and
change its rowsource property (easiest way: invoke thequery builder) so it
selects the ID field, surname field, first name field; leave the bound
column property to 1, then go to the Format tab, set the column count
property to 3 if different, and specify column widths in the pertinent
property, making sure the first one is 0 so it doesn't show in the list.
That's all there is to it! Now you can use the reference to the listbox to
DLookup your detail textboxes on the ID field.

HTH,
Nikos



"Andrew C" wrote in message
...
Hi

On the side of my form i have a list box and when you
click on the client names it brings there details up.

Only problem i have is that if two people have the same
surname it picks up the first person details. I can
change it to pick up the first name but that doesnt solve
my problem.

Each client has an ID Number is it possible to use that in
the list. If so can someone help me out

Thanks

Andrew

Hi Andrew,

It sounds like the ID field needs to be included in the rowsource of your
listbox. If the Id number is the field that the users would recognize to
allow them to distinguish between two "John Smith" records, then the Id
field should be included in the rowsource of the listbox and should not be
hidden. If there are other fields that allow the user to make the selection
then they should be included and displayed. Are you using the AfterUpdate
event of the listbox to navigate to the selected record? Or are the results
displayed in a subform which uses the listbox in the LinkMasterFields?

Either way, be sure that the search field(s) or linking field(s) represent a
Unique Index to the table. If you use a single numeric primary key field
then this is the easiest choice for record selection.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Andrew C wrote:
Hi

On the side of my form i have a list box and when you
click on the client names it brings there details up.

Only problem i have is that if two people have the same
surname it picks up the first person details. I can
change it to pick up the first name but that doesnt solve
my problem.

Each client has an ID Number is it possible to use that in
the list. If so can someone help me out

Thanks

Andrew

I did that and it wont select anything now. Could the
problem be the the ID Number is an autonumber and not a
text field.


-----Original Message-----
Andrew,

Having the ID number in your table is a very good start.
As a general rule,
using names as a PK is bad practice (but also a very
common mistake among
new users). Having said that, obviously you need to
change your desing so it
utilizes the ID field, and looks up the details based on
that. The trick is
to make the listbox display the surname and first name,
while it actually
returns the ID field. To do that, select the listbox in
form design, and
change its rowsource property (easiest way: invoke
thequery builder) so it
selects the ID field, surname field, first name field;
leave the bound
column property to 1, then go to the Format tab, set the
column count
property to 3 if different, and specify column widths in
the pertinent
property, making sure the first one is 0 so it doesn't
show in the list.
That's all there is to it! Now you can use the reference
to the listbox to
DLookup your detail textboxes on the ID field.

HTH,
Nikos



"Andrew C" wrote in
message
...
Hi

On the side of my form i have a list box and when you
click on the client names it brings there details up.

Only problem i have is that if two people have the same
surname it picks up the first person details. I can
change it to pick up the first name but that doesnt
solve
my problem.

Each client has an ID Number is it possible to use that
in
the list. If so can someone help me out

Thanks

Andrew


.

Andrew,

I would say the first thing to do is to make sure the listbox returns what
it is supposed to. To check, press Ctrl+G while the form is open and a name
is selected in the listbox; this will take you to the immediate window of
the VB editor. Type the following in the

?Forms![MyFormName]![ListBoxName]

using the actual form and listbox name instead of my generic ones, and press
enter. Whatever the lsitbox returns will ne printed in the next line of the
immediate window, so you'll know. Is it indeed the value of the autonumber
field corresponding to the selected name?
If that's OK, then you need to check your DLookups, which should look
something like:

DLookup("[SomeField]","TableName","[IDFieldName]=" &
Forms![MyFormName]![ListBoxName])

If your DLookups were previously using a text field, it may be that there
are some extra quotes for text left around the listbox reference in the
expressions; if that's the case, they should be removed since the ID field
is numeric.

HTH,
Nikos

"Andrew C" wrote in message
...
I did that and it wont select anything now. Could the
problem be the the ID Number is an autonumber and not a
text field.


-----Original Message-----
Andrew,

Having the ID number in your table is a very good start.
As a general rule,
using names as a PK is bad practice (but also a very
common mistake among
new users). Having said that, obviously you need to
change your desing so it
utilizes the ID field, and looks up the details based on
that. The trick is
to make the listbox display the surname and first name,
while it actually
returns the ID field. To do that, select the listbox in
form design, and
change its rowsource property (easiest way: invoke
thequery builder) so it
selects the ID field, surname field, first name field;
leave the bound
column property to 1, then go to the Format tab, set the
column count
property to 3 if different, and specify column widths in
the pertinent
property, making sure the first one is 0 so it doesn't
show in the list.
That's all there is to it! Now you can use the reference
to the listbox to
DLookup your detail textboxes on the ID field.

HTH,
Nikos



"Andrew C" wrote in
message
...
Hi

On the side of my form i have a list box and when you
click on the client names it brings there details up.

Only problem i have is that if two people have the same
surname it picks up the first person details. I can
change it to pick up the first name but that doesnt
solve
my problem.

Each client has an ID Number is it possible to use that
in
the list. If so can someone help me out

Thanks

Andrew


.

Ok

i think the problem maybe with the Dlookup section. I
have had a look through and cant find anything related to
Dlookup.

I use an afterupdate which has the following in it.

Private Sub List101_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Findfirst "[Lastname] = '" & Me![List101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Other than that i need more help. Thanks for being
patient with me.

Andrew
-----Original Message-----
Andrew,

I would say the first thing to do is to make sure the
listbox returns what
it is supposed to. To check, press Ctrl+G while the form
is open and a name
is selected in the listbox; this will take you to the
immediate window of
the VB editor. Type the following in the

?Forms![MyFormName]![ListBoxName]

using the actual form and listbox name instead of my
generic ones, and press
enter. Whatever the lsitbox returns will ne printed in
the next line of the
immediate window, so you'll know. Is it indeed the value
of the autonumber
field corresponding to the selected name?
If that's OK, then you need to check your DLookups, which
should look
something like:

DLookup("[SomeField]","TableName","[IDFieldName]=" &
Forms![MyFormName]![ListBoxName])

If your DLookups were previously using a text field, it
may be that there
are some extra quotes for text left around the listbox
reference in the
expressions; if that's the case, they should be removed
since the ID field
is numeric.

HTH,
Nikos

"Andrew C" wrote in
message
...
I did that and it wont select anything now. Could the
problem be the the ID Number is an autonumber and not a
text field.


-----Original Message-----
Andrew,

Having the ID number in your table is a very good
start.
As a general rule,
using names as a PK is bad practice (but also a very
common mistake among
new users). Having said that, obviously you need to
change your desing so it
utilizes the ID field, and looks up the details based
on
that. The trick is
to make the listbox display the surname and first name,
while it actually
returns the ID field. To do that, select the listbox in
form design, and
change its rowsource property (easiest way: invoke
thequery builder) so it
selects the ID field, surname field, first name field;
leave the bound
column property to 1, then go to the Format tab, set
the
column count
property to 3 if different, and specify column widths
in
the pertinent
property, making sure the first one is 0 so it doesn't
show in the list.
That's all there is to it! Now you can use the
reference
to the listbox to
DLookup your detail textboxes on the ID field.

HTH,
Nikos



"Andrew C" wrote
in
message
...
Hi

On the side of my form i have a list box and when you
click on the client names it brings there details up.

Only problem i have is that if two people have the
same
surname it picks up the first person details. I can
change it to pick up the first name but that doesnt
solve
my problem.

Each client has an ID Number is it possible to use
that
in
the list. If so can someone help me out

Thanks

Andrew


.



.

Andrew,

OK, we're getting there. DLookup was just an assumption on my part, while
you've been doing this in a more "professional" way.
What's happening is your code is trying to match the current value of the
listbox to the value of field Lastname (as was the original setup), while
the listbox now returns the person's ID, so it obviously fails. To fix this,
you need to change your code to:

Private Sub List101_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Findfirst "[ID Number] = " & Me![List101]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

so it tries to match the correct field. I have assumed that the field name
is 'ID Number', if different change accordingly.
Note that I have removed the extra quotes around the Me![List101]; this is
assuming the ID number field is indeed numeric. If by any chance it is text,
then it should be:
rs.Findfirst "[ID Number] = '" & Me![List101] & "'"

Last, but not least, yo should make sure your form's recordset includes the
ID Number field. If the recordset is the people table itself then it
definitely does; if it is a query, check the query design to make sure it is
included, or add it otherwise.

HTH,
Nikos


"Andrew C" wrote in message
...
Ok

i think the problem maybe with the Dlookup section. I
have had a look through and cant find anything related to
Dlookup.

I use an afterupdate which has the following in it.

Private Sub List101_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Findfirst "[Lastname] = '" & Me![List101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Other than that i need more help. Thanks for being
patient with me.

Andrew
-----Original Message-----
Andrew,

I would say the first thing to do is to make sure the
listbox returns what
it is supposed to. To check, press Ctrl+G while the form
is open and a name
is selected in the listbox; this will take you to the
immediate window of
the VB editor. Type the following in the

?Forms![MyFormName]![ListBoxName]

using the actual form and listbox name instead of my
generic ones, and press
enter. Whatever the lsitbox returns will ne printed in
the next line of the
immediate window, so you'll know. Is it indeed the value
of the autonumber
field corresponding to the selected name?
If that's OK, then you need to check your DLookups, which
should look
something like:

DLookup("[SomeField]","TableName","[IDFieldName]=" &
Forms![MyFormName]![ListBoxName])

If your DLookups were previously using a text field, it
may be that there
are some extra quotes for text left around the listbox
reference in the
expressions; if that's the case, they should be removed
since the ID field
is numeric.

HTH,
Nikos

"Andrew C" wrote in
message
...
I did that and it wont select anything now. Could the
problem be the the ID Number is an autonumber and not a
text field.


-----Original Message-----
Andrew,

Having the ID number in your table is a very good
start.
As a general rule,
using names as a PK is bad practice (but also a very
common mistake among
new users). Having said that, obviously you need to
change your desing so it
utilizes the ID field, and looks up the details based
on
that. The trick is
to make the listbox display the surname and first name,
while it actually
returns the ID field. To do that, select the listbox in
form design, and
change its rowsource property (easiest way: invoke
thequery builder) so it
selects the ID field, surname field, first name field;
leave the bound
column property to 1, then go to the Format tab, set
the
column count
property to 3 if different, and specify column widths
in
the pertinent
property, making sure the first one is 0 so it doesn't
show in the list.
That's all there is to it! Now you can use the
reference
to the listbox to
DLookup your detail textboxes on the ID field.

HTH,
Nikos



"Andrew C" wrote
in
message
...
Hi

On the side of my form i have a list box and when you
click on the client names it brings there details up.

Only problem i have is that if two people have the
same
surname it picks up the first person details. I can
change it to pick up the first name but that doesnt
solve
my problem.

Each client has an ID Number is it possible to use
that
in
the list. If so can someone help me out

Thanks

Andrew


.



.