By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,960 Members | 990 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,960 IT Pros & Developers. It's quick & easy.

assign auto number using VBA

P: n/a
Hi,

I have a table that contains two key fields. I would like to assign the
auto number for the secondary key field by starting from 1. For example

primary Key id second Key Id
------------------ -----------------------------------------
1 1
1 2
1 3
2 1
2 2
2 3

Notice that I want the second key ID to be reset when the new primary
key is changed. Anyone please advice me how to do so/

Many thanks

Peddie

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
you need to use the standard DMAX thing.

Something like this should work:
?DMax("[Key2]","Table1","[Key1]=2")+1

Nov 13 '05 #2

P: n/a
Here's a two query approach:

tblTwoKeyFields:
AutoID PrimaryKeyID SecondaryKeyID
1 1 Null
2 1 Null
3 1 Null
4 2 Null
5 2 Null

qryMakeNewSecondaryKeyIDs (MakeTable Query):
SELECT tblTwoKeyFields.AutoID, tblTwoKeyFields.PrimaryKeyID, (SELECT
Count(A.AutoID) FROM tblTwoKeyFields AS A WHERE A.PrimaryKeyID =
tblTwoKeyFields.PrimaryKeyID AND A.AutoID < tblTwoKeyFields.AutoID)+1
AS NewSecondaryKeyID INTO tblNewSecondaryKeyIDs FROM tblTwoKeyFields;

! qryMakeNewSecondaryKeyIDs:

tblNewSecondaryKeyIDs
AutoID PrimaryKeyID NewSecondaryKeyID
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2

qryUpdateSecondaryKeyIDs (Update Query):
UPDATE tblNewSecondaryKeyIDs INNER JOIN tblTwoKeyFields ON
tblNewSecondaryKeyIDs.AutoID = tblTwoKeyFields.AutoID SET
SecondaryKeyID = [NewSecondaryKeyID];

! qryUpdateSecondaryKeyIDs:

tblTwoKeyFields
AutoID PrimaryKeyID SecondaryKeyID
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2

Besides allowing the counts to work, the AutoID field causes the same
SecondaryKeyID to be assigned to existing records each time provided no
records have been deleted. I tried to do it all in one update query
but got the error message: "Operation must use an updatable query." I
stared at Piet's post but didn't see quickly how the nuts and bolts of
his suggestion could be assembled into a viable update query without
resorting to VBA (although the OP did ask for a VBA solution). Perhaps
using the DMax function inside a subquery could accomplish the same
thing.

James A. Fortune

Nov 13 '05 #3

P: n/a

Thanks James. It did work. I have never worked with DMAX function
before so I am note sure how to do this.
many thanks
Peddie
ji********@compumarc.com wrote:
Here's a two query approach:

tblTwoKeyFields:
AutoID PrimaryKeyID SecondaryKeyID
1 1 Null
2 1 Null
3 1 Null
4 2 Null
5 2 Null

qryMakeNewSecondaryKeyIDs (MakeTable Query):
SELECT tblTwoKeyFields.AutoID, tblTwoKeyFields.PrimaryKeyID, (SELECT
Count(A.AutoID) FROM tblTwoKeyFields AS A WHERE A.PrimaryKeyID =
tblTwoKeyFields.PrimaryKeyID AND A.AutoID < tblTwoKeyFields.AutoID)+1
AS NewSecondaryKeyID INTO tblNewSecondaryKeyIDs FROM tblTwoKeyFields;

! qryMakeNewSecondaryKeyIDs:

tblNewSecondaryKeyIDs
AutoID PrimaryKeyID NewSecondaryKeyID
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2

qryUpdateSecondaryKeyIDs (Update Query):
UPDATE tblNewSecondaryKeyIDs INNER JOIN tblTwoKeyFields ON
tblNewSecondaryKeyIDs.AutoID = tblTwoKeyFields.AutoID SET
SecondaryKeyID = [NewSecondaryKeyID];

! qryUpdateSecondaryKeyIDs:

tblTwoKeyFields
AutoID PrimaryKeyID SecondaryKeyID
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2

Besides allowing the counts to work, the AutoID field causes the same
SecondaryKeyID to be assigned to existing records each time provided no
records have been deleted. I tried to do it all in one update query
but got the error message: "Operation must use an updatable query." I
stared at Piet's post but didn't see quickly how the nuts and bolts of
his suggestion could be assembled into a viable update query without
resorting to VBA (although the OP did ask for a VBA solution). Perhaps
using the DMax function inside a subquery could accomplish the same
thing.

James A. Fortune


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.