Combo box and table relationships doesn't work as expected

[Jane Schuster]

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?

 

[Jeff Boyce]

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]

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

 

[Jane Schuster]

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?

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

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?

 

[Ken Sheridan]

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]

Me again. Yes, I was trying to do what you suggested yesterday, but I ran
into a bump and now I have run into another one. I followed the steps below,
added a ClientScheduleID to the tblClientPlan, created the relationship, the
form and the combo boxes. The ClientName field works great, the DueDate
works great, but for some reason, after I've entered some plan information
and then look at the tblClientPlan, all of the info is there except the
ClientID, ScheduleID and DueDate. Obviously I'm missing something.

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

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?

 

[Ken Sheridan]

Jane:

As Jeff said in his reply it sounds like the combo boxes are not 'bound' to
the ClientID and ClientScheduleID (I'm assuming that's what you meant by
ScheduleID) columns. Their ControlSource properties should be ClientID and
ClientScheduleID respectively. Also, with the use of the ClientScheduleID
column in the tblClientPlan table you don't need a DueDate column in that
table as the relationship via the ClientScheduleID columns pulls in the date
from the tblClientSchedule table.

If all else fails feel free to mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

and I can send you a little file with the tables and form set up.

Ken Sheridan
Stafford, England

Me again. Yes, I was trying to do what you suggested yesterday, but I ran
into a bump and now I have run into another one. I followed the steps below,
added a ClientScheduleID to the tblClientPlan, created the relationship, the
form and the combo boxes. The ClientName field works great, the DueDate
works great, but for some reason, after I've entered some plan information
and then look at the tblClientPlan, all of the info is there except the
ClientID, ScheduleID and DueDate. Obviously I'm missing something.

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

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?

 

[Jane Schuster]

Thank you, thank you. It's now working perfectly. I should have just
followed your instructions instead of trying to use them and the book to
figure out what I was doing. I now know what it was that I was doing. Your
patience and help is very much appreciated.

Jane:

As Jeff said in his reply it sounds like the combo boxes are not 'bound' to
the ClientID and ClientScheduleID (I'm assuming that's what you meant by
ScheduleID) columns. Their ControlSource properties should be ClientID and
ClientScheduleID respectively. Also, with the use of the ClientScheduleID
column in the tblClientPlan table you don't need a DueDate column in that
table as the relationship via the ClientScheduleID columns pulls in the date
from the tblClientSchedule table.

If all else fails feel free to mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

and I can send you a little file with the tables and form set up.

Ken Sheridan
Stafford, England

Me again. Yes, I was trying to do what you suggested yesterday, but I ran
into a bump and now I have run into another one. I followed the steps below,
added a ClientScheduleID to the tblClientPlan, created the relationship, the
form and the combo boxes. The ClientName field works great, the DueDate
works great, but for some reason, after I've entered some plan information
and then look at the tblClientPlan, all of the info is there except the
ClientID, ScheduleID and DueDate. Obviously I'm missing something.

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

:

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?