Display a list of records (report?) in a text box based on theselection from a combo-box

[Ratbert_CP]

This should be simple...

I have two tables in my DB, one is a simple name/ID lookup, the other
is an ID/Date/Notes table, that contains all the meeting notes. I
have two combo boxes that properly work in sync to select either the
name or the ID of a target person, and even a date picker to set a
date other than today. I also have an enabled text field to allow the
insertion of new notes (although I use VBA and raw SQL to do the
insertions). What I'm having trouble with is getting a locked/
disabled text field to display a sorted list of all date/notes entries
for the selected target ID.

The use case is something like this: 1) Select a target person by name
or ID, 2) Review historical notes, 3) Enter and save new notes.

I'm familiar with RDBS and SQL as a query language, but Access is
foreign to me. The reason I'm not doing a more involved project is
that I wanted this to be a quickly knocked out tool for my wife to
use, and I wanted to leverage the existing tools she would have on her
work computer. So much for that. I'm already over schedule...

Thank you for any help, I've Googled and sifted to no avail. I'm
either trying to do something so basic, no one has any tutorials or
how-tos on the subject, or I'm going at it from such a non-Access way
that no one would think it was an issue. Please enlighten me!

Ken Cummins

 

[Ken Sheridan]

Ken:

The simplest option would be a form based on the 'people' table and a
subform within it based on the 'notes' table. The main 'people' form must be
in single form view, but the subform can be in single, continuous form or
even datasheet view.

Start by designing the subform, with its RecordSource a query along these
lines:

SELECT *
FROM People
ORDER BY lastname, firstname;

Don't include a control bound to the ID column in the subform. That will be
populated automatically behind the scenes via the linking mechanism.

Then design the parent form, basing it on a query such as:

SELECT *
FROM Notes
ORDER BY NoteDate;

The parent form will be sorted by name, the subform by date. If you want
the subform sorted with the latest date first ORDER BY NoteDate DESC;

Embed the subform in the parent form, setting the LinkMasterFields and
LinkChildFields properties of the subform control to ID.

You'll now see just the notes for the current person as you navigate through
the parent form. Adding a new note is simply a case of inserting a new row
in the subform.

For navigating to a particular person add an unbound combo box,
cboFindperson say, to the parent form set up like this:

RowSource: SELECT ID, firstname & " " & lastname FROM People ORDER BY
lastname, firstname;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

In the combo box's AfterUpdate event procedure put:

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone
Set ctrl = Me.ActiveControl

With rst
.FindFirst "ID = " & ctrl
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

This assumes the ID columns are a number data type. If by any chance they
are text amend the code to:

.FindFirst "ID = """ & ctrl & """"

In the parent form's Current event procedure put:

Me.cboFindPerson = Me.ID

to keep the combo box in sync with form if the user navigates with the
navigation bar or keyboard.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

PS: To lock/disable the controls in the subform other than when entering a
new record put something like the following in the subform's Current event
procedure:

Me.txtNotes.Enabled = Me.NewRecord
Me.txtNotes.Locked = Not Me.NewRecord

Repeat for whatever controls you want to lock/disable.

Ken Sheridan
Stafford, England

 

[Ken Cummins]

Thanks! That did the trick, and it helped me understand how Access
works. Of course, I'm still new to this, so...

I've reworked my form into a tabbed form, and I'd like to use some
aggregate data in one of the pages. i.e., Select a person, and have
the first, last and count of their notes. Is this something that's
reasonable to do? I tried a subform, but it didn't seem to work
well... Wait... Be right back... Never mind. The headers and
footers won't show up in datasheet view, will they? :^P

Thanks again!

Ken Cummins

 

[Ken Sheridan]

Ken:

One word of warning; don't assume that the First and Last operators do what
they say. Tables in a relational database are sets and sets have no
intrinsic order, so concepts like first and last are pretty meaningless.
Order can be imposed by an ORDER BY clause in a query, the OrderBy property
of a form or report, or by sorting a report by means of its internal sorting
and grouping mechanism, but a table as such has no order.

First note in this context really means the note for this person with the
earliest date, and conversely last not means the note for this person with
the latest date. One way would be to base the subform on a query with a
subquery to return the latest note, but the query would not be updatable so
you wouldn't be able to add notes in the subform. You can use a combination
of the DLookup and Din or DMax functions, however. This could be done in the
query, but its simpler to do it in an unbound text box control on the report.
For the first note the ControlSource property would be:

=DLookup("[Note]","[Notes]","[ID] =" & [ID] & " And [NoteDate]=#" &
Format(DMin("NoteDate",[Notes]",""[ID] =" & [ID]),"yyyy-mm-dd") & "#"))

For the last:

=DLookup("[Note]","[Notes]","[ID] =" & [ID] & " And [NoteDate]=#" &
Format(DMax("NoteDate",[Notes]",""[ID] =" & [ID]),"yyyy-mm-dd") & "#"))

These should be entered as a single line in the controls' properties sheets;
they'll probably have been split over several lines by your newsreader.

The formatting of the date BTW is because date literals have to be either in
US date format or an otherwise internationally unambiguous format.
YYYY-MM-DD is the ISO format for data notation.

Ken Sheridan
Stafford, England