autoincrement PK

On Tue, 25 Sep 2007 20:22:00 -0700, Chuck
wrote:

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]

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





"John W. Vinson" wrote:

On Tue, 25 Sep 2007 20:22:00 -0700, Chuck
wrote:

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]

On Sep 25, 4:43 pm, John W. Vinson
wrote:
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.

--

On Sep 26, 3:56 pm, Jamie Collins wrote:

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

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

Jamie.

--

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

"Jamie Collins" wrote:

On Sep 26, 3:56 pm, Jamie Collins wrote:

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

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

Jamie.

--

On Wed, 26 Sep 2007 07:18:03 -0700, Chuck
wrote:

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]

On Sep 26, 5:02 pm, Chuck wrote:
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.

--

On Thu, 27 Sep 2007 00:35:39 -0700, Jamie Collins
wrote:

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]

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" wrote:

On Thu, 27 Sep 2007 00:35:39 -0700, Jamie Collins
wrote:

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]

On Thu, 27 Sep 2007 10:33:02 -0700, Chuck
wrote:

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]