synchronizing form and list box

I have a list box on a form that when you click on a record in the list box
the data relating to this record automatically populates the form. As you
change records in the list box, the data on the form changes
accordingly.(The list box contains the following fields:
PersonID,LName,FName. The form contains these three fields plus many more.)

However, if on the form you navigate to a different record, the record
highlighted in the list box does not correspondingly change to reflect the
new record.

What can I do to make it so that if I change records on the form, the
highlighted record in the list box changes to reflect the new record
identified on the form? I have tried everything I can think of including
using the forms current event but nothing seems to work. I must be doing
something wrong but I am not sure what. I am not sure what code to use and
where to put it.

Can anyone help?


PS. I sent this request to the forms.programming newsgroup but then wan not
sure if is was the right place. Sorry for the duplication.

In the OnCurrent event of the Form, put something like the following:
Private Sub Form_Current()
Me.lstPerson = PersonID
End Sub
(where lstPerson is the name of the listbox)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...

I have a list box on a form that when you click on a record in the list
box
the data relating to this record automatically populates the form. As you
change records in the list box, the data on the form changes
accordingly.(The list box contains the following fields:
PersonID,LName,FName. The form contains these three fields plus many
more.)

However, if on the form you navigate to a different record, the record
highlighted in the list box does not correspondingly change to reflect the
new record.

What can I do to make it so that if I change records on the form, the
highlighted record in the list box changes to reflect the new record
identified on the form? I have tried everything I can think of including
using the forms current event but nothing seems to work. I must be doing
something wrong but I am not sure what. I am not sure what code to use and
where to put it.

Can anyone help?


PS. I sent this request to the forms.programming newsgroup but then wan
not
sure if is was the right place. Sorry for the duplication.

Thanks for the suggestion. It partially works. It works as long as I just
use the tab or up or down keys. Unfortunately, if I select a record in the
list box to view and then move to other records in the list box and click,
multiple records remain highlighted and there is other wierd sort of stuff.

I have used the following code to ensure that the record selected in the
list box populates the fields on the form with the appropriate related data.
This seems to affect the on current event.

Dim rst As Object
Set rst = Me.Recordset.Clone
rst.FindFirst "[PersonID] = " & Str(Nz(Me![lstPerson], 2))
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Is there a better way to update my form with my list box selection or is
there other code I could use in forms On current event to make this work.

I unfortunately do not have the knowledge to figure it out and am really
stuck.

Thanks again for helping

Deb

"Roger Carlson" wrote in message
...
In the OnCurrent event of the Form, put something like the following:
Private Sub Form_Current()
Me.lstPerson = PersonID
End Sub
(where lstPerson is the name of the listbox)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...

I have a list box on a form that when you click on a record in the list
box
the data relating to this record automatically populates the form. As
you
change records in the list box, the data on the form changes
accordingly.(The list box contains the following fields:
PersonID,LName,FName. The form contains these three fields plus many
more.)

However, if on the form you navigate to a different record, the record
highlighted in the list box does not correspondingly change to reflect
the
new record.

What can I do to make it so that if I change records on the form, the
highlighted record in the list box changes to reflect the new record
identified on the form? I have tried everything I can think of including
using the forms current event but nothing seems to work. I must be doing
something wrong but I am not sure what. I am not sure what code to use
and
where to put it.

Can anyone help?


PS. I sent this request to the forms.programming newsgroup but then wan
not
sure if is was the right place. Sorry for the duplication.

I'm not sure, now that we are talking about the same thing. I was assuming
you were using the listbox to find particular records. Much like the
"ComboChoosesRecord.mdb" sample on my website. And what I discussed should
work if that's what you are doing.

However, some of the things you say, lead be to believe you are actually
doing something else. Can you explain in greater detail what you are trying
to do, including form, control and field names as are pertinent.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...
Thanks for the suggestion. It partially works. It works as long as I just
use the tab or up or down keys. Unfortunately, if I select a record in the
list box to view and then move to other records in the list box and click,
multiple records remain highlighted and there is other wierd sort of
stuff.

I have used the following code to ensure that the record selected in the
list box populates the fields on the form with the appropriate related
data.
This seems to affect the on current event.

Dim rst As Object
Set rst = Me.Recordset.Clone
rst.FindFirst "[PersonID] = " & Str(Nz(Me![lstPerson], 2))
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Is there a better way to update my form with my list box selection or is
there other code I could use in forms On current event to make this work.

I unfortunately do not have the knowledge to figure it out and am really
stuck.

Thanks again for helping

Deb

