"Find Next"

D

Danu

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.
 
K

Ken Sheridan

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
 
D

Danu

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?
 
K

Ken Sheridan

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
 
D

Danu

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?
 
D

Danu

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.
 
K

Ken Sheridan

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
 
D

Danu

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.
 
K

Ken Sheridan

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
 
D

Danu

.....Once again, thank you!!!

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