Sub Table

[Emma]

Hi I'm not sure where to start here. I'm now starting to beta test so I made
a backup then I deleted all of the fake entries from the database. So now the
tables are empty. I started entering real data and everything went wrong. The
number generator for the unique id (or client number) repeated itself for the
1st and 2nd entries then started working on the 3rd. Secondly the Case Notes
which are stored in a subtable of the original table are not unique and do
not reflect the client which is openned. I don't know what code to show you?
Does anyone have any advice it would be greatly appreciated?

 

[Emma]

I can see the problem now that the client ID isn't populating. I'm using it
in this unique Identifier =Format([Session Start Date],"yyyy\:mm") & [Client
ID]. I just want the client id to start at 1 and continue incrementing by 1
so we can track how many clients we have quickly.

 

[Gina Whipp]

Emma,

If you are using the AutoNumber as your number generator, then unforunately,
sometimes this happens. You might want to switch to generating your own
Unique ID, ie: DMax("YourPrimaryKey", "YourTable") + 1. You also do not
indicate whether you have set up your Uniquie ID as a Primary Key, No
Duplicates.

As for the Case Notes not reflecting the correct Unique ID. First I would
say to make sure relationships are set for 'Cascading Updates', then if you
subform is not pulling the correct ID you might have to write something in
the Befroe_Update of the subform to 'force' the issue.

It would also help to give us the fields of the tables in question.

 

[John W. Vinson]

I can see the problem now that the client ID isn't populating. I'm using it
in this unique Identifier =Format([Session Start Date],"yyyy\:mm") & [Client
ID]. I just want the client id to start at 1 and continue incrementing by 1
so we can track how many clients we have quickly.

No, you *don't* want to do this.

Primary keys are one thing. Counting records is a *very different* thing!
Think about this scenario: you have 2381 clients in the database, each record
related to anywhere from 1 to 40 records in child tables.

You find that clientID 2 was entered in error and must be deleted.

To have the ID equal to the count, you would need to reassign 2379 ID's,
change them in all the related tables, change them on all the printouts you've
ever done, change them in all the minds of people who remember them... OUCH!

Your ID should be a unique, meaningless identifier. It should NOT contain any
other information such as a count, or a date!

If you want to count clients... use a totals query to count clients. It might
take as much as a couple of seconds to run, but it doesn't compromise the
integrity of your data structure!

 

[Gina Whipp]

John,

I don't even see that message from Emma. Is Microsoft having issues again?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

I can see the problem now that the client ID isn't populating. I'm using
it
in this unique Identifier =Format([Session Start Date],"yyyy\:mm") &
[Client
ID]. I just want the client id to start at 1 and continue incrementing by
1
so we can track how many clients we have quickly.

No, you *don't* want to do this.

Primary keys are one thing. Counting records is a *very different* thing!
Think about this scenario: you have 2381 clients in the database, each
record
related to anywhere from 1 to 40 records in child tables.

You find that clientID 2 was entered in error and must be deleted.

To have the ID equal to the count, you would need to reassign 2379 ID's,
change them in all the related tables, change them on all the printouts
you've
ever done, change them in all the minds of people who remember them...
OUCH!

Your ID should be a unique, meaningless identifier. It should NOT contain
any
other information such as a count, or a date!

If you want to count clients... use a totals query to count clients. It
might
take as much as a couple of seconds to run, but it doesn't compromise the
integrity of your data structure!

 

[John W. Vinson]

I don't even see that message from Emma. Is Microsoft having issues again?

I'm afraid so. Several MVP's posted that they were seeing "message not
available" warnings and missing messages earlier today.

 

[Gina Whipp]

Okie dokie then... THANKS!

 

[Emma]

Thanks John you are very right about records being deleted and screwing up my
count. I set the Client ID to Indexed(Yes; no Duplicates) and it seems to be
working now.

 

[Emma]

Hi can anyone tell me how to avoid getting the number 0 twice which won't
allow because I set it to no duplicates, the new record has an id of 0 and
the first id is 0 so when I go to enter a record in a field that down't have
an id yet but when I go to new record it works fine. Here's the error message:

The changed you requested to the table were not successful because they
would create duplicate values in the index, primary key or relationship.
Change the data in the field or fields that contain the duplicate data,
remove the index or redefine the index to permit duplicate entries and try
again.

Please note I'm using a button to go to the next record and it brings up the
field with the 0 in it is there anything I can change in the button's VB?

 

[John W. Vinson]

Hi can anyone tell me how to avoid getting the number 0 twice which won't
allow because I set it to no duplicates, the new record has an id of 0 and
the first id is 0 so when I go to enter a record in a field that down't have
an id yet but when I go to new record it works fine. Here's the error message:

The changed you requested to the table were not successful because they
would create duplicate values in the index, primary key or relationship.
Change the data in the field or fields that contain the duplicate data,
remove the index or redefine the index to permit duplicate entries and try
again.

Please note I'm using a button to go to the next record and it brings up the
field with the 0 in it is there anything I can change in the button's VB?

First off, open the table in design view and remove the 0 from the field's
Default Value property. Access (stupidly!!) puts a 0 as the default on any
number field; that's sometimes a good default but often it's not, as in this
case.

Then you'll need some VB code to assign a new ID when appropriate... but it
may not be totally obvious when it IS appropriate! Is this a one-user
database, or is it shared among multiple users? Do you always enter data
through this form, or might you create new records by importing or
programmatically? It's possible to automatically assign an incrementing ID in
any of these cases, but the code would be different.