"Roger Carlson" wrote in message
...
In the OnCurrent event of the Form, put something like the following:
Private Sub Form_Current()
Me.lstPerson = PersonID
End Sub
(where lstPerson is the name of the listbox)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...

I have a list box on a form that when you click on a record in the
list
box
the data relating to this record automatically populates the form. As
you
change records in the list box, the data on the form changes
accordingly.(The list box contains the following fields:
PersonID,LName,FName. The form contains these three fields plus many
more.)

However, if on the form you navigate to a different record, the record
highlighted in the list box does not correspondingly change to reflect
the
new record.

What can I do to make it so that if I change records on the form, the
highlighted record in the list box changes to reflect the new record
identified on the form? I have tried everything I can think of
including
using the forms current event but nothing seems to work. I must be
doing
something wrong but I am not sure what. I am not sure what code to use
and
where to put it.

Can anyone help?


PS. I sent this request to the forms.programming newsgroup but then
wan
not
sure if is was the right place. Sorry for the duplication.

Here is some further information about my needs and data base structure that
may assist in iudentifying what I am doing wrong.

The list box on my form is populated from my Person Table and identifies all
individuals who have already been entered into the data base. Besides the
list box, all the fields from the Person table are included on this form as
it is the primary data entry form for all personal data. When you open the
form it opens to a blank record, but the list box shows all persons already
in the database.

Since the data base contains hundreds of records of people that potentially
link to event information (Other tables), I wanted to make the data base
more user-friendly. I want to show the user all individuals already in the
data base so all they have to do is click on the person in the list box and
the form populates with the information already entered. Equally, the person
can navigate through the records without the use of list box. As the person
scrolls up and down the list box, the fields on the form update and
correspond to the individual in list box which is the same individual record
on the form. If the person is not on the list, the user can enter a new
individual from this same form and update the list with their new entry.

I have added an option group (grpLastNameFilter) that comprises a series of
alpha buttons to assist the user in easily accessing their contact names.
The option group is linked to a hidden, unbound text box (txtLastNameFilter)
that holds the results of the list box query.

FINALLY.What do I want?...I want the highlighted record in the list box to
correspond to the individual identified on the form no matter if you are
scrolling through the list box or navigating through records on the form.

Here is some of the pertinent information about the structure of this form
that may help in identifying what I am doing wrong.

Table Name -tblPerson

List Box Name -lstPerson

List Box based on tblPerson and contains 3 fields, LName, FName,PersonID

Criteria used in lstPerson under LName is
Like{Forms]![Person]![txtLastNameFilter]& "*"

Code used in the AfterUpdate Event for lstPerson

Dim rst As Object

Set rst = Me.Recordset.Clone
rst.FindFirst "[Person ID] = " & Str(Nz(Me![List115], 2))
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Hidden, unbound text box that holds the results of list box query
called -txtLastNameFilter (There are no event procedures attached to this
text box)

Option Group called - grpLastNameFilter

Code used in AfterUpdate Event of Option Group

If grpLastNameFilter = 1 Then

grpLastNameFilter = "A"

txtLastNameFilter = grpLastNameFilter

DoCmd.ApplyFilter "", "[LastName] Like ""[AÀÁÂÃÄ]*"""

Me.List115.Requery

Me.List115.SetFocus

End If

The same code is used for each letter of the alphabet with the following
code being used to capture all records

If grpLastNameFilter = 27 Then

grpLastNameFilter = "*"

txtLastNameFilter = grpLastNameFilter

DoCmd.ShowAllRecords

Me.List115.Requery

Me.List115.SetFocus

Using the above, I have somehow managed to make it so that the list box
populates with the records, relating to a specific alpha letter and that
when a specific letter is chosen, the record showing on the main form is the
first record in the list box that corresponds to the letter. As I stated
before, as I scroll down through the records in the list, the form
information changes accordingly.

I am new to using Access, and have never developed a database or anything
before. What I have come up with is a result of trial and error, reading
books, help from places like the newsgroup etc. I am sure there are better
ways of accomplishing the same task but this is what I have come up with. I
am at the final stages of developing this database and could really use some
help in figuring out what I have done wrong. I do want to understand.

If you can provide some suggestions, I would really appreciate it.

Deb

"Roger Carlson" wrote in message
...
I'm not sure, now that we are talking about the same thing. I was
assuming
you were using the listbox to find particular records. Much like the
"ComboChoosesRecord.mdb" sample on my website. And what I discussed
should
work if that's what you are doing.

