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...).

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@NoEmail.com wrote:

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.

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!)

NoEmail.com> wrote in message
news:6s5oj3509ih3mhd7krliig838mb4or2rgh@4ax.com...

On Thu, 15 Nov 2007 11:04:46 GMT, "Neil" nospam.net> wrote:

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.)

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.

On Fri, 16 Nov 2007 07:23:08 GMT, "Neil" nospam.net> wrote:

That worked. Thanks again.

On Fri, 16 Nov 2007 23:14:44 -0500, Doug@NoEmail.com wrote:

Requery the combo box in the code which adds the new records (the form's
AfterUpdate event for example).

John W. Vinson [MVP]

How are you adding new records? In the above, you stated that you wanted to
take out the record selectors and only show one record at a time. If so,
then you should not let your users add new records by going to the new
record in the form (because then you'll have two records in the form instead
of one, when they add a new record). So, how are you adding new records?

By "record selectors" I assume you mean "navigation buttons" at the bottom
of the screen, right? (The record selector is the vertical bar on the left
that has an arrowhead in it.)

You can turn off the navigation bar and/or the record selector in the form's
property. They're both listed there with Yes/No options.

Well, as John noted, just requery the combo box when a new record is
inserted (I'd do the After Insert event of the form). Just do:

Me.MyComboBox.Requery