Help needed with simple field problem (0/1)

#1

Hi,

I'm new to Access. I am a programmer, but my only database experience
was when I made a medium sized FoxBase application for a small
business years ago.

The project I've started working on is way to simple to justify
writing an application. The database just needs to have client contact
information, appointment dates, pending messages, and payments
records.

I'm using Microsoft Access 2000.

I started learning this today. Here's my hang-up:

I'm trying to get a field to display a Clients name from another
table. When I select the client ID, while adding a new record (with a
Combo Box), it will only place the ClientID in the record, and not the
name.

I tried to do it the way it is in the sample NWIND database (attached
here) that comes with Access. Select the Orders table. When you add a
new record, there's a drop down for customer name. When you select
one, the name is added to the record (as expected...).

Now open the ClientDB database (attached here), select the Messages
table, and add a new record. When you select a client name (there's
only two), it places the ClientID in the Clients name field. I tried
changing different things, but I can't get it to work. I don't see a
significant difference between what I have and what's in the NWIND
database. I've spent a couple hours trying to get it to display the
clients name.

Any help appreciated.

I have attached the two databases to this message. (The NWIND sample
database open with 5 errors, but it does what I'm doing here okay.)

I have another question. Is there going to be a way to add messages
(table) and appointments (table) from the customers table? (Rather
than selecting the messages table, and then selecting the customers
name from a drop down list.) The person this is for is going to want
to be able to do everything from the customer list. (Adding records,
that is - I will set up reports he can select. He's going to want to
select a customer, and then do whatever with it - add dates, add
messgaes.) If there's not going to be a way to do this (other than
switching to the given table...), I need to stop here and find another
application, rather than learn this one. (If so, suggestions?)

#2

The Attachments didn't get included.
I just put them on OrbitFiles instead.
You can download them here:
http://www.orbitfiles.com/download/id2209446471.html (ClientDB)
http://www.orbitfiles.com/download/id2209445579.html (ClientDB)
http://www.orbitfiles.com/download/id2209448731.html (NWIND)

On Thu, 15 Nov 2007 05:44:58 -0500, Doug (AT) NoEmail (DOT) com wrote:

Quote:

Hi,

I'm new to Access. I am a programmer, but my only database experience
was when I made a medium sized FoxBase application for a small
business years ago.

The project I've started working on is way to simple to justify
writing an application. The database just needs to have client contact
information, appointment dates, pending messages, and payments
records.

I'm using Microsoft Access 2000.

I started learning this today. Here's my hang-up:

I'm trying to get a field to display a Clients name from another
table. When I select the client ID, while adding a new record (with a
Combo Box), it will only place the ClientID in the record, and not the
name.

I tried to do it the way it is in the sample NWIND database (attached
here) that comes with Access. Select the Orders table. When you add a
new record, there's a drop down for customer name. When you select
one, the name is added to the record (as expected...).

Now open the ClientDB database (attached here), select the Messages
table, and add a new record. When you select a client name (there's
only two), it places the ClientID in the Clients name field. I tried
changing different things, but I can't get it to work. I don't see a
significant difference between what I have and what's in the NWIND
database. I've spent a couple hours trying to get it to display the
clients name.

Any help appreciated.

I have attached the two databases to this message. (The NWIND sample
database open with 5 errors, but it does what I'm doing here okay.)

I have another question. Is there going to be a way to add messages
(table) and appointments (table) from the customers table? (Rather
than selecting the messages table, and then selecting the customers
name from a drop down list.) The person this is for is going to want
to be able to do everything from the customer list. (Adding records,
that is - I will set up reports he can select. He's going to want to
select a customer, and then do whatever with it - add dates, add
messgaes.) If there's not going to be a way to do this (other than
switching to the given table...), I need to stop here and find another
application, rather than learn this one. (If so, suggestions?)

#3

Quote:

I'm trying to get a field to display a Clients name from another
table. When I select the client ID, while adding a new record (with a
Combo Box), it will only place the ClientID in the record, and not the
name.

Not sure what you're trying to do. But I'll cover all three cases I can
think of