However, some of the things you say, lead be to believe you are actually
doing something else. Can you explain in greater detail what you are
trying
to do, including form, control and field names as are pertinent.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...
Thanks for the suggestion. It partially works. It works as long as I
just
use the tab or up or down keys. Unfortunately, if I select a record in
the
list box to view and then move to other records in the list box and
click,
multiple records remain highlighted and there is other wierd sort of
stuff.

I have used the following code to ensure that the record selected in the
list box populates the fields on the form with the appropriate related
data.
This seems to affect the on current event.

Dim rst As Object
Set rst = Me.Recordset.Clone
rst.FindFirst "[PersonID] = " & Str(Nz(Me![lstPerson], 2))
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Is there a better way to update my form with my list box selection or is
there other code I could use in forms On current event to make this
work.

I unfortunately do not have the knowledge to figure it out and am really
stuck.

Thanks again for helping

Deb

"Roger Carlson" wrote in message
...
In the OnCurrent event of the Form, put something like the following:
Private Sub Form_Current()
Me.lstPerson = PersonID
End Sub
(where lstPerson is the name of the listbox)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...

I have a list box on a form that when you click on a record in the
list
box
the data relating to this record automatically populates the form.
As
you
change records in the list box, the data on the form changes
accordingly.(The list box contains the following fields:
PersonID,LName,FName. The form contains these three fields plus many
more.)

However, if on the form you navigate to a different record, the
record
highlighted in the list box does not correspondingly change to
reflect
the
new record.

What can I do to make it so that if I change records on the form,
the
highlighted record in the list box changes to reflect the new record
identified on the form? I have tried everything I can think of
including
using the forms current event but nothing seems to work. I must be
doing
something wrong but I am not sure what. I am not sure what code to
use
and
where to put it.

Can anyone help?


PS. I sent this request to the forms.programming newsgroup but then
wan
not
sure if is was the right place. Sorry for the duplication.

There are a couple of things I would suggest:

1) get rid of this line:
grpLastNameFilter = "A"
in each of your "IF" statements. Setting the text box is enough.

2) Change the code for the listbox from the AfterUpdate to the OnClick event
and change it to something like this:


"Deb Smith" wrote in message
...
Here is some further information about my needs and data base structure
that
may assist in iudentifying what I am doing wrong.

The list box on my form is populated from my Person Table and identifies
all
individuals who have already been entered into the data base. Besides the
list box, all the fields from the Person table are included on this form
as
it is the primary data entry form for all personal data. When you open the
form it opens to a blank record, but the list box shows all persons
already
in the database.

Since the data base contains hundreds of records of people that
potentially
link to event information (Other tables), I wanted to make the data base
more user-friendly. I want to show the user all individuals already in the
data base so all they have to do is click on the person in the list box
and
the form populates with the information already entered. Equally, the
person
can navigate through the records without the use of list box. As the
person
scrolls up and down the list box, the fields on the form update and
correspond to the individual in list box which is the same individual
record
on the form. If the person is not on the list, the user can enter a new
individual from this same form and update the list with their new entry.

I have added an option group (grpLastNameFilter) that comprises a series
of
alpha buttons to assist the user in easily accessing their contact names.
The option group is linked to a hidden, unbound text box
(txtLastNameFilter)
that holds the results of the list box query.

FINALLY.What do I want?...I want the highlighted record in the list box to
correspond to the individual identified on the form no matter if you are
scrolling through the list box or navigating through records on the form.

Here is some of the pertinent information about the structure of this form
that may help in identifying what I am doing wrong.

Table Name -tblPerson

List Box Name -lstPerson

List Box based on tblPerson and contains 3 fields, LName, FName,PersonID

Criteria used in lstPerson under LName is
Like{Forms]![Person]![txtLastNameFilter]& "*"

Code used in the AfterUpdate Event for lstPerson

Dim rst As Object

Set rst = Me.Recordset.Clone
rst.FindFirst "[Person ID] = " & Str(Nz(Me![List115], 2))
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Hidden, unbound text box that holds the results of list box query
called -txtLastNameFilter (There are no event procedures attached to this
text box)

Option Group called - grpLastNameFilter

Code used in AfterUpdate Event of Option Group

If grpLastNameFilter = 1 Then

grpLastNameFilter = "A"

txtLastNameFilter = grpLastNameFilter

DoCmd.ApplyFilter "", "[LastName] Like ""[AÀÁÂÃÄ]*"""

Me.List115.Requery

Me.List115.SetFocus

End If

The same code is used for each letter of the alphabet with the following
code being used to capture all records

If grpLastNameFilter = 27 Then

grpLastNameFilter = "*"

txtLastNameFilter = grpLastNameFilter

DoCmd.ShowAllRecords

Me.List115.Requery

