 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / March 2007"Find Next"
I have a combo box which lists codes from a table. There are duplicate codes. I have a "dependent" combo box which takes the code entered and displays the text description of the code. If there is more than one text description for the code, I need to be able to have the user to find the next instance and display the next text description for the code. I tried using the Wizard Control Record Navigation Find Next Record function and modify the code did not have any success. Any ideas to get the user to the next instance of the code entered and display the results? Also, the next phase of this small program involves a code which can have a couple of descriptions and these descriptions can have more information. Would a cascade combo box(es) be appropriate here? Thank you in advance for any help. I am a newbie at this. You should be able to do this very easily with a subform. This can be in single form view, continuous form view or datasheet view as you prefer. First create a form with the table (or a sorted query based on the table) as its RecordSource. Add controls to the form for the Description and whatever other fields you want to show. You don't need to include a control for the Code field, however, as that will already be shown in the combo box, but you do need it to be returned in the subform's underlying recordset if you are using a query rather than the table itself as the RecordSource. Include the combo box which lists the codes in another form, this time probably an unbound one, though not necessarily so, and add the first form to this form as a subform. Select the subform control in the main form (that's the control which houses the subform) and set its LinkMasterFields property to the name of the combo box in the main parent form, and the LinkChildFields property to the name of the Code field in the subform's underlying table. When you select a code in the combo box the subform will show only the records with that code and you can navigate between them in the usual way. If the main parent form is unbound make sure that you set its NavigationButtons property to False (No) and the subform's NavigationButtons property to True (Yes) or you'll end up with two sets of navigation buttons which could be confusing. If the subform is in continuous form or datasheet view you might even prefer to do without navigation buttons in it too, and rely on scrolling to navigate between rows. Ken Sheridan Stafford, England > I have a combo box which lists codes from a table. There are duplicate codes. > I have a "dependent" combo box which takes the code entered and displays the [quoted text clipped - 13 lines] > > Thank you in advance for any help. I am a newbie at this. Thank you, Ken! It is working but now I have another issue. Each time I reopen the form, the text description of the last search is displayed. Any way to clear this out so that the user starts with a fresh form each time? > You should be able to do this very easily with a subform. This can be in > single form view, continuous form view or datasheet view as you prefer. [quoted text clipped - 41 lines] > > > > Thank you in advance for any help. I am a newbie at this. That surprises me as, being an unbound control, the combo box on the main form should revert to Null when the form is closed and then reopened and the subform should therefore not show any records. I'm a little concerned that you might actually be using a bound control and changing the value of a the field in a record in the table. Even if the form is a bound one the combo box should not be bound as its purely for navigational purposes not for data entry. So if the form is bound make sure the combo box is not, i.e. has a blank ControlSource property. If you do need a bound Code control on the main form keep it separate from the navigational control. If you do link a subform via a bound control then the way to change the records shown in the subform is to navigate to the corresponding record in the main form, not to change the value of the control (and therefore the underlying field) in the current record in the main form. In fact this is how most subforms operate because they are most commonly used to show records from a table on the many side of a relationship to the main form's underlying table. The other theoretical possibility, though unless you've specifically set it up its difficult to see how it could have arisen, is that the default property of the (unbound) combo box is somehow being set and saved each time. Ken Sheridan Stafford, England > Thank you, Ken! It is working but now I have another issue. Each time I > reopen the form, the text description of the last search is displayed. Any [quoted text clipped - 45 lines] > > > > > > Thank you in advance for any help. I am a newbie at this. The form where the combo box is located is bound to the table BUT the combo box is unbound. The properties of the subform indicated that they are linked through the code field. The form on which the text description datasheet has a record source of the table from which the code and the text description are sitting. I noticed that the text description, on opening the form, reverts to the FIRST description in the table, not the last one entered as I had previously written. Can you help? > That surprises me as, being an unbound control, the combo box on the main > form should revert to Null when the form is closed and then reopened and the [quoted text clipped - 72 lines] > > > > > > > > Thank you in advance for any help. I am a newbie at this. PS - The only coding I can find is the coding the Control Wizard entered when the combo box was created. I had checked the box to find a record on the form based on the combo box entry. > That surprises me as, being an unbound control, the combo box on the main > form should revert to Null when the form is closed and then reopened and the [quoted text clipped - 72 lines] > > > > > > > > Thank you in advance for any help. I am a newbie at this. Judging by what you say what's happening is this: 1. When you select an item in the combo box the code which the wizard created is causing the main form to go to the first record which matches the code selected. Even if you have no bound controls in the main form its nevertheless going to that record. 2. Because the LinkMasterFields and LinkChildFields controls are set to the name of the Code field the subform is showing the records for that code. So selecting a code in the combo box does what you want, but not indirectly, not the way I described in my original reply. 3, When you open the main form its at the first record in its underlying recordset so the subform shows the descriptions for that first record. This is in fact what you'd normally expect such a navigational combo box to do, because generally the main form would be based on one table and have controls bound to fields in that table, e.g. it might be a Customers table, and the subform would be based on another related table in which there can be many records for each one in the main form, e.g. an Orders table. The way I think you want it to work is that you have no controls in the main form bound to fields in the table, just the navigational combo box, so the data is presented only in the subform. To get it to work that way this is what you'd need to do: 1. Delete the name of the table from the main form's RecordSource property. The main form is then unbound. If there are any bound controls on the main form delete them. 2. Delete all the wizard generated code behind the combo box, which is probably in its AfterUpdate event procedure. 3. Change the LinkMasterFields property of the subform control from the name of the Code field to the name of the unbound combo box. If Access has given the combo box some meaningless name like Combo42 you can leave it at that if you wish, but I'd suggest changing it in the combo box's property sheet to something more meaningful, e.g. cbFindCode. It’s a good idea to do this whenever you add a control to a form before doing anything else as if you write some code in a control's event procedure and *then* change the name of the control the code won't work and you'd have to rebuild the event procedure. As you've deleted the code in this case that doesn't matter of course. The Name property of a control is on the 'Other' tab of its properties sheet. Whatever you do make sure the name you put in the LinkMasterFields property of the subform control is *exactly* the same as that used for the control name. I'm not sure where you are, but its getting late here in Yurp, so I'm signing off for today. Ken Sheridan Stafford, England > PS - The only coding I can find is the coding the Control Wizard entered when > the combo box was created. I had checked the box to find a record on the form [quoted text clipped - 76 lines] > > > > > > > > > > Thank you in advance for any help. I am a newbie at this. THANK YOU! That is exactly what I want it to do! Can you point me in any direction for good reference material for working within Access? (I'm in the US.) Again, your help is truly appreciated. > Judging by what you say what's happening is this: > [quoted text clipped - 128 lines] > > > > > > > > > > > > Thank you in advance for any help. I am a newbie at this. As regards reference material you'll find most of what you need in the Help system, though it has to be admitted its not always easy to find. Also take some time to study the sample Northwind database which comes with Access. You'll find something similar online in the form of the Orders database, but with more explanation, at the following link, along with the Solutions database in the same download, which contains useful tips and tricks: http://www.mvps.org/access/resources/downloads.htm There are various online tutorials I've seen (try Googling "Access Tutorial") which you might want to look at. I prefer books myself, and for someone new to Access a good general primer is John L. Viescas's 'Running Microsoft Access'. For using VBA in Access Evan Callahan's 'Microsoft Access/Visual Basic Step by Step' is a very good and easily followed introduction (its well worth developing some VBA skills as macros, while cheap and cheerful, have their limitations). Both come with practice files on CD. Both come in editions for different versions of Access (I don't know of either are up to 2007 yet), are published by Microsoft Press (purely coincidental; I don't have any axe to grind on their behalf. For an introduction to the theoretical basis of the database relational model, something its crucial to know about, a useful little book is 'Inside Relational Databases – With Examples in Access' by Mark Whitehorn and Bill Marklyn, published by Springer. Its largely based on stuff from Mark's column in Personal Computer World magazine (Bill Marklyn was the original Access Product Manager at Microsoft), and I have to acknowledge a small interest here as Mark is a contact of mine and has included some of my stuff in the column from time to time. Good luck, Ken Sheridan Stafford, England > THANK YOU! That is exactly what I want it to do! > > Can you point me in any direction for good reference material for working > within Access? (I'm in the US.) > > Again, your help is truly appreciated. ....Once again, thank you!!! > As regards reference material you'll find most of what you need in the Help > system, though it has to be admitted its not always easy to find. Also take [quoted text clipped - 35 lines] > > > > Again, your help is truly appreciated. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.