Link forms using two primary keys

[Katherine R]

Hi. I may be giving more information than needed, but here goes. I've
posted the below previously, but didn't get a lot of feed back. I've
received much needed help from this newsgroup over the past year, but not
having much luck with this particular problem. I may be asking for something
that can't be done and I'm probably doing a lousy job of presenting my
question. I'm using Access 2003. Much thanks to anyone that can help!

It was suggested that I create a cross reference table and I have done so.
The
cross reference table is as follows:

tblLabelDates
LabelID (PK)
WholesalerID (PK)
ApprovedDate
EndDate

I have a one-to-many relationship from tblLabel to tblLabelDates and from
tblWholesaler to tblLabelDates.

I have a query that brings the Brand, Label, & Wholesaler together and a
form based on this query. A command button opens a form that is based on a
LabelDates query which is based on the LabelDates table. The command button
links the two forms together by LabelID. I think it needs to link them
together by the combination of LabelID AND WholesalerID. Can this be done?
I think this may solve my problem if someone can tell me how to do it. The
code behind the command button is:

Private Sub cmdOpenfrmLabelDates_Click()
On Error GoTo Err_cmdOpenfrmLabelDates_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLabelDates"

stLinkCriteria = "[LabelID]=" & Me![LabelID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenfrmLabelDates_Click:
Exit Sub

Err_cmdOpenfrmLabelDates_Click:
MsgBox Err.Description
Resume Exit_cmdOpenfrmLabelDates_Click

End Sub

Perhaps a better description of what I need is:

BrandID tblBrand LabelID tblLabel WholesalerID tblWholesaler
1 Budweiser 14 Bud Dry 469 ABC Beverage
1 Budweiser 14 Bud Dry 963 AAA Distributors

Basically, I need 1-14-469 to have a registration date of 01/01/2006 and
1-14-963 a date of 03/01/2006 and I need a way to enter the registration and
end dates for each label/wholesaler combination.

I apologize for such a long posting. My previous posting listing my table
structure is below. Thanks!

Previous Posting:

I'm using Access 2003: I need to add two fields to my database, ApprovedDate
and EndDate. I don’t know where to put them or how to relate them. The Date
Approved is the date that a Label is assigned to/approved for a specific
wholesaler (not every wholesaler). Using the following scenario, Bud Dry
could have an approved date of 01/01/01 for ABC Beverage, but could have an
approved date of 07/15/02 for AAA Distributors. Do a need a separate table
for Approved and End dates? How would I form the relationships? Everything
I've tried ends up with a label having the same approved/end dates for every
wholesaler.

My tables and relationships are set up as shown below and I’m using forms
and subforms. (frmLabel - formatted as a continuous form, is a subform of
frmBrand; frmBrand – single form, is a subform of frmShipper; frmWholesaler –
continuous, is a subform of frmShipper; A command button on frmWholesaler
opens frmCounties – continuous, which is used to assign the counties to a
wholesaler. (And frmShipper is formatted as a single form.)

If you could picture the following data on my form with subforms:

Shipper is Anheuser Busch
Brand “Budweiser†has Labels Bud Dry, Bud Light, and Bud Ice AND
Brand “Budweiser†can ship to Wholesalers “ABC Beverageâ€, and “AAA
Distributorsâ€, AND
Wholesaler “ABC Beverage†can service the counties White, Brown, and
Scarlett, AND
Wholesaler “AAA Distributors†can service the counties of Finch, Robin, and
Eagle

Tables:

tblShipper
ShipperID
ShipperName

tblBrand
BrandID
BrandName
ShipperID

tblBrandDetails
BrandID
WholesalerID

tblLabel
LabelID
LabelName
BrandID
DateApproved
EndDate

tblWholesaler
WholesalerID
BrandID
WholesalerName

tblWholesalerDetails
WholesalerID
CountyCode

tblCounty
CountyCode
County

Relationships:

One-to-Many from tblShipper to tblBrand
One-to-Many from tblBrand to tblBrandDetails
One-to-Many from tblBrand to tblLabel
One-to-Many from tblWholesaler to tblBrandDetails
One-to-Many from tblWholesaler to tblWholesalerDetails
One-to-Many from tblWholesalerDetails to tblCounty

 

[tina]

try

stLinkCriteria = "LabelID=" & Me!LabelID _
& " And WholesalerID=" & Me!WholesalerID

the above assumes that you have a control or field on the open form called
"WholesalerID".

btw, and just as an aside, the OpenForm code does not "link" the two forms
together, it merely uses the WHERE clause defined in stLinkCriteria to
restrict the records returned by the RecordSource of the form that is being
opened. that's an important distinction, because when two forms (usually a
mainform/subform, sometimes two subforms on the same mainform) are truly
"linked", they show specific and predictable behavior - moving from record
to record in the parent form with cause the recordset of the child form to
be requeried, as well as other specific behaviors.

hth

Hi. I may be giving more information than needed, but here goes. I've
posted the below previously, but didn't get a lot of feed back. I've
received much needed help from this newsgroup over the past year, but not
having much luck with this particular problem. I may be asking for something
that can't be done and I'm probably doing a lousy job of presenting my
question. I'm using Access 2003. Much thanks to anyone that can help!

It was suggested that I create a cross reference table and I have done so.
The
cross reference table is as follows:

tblLabelDates
LabelID (PK)
WholesalerID (PK)
ApprovedDate
EndDate

I have a one-to-many relationship from tblLabel to tblLabelDates and from
tblWholesaler to tblLabelDates.

I have a query that brings the Brand, Label, & Wholesaler together and a
form based on this query. A command button opens a form that is based on a
LabelDates query which is based on the LabelDates table. The command button
links the two forms together by LabelID. I think it needs to link them
together by the combination of LabelID AND WholesalerID. Can this be done?
I think this may solve my problem if someone can tell me how to do it. The
code behind the command button is:

Private Sub cmdOpenfrmLabelDates_Click()
On Error GoTo Err_cmdOpenfrmLabelDates_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLabelDates"

stLinkCriteria = "[LabelID]=" & Me![LabelID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenfrmLabelDates_Click:
Exit Sub

Err_cmdOpenfrmLabelDates_Click:
MsgBox Err.Description
Resume Exit_cmdOpenfrmLabelDates_Click

End Sub

Perhaps a better description of what I need is:

BrandID tblBrand LabelID tblLabel WholesalerID tblWholesaler
1 Budweiser 14 Bud Dry 469 ABC Beverage
1 Budweiser 14 Bud Dry 963 AAA Distributors

Basically, I need 1-14-469 to have a registration date of 01/01/2006 and
1-14-963 a date of 03/01/2006 and I need a way to enter the registration and
end dates for each label/wholesaler combination.

I apologize for such a long posting. My previous posting listing my table
structure is below. Thanks!

Previous Posting:

I'm using Access 2003: I need to add two fields to my database, ApprovedDate
and EndDate. I don't know where to put them or how to relate them. The Date
Approved is the date that a Label is assigned to/approved for a specific
wholesaler (not every wholesaler). Using the following scenario, Bud Dry
could have an approved date of 01/01/01 for ABC Beverage, but could have an
approved date of 07/15/02 for AAA Distributors. Do a need a separate table
for Approved and End dates? How would I form the relationships? Everything
I've tried ends up with a label having the same approved/end dates for every
wholesaler.

My tables and relationships are set up as shown below and I'm using forms
and subforms. (frmLabel - formatted as a continuous form, is a subform of
frmBrand; frmBrand - single form, is a subform of frmShipper; frmWholesaler -
continuous, is a subform of frmShipper; A command button on frmWholesaler
opens frmCounties - continuous, which is used to assign the counties to a
wholesaler. (And frmShipper is formatted as a single form.)

If you could picture the following data on my form with subforms:

Shipper is Anheuser Busch
Brand "Budweiser" has Labels Bud Dry, Bud Light, and Bud Ice AND
Brand "Budweiser" can ship to Wholesalers "ABC Beverage", and "AAA
Distributors", AND
Wholesaler "ABC Beverage" can service the counties White, Brown, and
Scarlett, AND
Wholesaler "AAA Distributors" can service the counties of Finch, Robin, and
Eagle

Tables:

tblShipper
ShipperID
ShipperName

tblBrand
BrandID
BrandName
ShipperID

tblBrandDetails
BrandID
WholesalerID

tblLabel
LabelID
LabelName
BrandID
DateApproved
EndDate

tblWholesaler
WholesalerID
BrandID
WholesalerName

tblWholesalerDetails
WholesalerID
CountyCode

tblCounty
CountyCode
County

Relationships:

One-to-Many from tblShipper to tblBrand
One-to-Many from tblBrand to tblBrandDetails
One-to-Many from tblBrand to tblLabel
One-to-Many from tblWholesaler to tblBrandDetails
One-to-Many from tblWholesaler to tblWholesalerDetails
One-to-Many from tblWholesalerDetails to tblCounty

 

[Katherine R]

Hi Tina. Thanks for replying. There is a field on the open form called
"WholesalerID" (and a field "LabelID"). I've tried your suggestion, but I
get an error "The OpenForm action was canceled."

try

stLinkCriteria = "LabelID=" & Me!LabelID _
& " And WholesalerID=" & Me!WholesalerID

the above assumes that you have a control or field on the open form called
"WholesalerID".

btw, and just as an aside, the OpenForm code does not "link" the two forms
together, it merely uses the WHERE clause defined in stLinkCriteria to
restrict the records returned by the RecordSource of the form that is being
opened. that's an important distinction, because when two forms (usually a
mainform/subform, sometimes two subforms on the same mainform) are truly
"linked", they show specific and predictable behavior - moving from record
to record in the parent form with cause the recordset of the child form to
be requeried, as well as other specific behaviors.

hth

Hi. I may be giving more information than needed, but here goes. I've
posted the below previously, but didn't get a lot of feed back. I've
received much needed help from this newsgroup over the past year, but not
having much luck with this particular problem. I may be asking for something
that can't be done and I'm probably doing a lousy job of presenting my
question. I'm using Access 2003. Much thanks to anyone that can help!

It was suggested that I create a cross reference table and I have done so.
The
cross reference table is as follows:

tblLabelDates
LabelID (PK)
WholesalerID (PK)
ApprovedDate
EndDate

I have a one-to-many relationship from tblLabel to tblLabelDates and from
tblWholesaler to tblLabelDates.

I have a query that brings the Brand, Label, & Wholesaler together and a
form based on this query. A command button opens a form that is based on a
LabelDates query which is based on the LabelDates table. The command button
links the two forms together by LabelID. I think it needs to link them
together by the combination of LabelID AND WholesalerID. Can this be done?
I think this may solve my problem if someone can tell me how to do it. The
code behind the command button is:

Private Sub cmdOpenfrmLabelDates_Click()
On Error GoTo Err_cmdOpenfrmLabelDates_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLabelDates"

stLinkCriteria = "[LabelID]=" & Me![LabelID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenfrmLabelDates_Click:
Exit Sub

Err_cmdOpenfrmLabelDates_Click:
MsgBox Err.Description
Resume Exit_cmdOpenfrmLabelDates_Click

End Sub

Perhaps a better description of what I need is:

BrandID tblBrand LabelID tblLabel WholesalerID tblWholesaler
1 Budweiser 14 Bud Dry 469 ABC Beverage
1 Budweiser 14 Bud Dry 963 AAA Distributors

Basically, I need 1-14-469 to have a registration date of 01/01/2006 and
1-14-963 a date of 03/01/2006 and I need a way to enter the registration and
end dates for each label/wholesaler combination.

I apologize for such a long posting. My previous posting listing my table
structure is below. Thanks!

Previous Posting:

I'm using Access 2003: I need to add two fields to my database, ApprovedDate
and EndDate. I don't know where to put them or how to relate them. The Date
Approved is the date that a Label is assigned to/approved for a specific
wholesaler (not every wholesaler). Using the following scenario, Bud Dry
could have an approved date of 01/01/01 for ABC Beverage, but could have an
approved date of 07/15/02 for AAA Distributors. Do a need a separate table
for Approved and End dates? How would I form the relationships? Everything
I've tried ends up with a label having the same approved/end dates for every
wholesaler.

My tables and relationships are set up as shown below and I'm using forms
and subforms. (frmLabel - formatted as a continuous form, is a subform of
frmBrand; frmBrand - single form, is a subform of frmShipper; frmWholesaler -
continuous, is a subform of frmShipper; A command button on frmWholesaler
opens frmCounties - continuous, which is used to assign the counties to a
wholesaler. (And frmShipper is formatted as a single form.)

If you could picture the following data on my form with subforms:

Shipper is Anheuser Busch
Brand "Budweiser" has Labels Bud Dry, Bud Light, and Bud Ice AND
Brand "Budweiser" can ship to Wholesalers "ABC Beverage", and "AAA
Distributors", AND
Wholesaler "ABC Beverage" can service the counties White, Brown, and
Scarlett, AND
Wholesaler "AAA Distributors" can service the counties of Finch, Robin, and
Eagle

Tables:

tblShipper
ShipperID
ShipperName

tblBrand
BrandID
BrandName
ShipperID

tblBrandDetails
BrandID
WholesalerID

tblLabel
LabelID
LabelName
BrandID
DateApproved
EndDate

tblWholesaler
WholesalerID
BrandID
WholesalerName

tblWholesalerDetails
WholesalerID
CountyCode

tblCounty
CountyCode
County

Relationships:

One-to-Many from tblShipper to tblBrand
One-to-Many from tblBrand to tblBrandDetails
One-to-Many from tblBrand to tblLabel
One-to-Many from tblWholesaler to tblBrandDetails
One-to-Many from tblWholesaler to tblWholesalerDetails
One-to-Many from tblWholesalerDetails to tblCounty

 

[Roger Carlson]

It sounds like you are trying to create a form that implements an
Many-To-Many relationship. On my website (www.rogersaccesslibrary.com), is
a small Access database sample called "ImplementingM2MRelationship.mdb"
which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi. I may be giving more information than needed, but here goes. I've
posted the below previously, but didn't get a lot of feed back. I've
received much needed help from this newsgroup over the past year, but not
having much luck with this particular problem. I may be asking for something
that can't be done and I'm probably doing a lousy job of presenting my
question. I'm using Access 2003. Much thanks to anyone that can help!

It was suggested that I create a cross reference table and I have done so.
The
cross reference table is as follows:

tblLabelDates
LabelID (PK)
WholesalerID (PK)
ApprovedDate
EndDate

I have a one-to-many relationship from tblLabel to tblLabelDates and from
tblWholesaler to tblLabelDates.

I have a query that brings the Brand, Label, & Wholesaler together and a
form based on this query. A command button opens a form that is based on a
LabelDates query which is based on the LabelDates table. The command button
links the two forms together by LabelID. I think it needs to link them
together by the combination of LabelID AND WholesalerID. Can this be done?
I think this may solve my problem if someone can tell me how to do it. The
code behind the command button is:

Private Sub cmdOpenfrmLabelDates_Click()
On Error GoTo Err_cmdOpenfrmLabelDates_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLabelDates"

stLinkCriteria = "[LabelID]=" & Me![LabelID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenfrmLabelDates_Click:
Exit Sub

Err_cmdOpenfrmLabelDates_Click:
MsgBox Err.Description
Resume Exit_cmdOpenfrmLabelDates_Click

End Sub

Perhaps a better description of what I need is:

BrandID tblBrand LabelID tblLabel WholesalerID tblWholesaler
1 Budweiser 14 Bud Dry 469 ABC Beverage
1 Budweiser 14 Bud Dry 963 AAA Distributors

Basically, I need 1-14-469 to have a registration date of 01/01/2006 and
1-14-963 a date of 03/01/2006 and I need a way to enter the registration and
end dates for each label/wholesaler combination.

I apologize for such a long posting. My previous posting listing my table
structure is below. Thanks!

Previous Posting:

I'm using Access 2003: I need to add two fields to my database, ApprovedDate
and EndDate. I don't know where to put them or how to relate them. The Date
Approved is the date that a Label is assigned to/approved for a specific
wholesaler (not every wholesaler). Using the following scenario, Bud Dry
could have an approved date of 01/01/01 for ABC Beverage, but could have an
approved date of 07/15/02 for AAA Distributors. Do a need a separate table
for Approved and End dates? How would I form the relationships? Everything
I've tried ends up with a label having the same approved/end dates for every
wholesaler.

My tables and relationships are set up as shown below and I'm using forms
and subforms. (frmLabel - formatted as a continuous form, is a subform of
frmBrand; frmBrand - single form, is a subform of frmShipper; frmWholesaler -
continuous, is a subform of frmShipper; A command button on frmWholesaler
opens frmCounties - continuous, which is used to assign the counties to a
wholesaler. (And frmShipper is formatted as a single form.)

If you could picture the following data on my form with subforms:

Shipper is Anheuser Busch
Brand "Budweiser" has Labels Bud Dry, Bud Light, and Bud Ice AND
Brand "Budweiser" can ship to Wholesalers "ABC Beverage", and "AAA
Distributors", AND
Wholesaler "ABC Beverage" can service the counties White, Brown, and
Scarlett, AND
Wholesaler "AAA Distributors" can service the counties of Finch, Robin, and
Eagle

Tables:

tblShipper
ShipperID
ShipperName

tblBrand
BrandID
BrandName
ShipperID

tblBrandDetails
BrandID
WholesalerID

tblLabel
LabelID
LabelName
BrandID
DateApproved
EndDate

tblWholesaler
WholesalerID
BrandID
WholesalerName

tblWholesalerDetails
WholesalerID
CountyCode

tblCounty
CountyCode
County

Relationships:

One-to-Many from tblShipper to tblBrand
One-to-Many from tblBrand to tblBrandDetails
One-to-Many from tblBrand to tblLabel
One-to-Many from tblWholesaler to tblBrandDetails
One-to-Many from tblWholesaler to tblWholesalerDetails
One-to-Many from tblWholesalerDetails to tblCounty

 

[Katherine R]

Roger - I checked out your website and it is wonderful. I know it will be a
big help and I'm looking forward to checking it out more thouroughly.
Thanks! In reference to my current situation, I'm not sure now that what I
need is a M2M relationship to resolve my date issue. To expand a bit on an
illustration in my previous posting:

BrandID tblBrand LblID tblLabel WhlID tblWholesaler
1 Budweiser 14 Bud Dry 469 ABC Beverage
1 Budweiser 14 Bud Dry 963 AAA Distributors
1 Budweiser 15 Bud Ice 963 AAA Distributors
2 Busch 18 Busch Ice 963 AAA Distributors

Each Wholesaler would have an approved date (and in some instances an end
date) for each label that is registered to it. Each of the records above
could have a different date. I'm wondering if I should delete the cross
reference table and insert the fields ApprovalDate and EndDate in
tblWholesaler with a one-to-many relationship from tblWholesaler to tblLabel.
I guess I would have to add WholesalerID as a foreign key to tblLabel.
Would this be the correct way to handle the date fields?

It sounds like you are trying to create a form that implements an
Many-To-Many relationship. On my website (www.rogersaccesslibrary.com), is
a small Access database sample called "ImplementingM2MRelationship.mdb"
which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi. I may be giving more information than needed, but here goes. I've
posted the below previously, but didn't get a lot of feed back. I've
received much needed help from this newsgroup over the past year, but not
having much luck with this particular problem. I may be asking for something
that can't be done and I'm probably doing a lousy job of presenting my
question. I'm using Access 2003. Much thanks to anyone that can help!

It was suggested that I create a cross reference table and I have done so.
The
cross reference table is as follows:

tblLabelDates
LabelID (PK)
WholesalerID (PK)
ApprovedDate
EndDate

I have a one-to-many relationship from tblLabel to tblLabelDates and from
tblWholesaler to tblLabelDates.

I have a query that brings the Brand, Label, & Wholesaler together and a
form based on this query. A command button opens a form that is based on a
LabelDates query which is based on the LabelDates table. The command button
links the two forms together by LabelID. I think it needs to link them
together by the combination of LabelID AND WholesalerID. Can this be done?
I think this may solve my problem if someone can tell me how to do it. The
code behind the command button is:

Private Sub cmdOpenfrmLabelDates_Click()
On Error GoTo Err_cmdOpenfrmLabelDates_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLabelDates"

stLinkCriteria = "[LabelID]=" & Me![LabelID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenfrmLabelDates_Click:
Exit Sub

Err_cmdOpenfrmLabelDates_Click:
MsgBox Err.Description
Resume Exit_cmdOpenfrmLabelDates_Click

End Sub

Perhaps a better description of what I need is:

BrandID tblBrand LabelID tblLabel WholesalerID tblWholesaler
1 Budweiser 14 Bud Dry 469 ABC Beverage
1 Budweiser 14 Bud Dry 963 AAA Distributors

Basically, I need 1-14-469 to have a registration date of 01/01/2006 and
1-14-963 a date of 03/01/2006 and I need a way to enter the registration and
end dates for each label/wholesaler combination.

I apologize for such a long posting. My previous posting listing my table
structure is below. Thanks!

Previous Posting:

I'm using Access 2003: I need to add two fields to my database, ApprovedDate
and EndDate. I don't know where to put them or how to relate them. The Date
Approved is the date that a Label is assigned to/approved for a specific
wholesaler (not every wholesaler). Using the following scenario, Bud Dry
could have an approved date of 01/01/01 for ABC Beverage, but could have an
approved date of 07/15/02 for AAA Distributors. Do a need a separate table
for Approved and End dates? How would I form the relationships? Everything
I've tried ends up with a label having the same approved/end dates for every
wholesaler.

My tables and relationships are set up as shown below and I'm using forms
and subforms. (frmLabel - formatted as a continuous form, is a subform of
frmBrand; frmBrand - single form, is a subform of frmShipper; frmWholesaler -
continuous, is a subform of frmShipper; A command button on frmWholesaler
opens frmCounties - continuous, which is used to assign the counties to a
wholesaler. (And frmShipper is formatted as a single form.)

If you could picture the following data on my form with subforms:

Shipper is Anheuser Busch
Brand "Budweiser" has Labels Bud Dry, Bud Light, and Bud Ice AND
Brand "Budweiser" can ship to Wholesalers "ABC Beverage", and "AAA
Distributors", AND
Wholesaler "ABC Beverage" can service the counties White, Brown, and
Scarlett, AND
Wholesaler "AAA Distributors" can service the counties of Finch, Robin, and
Eagle

Tables:

tblShipper
ShipperID
ShipperName

tblBrand
BrandID
BrandName
ShipperID

tblBrandDetails
BrandID
WholesalerID

tblLabel
LabelID
LabelName
BrandID
DateApproved
EndDate

tblWholesaler
WholesalerID
BrandID
WholesalerName

tblWholesalerDetails
WholesalerID
CountyCode

tblCounty
CountyCode
County

Relationships:

One-to-Many from tblShipper to tblBrand
One-to-Many from tblBrand to tblBrandDetails
One-to-Many from tblBrand to tblLabel
One-to-Many from tblWholesaler to tblBrandDetails
One-to-Many from tblWholesaler to tblWholesalerDetails
One-to-Many from tblWholesalerDetails to tblCounty

 

[Katherine R]

Oops. I took another look at your M2M sample. I think this is what I need.
I'm trying to get it to work in my application now. I'm working at a
beginner level so I'm a little slow. Thanks! I'll let you know how it goes.

It sounds like you are trying to create a form that implements an
Many-To-Many relationship. On my website (www.rogersaccesslibrary.com), is
a small Access database sample called "ImplementingM2MRelationship.mdb"
which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi. I may be giving more information than needed, but here goes. I've
posted the below previously, but didn't get a lot of feed back. I've
received much needed help from this newsgroup over the past year, but not
having much luck with this particular problem. I may be asking for something
that can't be done and I'm probably doing a lousy job of presenting my
question. I'm using Access 2003. Much thanks to anyone that can help!

It was suggested that I create a cross reference table and I have done so.
The
cross reference table is as follows:

tblLabelDates
LabelID (PK)
WholesalerID (PK)
ApprovedDate
EndDate

I have a one-to-many relationship from tblLabel to tblLabelDates and from
tblWholesaler to tblLabelDates.

I have a query that brings the Brand, Label, & Wholesaler together and a
form based on this query. A command button opens a form that is based on a
LabelDates query which is based on the LabelDates table. The command button
links the two forms together by LabelID. I think it needs to link them
together by the combination of LabelID AND WholesalerID. Can this be done?
I think this may solve my problem if someone can tell me how to do it. The
code behind the command button is:

Private Sub cmdOpenfrmLabelDates_Click()
On Error GoTo Err_cmdOpenfrmLabelDates_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLabelDates"

stLinkCriteria = "[LabelID]=" & Me![LabelID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenfrmLabelDates_Click:
Exit Sub

Err_cmdOpenfrmLabelDates_Click:
MsgBox Err.Description
Resume Exit_cmdOpenfrmLabelDates_Click

End Sub

Perhaps a better description of what I need is:

BrandID tblBrand LabelID tblLabel WholesalerID tblWholesaler
1 Budweiser 14 Bud Dry 469 ABC Beverage
1 Budweiser 14 Bud Dry 963 AAA Distributors

Basically, I need 1-14-469 to have a registration date of 01/01/2006 and
1-14-963 a date of 03/01/2006 and I need a way to enter the registration and
end dates for each label/wholesaler combination.

I apologize for such a long posting. My previous posting listing my table
structure is below. Thanks!

Previous Posting:

I'm using Access 2003: I need to add two fields to my database, ApprovedDate
and EndDate. I don't know where to put them or how to relate them. The Date
Approved is the date that a Label is assigned to/approved for a specific
wholesaler (not every wholesaler). Using the following scenario, Bud Dry
could have an approved date of 01/01/01 for ABC Beverage, but could have an
approved date of 07/15/02 for AAA Distributors. Do a need a separate table
for Approved and End dates? How would I form the relationships? Everything
I've tried ends up with a label having the same approved/end dates for every
wholesaler.

My tables and relationships are set up as shown below and I'm using forms
and subforms. (frmLabel - formatted as a continuous form, is a subform of
frmBrand; frmBrand - single form, is a subform of frmShipper; frmWholesaler -
continuous, is a subform of frmShipper; A command button on frmWholesaler
opens frmCounties - continuous, which is used to assign the counties to a
wholesaler. (And frmShipper is formatted as a single form.)

If you could picture the following data on my form with subforms:

Shipper is Anheuser Busch
Brand "Budweiser" has Labels Bud Dry, Bud Light, and Bud Ice AND
Brand "Budweiser" can ship to Wholesalers "ABC Beverage", and "AAA
Distributors", AND
Wholesaler "ABC Beverage" can service the counties White, Brown, and
Scarlett, AND
Wholesaler "AAA Distributors" can service the counties of Finch, Robin, and
Eagle

Tables:

tblShipper
ShipperID
ShipperName

tblBrand
BrandID
BrandName
ShipperID

tblBrandDetails
BrandID
WholesalerID

tblLabel
LabelID
LabelName
BrandID
DateApproved
EndDate

tblWholesaler
WholesalerID
BrandID
WholesalerName

tblWholesalerDetails
WholesalerID
CountyCode

tblCounty
CountyCode
County

Relationships:

One-to-Many from tblShipper to tblBrand
One-to-Many from tblBrand to tblBrandDetails
One-to-Many from tblBrand to tblLabel
One-to-Many from tblWholesaler to tblBrandDetails
One-to-Many from tblWholesaler to tblWholesalerDetails
One-to-Many from tblWholesalerDetails to tblCounty

 

[Katherine R]

You are brilliant! I've been stuck on this for a couple of weeks now. Your
sample was very easy to follow, once I read it. Thank you very much. I will
be sure to check your other samples out.