Me.List115.SetFocus

Using the above, I have somehow managed to make it so that the list box
populates with the records, relating to a specific alpha letter and that
when a specific letter is chosen, the record showing on the main form is
the
first record in the list box that corresponds to the letter. As I stated
before, as I scroll down through the records in the list, the form
information changes accordingly.

I am new to using Access, and have never developed a database or anything
before. What I have come up with is a result of trial and error, reading
books, help from places like the newsgroup etc. I am sure there are better
ways of accomplishing the same task but this is what I have come up with.
I
am at the final stages of developing this database and could really use
some
help in figuring out what I have done wrong. I do want to understand.

If you can provide some suggestions, I would really appreciate it.

Deb

"Roger Carlson" wrote in message
...
I'm not sure, now that we are talking about the same thing. I was
assuming
you were using the listbox to find particular records. Much like the
"ComboChoosesRecord.mdb" sample on my website. And what I discussed
should
work if that's what you are doing.

However, some of the things you say, lead be to believe you are actually
doing something else. Can you explain in greater detail what you are
trying
to do, including form, control and field names as are pertinent.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...
Thanks for the suggestion. It partially works. It works as long as I
just
use the tab or up or down keys. Unfortunately, if I select a record in
the
list box to view and then move to other records in the list box and
click,
multiple records remain highlighted and there is other wierd sort of
stuff.

I have used the following code to ensure that the record selected in
the
list box populates the fields on the form with the appropriate related
data.
This seems to affect the on current event.

Dim rst As Object
Set rst = Me.Recordset.Clone
rst.FindFirst "[PersonID] = " & Str(Nz(Me![lstPerson], 2))
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Is there a better way to update my form with my list box selection or
is
there other code I could use in forms On current event to make this
work.

I unfortunately do not have the knowledge to figure it out and am
really
stuck.

Thanks again for helping

Deb

"Roger Carlson" wrote in message
...
In the OnCurrent event of the Form, put something like the
following:
Private Sub Form_Current()
Me.lstPerson = PersonID
End Sub
(where lstPerson is the name of the listbox)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...

I have a list box on a form that when you click on a record in the
list
box
the data relating to this record automatically populates the form.
As
you
change records in the list box, the data on the form changes
accordingly.(The list box contains the following fields:
PersonID,LName,FName. The form contains these three fields plus
many
more.)

However, if on the form you navigate to a different record, the
record
highlighted in the list box does not correspondingly change to
reflect
the
new record.

What can I do to make it so that if I change records on the form,
the
highlighted record in the list box changes to reflect the new
record
identified on the form? I have tried everything I can think of
including
using the forms current event but nothing seems to work. I must be
doing
something wrong but I am not sure what. I am not sure what code to
use
and
where to put it.

Can anyone help?


PS. I sent this request to the forms.programming newsgroup but
then
wan
not
sure if is was the right place. Sorry for the duplication.

There are a couple of things I would suggest:

1) get rid of this line:
grpLastNameFilter = "A"
in each of your "IF" statements. Setting the text box is enough.

2) Change the code for the listbox from the AfterUpdate to the OnClick event
and change it to something like this:

Private Sub lstPerson_Click()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PersonID] = " & Me![lstPerson]
Me.Bookmark = rs.Bookmark
Me.lstPerson = Me.PersonID
End Sub

It's this last line before "End Sub" that gets rid of all those other wierd
selections in the list box.

I have a sample on my website (see sig below) called:
"ImproveFormPerformance.mdb" which illustrates how I would do it.

My sample is a little different from what you are doing. You are loading
the whole recordset into the form and then filtering. This is OK for small
recordsets, but for large recordsets, it is very slow. In my sample, I am
programmatically changing the Record Source for the form, which is much
faster. Also, there is no downside when using it on a small recordset, so I
use it all the time instead of filtering.

Take a look.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org



"Deb Smith" wrote in message
...
Here is some further information about my needs and data base structure
that
may assist in iudentifying what I am doing wrong.

The list box on my form is populated from my Person Table and identifies
all
individuals who have already been entered into the data base. Besides the
list box, all the fields from the Person table are included on this form
as
it is the primary data entry form for all personal data. When you open the
form it opens to a blank record, but the list box shows all persons
already
in the database.

Since the data base contains hundreds of records of people that
potentially
link to event information (Other tables), I wanted to make the data base
more user-friendly. I want to show the user all individuals already in the
data base so all they have to do is click on the person in the list box
and
the form populates with the information already entered. Equally, the
person
can navigate through the records without the use of list box. As the
person
scrolls up and down the list box, the fields on the form update and
correspond to the individual in list box which is the same individual
record
on the form. If the person is not on the list, the user can enter a new
individual from this same form and update the list with their new entry.

