Prev: Converting to Access 2003 (from 2000 format)
Next: Making it stay the right size
From: Jane Schuster on 30 Jun 2008 12:43
I have 3 tables

tblClient tbClientSchedule tblClientPlan
ClientID ClientScheduleID ClientPlansID
Client Name ClientID ClientID
Address DueDate Plan

PlanRcvdDate

Clients submit plans according to a set schedule. I created a form that
tracks the date each plan is received, but I wanted to tie that to the date
it was due, so I created a data entry form with the DueDate in a Combo Box.
The problem is, the DueDate doesn't stick with the record. I thought this
was one way to relate the two tables ClientSchedule and ClientPlan. This is
all new to me, but from what I've read here, this should work. What am I
missing?

From: Jeff Boyce on 30 Jun 2008 13:43
Jane

If the "[combobox's] DueDate doesn't stick with the record", there's a
pretty good chance that the combobox is "unbound". That is, the form
doesn't know which field to connect the data to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jane Schuster" <JaneSchuster(a)discussions.microsoft.com> wrote in message
news:B95A33A4-FD69-41FA-9590-589803B69F78(a)microsoft.com...
>I have 3 tables
>
> tblClient tbClientSchedule tblClientPlan
> ClientID ClientScheduleID ClientPlansID
> Client Name ClientID ClientID
> Address DueDate Plan
>
> PlanRcvdDate
>
> Clients submit plans according to a set schedule. I created a form that
> tracks the date each plan is received, but I wanted to tie that to the
> date
> it was due, so I created a data entry form with the DueDate in a Combo
> Box.
> The problem is, the DueDate doesn't stick with the record. I thought this
> was one way to relate the two tables ClientSchedule and ClientPlan. This
> is
> all new to me, but from what I've read here, this should work. What am I
> missing?
>


From: Jane Schuster on 30 Jun 2008 13:50


"Jane Schuster" wrote:

> I have 3 tables
>
> tblClient tbClientSchedule tblClientPlan
> ClientID ClientScheduleID ClientPlansID
> Client Name ClientID ClientID
> Address DueDate Plan
>

Tb
> PlanRcvdDate
>
> Clients submit plans according to a set schedule. I created a form that
> tracks the date each plan is received, but I wanted to tie that to the date
> it was due, so I created a data entry form with the DueDate in a Combo Box.
> The problem is, the DueDate doesn't stick with the record. I thought this
> was one way to relate the two tables ClientSchedule and ClientPlan. This is
> all new to me, but from what I've read here, this should work. What am I
> missing?
>
>This is hopefully more readable
>
> tblClient
> ClientID
> ClientName
> Address
>
> tblClientSchedule
> ClientScheduleID
> ClientID
> DueDate
>
>tblClientPlan
>ClientPlanID
>ClientID
>Plan
>DateRcvd

> Clients submit plans according to a set schedule. I created a form that
> tracks the date each plan is received, but I wanted to tie that to the date
> it was due, so I created a data entry form with the DueDate in a Combo Box.
> The problem is, the DueDate doesn't stick with the record. I thought this
> was one way to relate the two tables ClientSchedule and ClientPlan. This is
> all new to me, but from what I've read here, this should work. What am I
> missing?
>
From: Jane Schuster on 30 Jun 2008 15:27
When I created the ComboBox I used the Wizard and chose to lookup values from
the tblClientSchedule, specifically, the DueDate, which is listed as the
control source in the properties of the combobox. What step did I miss? Or,
rather, is there something else to bind it to the form?

"Jeff Boyce" wrote:

