autoincrement PK

[Guest]

I'm trying to simply increment a field in a table when i am in a form.
The table is tblhome and the form field is Home_addr_PK. The last record is
HA00052. When i create a new record, i would like HA0053 populated in the
Home_Addr_PK field. I am getting a "Type mismatch" error when i run this code
from a command button (new home address). Please help.


Private Sub New_Home_Address_Click()
On Error GoTo Err_New_Home_Address_Click

DoCmd.GoToRecord , , acNewRec
'Me!Home_Addr_PK = Format(DMax("[Home_Addr_PK])", "tblHome"), 0) + 1,
"0000")

Exit_New_Home_Address_Click:
Exit Sub

Err_New_Home_Address_Click:
MsgBox Err.Description
Resume Exit_New_Home_Address_Click

 

[John W. Vinson]

I'm trying to simply increment a field in a table when i am in a form.
The table is tblhome and the form field is Home_addr_PK. The last record is
HA00052. When i create a new record, i would like HA0053 populated in the
Home_Addr_PK field. I am getting a "Type mismatch" error when i run this code
from a command button (new home address). Please help.

The Format function returns a String (and "HA0053" is a string). You can't do
arithmatic with strings!

You'll need to parse the numeric portion out as a number, increment *it*, and
repackage it.

Actually, if the HA is constant for all records, you shouldn't include it in
the field at all. Instead, just store a Long Integer and add the HA with a
format like

"\H\A0000"

to display a literal H, a literal A, and four digits with leading zeroes.

To store the first two alpha characters (whatever they are, HA or otherwise)
and still increment the number try

Private Sub New_Home_Address_Click()
Dim strAddr As String
Dim intAddr As Integer
On Error GoTo Err_New_Home_Address_Click