1) If you're trying to simply display the client's name in the combo box,
which is bound to the client ID, then make the combo box have two columns,
with the second column being the name, and set the width of the first column
to 0, which will hide the client ID.

2) If you're trying to populate a client name field in the form that also
has the client ID field (which would be a non-normalized design), then use
the AfterUpdate event of the combo box to populate that field.

3) If (as I think is the case) you want to display information from the
Clients table when you select a Client ID in the form bound to the Messages
table, then what you do is:

a) In the form's recordsource, add the Clients table. So you would have
two tables in the recordsource: Clients and Messages, and join them on the
ClientID field.

b) Bind the client-related controls in your form to the fields from the
Clients table.

c) Make sure your combo box is bound to the ClientID field in the
Messages table.

Now when you select a ClientID from the drop-down, the data from the
fields from the corresponding record in the Clients table should
automatically appear on the form.

Quote:

I have another question. Is there going to be a way to add messages
(table) and appointments (table) from the customers table? (Rather
than selecting the messages table, and then selecting the customers
name from a drop down list.) The person this is for is going to want
to be able to do everything from the customer list

Yes, you can do that one of two ways. Either create subforms in the
Customers form for the Messages and Appointments tables (the subforms will
be linked on the ClientID field, and Access will manage creating and
filtering the records for you); or use pop-ups from the Customers form to
enter Messages and Appointments (here, you'd have to manage the records
yourself).

Obviously, subforms are a lot more convenient, both for you to set up, as
well as for the user to use, since they would be right there on the form,
and the data is visible at all times. The downside of subforms is form real
estate, since they take up room. If you don't have a lot of room to work
with; or if you want a lot of information in your subforms, then pop-ups
might be better. Subforms also use up more system resources, since they're
open all the time. But I don't think that's anything you'd have to worry
about, really.

.. > that is - I will set up reports he can select. He's going to want to

Quote:

select a customer, and then do whatever with it - add dates, add
messgaes.) If there's not going to be a way to do this (other than
switching to the given table...), I need to stop here and find another
application, rather than learn this one. (If so, suggestions?)

I think you'll find Access subforms to be just what you're looking for. Very
easy to work with, and a very convenient tool.

Neil

#4

Well, the attachements did show up. I'm using a new newsreader. The
attachement are in a seperate message with a 1/1 appended to the
subject header.

On Thu, 15 Nov 2007 06:04:42 -0500, Doug (AT) NoEmail (DOT) com wrote:

Quote:

The Attachments didn't get included.
I just put them on OrbitFiles instead.
You can download them here:
http://www.orbitfiles.com/download/id2209446471.html (ClientDB)
http://www.orbitfiles.com/download/id2209445579.html (ClientDB)
http://www.orbitfiles.com/download/id2209448731.html (NWIND)

On Thu, 15 Nov 2007 05:44:58 -0500, Doug (AT) NoEmail (DOT) com wrote:

Hi,

I'm new to Access. I am a programmer, but my only database experience
was when I made a medium sized FoxBase application for a small
business years ago.

The project I've started working on is way to simple to justify
writing an application. The database just needs to have client contact
information, appointment dates, pending messages, and payments
records.

I'm using Microsoft Access 2000.

I started learning this today. Here's my hang-up:

I'm trying to get a field to display a Clients name from another
table. When I select the client ID, while adding a new record (with a
Combo Box), it will only place the ClientID in the record, and not the
name.

I tried to do it the way it is in the sample NWIND database (attached
here) that comes with Access. Select the Orders table. When you add a
new record, there's a drop down for customer name. When you select
one, the name is added to the record (as expected...).

Now open the ClientDB database (attached here), select the Messages
table, and add a new record. When you select a client name (there's
only two), it places the ClientID in the Clients name field. I tried
changing different things, but I can't get it to work. I don't see a
significant difference between what I have and what's in the NWIND
database. I've spent a couple hours trying to get it to display the
clients name.

Any help appreciated.

