synchronizing form and list box

  • Thread starter Thread starter Deb Smith
  • Start date Start date
D

Deb Smith

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)
 
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
 
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 said:
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 said:
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

list
box wan
not
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


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

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


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
Click to expand...
then
wan
not
sure if is was the right place. Sorry for the duplication.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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


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
Click to expand...
then
wan
not
sure if is was the right place. Sorry for the duplication.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
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
Click to expand...
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 said:
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

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
Click to expand...
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
Click to expand...
Click to expand...
or
is form.
As form,
the to
use
Click to expand...
Click to expand...
 
Glad it worked out.

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

Deb Smith said:
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 said:
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,
Click to expand...
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



structure
that identifies
all form
as box
and series
Click to expand...
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
Click to expand...
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
Click to expand...
with.
I
am at the final stages of developing this database and could really
Click to expand...
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

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

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

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


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
Click to expand...
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
Click to expand...
Click to expand...
must
be
Click to expand...
code
to
Click to expand...
Click to expand...
 
Back
Top