Simple Question (?) about code generated PK's

B

BetaMike

Hi all,
Our business rules dictate that we assign a sequential 6-digit ContractNo to
each new customer.
(tblCustomers "Bloggs International" has a ContractNo of 000221 (PK))

If "Bloggs" contracts us to work at two of their business premises we would
add two records to tblSites for their respective address details.
(tblSites "BloggsUK" and "BloggsUSA" are assigned '01' and '02' under SiteID)

I would like to combine both of the above values into one field (in
tblSites) to be used as a PK.
Eg. "BloggsUK" = '000221-01' and "BloggsUSA" = '000221-02'

tblCustomers
ContractNo = 000221 (PK)

tblSites
ContractNo = 000221 (FK)
SiteID = 01
SiteNo = 000221-01 (PK)

tblOthers
SiteNo = 000221-01 (FK)

This seems quite complicated but it's the only way I can see it working for
us. Does anybody have any other suggestions or can anyone give an example of
code that would perform the above?

Thanks.

PS...
I've just bought the 'Access 2003 - Inside Out' book. Have I spent wisely or
not? ;)
 
J

John Vinson

Hi all,
Our business rules dictate that we assign a sequential 6-digit ContractNo to
each new customer.
(tblCustomers "Bloggs International" has a ContractNo of 000221 (PK))

If "Bloggs" contracts us to work at two of their business premises we would
add two records to tblSites for their respective address details.
(tblSites "BloggsUK" and "BloggsUSA" are assigned '01' and '02' under SiteID)

I would like to combine both of the above values into one field (in
tblSites) to be used as a PK.
Eg. "BloggsUK" = '000221-01' and "BloggsUSA" = '000221-02'

tblCustomers
ContractNo = 000221 (PK)

tblSites
ContractNo = 000221 (FK)
SiteID = 01
SiteNo = 000221-01 (PK)

tblOthers
SiteNo = 000221-01 (FK)

This seems quite complicated but it's the only way I can see it working for
us. Does anybody have any other suggestions or can anyone give an example of
code that would perform the above?
Click to expand...

Note that a Primary Key does not need to consist of only one field: it
can consist of up to TEN fields!

I'd just use two fields - ContractNo and SiteID. In table design view
ctrl-click these two fields and click the Key icon to create a joint,
two-field primary key. This will let you have multiple sites for a
contract, or multiple contracts all with Site 01, but will prohibit
two records duplicating the combination.

You can even use VBA code on a Form to automatically assign the next
sequential site; use a Subform, and in the subform's BeforeInsert
event put code like

Me!txtSiteNo = NZ(DMax("[SiteID]", "tblSites", _
"[ContractNo] = '" & Me!txtContractNo & "'")) + 1

For Reports or for display purposes, simply concatenate the two
fields.

John W. Vinson[MVP]
 
B

BetaMike

So in the relationships window, do I ctrl-click both of these fields and drag
them on to just one field in the table that I want to relate to?

Thanks.

John Vinson said:
Hi all,
Our business rules dictate that we assign a sequential 6-digit ContractNo to
each new customer.
(tblCustomers "Bloggs International" has a ContractNo of 000221 (PK))

If "Bloggs" contracts us to work at two of their business premises we would
add two records to tblSites for their respective address details.
(tblSites "BloggsUK" and "BloggsUSA" are assigned '01' and '02' under SiteID)

I would like to combine both of the above values into one field (in
tblSites) to be used as a PK.
Eg. "BloggsUK" = '000221-01' and "BloggsUSA" = '000221-02'

tblCustomers
ContractNo = 000221 (PK)

tblSites
ContractNo = 000221 (FK)
SiteID = 01
SiteNo = 000221-01 (PK)

tblOthers
SiteNo = 000221-01 (FK)

This seems quite complicated but it's the only way I can see it working for
us. Does anybody have any other suggestions or can anyone give an example of
code that would perform the above?
Click to expand...

Note that a Primary Key does not need to consist of only one field: it
can consist of up to TEN fields!

I'd just use two fields - ContractNo and SiteID. In table design view
ctrl-click these two fields and click the Key icon to create a joint,
two-field primary key. This will let you have multiple sites for a
contract, or multiple contracts all with Site 01, but will prohibit
two records duplicating the combination.

