Help needed with simple field problem (0/1)

[Doug]

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

 

[Doug]

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)

 

[Neil]

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.

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

 

[Doug]

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.

 

[Douglas J. Steele]

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]

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.

 

[Doug]

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

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.


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

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?

 

[Neil]

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.

 

[Doug]

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.

 

[John W. Vinson]

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

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

John W. Vinson [MVP]

 

[Neil]

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?

 

[Doug]

I added records using the record selectors. I haven't figured out how
to delete them yet - they don't appear on the Design View screen. I
haven't looked that hard - been doing other things.

Once I use the combo box to select a record, the record selector count
changes to 1. I can still arrow over to 2 and enter a new record.
When I do that the new record doesn't show up in the combo box list
(the others still do). If I close the form and reopen it, the combo
box sees the new record(s).

 

[Neil]

I added records using the record selectors. I haven't figured out how
to delete them yet - they don't appear on the Design View screen. I
haven't looked that hard - been doing other things.

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.

Once I use the combo box to select a record, the record selector count
changes to 1. I can still arrow over to 2 and enter a new record.
When I do that the new record doesn't show up in the combo box list
(the others still do). If I close the form and reopen it, the combo
box sees the new record(s).

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

That's all.

But, as I noted, you shouldn't add records with the navigation buttons if
you're only displaying one record at a time. So, here's what you do:

1) In the form's properties, set Allow Additions to No. That will prevent
the user from adding a record manually.

2) Add a button to your form for adding records. When the user clicks the
button, prompt them for any required field information. If there is no
required field information, and if the primary key is automatically
generated, then you don't need a prompt.

3) In the button's On Click event, after the form (if any) prompts for
values, do the following:

=======================================
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)

rs.AddNew
rs!FieldPK = somevalue
rs!OtherField = someothervalue 'This is optional
rs.Update

Me.Recordsource = "Select * From MyTable Where FieldPK="& somevalue

rs.close
set rs = nothing
=========================================

The above is for if you prompt the user for the PK (customer ID?). If the PK
is an autonumber PK, then you'd do this instead:

=========================================
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)

rs.AddNew
rs!OtherField = someothervalue 'This is optional.
rs.Update

rs.bookmark = rs.LastModified
Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK

rs.close
set rs = nothing
=========================================

4) While in a code module, go to Tools | References. Uncheck Microsoft
ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked,
then scroll down the list, and check the highest numbered version of it.

And that should do it.

Neil

 

[Doug]

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

I've put that in three places (below). The only thing that updates the
combo box is closing the form and reopening it.

Private Sub Combo36_GotFocus()
Me.Combo36.Requery
End Sub

Private Sub Form_AfterInsert()
Me.Combo36.Requery
End Sub

Private Sub Form_AfterUpdate()
Me.Combo36.Requery
End Sub


(SNIP)

The above is for if you prompt the user for the PK (customer ID?). If the PK
is an autonumber PK, then you'd do this instead:

=========================================
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)

rs.AddNew
rs!OtherField = someothervalue 'This is optional.
rs.Update

rs.bookmark = rs.LastModified
Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK

rs.close
set rs = nothing
=========================================

That worked. Thanks.

4) While in a code module, go to Tools | References. Uncheck Microsoft
ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked,
then scroll down the list, and check the highest numbered version of it.

And that should do it.

Neil

(SNIP)

 

[Wayne Gillespie]

I've put that in three places (below). The only thing that updates the
combo box is closing the form and reopening it.

Private Sub Combo36_GotFocus()
Me.Combo36.Requery
End Sub

Private Sub Form_AfterInsert()
Me.Combo36.Requery
End Sub

I usually use the OnEnter event when I want to requery a combo.
Wayne Gillespie
Gosford NSW Australia

 

[Neil]

Well, it should work. What's the rowsource of your combo box? Please copy
and paste it here exactly.

 

[Neil]

Problem with the OnEnter event is that then you're requerying the combo
every time you enter it, instead of just when the data changes.

 

[Neil]

Also, please note how you're adding records. Are you still adding them
manually through the form, or did you switch to adding them in code? The
requery of the combo box should work either way; but I just need to know
which method we're dealing with.

 

[Doug]

Well, it should work. What's the rowsource of your combo box? Please copy
and paste it here exactly.

Row Source Type: Table/Query
Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName],
[Clients].[MiddleName], [Clients].[LastName] FROM Clients;

I am adding records from a control button on the form now.

I tested this again, and now the combo box is updated with
Combo36_gotFocus(). I'd prefer to only update that when necessary,
not every time a new record is selected... I don't know why that
works now. The combo box is still not updated with either
Form_AfterInsert() or Form_AfterUpdate().

 

[Neil]

Well, if you changed the code to add records from a command button, per the
instructions I sent, then that would explain why it's not working from the
form's AfterUpdate or AfterInsert event: the form is not inserting the
record; you are, though the code. So the AfterUpdate and AfterInsert events
wouldn't apply.

Just put the combo box requery code at the end of the routine that adds the
new record, and you should be fine.

Well, it should work. What's the rowsource of your combo box? Please copy
and paste it here exactly.

Row Source Type: Table/Query
Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName],
[Clients].[MiddleName], [Clients].[LastName] FROM Clients;

I am adding records from a control button on the form now.

I tested this again, and now the combo box is updated with
Combo36_gotFocus(). I'd prefer to only update that when necessary,
not every time a new record is selected... I don't know why that
works now. The combo box is still not updated with either
Form_AfterInsert() or Form_AfterUpdate().