If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
synchronizing form and list box
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. |
#3
|
|||
|
|||
synchronizing form and list box
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. |
#4
|
|||
|
|||
synchronizing form and list box
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. |
#5
|
|||
|
|||
synchronizing form and list box
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. |
#6
|
|||
|
|||
synchronizing form and list box
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. |
#7
|
|||
|
|||
synchronizing form and list box
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. |
#8
|
|||
|
|||
synchronizing form and list box
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. |
#9
|
|||
|
|||
synchronizing form and list box
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. |
Thread Tools | |
Display Modes | |
|
|