Drop Down stuck on previous entry

[piscogirl]

How to explain this... perhaps if I fully understood, I wouldn't be
having the problem in the first place.

I'm setting up a database to register students for an after-school
course.

The primary tables are for Events and Students, and then I have some
secondary tables for locations, instructor info, institution info, etc.

Now I'm trying to set up the actual record of attendance.

I've got it as a subform on the Events table, so that we can easily
take a pile of registration forms or a sign-in sheet and add the names
in datasheet view. For the table underlying the subform, I'm trying to
get it so that rather than typing in the names individually, and risk
either a) typos or b) entering people who aren't in the system yet,
there is a drop down on Last Name, and then First Name.

The Last Name will contain a list of all the students, so that the user
can either start typing the name and let it auto-complete, or actually
use the dropdown. Then, the first name field only contains a list of
the first names that are available for that last name. Finally, the
Institution field (since the Students are coming from different Public
Schools) will only show choices available for that First/Last name
combo.

Here are the problems I'm having.

1 - Even though the Last Name has already been entered into the table,
the first time I add a record it prompts me to enter it manually. Once
the Last Name is entered, the First Name comes up as planned.

2 - Once I enter one record, I'm no longer prompted for a Last Name.
Instead, I'm given a drop down with only one choice - the name of the
person I entered for the first record.

Any ideas of how I can clean this up?

Thanks,

Melissa

 

[Jeff Boyce]

Melissa

A minor simplification for you...

Instead of using two combo boxes (LastName, FirstName), you could use a
single combo box. That combo box would be based on a query that includes
PersonID
LastName & ", " & Firstname

This way, your user would only need to start typing in one combo box, and
could pick the full name from the drop-down. Of course, if you have more
than one "Smith, John", you might need to add additional fields to the combo
box's query to help the user decide which one.

I'm a little confused by your description of "subforms" on a "table". Are
you working directly in the tables, or have you set up Forms?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Melissa:

Correlated combo boxes don't really work well with forms in continuous or
datasheet view and are best used only on forms in single form view. It can
be done by means of a hybrid control of a text box superimposed on a combo
box so that it looks like a single control. I've posted a demo which
includes this at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


but as you'll see if you download it I actually recommend another approach,
which I'll return to below. Before doing that, however, lets look at your
logical model, because I think you need another table.

Assuming each student can be registered for one or more events , and each
event can be attended by one or more students, the relationship between
Students and events is many-to-many. Such relationships are modelled by
another table with foreign key columns which reference the primary keys of
the other two tables. So in your case you'd have a StudentEvents table as
well as the Events and Students tables. The Students table would have one
row per student and a primary key such as EventID, Students would similarly
have one row per student and a primary key such as StudentID, These key
columns can be autonumbers. Do not use people's names as keys as names can
be duplicated. You do not need any foreign key columns in these tables which
reference the other table.

The StudentEvents table would have foreign key columns StudentID and
EventID, but of straightforward long integer number data type this time, not
autonumbers. Together these two columns can be made the table's composite
primary key.

For data entry you'd use an Events form based on the Events table (or better
on a sorted query based on the table). The events subform would be based on
the StudentEvents table or a query based on that table.

My recommended approach for the subform would be to use a continuous view
form. Link the for and subform on EventID by setting this as the
LinkMasterFields and LinkChildFields properties of the subform control. Ad a
combo box bound to the StudentID column and set its RowSource property to:

SELECT StudentID, LastName, FirstName
FROM Students
ORDER BY LastName, FirstName;

Set the combo box's properties as follows:

BoundColumn 1
ColumnCount 3
ColumnWiudths 0cm;3cm;6cm
ListWidth 6cm

The last two properties can be in inches of course and you should experiment
with the second and third dimensions of the ColumnWidths to get the best fit,
but the first dimension must be zero to hide the StudentID column. The
ListWidth should be the sum of the ColumnWidths dimensions.

Add an unbound txtFirstName text box alongside the combo box with a
ControlSource of:

=cboStudentID.Column(2)

where cboStudentID is the Name property of the combo box. The Column
property is zero-based, so Column(2) is the third column, FirstName, of the
combo box's RowSource.

You might also want to consider whether you should include more columns in
the combo box's RowSource (and in unbound text boxes on the subform) to
differentiate between students who have the same first and last names.

To select a student you'd scroll down the combo box to the Lastname with the
required FirstName. When you make the selection the FirstName will show in
the unbound txtFirstname text box. Users can type the last name to go to the
first match of course and then scroll from there until they get to the
required first name.

Because LastName values can be duplicated you can't use the combo box's
NotInList event procedure to automatically enter a new name. Instead use the
combo box's DblClick event procedure to open a form bound to the Students
table at a new record and enter the name there. Code for the DblClick event
procedure would go like this:

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add new student to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmStudents", _
DataMode:=acFormAdd, _
WindowMode:=acDialog
ctrl.Requery
End If

You can if you wish use the NotInList event procedure to pop up a message
box telling users to double-click the control, but this will not fire if the
new student has a LastName which is shared by an existing student in the
Students table of course.

Ken Sheridan
Stafford, England

 

[piscogirl]

Thank you so much, Ken! Your response is extremely helpful!

Here are some questions that will probably let you in on just what
level of Access user I am...


"The StudentEvents table would have foreign key columns StudentID and

EventID, but of straightforward long integer number data type this
time, not
autonumbers. Together these two columns can be made the table's
composite
primary key."

How do I concatenate the two columns so that I can use them as the
Primary Key?




"For data entry you'd use an Events form based on the Events table
(or better
on a sorted query based on the table). The events subform would be
based on
the StudentEvents table or a query based on that table."

I've got my form based directly on the Events table. Why would it be
better to use a sorted query based on the table?

 

[piscogirl]

Thanks for replying, Jeff. Your single combo box solution is also in
Ken's plan of attack.

 

[Larry Linson]

How do I concatenate the two columns
so that I can use them as the
Primary Key?

In design view, highlight both Fields, then click the Key icon in the
Toolbar.

"For data entry you'd use an Events
form based on the Events table (or
better on a sorted query based on the
table). The events subform would be
based on the StudentEvents table or
a query based on that table."

I've got my form based directly on the
Events table. Why would it be
better to use a sorted query based on the table?

Because, by definition, Records in a relational database Table are
_unordered_. Most often, Records from a Table are displayed in Primary Key
order, but that is not guaranteed and could change if there are other
changes in your database.

If you want the Records in a particular order, retrieve them via a Query,
with sorting specified.

Larry Linson
Microsoft Access MVP

 

[Guest]

Melissa:

To add to what Larry has said, basing a form on a sorted query means that
when the form is opened the records will be in some meaningful order. With a
table's of people for instance you'd probably sort by LastName,FirstName so
the records came out in order by last name, and where several people have the
same last name by first name within each subset with the same last name.
With a table of financial transactions you might want to sort by
TransactionDate DESC so the latest transaction comes first.

Ken Sheridan
Stafford, England