I have added an option group (grpLastNameFilter) that comprises a series
of
alpha buttons to assist the user in easily accessing their contact names.
The option group is linked to a hidden, unbound text box
(txtLastNameFilter)
that holds the results of the list box query.

FINALLY.What do I want?...I want the highlighted record in the list box to
correspond to the individual identified on the form no matter if you are
scrolling through the list box or navigating through records on the form.

Here is some of the pertinent information about the structure of this form
that may help in identifying what I am doing wrong.

Table Name -tblPerson

List Box Name -lstPerson

List Box based on tblPerson and contains 3 fields, LName, FName,PersonID

Criteria used in lstPerson under LName is
Like{Forms]![Person]![txtLastNameFilter]& "*"

Code used in the AfterUpdate Event for lstPerson

Dim rst As Object

Set rst = Me.Recordset.Clone
rst.FindFirst "[Person ID] = " & Str(Nz(Me![List115], 2))
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Hidden, unbound text box that holds the results of list box query
called -txtLastNameFilter (There are no event procedures attached to this
text box)

Option Group called - grpLastNameFilter

Code used in AfterUpdate Event of Option Group

If grpLastNameFilter = 1 Then

grpLastNameFilter = "A"

txtLastNameFilter = grpLastNameFilter

DoCmd.ApplyFilter "", "[LastName] Like ""[AÀÁÂÃÄ]*"""

Me.List115.Requery

Me.List115.SetFocus

End If

The same code is used for each letter of the alphabet with the following
code being used to capture all records

If grpLastNameFilter = 27 Then

grpLastNameFilter = "*"

txtLastNameFilter = grpLastNameFilter

DoCmd.ShowAllRecords

Me.List115.Requery

Me.List115.SetFocus

Using the above, I have somehow managed to make it so that the list box
populates with the records, relating to a specific alpha letter and that
when a specific letter is chosen, the record showing on the main form is
the
first record in the list box that corresponds to the letter. As I stated
before, as I scroll down through the records in the list, the form
information changes accordingly.

I am new to using Access, and have never developed a database or anything
before. What I have come up with is a result of trial and error, reading
books, help from places like the newsgroup etc. I am sure there are better
ways of accomplishing the same task but this is what I have come up with.
I
am at the final stages of developing this database and could really use
some
help in figuring out what I have done wrong. I do want to understand.

If you can provide some suggestions, I would really appreciate it.

Deb

"Roger Carlson" wrote in message
...
I'm not sure, now that we are talking about the same thing. I was
assuming
you were using the listbox to find particular records. Much like the
"ComboChoosesRecord.mdb" sample on my website. And what I discussed
should
work if that's what you are doing.

However, some of the things you say, lead be to believe you are actually
doing something else. Can you explain in greater detail what you are
trying
to do, including form, control and field names as are pertinent.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...
Thanks for the suggestion. It partially works. It works as long as I
just
use the tab or up or down keys. Unfortunately, if I select a record in
the
list box to view and then move to other records in the list box and
click,
multiple records remain highlighted and there is other wierd sort of
stuff.

I have used the following code to ensure that the record selected in
the
list box populates the fields on the form with the appropriate related
data.
This seems to affect the on current event.

Dim rst As Object
Set rst = Me.Recordset.Clone
rst.FindFirst "[PersonID] = " & Str(Nz(Me![lstPerson], 2))
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Is there a better way to update my form with my list box selection or
is
there other code I could use in forms On current event to make this
work.

I unfortunately do not have the knowledge to figure it out and am
really
stuck.

Thanks again for helping

Deb

"Roger Carlson" wrote in message
...
In the OnCurrent event of the Form, put something like the
following:
Private Sub Form_Current()
Me.lstPerson = PersonID
End Sub
(where lstPerson is the name of the listbox)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...

I have a list box on a form that when you click on a record in the
list
box
the data relating to this record automatically populates the form.
As
you
change records in the list box, the data on the form changes
accordingly.(The list box contains the following fields:
PersonID,LName,FName. The form contains these three fields plus
many
more.)

However, if on the form you navigate to a different record, the
record
highlighted in the list box does not correspondingly change to
reflect
the
new record.

What can I do to make it so that if I change records on the form,
the
highlighted record in the list box changes to reflect the new
record
identified on the form? I have tried everything I can think of
including
using the forms current event but nothing seems to work. I must be
doing
something wrong but I am not sure what. I am not sure what code to
use
and
where to put it.