DoCmd.GoToRecord , , acNewRec
strAddr = NZ(DMax("[Home_Addr_PK]", "tblHome"). "HA0000")
intAddr = Val(Mid(strAddr, 3) ' extract numeric portion
If intAddr < 9999 Then
Me!Home_Addr_PK = Left(strAddr, 2) & Format(intAddr + 1, "0000")
Else
MsgBox "Turn off the computer - out of address numbers", vbOKOnly
End If
Exit_New_Home_Address_Click:
Exit Sub

Err_New_Home_Address_Click:
MsgBox Err.Description
Resume Exit_New_Home_Address_Click
End Sub

John W. Vinson [MVP]

 

[Guest]

Thanks John, you've given me two options;
so you are saying if i just make the home_Addr_pk field an integer with format
"\H\A0000" the data value will automatically increment by 1 upon new record
creation and the value in the field will be HA0053 (next record) and i dont
need seperate VBA code?

As you can see, i want to make this a PK for another table and want
sequential numbering. Once the value is created i would like it to be
"copied" to another field in another table that can link the address with the
customer name. Any suggestions?

I'm trying to simply increment a field in a table when i am in a form.
The table is tblhome and the form field is Home_addr_PK. The last record is
HA00052. When i create a new record, i would like HA0053 populated in the
Home_Addr_PK field. I am getting a "Type mismatch" error when i run this code
from a command button (new home address). Please help.

The Format function returns a String (and "HA0053" is a string). You can't do
arithmatic with strings!

You'll need to parse the numeric portion out as a number, increment *it*, and
repackage it.

Actually, if the HA is constant for all records, you shouldn't include it in
the field at all. Instead, just store a Long Integer and add the HA with a
format like

"\H\A0000"

to display a literal H, a literal A, and four digits with leading zeroes.

To store the first two alpha characters (whatever they are, HA or otherwise)
and still increment the number try

Private Sub New_Home_Address_Click()
Dim strAddr As String
Dim intAddr As Integer
On Error GoTo Err_New_Home_Address_Click

DoCmd.GoToRecord , , acNewRec
strAddr = NZ(DMax("[Home_Addr_PK]", "tblHome"). "HA0000")
intAddr = Val(Mid(strAddr, 3) ' extract numeric portion
If intAddr < 9999 Then
Me!Home_Addr_PK = Left(strAddr, 2) & Format(intAddr + 1, "0000")
Else
MsgBox "Turn off the computer - out of address numbers", vbOKOnly
End If
Exit_New_Home_Address_Click:
Exit Sub

Err_New_Home_Address_Click:
MsgBox Err.Description
Resume Exit_New_Home_Address_Click
End Sub

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks John, you've given me two options;
so you are saying if i just make the home_Addr_pk field an integer with format
"\H\A0000" the data value will automatically increment by 1 upon new record
creation and the value in the field will be HA0053 (next record) and i dont
need seperate VBA code?

No. You'll still need code; but you can simply use the integer value directly,
rather than unpackaging it and repackaging it. It just cuts a couple of steps
out of the code.

As you can see, i want to make this a PK for another table and want
sequential numbering. Once the value is created i would like it to be
"copied" to another field in another table that can link the address with the
customer name. Any suggestions?

To "copy" it use a Subform (unless you're doing something really unusual). A
Form based on the "one" side table with a Subform based on the "many", using
the ID as the master/child link field, will keep the two in synch.

A couple of issues about the sequential number should be mentioned.
Autonumbers develop gaps when records are deleted - you'll have to face the
same problem! What will you do when someone moves, and record HA0004 becomes
invalid and irrelevant to your database? Surely you won't want to renumber
HA0005 through HA2156 just to maintain the gap-free sequence! You may instead
need to leave the (now invalid) record in your table and mark it as obsolete,
either with a yes/no field or by altering the contents from "31140 Circle Dr."
to "Invalid" or the like. In either case, you're cluttering your table with
data that you KNOW is invalid, just to maintain sequential numbers.... *which
your users will probably never need to see*.

What good does it do your user to have a "handle" HA2156 on an address? You're
using a powerful database; if the user wants to find an address, they can
search for the address by the contents of the address, or by a link from
another table. It's *NOT* necessary for them to memorize and use a handle.

I'd just use an autonumber, gaps and all, and not worry about the sequence;
and not expose the primary key value to user view at all.

John W. Vinson [MVP]

 

[Guest]

re: VBA code - I'll remove the "unpacking" code and give it a try

re: Copying PK - my rationale is; step1 i create a record for an address in
the tblhome with PK HA0001. step2, I create a record in tblclient with client
name etc and wish to link to tblhome. At this time i haven't linked the
client to an address. You are suggesting using a subform (with query) to do
this which is fine but the user needs to know the correct address to link,
hence adding the PK from tblhome to a field in tblclient. Is this done
automatically, manually or through a procedure.

re: autonumber sequence, no, i'm not trying to keep records sequential after
a delete (i dont think i will be deleting), i just thought using this method
could somehow guarantee the n + 1 sequence vs using the autonumber which can
develop gaps if a record is created but fields not populated etc.

 

[John W. Vinson]

re: Copying PK - my rationale is; step1 i create a record for an address in
the tblhome with PK HA0001. step2, I create a record in tblclient with client
name etc and wish to link to tblhome. At this time i haven't linked the
client to an address. You are suggesting using a subform (with query) to do
this which is fine but the user needs to know the correct address to link,
hence adding the PK from tblhome to a field in tblclient. Is this done
automatically, manually or through a procedure.

I guess I'm not understanding. You know the client's address before you know
who the client is? Who are your clients? Are you focusing on the homes with
the clients secondary? Does each address pertain to one and only one client?
Does each client have one and only one address?

If the user wants to find an address... presumably they'll *know the address*,
right? How does knowing "31140 Circle Dr" help them know to enter HA2365?
Couldn't you just use a combo box storing the concealed ID while displaying
the actual address?

re: autonumber sequence, no, i'm not trying to keep records sequential after
a delete (i dont think i will be deleting), i just thought using this method
could somehow guarantee the n + 1 sequence vs using the autonumber which can
develop gaps if a record is created but fields not populated etc.

But my question is: *why do you need a n+1 sequence*? What good does it do
you?

John W. Vinson [MVP]

 

[Guest]

I'll try and explain busines flow
1. there is a prospect table (people's names, tel# etc) not linked to any
other table. [reason for this is likely my intermediate design knowledge of
Access)
2. When prospect becomes a "client" i want to copy some fields from
tblprospect to tblclient (so i dont wish to retype and i dont want to clutter
client table with propsects that dont become cleints). At this time i know
the prospect address so i want to add this information to tblhome. Primary
key is HA1234. I can have multiple clients at an address, hence one-to-many
relationship
3. Now i have client info and address info in two seperate tables and want
to add HA1234 field to tblclient to link address and client. What is the best
way to add on a form/procedure.

 

[John W. Vinson]

2. When prospect becomes a "client" i want to copy some fields from
tblprospect to tblclient (so i dont wish to retype and i dont want to clutter
client table with propsects that dont become cleints). At this time i know
the prospect address so i want to add this information to tblhome. Primary
key is HA1234. I can have multiple clients at an address, hence one-to-many
relationship
3. Now i have client info and address info in two seperate tables and want
to add HA1234 field to tblclient to link address and client. What is the best
way to add on a form/procedure.

If it's one address to many clients, the simplest way is to base a form on the
Addresses table and use a Subform based on the Clients table, using the
AddressID as the master/child link field. Doing so removes any need for the ID
to be displayed to or entered by the user - so you can use a random autonumber
if you want.

John W. Vinson [MVP]

 

[Guest]

I will try what you are suggesting.
One more thing, I want to perform a differnet field value increment [PL0001]
but now i want to reference an external table (tblclients) from a form not
associated with tblclients. The form is frmprospects. I tied using Dmax but
it complained it couldn't find tblclients in the expression.

 

[Pieter Wijnen]

not very helpful w/o the complete DMax statement you tried

Pieter

I will try what you are suggesting.
One more thing, I want to perform a differnet field value increment
[PL0001]
but now i want to reference an external table (tblclients) from a form not
associated with tblclients. The form is frmprospects. I tied using Dmax
but
it complained it couldn't find tblclients in the expression.

If it's one address to many clients, the simplest way is to base a form
on the
Addresses table and use a Subform based on the Clients table, using the
AddressID as the master/child link field. Doing so removes any need for
the ID
to be displayed to or entered by the user - so you can use a random
autonumber
if you want.

John W. Vinson [MVP]

 

[John W. Vinson]

I will try what you are suggesting.
One more thing, I want to perform a differnet field value increment [PL0001]
but now i want to reference an external table (tblclients) from a form not
associated with tblclients. The form is frmprospects. I tied using Dmax but
it complained it couldn't find tblclients in the expression.

By far the simplest way to add a record from tblProspects to tblClients would
be by running an Append query, using the form to select the desired client ID
as a criterion.

That said... post your DMax() expression, or (better) the code using it.

John W. Vinson [MVP]

 

[Guest]

I used the same similar code you suggested in an earlier thread...
I am launching this from frmProspects

Dim strAddr As String
Dim intAddr As Integer
On Error GoTo Err_New_Client_Click

DoCmd.GoToRecord , , acNewRec
strAddr = NZ(DMax("[SL_Client_No]", "tblClients"). "SL0000")
intAddr = Val(Mid(strAddr, 3) ' extract numeric portion
If intAddr < 9999 Then
Me!SL_Client_No = Left(strAddr, 2) & Format(intAddr + 1, "0000")
Else
MsgBox "Out of address numbers", vbOKOnly
End If
Exit_New_Client_Click:
Exit Sub

I think the correct method is Dlookup though...please correct me.

Second action is to copy specific fields (about 10) from tblProspects to
various fields in tblClients (and other related tables) from fmrProspects. Is
Append query still the way to go? How do i create Append Query (is there a
wizard?)

I will try what you are suggesting.
One more thing, I want to perform a differnet field value increment [PL0001]
but now i want to reference an external table (tblclients) from a form not
associated with tblclients. The form is frmprospects. I tied using Dmax but
it complained it couldn't find tblclients in the expression.

By far the simplest way to add a record from tblProspects to tblClients would
be by running an Append query, using the form to select the desired client ID
as a criterion.

That said... post your DMax() expression, or (better) the code using it.

John W. Vinson [MVP]

 

[Jamie Collins]

A couple of issues about the sequential number should be mentioned.
Autonumbers develop gaps when records are deleted - you'll have to face the
same problem! What will you do when someone moves, and record HA0004 becomes
invalid and irrelevant to your database? Surely you won't want to renumber
HA0005 through HA2156 just to maintain the gap-free sequence!

Well, the OP could change the algorithm from MAX+1 e.g.

SELECT MAX(ID) + 1 AS next_ID
FROM MyTable;

to one that uses the MIN unused value e.g.

SELECT MIN(seq) AS next_ID
FROM Sequence AS S1
WHERE seq BETWEEN 1 AND 99999
AND NOT EXISTS (
SELECT *
FROM MyTable
WHERE Sequence.seq = MyTable.ID);

This way the gaps eventually get filled.

An important consideration is that "you can get duplicate values in
your custom counter field if two applications add records in less time
than it takes for the cache to refresh and the lazy-write mechanism to
flush to disk". See:

How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1

Jamie.

--

 

[Jamie Collins]

How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1

http://support.microsoft.com/kb/240317

Jamie.

--

 

[Guest]

Jamie, there is only one user so there is not problem, please see
post/question from today - John, can you please respond.

 

[John W. Vinson]

I used the same similar code you suggested in an earlier thread...
I am launching this from frmProspects

Dim strAddr As String
Dim intAddr As Integer
On Error GoTo Err_New_Client_Click

DoCmd.GoToRecord , , acNewRec
strAddr = NZ(DMax("[SL_Client_No]", "tblClients"). "SL0000")
intAddr = Val(Mid(strAddr, 3) ' extract numeric portion
If intAddr < 9999 Then
Me!SL_Client_No = Left(strAddr, 2) & Format(intAddr + 1, "0000")
Else
MsgBox "Out of address numbers", vbOKOnly
End If
Exit_New_Client_Click:
Exit Sub

I think the correct method is Dlookup though...please correct me.

Second action is to copy specific fields (about 10) from tblProspects to
various fields in tblClients (and other related tables) from fmrProspects. Is
Append query still the way to go? How do i create Append Query (is there a
wizard?)

Chuck... You are making your own job much more difficult, and restricting the
flexibility of your database, by using these "intelligent" multicomponent key
fields.

I'd *REALLY* suggest abandoning the DL0000 and SL0000 idea. It gets you
*NOTHING* except headaches.

I have no trace of an idea what you mean by suggesting that "the correct
method is dlookup".

John W. Vinson [MVP]

 

[Jamie Collins]

Jamie, there is only one user so there is not problem, please see
post/question from today

Suggestion: have a table(s) of the complete set of values (HA0000,
HA0001, HA0002, HA0003, ... HA9999) ready rolled and *allocate* them
as and when required. This way you can _query_ the 'next' value e.g.
something like:

SELECT MIN(H1.ha_number)
FROM MyHaNumbers AS H1
WHERE NOT EXISTS (
SELECT *
FROM MyEntityTable AS M1
WHERE H1.ha_number = M1.ha_number);

In my head I've got the analogy of a check/cheque book: pre-printed
sheets with unique identifiers waiting to completed and handed out.
BTW my advice is for free <g>.

Jamie.

--

 

[John W. Vinson]

Suggestion: have a table(s) of the complete set of values (HA0000,
HA0001, HA0002, HA0003, ... HA9999) ready rolled and *allocate* them
as and when required. This way you can _query_ the 'next' value e.g.
something like:

For that size of domain... absolutely a better idea. Thanks Jamie! (It
wouldn't work for a Long Integer because there are too many).

I believe that the JET query engine processes frustrated outer joins more
efficiently than NOT EXISTS clauses - if Jamie's suggestion is slow try

SELECT MIN(H1.ha_number)
FROM MyHaNumbers AS H1
RIGHT JOIN MyEntityTable AS M1
ON H1.ha_number = M1.ha_number
WHERE M1.ha_number IS NULL;

John W. Vinson [MVP]

 

[Guest]

Guys, i have no trouble creating a new record and incrementing HAxxxx.
I'm in a seperate form now and i seach on this value (HA1234) using unbound
combo box and i want to update a table (tblclients, not related to form im
in) and copy HA1234 into a field in tblclients. How do i do this?
is it currentdb.execute .......?????

 

[John W. Vinson]

Guys, i have no trouble creating a new record and incrementing HAxxxx.
I'm in a seperate form now and i seach on this value (HA1234) using unbound
combo box and i want to update a table (tblclients, not related to form im
in) and copy HA1234 into a field in tblclients. How do i do this?
is it currentdb.execute .......?????

How do you know which record in tblClients to update? Do you want to update an
existing record, or add a new record? Where is the other data for this record
coming from?

John W. Vinson [MVP]