> Jane
>
> If the "[combobox's] DueDate doesn't stick with the record", there's a
> pretty good chance that the combobox is "unbound". That is, the form
> doesn't know which field to connect the data to.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Jane Schuster" <JaneSchuster(a)discussions.microsoft.com> wrote in message
> news:B95A33A4-FD69-41FA-9590-589803B69F78(a)microsoft.com...
> >I have 3 tables
> >
> > tblClient tbClientSchedule tblClientPlan
> > ClientID ClientScheduleID ClientPlansID
> > Client Name ClientID ClientID
> > Address DueDate Plan
> >
> > PlanRcvdDate
> >
> > Clients submit plans according to a set schedule. I created a form that
> > tracks the date each plan is received, but I wanted to tie that to the
> > date
> > it was due, so I created a data entry form with the DueDate in a Combo
> > Box.
> > The problem is, the DueDate doesn't stick with the record. I thought this
> > was one way to relate the two tables ClientSchedule and ClientPlan. This
> > is
> > all new to me, but from what I've read here, this should work. What am I
> > missing?
> >
>
>
>
From: Ken Sheridan on 30 Jun 2008 18:15
Jane:

It looks like you are trying to implement the advice I gave you in your
other thread. I'd have thought that PlanRcvdDate should be a column (field)
in the tblClientPlan table. In your post it looks more like it's a column in
tblClient (that might be just the way the newsreader is showing it, however,
and not what you intended).

With the PlanRcvdDate column in tblClientPlan you are tracking when a plan
is received. To "tie that to the date it was due" I suggested in my reply to
your other post that you also needed a DueDate column in tblClientPlan as
well as in tblClientSchedule, with ClientID and DueDate being the composite
primary key of tblClientSchedule. However, you have included a
ClientScheduleID, presumably an autonumber column, as the primary key of
ClientScheduleID. So you can relate the tables on a single column rather
than two, by adding a ClientScheduleID column to the tblClientPlan table. In
this case it should not be an autonumber, but a straightforward (long
integer) number data type; just select 'number' as the data type. This
column is a foreign key in tblClientPlan and its by this you can "tie that
(the date received) to the date it was due"

In a form based on the tblClientPlan table you'll have two combo boxes, one
set up with its properties like this:

Name: cboClient

ControlSource: ClientID

RowSource: SELECT ClientID, [Client Name] FROM tblClient ORDER BY
[Client Name];

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 this combo box's AfterUpdate event procedure put the following line of
code which causes the combo box to show only the dates from the selected
client's schedule:

Me.cboDueDate.Requery

To do this select the combo box control in form design view and open its
properties sheet if its not already open. Then select the After Update event
property in the properties sheet. Click on the 'build' button; that's the
one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the line of code between these two
existing lines.

You also need to put the same line of code in the form's Current event
procedure, which you do in the same way after selecting the On Current event
property in the form's properties sheet.

The other combo will be set up similarly with its properties like this:

Name: cboDueDate

ControlSource: ClientScheduleID

RowSource: SELECT ClientScheduleID, DueDate FROM tblClientSchedule WHERE
ClientID = Form!cboClient ORDER BY DueDate;

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

In this case no code is needed in any event procedure.

The controls on the form bound to the Plan and PlanRcvdDate will be text
boxes.

The way the form will work is that you'd first select a client from the
first combo box and then select a due date from the second. The Plan and
PlanRcvdDate values can be filled in at any stage. Presumably you'd fill in
Plan when creating a new record, and PlanRcvdDate at a later date when the
client submits it.

You can identify clients who fail to meet the deadline with a simple query
on the tblClientPlan table as I explained in my other reply, though you'd
probably include the tblClient table in the query as well so you can return
the clients' names rather than just the ClientID values.

Ken Sheridan
Stafford, England

"Jane Schuster" wrote:

> I have 3 tables
>
> tblClient tbClientSchedule tblClientPlan
> ClientID ClientScheduleID ClientPlansID
> Client Name ClientID ClientID
> Address DueDate Plan
>
> PlanRcvdDate
>
> Clients submit plans according to a set schedule. I created a form that
> tracks the date each plan is received, but I wanted to tie that to the date
> it was due, so I created a data entry form with the DueDate in a Combo Box.
> The problem is, the DueDate doesn't stick with the record. I thought this
> was one way to relate the two tables ClientSchedule and ClientPlan. This is
> all new to me, but from what I've read here, this should work. What am I
> missing?
>

 |  Next  |  Last
Pages: 1 2
Prev: Converting to Access 2003 (from 2000 format)
Next: Making it stay the right size