Can anyone help?


PS. I sent this request to the forms.programming newsgroup but
then
wan
not
sure if is was the right place. Sorry for the duplication.

Thank you ! Thank you!

It works just great. I did however, have to leave the "grpLastNameFilter =
"A" line in to make the whole thing work.

Your patience, persostence and willingness to help is greatly appreciated.

Deb


"Roger Carlson" wrote in message
...
There are a couple of things I would suggest:

1) get rid of this line:
grpLastNameFilter = "A"
in each of your "IF" statements. Setting the text box is enough.

2) Change the code for the listbox from the AfterUpdate to the OnClick
event
and change it to something like this:

Private Sub lstPerson_Click()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PersonID] = " & Me![lstPerson]
Me.Bookmark = rs.Bookmark
Me.lstPerson = Me.PersonID
End Sub

It's this last line before "End Sub" that gets rid of all those other
wierd
selections in the list box.

I have a sample on my website (see sig below) called:
"ImproveFormPerformance.mdb" which illustrates how I would do it.

My sample is a little different from what you are doing. You are loading
the whole recordset into the form and then filtering. This is OK for
small
recordsets, but for large recordsets, it is very slow. In my sample, I am
programmatically changing the Record Source for the form, which is much
faster. Also, there is no downside when using it on a small recordset, so
I
use it all the time instead of filtering.

Take a look.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org



"Deb Smith" wrote in message
...
Here is some further information about my needs and data base structure
that
may assist in iudentifying what I am doing wrong.

The list box on my form is populated from my Person Table and identifies
all
individuals who have already been entered into the data base. Besides
the
list box, all the fields from the Person table are included on this form
as
it is the primary data entry form for all personal data. When you open
the
form it opens to a blank record, but the list box shows all persons
already
in the database.

Since the data base contains hundreds of records of people that
potentially
link to event information (Other tables), I wanted to make the data base
more user-friendly. I want to show the user all individuals already in
the
data base so all they have to do is click on the person in the list box
and
the form populates with the information already entered. Equally, the
person
can navigate through the records without the use of list box. As the
person
scrolls up and down the list box, the fields on the form update and
correspond to the individual in list box which is the same individual
record
on the form. If the person is not on the list, the user can enter a new
individual from this same form and update the list with their new entry.

I have added an option group (grpLastNameFilter) that comprises a
series
of
alpha buttons to assist the user in easily accessing their contact
names.
The option group is linked to a hidden, unbound text box
(txtLastNameFilter)
that holds the results of the list box query.

FINALLY.What do I want?...I want the highlighted record in the list box
to
correspond to the individual identified on the form no matter if you are
scrolling through the list box or navigating through records on the
form.

Here is some of the pertinent information about the structure of this
form
that may help in identifying what I am doing wrong.

Table Name -tblPerson

List Box Name -lstPerson

List Box based on tblPerson and contains 3 fields, LName, FName,PersonID

Criteria used in lstPerson under LName is
Like{Forms]![Person]![txtLastNameFilter]& "*"

Code used in the AfterUpdate Event for lstPerson

Dim rst As Object

Set rst = Me.Recordset.Clone
rst.FindFirst "[Person ID] = " & Str(Nz(Me![List115], 2))
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Hidden, unbound text box that holds the results of list box query
called -txtLastNameFilter (There are no event procedures attached to
this
text box)

Option Group called - grpLastNameFilter

Code used in AfterUpdate Event of Option Group

If grpLastNameFilter = 1 Then

grpLastNameFilter = "A"

txtLastNameFilter = grpLastNameFilter

DoCmd.ApplyFilter "", "[LastName] Like ""[AÀÁÂÃÄ]*"""

Me.List115.Requery

Me.List115.SetFocus

End If

The same code is used for each letter of the alphabet with the following
code being used to capture all records

If grpLastNameFilter = 27 Then

grpLastNameFilter = "*"

txtLastNameFilter = grpLastNameFilter

DoCmd.ShowAllRecords

Me.List115.Requery

Me.List115.SetFocus

Using the above, I have somehow managed to make it so that the list box
populates with the records, relating to a specific alpha letter and that
when a specific letter is chosen, the record showing on the main form is
the
first record in the list box that corresponds to the letter. As I stated
before, as I scroll down through the records in the list, the form
information changes accordingly.

I am new to using Access, and have never developed a database or
anything
before. What I have come up with is a result of trial and error, reading
books, help from places like the newsgroup etc. I am sure there are
better
ways of accomplishing the same task but this is what I have come up
with.
I
am at the final stages of developing this database and could really use
some
help in figuring out what I have done wrong. I do want to understand.