I have attached the two databases to this message. (The NWIND sample
database open with 5 errors, but it does what I'm doing here okay.)

I have another question. Is there going to be a way to add messages
(table) and appointments (table) from the customers table? (Rather
than selecting the messages table, and then selecting the customers
name from a drop down list.) The person this is for is going to want
to be able to do everything from the customer list. (Adding records,
that is - I will set up reports he can select. He's going to want to
select a customer, and then do whatever with it - add dates, add
messgaes.) If there's not going to be a way to do this (other than
switching to the given table...), I need to stop here and find another
application, rather than learn this one. (If so, suggestions?)

#5

It would be redundant to store both the ClientID and ClientName in the
second table. What happens if you change the name in one of the two tables:
how will you know which name is correct?

Only the foreign key (the ClientID) should be stored in the second table.
You can join the two tables together in a query and use the query wherever
you would otherwise have used the table when you need the name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

<Doug (AT) NoEmail (DOT) com> wrote

Quote:

Hi,

I'm new to Access. I am a programmer, but my only database experience
was when I made a medium sized FoxBase application for a small
business years ago.

The project I've started working on is way to simple to justify
writing an application. The database just needs to have client contact
information, appointment dates, pending messages, and payments
records.

I'm using Microsoft Access 2000.

I started learning this today. Here's my hang-up:

I'm trying to get a field to display a Clients name from another
table. When I select the client ID, while adding a new record (with a
Combo Box), it will only place the ClientID in the record, and not the
name.

I tried to do it the way it is in the sample NWIND database (attached
here) that comes with Access. Select the Orders table. When you add a
new record, there's a drop down for customer name. When you select
one, the name is added to the record (as expected...).

Now open the ClientDB database (attached here), select the Messages
table, and add a new record. When you select a client name (there's
only two), it places the ClientID in the Clients name field. I tried
changing different things, but I can't get it to work. I don't see a
significant difference between what I have and what's in the NWIND
database. I've spent a couple hours trying to get it to display the
clients name.

Any help appreciated.

I have attached the two databases to this message. (The NWIND sample
database open with 5 errors, but it does what I'm doing here okay.)

I have another question. Is there going to be a way to add messages
(table) and appointments (table) from the customers table? (Rather
than selecting the messages table, and then selecting the customers
name from a drop down list.) The person this is for is going to want
to be able to do everything from the customer list. (Adding records,
that is - I will set up reports he can select. He's going to want to
select a customer, and then do whatever with it - add dates, add
messgaes.) If there's not going to be a way to do this (other than
switching to the given table...), I need to stop here and find another
application, rather than learn this one. (If so, suggestions?)

#6

The clients name isn't be stored in the Messages table. I just want it
displayed when you are adding messages to the table, like what happens
in the sample NWIND database when you add an order. The problem is I
can't get it to work like NWIND, and I don't see why. The databases
are posted here in a message with the same subject, ending in 1/1
instead of 0/1.

On Thu, 15 Nov 2007 06:56:43 -0500, "Douglas J. Steele"
<NOSPAM_djsteele (AT) NOSPAM_canada (DOT) com> wrote:

Quote:

It would be redundant to store both the ClientID and ClientName in the
second table. What happens if you change the name in one of the two tables:
how will you know which name is correct?

Only the foreign key (the ClientID) should be stored in the second table.
You can join the two tables together in a query and use the query wherever
you would otherwise have used the table when you need the name.

#7

On Thu, 15 Nov 2007 11:04:46 GMT, "Neil" <nospam (AT) nospam (DOT) net> wrote:

Quote:

I'm trying to get a field to display a Clients name from another
table. When I select the client ID, while adding a new record (with a
Combo Box), it will only place the ClientID in the record, and not the
name.

Not sure what you're trying to do. But I'll cover all three cases I can
think of

1) If you're trying to simply display the client's name in the combo box,
which is bound to the client ID, then make the combo box have two columns,
with the second column being the name, and set the width of the first column
to 0, which will hide the client ID.

I guessed and put 0,1 here, which got converted to 0";1" - which
solved my problem. Thanks. (I hadn't made any forms yet.)

Quote:

2) If you're trying to populate a client name field in the form that also
has the client ID field (which would be a non-normalized design), then use
the AfterUpdate event of the combo box to populate that field.

3) If (as I think is the case) you want to display information from the
Clients table when you select a Client ID in the form bound to the Messages
table, then what you do is:

