List Box

  • Thread starter Thread starter Andrew C
  • Start date Start date
A

Andrew C

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
 
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.
 
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.
 
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 there:

?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
 
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 there:

?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

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.

start.
As a general rule, on
that. The trick is the
column count in
the pertinent reference
to the listbox to in
message that
in
Click to expand...


.
Click to expand...
 
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 said:
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 there:

?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

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



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


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top