If you can provide some suggestions, I would really appreciate it.

Deb

"Roger Carlson" wrote in message
...
I'm not sure, now that we are talking about the same thing. I was
assuming
you were using the listbox to find particular records. Much like the
"ComboChoosesRecord.mdb" sample on my website. And what I discussed
should
work if that's what you are doing.

However, some of the things you say, lead be to believe you are
actually
doing something else. Can you explain in greater detail what you are
trying
to do, including form, control and field names as are pertinent.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...
Thanks for the suggestion. It partially works. It works as long as I
just
use the tab or up or down keys. Unfortunately, if I select a record
in
the
list box to view and then move to other records in the list box and
click,
multiple records remain highlighted and there is other wierd sort of
stuff.

I have used the following code to ensure that the record selected in
the
list box populates the fields on the form with the appropriate
related
data.
This seems to affect the on current event.

Dim rst As Object
Set rst = Me.Recordset.Clone
rst.FindFirst "[PersonID] = " & Str(Nz(Me![lstPerson], 2))
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Is there a better way to update my form with my list box selection
or
is
there other code I could use in forms On current event to make this
work.

I unfortunately do not have the knowledge to figure it out and am
really
stuck.

Thanks again for helping

Deb

"Roger Carlson" wrote in message
...
In the OnCurrent event of the Form, put something like the
following:
Private Sub Form_Current()
Me.lstPerson = PersonID
End Sub
(where lstPerson is the name of the listbox)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...

I have a list box on a form that when you click on a record in
the
list
box
the data relating to this record automatically populates the
form.
As
you
change records in the list box, the data on the form changes
accordingly.(The list box contains the following fields:
PersonID,LName,FName. The form contains these three fields plus
many
more.)

However, if on the form you navigate to a different record, the
record
highlighted in the list box does not correspondingly change to
reflect
the
new record.

What can I do to make it so that if I change records on the
form,
the
highlighted record in the list box changes to reflect the new
record
identified on the form? I have tried everything I can think of
including
using the forms current event but nothing seems to work. I must
be
doing
something wrong but I am not sure what. I am not sure what code
to
use
and
where to put it.

Can anyone help?


PS. I sent this request to the forms.programming newsgroup but
then
wan
not
sure if is was the right place. Sorry for the duplication.

Glad it worked out.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...
Thank you ! Thank you!

It works just great. I did however, have to leave the "grpLastNameFilter =
"A" line in to make the whole thing work.

Your patience, persostence and willingness to help is greatly appreciated.

Deb


"Roger Carlson" wrote in message
...
There are a couple of things I would suggest:

1) get rid of this line:
grpLastNameFilter = "A"
in each of your "IF" statements. Setting the text box is enough.

2) Change the code for the listbox from the AfterUpdate to the OnClick
event
and change it to something like this:

Private Sub lstPerson_Click()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PersonID] = " & Me![lstPerson]
Me.Bookmark = rs.Bookmark
Me.lstPerson = Me.PersonID
End Sub

It's this last line before "End Sub" that gets rid of all those other
wierd
selections in the list box.

I have a sample on my website (see sig below) called:
"ImproveFormPerformance.mdb" which illustrates how I would do it.

My sample is a little different from what you are doing. You are
loading
the whole recordset into the form and then filtering. This is OK for
small
recordsets, but for large recordsets, it is very slow. In my sample, I
am
programmatically changing the Record Source for the form, which is much
faster. Also, there is no downside when using it on a small recordset,
so
I
use it all the time instead of filtering.

Take a look.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org



"Deb Smith" wrote in message
...
Here is some further information about my needs and data base
structure
that
may assist in iudentifying what I am doing wrong.

The list box on my form is populated from my Person Table and
identifies
all
individuals who have already been entered into the data base. Besides
the
list box, all the fields from the Person table are included on this
form
as
it is the primary data entry form for all personal data. When you open
the
form it opens to a blank record, but the list box shows all persons
already
in the database.

Since the data base contains hundreds of records of people that
potentially
link to event information (Other tables), I wanted to make the data
base
more user-friendly. I want to show the user all individuals already in
the
data base so all they have to do is click on the person in the list
box
and
the form populates with the information already entered. Equally, the
person
can navigate through the records without the use of list box. As the
person
scrolls up and down the list box, the fields on the form update and
correspond to the individual in list box which is the same individual
record
on the form. If the person is not on the list, the user can enter a
new
individual from this same form and update the list with their new
entry.

I have added an option group (grpLastNameFilter) that comprises a
series
of
alpha buttons to assist the user in easily accessing their contact
names.
The option group is linked to a hidden, unbound text box
(txtLastNameFilter)
that holds the results of the list box query.