a) In the form's recordsource, add the Clients table. So you would have
two tables in the recordsource: Clients and Messages, and join them on the
ClientID field.

b) Bind the client-related controls in your form to the fields from the
Clients table.

c) Make sure your combo box is bound to the ClientID field in the
Messages table.

Now when you select a ClientID from the drop-down, the data from the
fields from the corresponding record in the Clients table should
automatically appear on the form.

I have another question. Is there going to be a way to add messages
(table) and appointments (table) from the customers table? (Rather
than selecting the messages table, and then selecting the customers
name from a drop down list.) The person this is for is going to want
to be able to do everything from the customer list

Yes, you can do that one of two ways. Either create subforms in the
Customers form for the Messages and Appointments tables (the subforms will
be linked on the ClientID field, and Access will manage creating and
filtering the records for you); or use pop-ups from the Customers form to
enter Messages and Appointments (here, you'd have to manage the records
yourself).

Obviously, subforms are a lot more convenient, both for you to set up, as
well as for the user to use, since they would be right there on the form,
and the data is visible at all times. The downside of subforms is form real
estate, since they take up room. If you don't have a lot of room to work
with; or if you want a lot of information in your subforms, then pop-ups
might be better. Subforms also use up more system resources, since they're
open all the time. But I don't think that's anything you'd have to worry
about, really.

. > that is - I will set up reports he can select. He's going to want to
select a customer, and then do whatever with it - add dates, add
messgaes.) If there's not going to be a way to do this (other than
switching to the given table...), I need to stop here and find another
application, rather than learn this one. (If so, suggestions?)

I think you'll find Access subforms to be just what you're looking for. Very
easy to work with, and a very convenient tool.

Neil

I set up a form, with a subform. This will works well. Thanks.

On the form, I would like a combo box on top, where I can choose a
different client from a list, which changes what record I'm looking at
- like the arrow buttons at the bottom do (which I'd like to remove).
Can this be done with an 'on change' event?

#8

No, you'd use the AfterUpdate event of the combo box, and then use FindFirst
against the recordsetclone object to find the appropriate record. After
finding the appropriate record, set the form's Bookmark property to the
Recordsetclone's Bookmark property.

But, if you're going to get rid of the navigation buttons at the bottom and
are always going to just display one record, then a better approach would be
to just reset the form's recordsource after a client is selected:

Private Sub MyCombo_AfterUpdate

If Not Isnull(Me.MyCombo) Then
Me.Recordsource = "Select * From MyClientTable Where ClientID=" &
Me.MyCombo
End If

End Sub

You could also refer to a query instead of the table, if you prefer. The
subforms should requery themselves automatically.

Quote:

I set up a form, with a subform. This will works well. Thanks.

On the form, I would like a combo box on top, where I can choose a
different client from a list, which changes what record I'm looking at
- like the arrow buttons at the bottom do (which I'd like to remove).
Can this be done with an 'on change' event?

#9

On Fri, 16 Nov 2007 07:23:08 GMT, "Neil" <nospam (AT) nospam (DOT) net> wrote:

Quote:

No, you'd use the AfterUpdate event of the combo box, and then use FindFirst
against the recordsetclone object to find the appropriate record. After
finding the appropriate record, set the form's Bookmark property to the
Recordsetclone's Bookmark property.

But, if you're going to get rid of the navigation buttons at the bottom and
are always going to just display one record, then a better approach would be
to just reset the form's recordsource after a client is selected:

Private Sub MyCombo_AfterUpdate

If Not Isnull(Me.MyCombo) Then
Me.Recordsource = "Select * From MyClientTable Where ClientID=" &
Me.MyCombo
End If

End Sub

That worked. Thanks again.

When new records are added, they don't show up in the combo box until
I close the client form and reopen it.

Quote:

You could also refer to a query instead of the table, if you prefer. The
subforms should requery themselves automatically.

I set up a form, with a subform. This will works well. Thanks.

On the form, I would like a combo box on top, where I can choose a
different client from a list, which changes what record I'm looking at
- like the arrow buttons at the bottom do (which I'd like to remove).
Can this be done with an 'on change' event?