You can even use VBA code on a Form to automatically assign the next
sequential site; use a Subform, and in the subform's BeforeInsert
event put code like

Me!txtSiteNo = NZ(DMax("[SiteID]", "tblSites", _
"[ContractNo] = '" & Me!txtContractNo & "'")) + 1

For Reports or for display purposes, simply concatenate the two
fields.

John W. Vinson[MVP]
Click to expand...
 
B

BetaMike

Ahh, got it now...
I had to make sure that the related tables had ContractNo and SiteNo in the
field list too :)

I can't get the SiteID to increase by 1 as I can only find 'BeforeUpdate'.
Can you point me in the right direction? (Access 2003)

Thanks.

John Vinson said:
Hi all,
Our business rules dictate that we assign a sequential 6-digit ContractNo to
each new customer.
(tblCustomers "Bloggs International" has a ContractNo of 000221 (PK))

If "Bloggs" contracts us to work at two of their business premises we would
add two records to tblSites for their respective address details.
(tblSites "BloggsUK" and "BloggsUSA" are assigned '01' and '02' under SiteID)

I would like to combine both of the above values into one field (in
tblSites) to be used as a PK.
Eg. "BloggsUK" = '000221-01' and "BloggsUSA" = '000221-02'

tblCustomers
ContractNo = 000221 (PK)

tblSites
ContractNo = 000221 (FK)
SiteID = 01
SiteNo = 000221-01 (PK)

tblOthers
SiteNo = 000221-01 (FK)

This seems quite complicated but it's the only way I can see it working for
us. Does anybody have any other suggestions or can anyone give an example of
code that would perform the above?
Click to expand...

Note that a Primary Key does not need to consist of only one field: it
can consist of up to TEN fields!

I'd just use two fields - ContractNo and SiteID. In table design view
ctrl-click these two fields and click the Key icon to create a joint,
two-field primary key. This will let you have multiple sites for a
contract, or multiple contracts all with Site 01, but will prohibit
two records duplicating the combination.

You can even use VBA code on a Form to automatically assign the next
sequential site; use a Subform, and in the subform's BeforeInsert
event put code like

Me!txtSiteNo = NZ(DMax("[SiteID]", "tblSites", _
"[ContractNo] = '" & Me!txtContractNo & "'")) + 1

For Reports or for display purposes, simply concatenate the two
fields.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

Ahh, got it now...
I had to make sure that the related tables had ContractNo and SiteNo in the
field list too :)

I can't get the SiteID to increase by 1 as I can only find 'BeforeUpdate'.
Can you point me in the right direction? (Access 2003)
Click to expand...

I'm not at all sure what you mean here. Are you using a Subform? Are
you viewing the Form's properties (not the properties of the textbox,
but of the Form itself)?


John W. Vinson[MVP]
 
B

BetaMike

John Vinson said:
I'm not at all sure what you mean here. Are you using a Subform? Are
you viewing the Form's properties (not the properties of the textbox,
but of the Form itself)?


John W. Vinson[MVP]
Click to expand...
Yes, the main form is pointing at tblCustomers and I have a subform pointing
at tblSites. I tried the properties of the form and the textbox but couldn't
find the 'BeforeInsert' command! (total newbie)

Thanks.
 
B

BetaMike

John Vinson said:
I'm not at all sure what you mean here. Are you using a Subform? Are
you viewing the Form's properties (not the properties of the textbox,
but of the Form itself)?


John W. Vinson[MVP]
Click to expand...

Got it now, I opened the subform in design view and selected 'Form' in the
properties drop down box. I chose [Event Procedure] in the Before Insert
field, clicked the "..." pasted your code and saved it. (I removed the
<space_space>

It flags up a runtime error of 2465 "MS Access can't find the field
'txtContractNo' referred to in your expression"

Any suggestions?

Thanks.
 
J

John Vinson

It flags up a runtime error of 2465 "MS Access can't find the field
'txtContractNo' referred to in your expression"

Any suggestions?
Click to expand...

Use the name of the control on your form which is bound to the
ContractNo field. Given that I cannot see your computer, I had to
guess at what that name might be. I should have clarified that you
need to change the fieldnames and controlnames to match your database!

John W. Vinson[MVP]
 
Top