FINALLY.What do I want?...I want the highlighted record in the list
box
to
correspond to the individual identified on the form no matter if you
are
scrolling through the list box or navigating through records on the
form.

Here is some of the pertinent information about the structure of this
form
that may help in identifying what I am doing wrong.

Table Name -tblPerson

List Box Name -lstPerson

List Box based on tblPerson and contains 3 fields, LName,
FName,PersonID

Criteria used in lstPerson under LName is
Like{Forms]![Person]![txtLastNameFilter]& "*"

Code used in the AfterUpdate Event for lstPerson

Dim rst As Object

Set rst = Me.Recordset.Clone
rst.FindFirst "[Person ID] = " & Str(Nz(Me![List115], 2))
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Hidden, unbound text box that holds the results of list box query
called -txtLastNameFilter (There are no event procedures attached to
this
text box)

Option Group called - grpLastNameFilter

Code used in AfterUpdate Event of Option Group

If grpLastNameFilter = 1 Then

grpLastNameFilter = "A"

txtLastNameFilter = grpLastNameFilter

DoCmd.ApplyFilter "", "[LastName] Like ""[AÀÁÂÃÄ]*"""

Me.List115.Requery

Me.List115.SetFocus

End If

The same code is used for each letter of the alphabet with the
following
code being used to capture all records

If grpLastNameFilter = 27 Then

grpLastNameFilter = "*"

txtLastNameFilter = grpLastNameFilter

DoCmd.ShowAllRecords

Me.List115.Requery

Me.List115.SetFocus

Using the above, I have somehow managed to make it so that the list
box
populates with the records, relating to a specific alpha letter and
that
when a specific letter is chosen, the record showing on the main form
is
the
first record in the list box that corresponds to the letter. As I
stated
before, as I scroll down through the records in the list, the form
information changes accordingly.

I am new to using Access, and have never developed a database or
anything
before. What I have come up with is a result of trial and error,
reading
books, help from places like the newsgroup etc. I am sure there are
better
ways of accomplishing the same task but this is what I have come up
with.
I
am at the final stages of developing this database and could really
use
some
help in figuring out what I have done wrong. I do want to understand.

If you can provide some suggestions, I would really appreciate it.

Deb

"Roger Carlson" wrote in message
...
I'm not sure, now that we are talking about the same thing. I was
assuming
you were using the listbox to find particular records. Much like
the
"ComboChoosesRecord.mdb" sample on my website. And what I discussed
should
work if that's what you are doing.

However, some of the things you say, lead be to believe you are
actually
doing something else. Can you explain in greater detail what you
are
trying
to do, including form, control and field names as are pertinent.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...
Thanks for the suggestion. It partially works. It works as long as
I
just
use the tab or up or down keys. Unfortunately, if I select a
record
in
the
list box to view and then move to other records in the list box
and
click,
multiple records remain highlighted and there is other wierd sort
of
stuff.

I have used the following code to ensure that the record selected
in
the
list box populates the fields on the form with the appropriate
related
data.
This seems to affect the on current event.

Dim rst As Object
Set rst = Me.Recordset.Clone
rst.FindFirst "[PersonID] = " & Str(Nz(Me![lstPerson], 2))
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Is there a better way to update my form with my list box selection
or
is
there other code I could use in forms On current event to make
this
work.

I unfortunately do not have the knowledge to figure it out and am
really
stuck.

Thanks again for helping

Deb

"Roger Carlson" wrote in message
...
In the OnCurrent event of the Form, put something like the
following:
Private Sub Form_Current()
Me.lstPerson = PersonID
End Sub
(where lstPerson is the name of the listbox)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Deb Smith" wrote in message
...

I have a list box on a form that when you click on a record in
the
list
box
the data relating to this record automatically populates the
form.
As
you
change records in the list box, the data on the form changes
accordingly.(The list box contains the following fields:
PersonID,LName,FName. The form contains these three fields
plus
many
more.)

However, if on the form you navigate to a different record,
the
record
highlighted in the list box does not correspondingly change to
reflect
the
new record.

What can I do to make it so that if I change records on the
form,
the
highlighted record in the list box changes to reflect the new
record
identified on the form? I have tried everything I can think of
including
using the forms current event but nothing seems to work. I
must
be
doing
something wrong but I am not sure what. I am not sure what
code
to
use
and
where to put it.

Can anyone help?


PS. I sent this request to the forms.programming newsgroup but
then
wan
not
sure if is was the right place. Sorry for the duplication.