Design help, please

The man with patience!
Thanks for the ponderings. The default value is still
not working.

I have cboAuction:
SELECT tblAuctions.AuctionID, tblAuctions.AuctionDate,
tblAuctions.AuctionName FROM tblAuctions ORDER BY
AuctionDate DESC;

I set AuctionID = [cboAuction]

I have text box with control source: AuctionItemID and
default value
=Nz(DMax("AuctionItemID","tblAuctionItems","Auctio nID = "
& [cboAuction]),0)+1

All components of CPK are required in the tables. Hmm.
Thanks, Sal.

-----Original Message-----
Yes, what we need is to define the default value of the
AuctionID control in
your main form (forgot to tell you that); that is, the
textbox that is bound
to the AuctionID field (not the combo box that is in the
form header).

Set the Default Value for this control to
=[cboAuction]

Now, when you select an auction from the combo box at
top of form, and you
start a new record, the AuctionID field will get the
value from the combo
box, and then the default value for the AuctionItemID
should work fine.

However, if you still see that the AuctionItemID value
is not being filled
in OR if it's getting an incorrect value, then what we
may want to do is to
scrap the use of Default Value for the AuctionID and
AuctionItemID fields,
and instead use a tiny amount of code in the form to
write the correct
values into those fields. But before we go there, let's
see if this works
for you.

Again, a reminder to be sure that you set the Required
property to Yes for
all the fields that together are the CPK in a table so
that those fields
cannot be empty in a record. This again will help to
ensure that the form is
doing what we want it to do.
--

Ken Snell
MS ACCESS MVP


"SillySally" wrote in
message
...
Well, I think your mind's eye is fabulous!
The default value still isn't working. I think I know
why, but don't understand it.

I looked at my 3 tables that have CPKs and here's what
I
found (although I'll just tell you about
tblAuctionItems)

tblAuctionItems
AuctionItemID
AuctionID
AuctionItemName

When I look at the table in datasheet view, only 1
field
appears: AuctionItemName. So that AuctionID and
AuctionItemID aren't getting populated. Which leads me
to believe that maybe I need to define the
relationships
between these tables (correct?). I did and that didn't
help. Grr. Any ideas why I'm having such table
trauma?
Thanks, Sal

-----Original Message-----
The #Error error message indicates that something is
not
right with the
expression; as I don't have the db right here, I may be
using a different
name for something than you are using.

Nz is a function that replaces a Null value with what
you use as the second
argument of the Nz function. In the example I gave you,
a Null from the DMax
function's value will be replaced with a zero. So what
the expression is
supposed to do is find the maximum value in the
tblAuctionItems for the
field AuctionItemID for the records that have a value
for AuctionID that
matches the one you selected in your combo box. . .

aha -- that is my error, I think. Use this expression
as
the default value
(need to use the combo box value!):
=Nz(DMax("AuctionItemID", "tblAuctionItems", "AuctionID
= " & [cboAuction]),
0) + 1

Sorry about that... sometimes it gets a little jumbled
in my "mind's eye"
when trying to "see" the setup there...

I'm not sure I'm following the sequence of events that
you say you're not
liking, but yes, it may be related to the AfterUpdate
code that you are
trying to use. Go with the DefaultValue expression
above
for the
AuctionItemID control, and delete the AfterUpdate code
that you have.

As for eliminating gifts from the combo box once
they've
been selected, yes,
this can be done. Unless you'll have thousands and
thousands of entries,
probably the easiest way is to change the Row Source
query for the
AuctionGiftID value in the subform to this:

SELECT AuctionGifts.AuctionGiftID,
AuctionGifts.AuctionGiftName, (other
fields) FROM AuctionGifts LEFT JOIN AuctionItemContents
ON
AuctionGifts.AuctionGiftID=AuctionItemContents. AuctionG
if
tID WHERE
AuctionItemContents.AuctionGiftID Is Null;

This will "omit" any AuctionGift records whose ID value
is already in a
record in the tblAuctionItemContents table.

Glad you're making progress.
--

Ken Snell
MS ACCESS MVP



"SillySally" wrote
in
message
.. .
Thanks for the advice- I added all of the ID fields
to
the form to see what was happening. I tried your
code
as
the default value and got "#Error".
So I tried:
Private Sub cboAuction_AfterUpdate()
AuctionItemID = Nz(DMax
("AuctionItemID", "tblAuctionItems", "AuctionID = " &
[AuctionID]), 0) + 1
Me.Requery
End Sub

which seemed to work (tell me if I'm crazy), but I
think
it's causing problems as well. Could you explain
what
the Nz(DMax) statement means so I can figure out how
to
use it as the default value?

I'm not loving this form. When I select an Auction
and
then give an ItemName- say Basket1- (assign an
AuctionItemID) so this is Record 1, and then add a
new
record and select an Auction, the form returns
Basket1
(Record 1) so I then hit the button to get to the
second record. But it's probably what happens when I
try
things on my own with the after update.

Is there a way to tell the combo box that once a gift
has
been selected, I don't want to be able to select it
again
unless I delete it from an item?

Thanks for getting me this far! Sal


-----Original Message-----
What I think is happening on your form (when you
select
an AuctionID from
the combo box) is that your current record on the
form
is never getting an
AuctionItemID value (you're not exposing it to the
user,
so the user isn't
entering a value for it; and you're not giving it a
value through your
form's programming), and as you're seeing, no field
in
a
composite primary
key index should be empty/Null. You'll need to have a
way of giving the
AuctionItemID field a value. If you're not going to
expose it to the user,
then use the DefaultValue of the textbox to which
it's
bound to give it a
value for new records. An expression similar to this
should work:
=Nz(DMax
("AuctionItemID", "tblAuctionItems", "AuctionID = " &
[AuctionID]), 0) + 1

This will assign an incrementing number, beginning at
1,
for each item for a
specific auction ID value, when you start a new
record
for an auction item
belonging to an auction.

In your table setups, you indicate that one of the
fields in each CPK pair
is marked as Required, and the other is not. I would
mark both fields in
each CPK as Required; that way, the table will
enforce
that you have a value
in each field for each record.

--

Ken Snell
MS ACCESS MVP


"SillySally" wrote
in
message
.. .
Thanks for the CPK lesson- I appreciate it! And
good
news! sfrmAuctionItemsContents is working. The new
Record Source and combo box work just as I
expected.
Woo
hoo!

Still having problems with frmAuctionItems of
Record
Source:
SELECT *
FROM tblAuctionItems
WHERE (((tblAuctionItems.AuctionID)=[Forms]!
[frmAuctionItems]![cboAuction]));

I have cboAuction:
SELECT tblAuctions.AuctionID,
tblAuctions.AuctionDate,
tblAuctions.AuctionName
FROM tblAuctions
ORDER BY tblAuctions.AuctionDate DESC;

As soon as I select an Auction, I get "Run-time
error '3058': Index or primary key cannot contain a
Null
value. And when I select debug, it sends me right
to
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub

You indicated that on the main portion, I need
AuctionItemID field of the table bound to a textbox
on
the form (which makes sense to me).
tblAuctionItems
has
CPK AuctionItemID and AuctionID so that
AuctionItemID
is
NOT an AutoNumber- just a long integer. I added
AuctionItemName to the table so that I don't have
to
expose AuctionItemID field value to the user. But
I
don't think I'm doing the AuctionItemID field of
the
table bound to a textbox correctly. I tried it two
ways:
I selected the "textbox" icon, and set the control
source
to AuctionItemID which doesn't seem any different
than
just dragging AuctionItemID field to the form. So
then I
tried the "textbox" icon and set it =
[AuctionItemID].
Did I do anything right? I don't want to enter
anything
in this invisible field, rather I want the user to
enter
into AuctionItemName. But how are AuctionItemID
and
AuctionItemName "linked"?

Then you said that if I see "cannot be Null" I
have 1
0f
3 problems (I'm so lucky it's probably 2 or 3 out
of
3!).
1) control bound to that field on my form so that I
can
enter a value (I don't want to enter a value,
correct?),
but I think something like this is the problem
2) not including the field in my form RecordSource-
don't
think this is the problem as I selected * from
tblAuctionItems.
3)LinkMaster/Child fields- don't think that's the
problem
as on sfrmAuctionItemContents, both LinkChildFields
and
LinkMasterFields are set to AuctionItemID.

Here's my table setup:
tblAuctionDonors
AuctionDonorID AutoNumber

tblAuctionGifts (CPK)
AuctionGiftsID Number (Long Integer); Required =
No
(don't know why)
AuctionDonorID Number (Long Integer); Required =
Yes

tblAuctionItemContents (CPK)
AuctionItemID Number (Long Integer); Required = No
AuctionGiftsID Number (Long Integer); Required =
Yes

tblAuctionItems (CPK)
AuctionItemID Number (Long Integer); Required = No
AuctionID Number (Long Integer): Required = Yes

tblAuctions
AcutionID AutoNumber

Thanks for the help and the fantastic explanations!
Sal

-----Original Message-----
Quick stepback here.

Autonumber fields are often used as primary key
fields.
But because they are
unique in and of themselves, they would be used as
single primary key field
(not a composite primary key field). Thus, if you
use
an
autonumber in a
table for the primary key, it should be the only
field
that is in the
primary key. (Autonumber fields are Long Integer
data
type.)

When a child table is using a field as a foreign
key
(meaning that it is
meant to be a joining field between the child and
the
parent tables), it
must have the same data type as the key in the
parent
table. Thus, if the
parent table contains MyID (an autonumber) as a
primary
key, and your child
table uses MyID for joining the tables, MyID in the
child table must by Long
Integer.

Composite primary keys are nothing more than a
combination of fields where
the combination of the values from those fields is
a
unique combination
(only one record in the table has that combination
of
values). Individually,
each field that is in the composite primary key may
have
the same value in
more than one record. For example, suppose my child
table contains two
fields: CarType and CarColor. Suppose that they
are
a
composite primary
key. That means that my table could contain these
records:
CarType CarColor
------- -------
Cadillac Red
Cadillac Yellow
Cadillac White
Pontiac Black
Pontiac Red
Impala While

Each combination is unique, even though some values
repeat in the records.

OK - now to the current issues.

When a field is part of a composite primary key,
that
field must have a
non-Null value in order to save the record. While
setting the Default Value
of the field to 0 (when it's a Long Integer data
type)
can avoid this error
occurring, it also masks the possibility that your
form
is not properly
writing a value to that field when you create a new
record. Thus, you may
think you're saving data with the correct value in
a
joining field, when in
reality they're all getting a value of 0, which
won't
join back to the
parent table (and then you'll wonder why you
can't "find" your data). So,
until you're sure that your form is working
correctly,
I
would leave the
Default Value as Null (or empty) for now.

The fact that you see the "cannot be Null" error
suggests that you don't
have a control bound to that field on your form so
that
you can enter a
value for that field; or it suggests that you're
not
including the field in
your form (or subform) RecordSource; or it suggests
that
your
LinkMasterFields and LinkChildFields properties are
not
set correctly for
the subform so that the form will write the value
into
that field for you.

This code:
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub

should not create any "duplicate value" problems
*unless* you've bound the
cboAuction control to a field in the form's
RecordSource. This control must
be unbound (the Control Source must be empty)
because
all you're using it
for is to navigate the form to the desired records.
If
you have a field name
in its ControlSource, then you're changing the
value
of
AuctionID in some
records in the tblAuctionItems table from what it
was
to
what you select --
and this will cause duplicate value problems (not
to
mention potentially
mess up your data).

I made an error in what I told you the RecordSource
for
the subform should
be (it does happen g ).

Change it to this:
SELECT tblAuctionItemContents.*
FROM tblAuctionItemContents;

Your combo box that will allow you to select the
AuctionGiftID should have a
Row Source query similar to this:
SELECT AuctionGiftID, AuctionGiftName, (other
fields) FROM AuctionGifts;
This combo box should be bound to AuctionGiftID
field
in
the subform's
RecordSource. The bound column of this combo box
should
be 1.

Now you should be able to select a gift so that it
can
be added to the
item's contents.

--

Ken Snell
MS ACCESS MVP


"SillySally"
wrote
in
message
...
Sorry- I wasn't clear. I was talking about a
combo
box on
the subform (I understand about navigating on the
main
form using *). These composite primary keys are
very
confusing to me (but I did change all of them to
long
integer). I'm having a couple of problems- first
with
the
tables. I was getting error messages that IDs
couldn't be
null, so I set the default values to 0 and then I
got
duplicate value violations.
Now that my CPK are long integers, should I have
a
default
value? And
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub
is giving me grief (duplicate values in the index
which
must be related to my table question).

2nd trauma
I created fmrAuctionItems with
sfrmAcutionItemContents. I
was creating a combo box on the subform, but now
somehow,
I'm unable to enter anything into the subform
(it's
all
grayed out). So I rebuilt the form and subform
again-
same thing. Which is frustrating because I
finally
see
where I'm going. I think I even built the
correct
combo
box for the subform before grayness took over.
Any
ideas
why can't I enter anything in the subform?

frmAuctionItems RecordSource:
SELECT *
FROM tblAuctionItems
WHERE (((tblAuctionItems.AuctionID)=[Forms]!
[frmAuctionItems]![cboAuction]));

sfrmAuctionItemContents RecordSource:
SELECT tblAuctionItemContents.*,
tblAuctionGifts.*
FROM tblAuctionItemContents INNER JOIN
tblAuctionGifts
ON
tblAuctionItemContents.AuctionGiftsID =
tblAuctionGifts.AuctionGiftsID;

Now, if I delete tblAcutionItemsContent from the
sfrm
and
just leave tblAuctionGits, the sql is fine. So
my
join
must be the problem? Although it's probably
related
to my
table woes.

Sorry to be such a pain! Sal



-----Original Message-----
You're still going in the right direction. What
you
need
to stop and think
about now is -- what am I wanting to do on this
form?
The
answer will then
tell you what you need to do.

The answer, from my perspective, is that you want
to
select or create an
auction item on the form, and then to assign
auction
gifts to that item.
Right?

So, on the main portion of frmAuctionItems, you
need
the
AuctionItemID field
of the table bound to a textbox on the form
(likely,
you're using an
autonumber field for this? so the textbox can be
hidden/invisible), and you
want a textbox to enter the name of a new auction
item.
Unless you plan to
expose the AuctionItemID field's value to the
user
(which
isn't necessary),
then you should have another field in
tblAuctionItems
that allows you to
give the record an identifying name. This is then
what
you display on the
form. Otherwise, if you want to use the
AuctionItemID
as
the identifying
value to the user, then display it in a textbox
that
is
not hidden.

Use the navigation buttons at bottom of form to
move
back
and forth between
existing records (auction items), or to create a
new
auction item (the *
button). No combo box is needed for this type of
design
(it's possible to
use a combo box for selecting the auction item
that
you
want, instead of
using navigation buttons -- you do that by using
an
unbound combo box that
has a Row Source that essentially is the same as
the
form's RecordSource
query, and then you would use code to move the
form
to
the selected item --
but let's leave that for the future when you're
comfortable with what you're
doing now).

In the subform, you would use a combo box to
select a
specific AuctionGift
to be a component of the AuctionItem being
displayed
on
the main form. It
should be bound to AuctionGiftID field in
tblAuctionItemContents. (Note:
this AuctionGiftID field should not be an
autonumber
field here; it should
be a Long Integer field so that its data type
matches
the
autonumber type
that you likely used for AuctionGiftID field in
tblAuctionGifts. Autonumber
field is used ONLY in the table where the field
value
is
the primary key
identifier (not a composite, but the only field
that
is
the primary key) for
that record; in the children table that join to
the
main
table via this key,
the field then is a Long Integer. You may have
this
set
up incorrectly in
your children tables.)

Oh, and yes, what you call "baskets" I am
calling "items". But I am going
one step beyond that. An individual item can be
composed
of a single gift,
and its contents would be that single
AuctionGiftID
(one
record in
tblAuctionItemContents table). Thus, if you have
a "baseball bat" up for
auction and that is a single entity, it should
have a
single record in
tblAuctionItemaContents table that shows that its
content
is a single gift.
Contrast that with a "basket" that is made up of
a
gift
certificate, candy,
and a travel voucher -- this item would have
three
records in
tblAuctionItemContents table. This structure
allows
you
to have a different
Name for an item from what it is named as a gift,
as
well
as any other
details that might differ from what you knew when
it
was
accepted as a gift
versus what you know/do when it becomes an item.
--

Ken Snell
MS ACCESS MVP




"SillySally"
wrote
in
message
bl...
Thanks for the step-through instructions. I
now
have
frmAuctionItems with sfrmAuctionItemContents.
And
I
previously created frmAuctions so I can enter
each
Auction.

On frmAuctionItems, cboAuction works just
right. I
even
added a field =cboAuction.Column(2) to show the
Auction
name.

You said that in the detail section of the
form,
I
could
put in the controls from tblAuctionItems that I
want
to
see/edit. tblAuctionItems only contains the
CPK
built
from AuctionItemID and AuctionID. I moved all
the
other
fields to tblAuctionGifts. So that just leaves
the
subform.

I chose the Auction name from the combo box in
the
form
header and it worked fine. And then, nothing.
The
subform doesn't have anything in it, so I
thought
I'd
make a combo box associated with
AuctionItemName
from
tblAuctionGifts to pull in information since
tblAuctionGifts contains the details about each
donation.
I created cboItemName with a Row Source:

SELECT tblAuctionGifts.AuctionGiftsID,
tblAuctionGifts.AuctionItemName
FROM tblAuctionGifts;

I bound it to AuctionGiftID in tblAuctionGifts,
but
then
I got the dreaded: "control can't be edited ;
it's
bound
to AutoNumber
field 'tblAuctionGifts.AuctionGiftsID'.

So I'm not doing it right, but I do need (I
think)
to be
able to select gifts to assign to items (I
think
you
are
calling "items" what I'm think are "baskets").
So
in
this form, I think I want to create "baskets"
and
choose
which gifts goes into the basket, or not
create a
basket,
but select the gift to stand alone (like the
cheese).
Are we thinking the same thing?

Thanks for the patience and help, Sal


-----Original Message-----
Sounds as if you're making good progress.

I think I would design your form for items and
their
contents this way.

Create a form (name it sfrmAuctionItemContents)
whose
recordsource is
similar to this:

SELECT tblAuctionItemContents.*,
tblAuctionGifts.*
FROM tblAuctionItemContents INNER JOIN
tblAuctionGifts ON
tblAuctionItemContents.AuctionGiftID
=
tblAuctionGifts.AuctionGiftID;

Set up this form as a Continuous Forms format
(it
will
be the subform for
the next form). Put the desired controls on the
form
that you'll want to
see/edit when adding individual gifts to an
item.

Save and close this form.


Create a form (name it frmAuctionItems) whose
recordsource is similar to
this:

SELECT * FROM tblAuctionItems
WHERE [AuctionID]=[Forms]![frmAuctionItems]!
[cboAuction];

In the form's header, put a combo box (name it
cboAuction) whose Row Source
is similar to this:
SELECT AuctionID, AuctionName, AuctionDate
FROM tblAuctions ORDER BY AuctionDate DESC;

Use the AfterUpdate event of this combo box to
requery
the main form's
RecordSource:
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub

(If you don't know how to create this code to
run
from
an event, let me
know.)

In the detail section of this form (it should
be
set
for
Single View
format), put controls from the tblAuctionItems
table
that you will want to
see/edit.

In the detail section, put a subform control.
Use
sfrmAuctionItemContents as
the Source Object. Set the LinkChildFields and
LinkMasterFields to
AuctionItemID. Now the subform will be used to
enter
the
gifts that are part
of each item.

Save and close this form.


This setup will not let you create a new
auction.
You'll
need a separate
form that will let you create a new record in
tblAuctions table for a new
auction. Then that auction can be seen in the
above
form
and be available
for selection.


--

Ken Snell
MS ACCESS MVP




"SillySally"
wrote in
message
news:058701c518ef$7833acb0$a601280a@phx .gbl...
Thanks for the clarification- before I
posted I
had
the
donor form/gifts subform correct, but then
got
confused
with items and gifts. So now I'm able to
enter
donors
and
their multiple donated gifts.

tblAuctionGifts
now contains date donated, value,
description,
min.
bid...

so I removed these type of fields from
tblAuctionItems
since tblAuctionItemContents has CPK to Items
and
Gifts.

I really do have an Auction table
tblAuctions
AuctionID
AuctionDate
AuctionName
AuctionLocation

In tblAuctionItems, I removed AuctionGiftsID
as
instructed.

Okay, then! So now I want to be able to list
Items
for
an
Auction as well as create baskets of items
for
an
auction. Once the item is assigned to a
basket,
I
don't
want it to be available as a "lone" item (but
of
course,
I may need to unassign it from a basket to
either
go
to a
different basket or stand alone).

I not really following what to do with
tblAuctionItems
and tblAuctionItemContents or how to create a
form
that
shows the items available and then create
baskets.
Not
much of a visionary, sadly.

Thanks for the guidance, Sal



-----Original Message-----
Answers inline....

--

Ken Snell
MS ACCESS MVP


"SillySally"

wrote
in
message
news:0d3a01c5186d$5fb3adc0
...
Thanks- hard to believe I'm still having
problems.
I have tables:

tblAuctionDonors
AuctionDonorID (PK)
FirstName
LastName

OK good.



tblAuctionGifts (I put the "key" symbol
next
to
both
the
ID fields which I hope gives me a composite
primary
key)
AuctionGiftsID
AuctionDonorID

Although this is what I posted in the
original
structure, I think you can
use just AuctionGiftsID as the primary key.
No
need to
have a composite
primary key now that I've thought about this
a
bit
more.
(And yes, one way
you can create a composite primary key is to
highlight
both fields and the
click the Key icon.) You also could add more
fields to
this table, such as
AuctionGiftDateDonated,
AuctionGiftDescription,
etc.



tblAuctionItems (CPK with the next 2 listed
IDs)
AuctionItemID
AuctionID
DateReceived
ItemValue
ShortDescription
MinimumBid
AuctionGiftsID (I added this myself,
think I
need
it;
or
maybe I should have added AuctionItemID to
AuctionGifts?)

Delete the AuctionGiftsID field from the
above
table.
AuctionGiftsID is used
in tblAuctionItemContents to relate an item
to
the
gift
(s) that compose that
item.




tblAuctionItemContents (CPK with the next 2
listed
IDs)
AuctionItemID
AuctionGiftsID

OK good.


You're missing the tblAuctions table, from
which
you
get
the value for
AuctionID in tblAuctionItems table.



Auction Donor Entry (form): Donor listing
(name,
address).
Record source is tblAuctionDonor.

So the main form is fine. But I'm having
problems
with
the subform. I tried to create a query
like
the
one
you
suggested:
SELECT tblAuctionGifts.AuctionDonorID,
tblAuctionItems.AuctionItemID,
tblAuctionItems.ItemValue,
tblAuctionItems.AuctionItemName,
tblAuctionItems.MinimumBid,
tblAuctionGifts.AuctionGiftsID,
FROM tblAuctionGifts INNER JOIN
tblAuctionItems
ON
tblAuctionGifts.AuctionGiftsID =
tblAuctionItems.AuctionGiftsID;

which seems like it has everything I need:
it
links
together DonorID, ItemID, and GiftsID.
Yet,
it
opens "grayed out" so that I can't enter
anything
into
it. I see that given the table setup, I
need
both
Gifts
and Items together to be able to link to
Donors. I
expect
this subform to allow me to list, in
datasheet
form,
the
items received from each donor.

The query that I'd posted is just an example.
It
will
not work for the donor
form and gift subform set up.

In this case, the subform should use a query
similar
to
this:

SELECT * FROM tblAuctionGifts;

Then you set the LinkMasterFields and
LinkChildFields
properties for the
subform control (the control on the main form
that
holds
the gifts subform)
to link the subform's records to the main
form's
records. In this case, set
both properties to AuctionDonorID. Then, as
you
select
a
donor on the main
form, the subform will show the records for
that
donor,
including the
ability to enter new records.




I'd like to get the first form working
before I
move
to
the other one. Thanks for your help, Sal



snipped for length



-----Original Message-----
What you would want is five tables (PK
=
primary
key;
CPK = composite
primary key)

tblAuctionDonors (the people who
donate
gifts)
AuctionDonorID (PK)
AuctionDonorName

tblAuctionGifts (the gifts you get
for
an
auction)
AuctionGiftID (CPK)
AuctionDonorID (CPK)

tblAuctions (the individual auctions)
AuctionID
AuctionName
AuctionDate

tblAuctionItems (the items that are
available
for
each
auction)
AuctionItemID (CPK)
AuctionID (CPK)
AuctionItemName

tblAuctionItemContents (the gifts that
are
in
each
item)
AuctionItemID (CPK)
AuctionGiftID (CPK)


.



.



.



.



.



.

Not sure what may be happening. If you can zip up your database and email it
to me, I'll take a look as time permits. Be sure to identify the forms,
tables, etc. that are pertinent.

My email address can be obtained by removing this is not real from my reply
email address.
--

Ken Snell
MS ACCESS MVP


"SillySally" wrote in message
...
The man with patience!
Thanks for the ponderings. The default value is still
not working.

I have cboAuction:
SELECT tblAuctions.AuctionID, tblAuctions.AuctionDate,
tblAuctions.AuctionName FROM tblAuctions ORDER BY
AuctionDate DESC;

I set AuctionID = [cboAuction]

I have text box with control source: AuctionItemID and
default value
=Nz(DMax("AuctionItemID","tblAuctionItems","Auctio nID = "
& [cboAuction]),0)+1

All components of CPK are required in the tables. Hmm.
Thanks, Sal.

-----Original Message-----
Yes, what we need is to define the default value of the
AuctionID control in
your main form (forgot to tell you that); that is, the
textbox that is bound
to the AuctionID field (not the combo box that is in the
form header).

Set the Default Value for this control to
=[cboAuction]

Now, when you select an auction from the combo box at
top of form, and you
start a new record, the AuctionID field will get the
value from the combo
box, and then the default value for the AuctionItemID
should work fine.

However, if you still see that the AuctionItemID value
is not being filled
in OR if it's getting an incorrect value, then what we
may want to do is to
scrap the use of Default Value for the AuctionID and
AuctionItemID fields,
and instead use a tiny amount of code in the form to
write the correct
values into those fields. But before we go there, let's
see if this works
for you.

Again, a reminder to be sure that you set the Required
property to Yes for
all the fields that together are the CPK in a table so
that those fields
cannot be empty in a record. This again will help to
ensure that the form is
doing what we want it to do.
--

Ken Snell
MS ACCESS MVP


"SillySally" wrote in
message
...
Well, I think your mind's eye is fabulous!
The default value still isn't working. I think I know
why, but don't understand it.

I looked at my 3 tables that have CPKs and here's what
I
found (although I'll just tell you about
tblAuctionItems)

tblAuctionItems
AuctionItemID
AuctionID
AuctionItemName

When I look at the table in datasheet view, only 1
field
appears: AuctionItemName. So that AuctionID and
AuctionItemID aren't getting populated. Which leads me
to believe that maybe I need to define the
relationships
between these tables (correct?). I did and that didn't
help. Grr. Any ideas why I'm having such table
trauma?
Thanks, Sal

-----Original Message-----
The #Error error message indicates that something is
not
right with the
expression; as I don't have the db right here, I may be
using a different
name for something than you are using.

Nz is a function that replaces a Null value with what
you use as the second
argument of the Nz function. In the example I gave you,
a Null from the DMax
function's value will be replaced with a zero. So what
the expression is
supposed to do is find the maximum value in the
tblAuctionItems for the
field AuctionItemID for the records that have a value
for AuctionID that
matches the one you selected in your combo box. . .

aha -- that is my error, I think. Use this expression
as
the default value
(need to use the combo box value!):
=Nz(DMax("AuctionItemID", "tblAuctionItems", "AuctionID
= " & [cboAuction]),
0) + 1

Sorry about that... sometimes it gets a little jumbled
in my "mind's eye"
when trying to "see" the setup there...

I'm not sure I'm following the sequence of events that
you say you're not
liking, but yes, it may be related to the AfterUpdate
code that you are
trying to use. Go with the DefaultValue expression
above
for the
AuctionItemID control, and delete the AfterUpdate code
that you have.

As for eliminating gifts from the combo box once
they've
been selected, yes,
this can be done. Unless you'll have thousands and
thousands of entries,
probably the easiest way is to change the Row Source
query for the
AuctionGiftID value in the subform to this:

SELECT AuctionGifts.AuctionGiftID,
AuctionGifts.AuctionGiftName, (other
fields) FROM AuctionGifts LEFT JOIN AuctionItemContents
ON
AuctionGifts.AuctionGiftID=AuctionItemContents .AuctionG
if
tID WHERE
AuctionItemContents.AuctionGiftID Is Null;

This will "omit" any AuctionGift records whose ID value
is already in a
record in the tblAuctionItemContents table.

Glad you're making progress.
--

Ken Snell
MS ACCESS MVP



"SillySally" wrote
in
message
. ..
Thanks for the advice- I added all of the ID fields
to
the form to see what was happening. I tried your
code
as
the default value and got "#Error".
So I tried:
Private Sub cboAuction_AfterUpdate()
AuctionItemID = Nz(DMax
("AuctionItemID", "tblAuctionItems", "AuctionID = " &
[AuctionID]), 0) + 1
Me.Requery
End Sub

which seemed to work (tell me if I'm crazy), but I
think
it's causing problems as well. Could you explain
what
the Nz(DMax) statement means so I can figure out how
to
use it as the default value?

I'm not loving this form. When I select an Auction
and
then give an ItemName- say Basket1- (assign an
AuctionItemID) so this is Record 1, and then add a
new
record and select an Auction, the form returns
Basket1
(Record 1) so I then hit the button to get to the
second record. But it's probably what happens when I
try
things on my own with the after update.

Is there a way to tell the combo box that once a gift
has
been selected, I don't want to be able to select it
again
unless I delete it from an item?

Thanks for getting me this far! Sal


-----Original Message-----
What I think is happening on your form (when you
select
an AuctionID from
the combo box) is that your current record on the
form
is never getting an
AuctionItemID value (you're not exposing it to the
user,
so the user isn't
entering a value for it; and you're not giving it a
value through your
form's programming), and as you're seeing, no field
in
a
composite primary
key index should be empty/Null. You'll need to have a
way of giving the
AuctionItemID field a value. If you're not going to
expose it to the user,
then use the DefaultValue of the textbox to which
it's
bound to give it a
value for new records. An expression similar to this
should work:
=Nz(DMax
("AuctionItemID", "tblAuctionItems", "AuctionID = " &
[AuctionID]), 0) + 1

This will assign an incrementing number, beginning at
1,
for each item for a
specific auction ID value, when you start a new
record
for an auction item
belonging to an auction.

In your table setups, you indicate that one of the
fields in each CPK pair
is marked as Required, and the other is not. I would
mark both fields in
each CPK as Required; that way, the table will
enforce
that you have a value
in each field for each record.

--

Ken Snell
MS ACCESS MVP


"SillySally" wrote
in
message
. ..
Thanks for the CPK lesson- I appreciate it! And
good
news! sfrmAuctionItemsContents is working. The new
Record Source and combo box work just as I
expected.
Woo
hoo!

Still having problems with frmAuctionItems of
Record
Source:
SELECT *
FROM tblAuctionItems
WHERE (((tblAuctionItems.AuctionID)=[Forms]!
[frmAuctionItems]![cboAuction]));

I have cboAuction:
SELECT tblAuctions.AuctionID,
tblAuctions.AuctionDate,
tblAuctions.AuctionName
FROM tblAuctions
ORDER BY tblAuctions.AuctionDate DESC;

As soon as I select an Auction, I get "Run-time
error '3058': Index or primary key cannot contain a
Null
value. And when I select debug, it sends me right
to
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub

You indicated that on the main portion, I need
AuctionItemID field of the table bound to a textbox
on
the form (which makes sense to me).
tblAuctionItems
has
CPK AuctionItemID and AuctionID so that
AuctionItemID
is
NOT an AutoNumber- just a long integer. I added
AuctionItemName to the table so that I don't have
to
expose AuctionItemID field value to the user. But
I
don't think I'm doing the AuctionItemID field of
the
table bound to a textbox correctly. I tried it two
ways:
I selected the "textbox" icon, and set the control
source
to AuctionItemID which doesn't seem any different
than
just dragging AuctionItemID field to the form. So
then I
tried the "textbox" icon and set it =
[AuctionItemID].
Did I do anything right? I don't want to enter
anything
in this invisible field, rather I want the user to
enter
into AuctionItemName. But how are AuctionItemID
and
AuctionItemName "linked"?

Then you said that if I see "cannot be Null" I
have 1
0f
3 problems (I'm so lucky it's probably 2 or 3 out
of
3!).
1) control bound to that field on my form so that I
can
enter a value (I don't want to enter a value,
correct?),
but I think something like this is the problem
2) not including the field in my form RecordSource-
don't
think this is the problem as I selected * from
tblAuctionItems.
3)LinkMaster/Child fields- don't think that's the
problem
as on sfrmAuctionItemContents, both LinkChildFields
and
LinkMasterFields are set to AuctionItemID.

Here's my table setup:
tblAuctionDonors
AuctionDonorID AutoNumber

tblAuctionGifts (CPK)
AuctionGiftsID Number (Long Integer); Required =
No
(don't know why)
AuctionDonorID Number (Long Integer); Required =
Yes

tblAuctionItemContents (CPK)
AuctionItemID Number (Long Integer); Required = No
AuctionGiftsID Number (Long Integer); Required =
Yes

tblAuctionItems (CPK)
AuctionItemID Number (Long Integer); Required = No
AuctionID Number (Long Integer): Required = Yes

tblAuctions
AcutionID AutoNumber

Thanks for the help and the fantastic explanations!
Sal

-----Original Message-----
Quick stepback here.

Autonumber fields are often used as primary key
fields.
But because they are
unique in and of themselves, they would be used as
single primary key field
(not a composite primary key field). Thus, if you
use
an
autonumber in a
table for the primary key, it should be the only
field
that is in the
primary key. (Autonumber fields are Long Integer
data
type.)

When a child table is using a field as a foreign
key
(meaning that it is
meant to be a joining field between the child and
the
parent tables), it
must have the same data type as the key in the
parent
table. Thus, if the
parent table contains MyID (an autonumber) as a
primary
key, and your child
table uses MyID for joining the tables, MyID in the
child table must by Long
Integer.

Composite primary keys are nothing more than a
combination of fields where
the combination of the values from those fields is
a
unique combination
(only one record in the table has that combination
of
values). Individually,
each field that is in the composite primary key may
have
the same value in
more than one record. For example, suppose my child
table contains two
fields: CarType and CarColor. Suppose that they
are
a
composite primary
key. That means that my table could contain these
records:
CarType CarColor
------- -------
Cadillac Red
Cadillac Yellow
Cadillac White
Pontiac Black
Pontiac Red
Impala While

Each combination is unique, even though some values
repeat in the records.

OK - now to the current issues.

When a field is part of a composite primary key,
that
field must have a
non-Null value in order to save the record. While
setting the Default Value
of the field to 0 (when it's a Long Integer data
type)
can avoid this error
occurring, it also masks the possibility that your
form
is not properly
writing a value to that field when you create a new
record. Thus, you may
think you're saving data with the correct value in
a
joining field, when in
reality they're all getting a value of 0, which
won't
join back to the
parent table (and then you'll wonder why you
can't "find" your data). So,
until you're sure that your form is working
correctly,
I
would leave the
Default Value as Null (or empty) for now.

The fact that you see the "cannot be Null" error
suggests that you don't
have a control bound to that field on your form so
that
you can enter a
value for that field; or it suggests that you're
not
including the field in
your form (or subform) RecordSource; or it suggests
that
your
LinkMasterFields and LinkChildFields properties are
not
set correctly for
the subform so that the form will write the value
into
that field for you.

This code:
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub

should not create any "duplicate value" problems
*unless* you've bound the
cboAuction control to a field in the form's
RecordSource. This control must
be unbound (the Control Source must be empty)
because
all you're using it
for is to navigate the form to the desired records.
If
you have a field name
in its ControlSource, then you're changing the
value
of
AuctionID in some
records in the tblAuctionItems table from what it
was
to
what you select --
and this will cause duplicate value problems (not
to
mention potentially
mess up your data).

I made an error in what I told you the RecordSource
for
the subform should
be (it does happen g ).

Change it to this:
SELECT tblAuctionItemContents.*
FROM tblAuctionItemContents;

Your combo box that will allow you to select the
AuctionGiftID should have a
Row Source query similar to this:
SELECT AuctionGiftID, AuctionGiftName, (other
fields) FROM AuctionGifts;
This combo box should be bound to AuctionGiftID
field
in
the subform's
RecordSource. The bound column of this combo box
should
be 1.

Now you should be able to select a gift so that it
can
be added to the
item's contents.

--

Ken Snell
MS ACCESS MVP


"SillySally"
wrote
in
message
l...
Sorry- I wasn't clear. I was talking about a
combo
box on
the subform (I understand about navigating on the
main
form using *). These composite primary keys are
very
confusing to me (but I did change all of them to
long
integer). I'm having a couple of problems- first
with
the
tables. I was getting error messages that IDs
couldn't be
null, so I set the default values to 0 and then I
got
duplicate value violations.
Now that my CPK are long integers, should I have
a
default
value? And
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub
is giving me grief (duplicate values in the index
which
must be related to my table question).

2nd trauma
I created fmrAuctionItems with
sfrmAcutionItemContents. I
was creating a combo box on the subform, but now
somehow,
I'm unable to enter anything into the subform
(it's
all
grayed out). So I rebuilt the form and subform
again-
same thing. Which is frustrating because I
finally
see
where I'm going. I think I even built the
correct
combo
box for the subform before grayness took over.
Any
ideas
why can't I enter anything in the subform?

frmAuctionItems RecordSource:
SELECT *
FROM tblAuctionItems
WHERE (((tblAuctionItems.AuctionID)=[Forms]!
[frmAuctionItems]![cboAuction]));

sfrmAuctionItemContents RecordSource:
SELECT tblAuctionItemContents.*,
tblAuctionGifts.*
FROM tblAuctionItemContents INNER JOIN
tblAuctionGifts
ON
tblAuctionItemContents.AuctionGiftsID =
tblAuctionGifts.AuctionGiftsID;

Now, if I delete tblAcutionItemsContent from the
sfrm
and
just leave tblAuctionGits, the sql is fine. So
my
join
must be the problem? Although it's probably
related
to my
table woes.

Sorry to be such a pain! Sal



-----Original Message-----
You're still going in the right direction. What
you
need
to stop and think
about now is -- what am I wanting to do on this
form?
The
answer will then
tell you what you need to do.

The answer, from my perspective, is that you want
to
select or create an
auction item on the form, and then to assign
auction
gifts to that item.
Right?

So, on the main portion of frmAuctionItems, you
need
the
AuctionItemID field
of the table bound to a textbox on the form
(likely,
you're using an
autonumber field for this? so the textbox can be
hidden/invisible), and you
want a textbox to enter the name of a new auction
item.
Unless you plan to
expose the AuctionItemID field's value to the
user
(which
isn't necessary),
then you should have another field in
tblAuctionItems
that allows you to
give the record an identifying name. This is then
what
you display on the
form. Otherwise, if you want to use the
AuctionItemID
as
the identifying
value to the user, then display it in a textbox
that
is
not hidden.

Use the navigation buttons at bottom of form to
move
back
and forth between
existing records (auction items), or to create a
new
auction item (the *
button). No combo box is needed for this type of
design
(it's possible to
use a combo box for selecting the auction item
that
you
want, instead of
using navigation buttons -- you do that by using
an
unbound combo box that
has a Row Source that essentially is the same as
the
form's RecordSource
query, and then you would use code to move the
form
to
the selected item --
but let's leave that for the future when you're
comfortable with what you're
doing now).

In the subform, you would use a combo box to
select a
specific AuctionGift
to be a component of the AuctionItem being
displayed
on
the main form. It
should be bound to AuctionGiftID field in
tblAuctionItemContents. (Note:
this AuctionGiftID field should not be an
autonumber
field here; it should
be a Long Integer field so that its data type
matches
the
autonumber type
that you likely used for AuctionGiftID field in
tblAuctionGifts. Autonumber
field is used ONLY in the table where the field
value
is
the primary key
identifier (not a composite, but the only field
that
is
the primary key) for
that record; in the children table that join to
the
main
table via this key,
the field then is a Long Integer. You may have
this
set
up incorrectly in
your children tables.)

Oh, and yes, what you call "baskets" I am
calling "items". But I am going
one step beyond that. An individual item can be
composed
of a single gift,
and its contents would be that single
AuctionGiftID
(one
record in
tblAuctionItemContents table). Thus, if you have
a "baseball bat" up for
auction and that is a single entity, it should
have a
single record in
tblAuctionItemaContents table that shows that its
content
is a single gift.
Contrast that with a "basket" that is made up of
a
gift
certificate, candy,
and a travel voucher -- this item would have
three
records in
tblAuctionItemContents table. This structure
allows
you
to have a different
Name for an item from what it is named as a gift,
as
well
as any other
details that might differ from what you knew when
it
was
accepted as a gift
versus what you know/do when it becomes an item.
--

Ken Snell
MS ACCESS MVP




"SillySally"
wrote
in
message
news:12cd01c51920$c850bef0$a501280a@phx. gbl...
Thanks for the step-through instructions. I
now
have
frmAuctionItems with sfrmAuctionItemContents.
And
I
previously created frmAuctions so I can enter
each
Auction.

On frmAuctionItems, cboAuction works just
right. I
even
added a field =cboAuction.Column(2) to show the
Auction
name.

You said that in the detail section of the
form,
I
could
put in the controls from tblAuctionItems that I
want
to
see/edit. tblAuctionItems only contains the
CPK
built
from AuctionItemID and AuctionID. I moved all
the
other
fields to tblAuctionGifts. So that just leaves
the
subform.

I chose the Auction name from the combo box in
the
form
header and it worked fine. And then, nothing.
The
subform doesn't have anything in it, so I
thought
I'd
make a combo box associated with
AuctionItemName
from
tblAuctionGifts to pull in information since
tblAuctionGifts contains the details about each
donation.
I created cboItemName with a Row Source:

SELECT tblAuctionGifts.AuctionGiftsID,
tblAuctionGifts.AuctionItemName
FROM tblAuctionGifts;

I bound it to AuctionGiftID in tblAuctionGifts,
but
then
I got the dreaded: "control can't be edited ;
it's
bound
to AutoNumber
field 'tblAuctionGifts.AuctionGiftsID'.

So I'm not doing it right, but I do need (I
think)
to be
able to select gifts to assign to items (I
think
you
are
calling "items" what I'm think are "baskets").
So
in
this form, I think I want to create "baskets"
and
choose
which gifts goes into the basket, or not
create a
basket,
but select the gift to stand alone (like the
cheese).
Are we thinking the same thing?

Thanks for the patience and help, Sal


-----Original Message-----
Sounds as if you're making good progress.

I think I would design your form for items and
their
contents this way.

Create a form (name it sfrmAuctionItemContents)
whose
recordsource is
similar to this:

SELECT tblAuctionItemContents.*,
tblAuctionGifts.*
FROM tblAuctionItemContents INNER JOIN
tblAuctionGifts ON
tblAuctionItemContents.AuctionGiftID
=
tblAuctionGifts.AuctionGiftID;

Set up this form as a Continuous Forms format
(it
will
be the subform for
the next form). Put the desired controls on the
form
that you'll want to
see/edit when adding individual gifts to an
item.

Save and close this form.


Create a form (name it frmAuctionItems) whose
recordsource is similar to
this:

SELECT * FROM tblAuctionItems
WHERE [AuctionID]=[Forms]![frmAuctionItems]!
[cboAuction];

In the form's header, put a combo box (name it
cboAuction) whose Row Source
is similar to this:
SELECT AuctionID, AuctionName, AuctionDate
FROM tblAuctions ORDER BY AuctionDate DESC;

Use the AfterUpdate event of this combo box to
requery
the main form's
RecordSource:
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub

(If you don't know how to create this code to
run
from
an event, let me
know.)

In the detail section of this form (it should
be
set
for
Single View
format), put controls from the tblAuctionItems
table
that you will want to
see/edit.

In the detail section, put a subform control.
Use
sfrmAuctionItemContents as
the Source Object. Set the LinkChildFields and
LinkMasterFields to
AuctionItemID. Now the subform will be used to
enter
the
gifts that are part
of each item.

Save and close this form.


This setup will not let you create a new
auction.
You'll
need a separate
form that will let you create a new record in
tblAuctions table for a new
auction. Then that auction can be seen in the
above
form
and be available
for selection.


--

Ken Snell
MS ACCESS MVP




"SillySally"
wrote in
message
news:058701c518ef$7833acb0$a601280a@ph x.gbl...
Thanks for the clarification- before I
posted I
had
the
donor form/gifts subform correct, but then
got
confused
with items and gifts. So now I'm able to
enter
donors
and
their multiple donated gifts.

tblAuctionGifts
now contains date donated, value,
description,
min.
bid...

so I removed these type of fields from
tblAuctionItems
since tblAuctionItemContents has CPK to Items
and
Gifts.

I really do have an Auction table
tblAuctions
AuctionID
AuctionDate
AuctionName
AuctionLocation

In tblAuctionItems, I removed AuctionGiftsID
as
instructed.

Okay, then! So now I want to be able to list
Items
for
an
Auction as well as create baskets of items
for
an
auction. Once the item is assigned to a
basket,
I
don't
want it to be available as a "lone" item (but
of
course,
I may need to unassign it from a basket to
either
go
to a
different basket or stand alone).

I not really following what to do with
tblAuctionItems
and tblAuctionItemContents or how to create a
form
that
shows the items available and then create
baskets.
Not
much of a visionary, sadly.

Thanks for the guidance, Sal



-----Original Message-----
Answers inline....

--

Ken Snell
MS ACCESS MVP


"SillySally"

wrote
in
message
news:0d3a01c5186d$5fb3adc0
...
Thanks- hard to believe I'm still having
problems.
I have tables:

tblAuctionDonors
AuctionDonorID (PK)
FirstName
LastName

OK good.



tblAuctionGifts (I put the "key" symbol
next
to
both
the
ID fields which I hope gives me a composite
primary
key)
AuctionGiftsID
AuctionDonorID

Although this is what I posted in the
original
structure, I think you can
use just AuctionGiftsID as the primary key.
No
need to
have a composite
primary key now that I've thought about this
a
bit
more.
(And yes, one way
you can create a composite primary key is to
highlight
both fields and the
click the Key icon.) You also could add more
fields to
this table, such as
AuctionGiftDateDonated,
AuctionGiftDescription,
etc.



tblAuctionItems (CPK with the next 2 listed
IDs)
AuctionItemID
AuctionID
DateReceived
ItemValue
ShortDescription
MinimumBid
AuctionGiftsID (I added this myself,
think I
need
it;
or
maybe I should have added AuctionItemID to
AuctionGifts?)

Delete the AuctionGiftsID field from the
above
table.
AuctionGiftsID is used
in tblAuctionItemContents to relate an item
to
the
gift
(s) that compose that
item.




tblAuctionItemContents (CPK with the next 2
listed
IDs)
AuctionItemID
AuctionGiftsID

OK good.


You're missing the tblAuctions table, from
which
you
get
the value for
AuctionID in tblAuctionItems table.



Auction Donor Entry (form): Donor listing
(name,
address).
Record source is tblAuctionDonor.

So the main form is fine. But I'm having
problems
with
the subform. I tried to create a query
like
the
one
you
suggested:
SELECT tblAuctionGifts.AuctionDonorID,
tblAuctionItems.AuctionItemID,
tblAuctionItems.ItemValue,
tblAuctionItems.AuctionItemName,
tblAuctionItems.MinimumBid,
tblAuctionGifts.AuctionGiftsID,
FROM tblAuctionGifts INNER JOIN
tblAuctionItems
ON
tblAuctionGifts.AuctionGiftsID =
tblAuctionItems.AuctionGiftsID;

which seems like it has everything I need:
it
links
together DonorID, ItemID, and GiftsID.
Yet,
it
opens "grayed out" so that I can't enter
anything
into
it. I see that given the table setup, I
need
both
Gifts
and Items together to be able to link to
Donors. I
expect
this subform to allow me to list, in
datasheet
form,
the
items received from each donor.

The query that I'd posted is just an example.
It
will
not work for the donor
form and gift subform set up.

In this case, the subform should use a query
similar
to
this:

SELECT * FROM tblAuctionGifts;

Then you set the LinkMasterFields and
LinkChildFields
properties for the
subform control (the control on the main form
that
holds
the gifts subform)
to link the subform's records to the main
form's
records. In this case, set
both properties to AuctionDonorID. Then, as
you
select
a
donor on the main
form, the subform will show the records for
that
donor,
including the
ability to enter new records.




I'd like to get the first form working
before I
move
to
the other one. Thanks for your help, Sal



snipped for length



-----Original Message-----
What you would want is five tables (PK
=
primary
key;
CPK = composite
primary key)

tblAuctionDonors (the people who
donate
gifts)
AuctionDonorID (PK)
AuctionDonorName

tblAuctionGifts (the gifts you get
for
an
auction)
AuctionGiftID (CPK)
AuctionDonorID (CPK)

tblAuctions (the individual auctions)
AuctionID
AuctionName
AuctionDate

tblAuctionItems (the items that are
available
for
each
auction)
AuctionItemID (CPK)
AuctionID (CPK)
AuctionItemName

tblAuctionItemContents (the gifts that
are
in
each
item)
AuctionItemID (CPK)
AuctionGiftID (CPK)


.



.



.



.



.



.

I've looked at the database.

The "Record cannot contain a Null value" error is because you had the
cboAuction combo box bound to the AuctionID field in the main form's
RecordSource. This combo box must be unbound (its ControlSource should be
empty). What was happening was that, after you selected an auction item, the
form began to create a new record (when the form opens, because there is no
selection initially in the AuctionID combo box, it is starting with a record
ready to be created; because the combo box was bound, your selection of an
auction began writing into a new record) and then the focus moved to the
subform before you could enter other data for the main form.

I've revised the frmAuctionItems form and returned the database to you by
private email. I removed the AuctionID field from the cboAuction control's
ControlSource, and I've added code for the form's Load event to put the
focus into the combo box right away. That will help avoid the focus going to
the subform when the form is opened.

Your thought about adding a field to tblAuctionGifts that will allow you to
identify a specific auction for which that gift was solicited is an ok idea.
Just don't set a relationship from it to tblAuctions unless you will always
have the auction already established in the database before you solicit the
gift.

--

Ken Snell
MS ACCESS MVP


"Ken Snell [MVP]" wrote in message
...
Not sure what may be happening. If you can zip up your database and email
it to me, I'll take a look as time permits. Be sure to identify the forms,
tables, etc. that are pertinent.

My email address can be obtained by removing this is not real from my
reply email address.
--

Ken Snell
MS ACCESS MVP


"SillySally" wrote in message
...
The man with patience!
Thanks for the ponderings. The default value is still
not working.

I have cboAuction:
SELECT tblAuctions.AuctionID, tblAuctions.AuctionDate,
tblAuctions.AuctionName FROM tblAuctions ORDER BY
AuctionDate DESC;

I set AuctionID = [cboAuction]

I have text box with control source: AuctionItemID and
default value
=Nz(DMax("AuctionItemID","tblAuctionItems","Auctio nID = "
& [cboAuction]),0)+1

All components of CPK are required in the tables. Hmm.
Thanks, Sal.

-----Original Message-----
Yes, what we need is to define the default value of the
AuctionID control in
your main form (forgot to tell you that); that is, the
textbox that is bound
to the AuctionID field (not the combo box that is in the
form header).

Set the Default Value for this control to
=[cboAuction]

Now, when you select an auction from the combo box at
top of form, and you
start a new record, the AuctionID field will get the
value from the combo
box, and then the default value for the AuctionItemID
should work fine.

However, if you still see that the AuctionItemID value
is not being filled
in OR if it's getting an incorrect value, then what we
may want to do is to
scrap the use of Default Value for the AuctionID and
AuctionItemID fields,
and instead use a tiny amount of code in the form to
write the correct
values into those fields. But before we go there, let's
see if this works
for you.

Again, a reminder to be sure that you set the Required
property to Yes for
all the fields that together are the CPK in a table so
that those fields
cannot be empty in a record. This again will help to
ensure that the form is
doing what we want it to do.
--

Ken Snell
MS ACCESS MVP


"SillySally" wrote in
message
.. .
Well, I think your mind's eye is fabulous!
The default value still isn't working. I think I know
why, but don't understand it.

I looked at my 3 tables that have CPKs and here's what
I
found (although I'll just tell you about
tblAuctionItems)

tblAuctionItems
AuctionItemID
AuctionID
AuctionItemName

When I look at the table in datasheet view, only 1
field
appears: AuctionItemName. So that AuctionID and
AuctionItemID aren't getting populated. Which leads me
to believe that maybe I need to define the
relationships
between these tables (correct?). I did and that didn't
help. Grr. Any ideas why I'm having such table
trauma?
Thanks, Sal

-----Original Message-----
The #Error error message indicates that something is
not
right with the
expression; as I don't have the db right here, I may be
using a different
name for something than you are using.

Nz is a function that replaces a Null value with what
you use as the second
argument of the Nz function. In the example I gave you,
a Null from the DMax
function's value will be replaced with a zero. So what
the expression is
supposed to do is find the maximum value in the
tblAuctionItems for the
field AuctionItemID for the records that have a value
for AuctionID that
matches the one you selected in your combo box. . .

aha -- that is my error, I think. Use this expression
as
the default value
(need to use the combo box value!):
=Nz(DMax("AuctionItemID", "tblAuctionItems", "AuctionID
= " & [cboAuction]),
0) + 1

Sorry about that... sometimes it gets a little jumbled
in my "mind's eye"
when trying to "see" the setup there...

I'm not sure I'm following the sequence of events that
you say you're not
liking, but yes, it may be related to the AfterUpdate
code that you are
trying to use. Go with the DefaultValue expression
above
for the
AuctionItemID control, and delete the AfterUpdate code
that you have.

As for eliminating gifts from the combo box once
they've
been selected, yes,
this can be done. Unless you'll have thousands and
thousands of entries,
probably the easiest way is to change the Row Source
query for the
AuctionGiftID value in the subform to this:

SELECT AuctionGifts.AuctionGiftID,
AuctionGifts.AuctionGiftName, (other
fields) FROM AuctionGifts LEFT JOIN AuctionItemContents
ON
AuctionGifts.AuctionGiftID=AuctionItemContent s.AuctionG
if
tID WHERE
AuctionItemContents.AuctionGiftID Is Null;

This will "omit" any AuctionGift records whose ID value
is already in a
record in the tblAuctionItemContents table.

Glad you're making progress.
--

Ken Snell
MS ACCESS MVP



"SillySally" wrote
in
message
.. .
Thanks for the advice- I added all of the ID fields
to
the form to see what was happening. I tried your
code
as
the default value and got "#Error".
So I tried:
Private Sub cboAuction_AfterUpdate()
AuctionItemID = Nz(DMax
("AuctionItemID", "tblAuctionItems", "AuctionID = " &
[AuctionID]), 0) + 1
Me.Requery
End Sub

which seemed to work (tell me if I'm crazy), but I
think
it's causing problems as well. Could you explain
what
the Nz(DMax) statement means so I can figure out how
to
use it as the default value?

I'm not loving this form. When I select an Auction
and
then give an ItemName- say Basket1- (assign an
AuctionItemID) so this is Record 1, and then add a
new
record and select an Auction, the form returns
Basket1
(Record 1) so I then hit the button to get to the
second record. But it's probably what happens when I
try
things on my own with the after update.

Is there a way to tell the combo box that once a gift
has
been selected, I don't want to be able to select it
again
unless I delete it from an item?

Thanks for getting me this far! Sal


-----Original Message-----
What I think is happening on your form (when you
select
an AuctionID from
the combo box) is that your current record on the
form
is never getting an
AuctionItemID value (you're not exposing it to the
user,
so the user isn't
entering a value for it; and you're not giving it a
value through your
form's programming), and as you're seeing, no field
in
a
composite primary
key index should be empty/Null. You'll need to have a
way of giving the
AuctionItemID field a value. If you're not going to
expose it to the user,
then use the DefaultValue of the textbox to which
it's
bound to give it a
value for new records. An expression similar to this
should work:
=Nz(DMax
("AuctionItemID", "tblAuctionItems", "AuctionID = " &
[AuctionID]), 0) + 1

This will assign an incrementing number, beginning at
1,
for each item for a
specific auction ID value, when you start a new
record
for an auction item
belonging to an auction.

In your table setups, you indicate that one of the
fields in each CPK pair
is marked as Required, and the other is not. I would
mark both fields in
each CPK as Required; that way, the table will
enforce
that you have a value
in each field for each record.

--

Ken Snell
MS ACCESS MVP


"SillySally" wrote
in
message
...
Thanks for the CPK lesson- I appreciate it! And
good
news! sfrmAuctionItemsContents is working. The new
Record Source and combo box work just as I
expected.
Woo
hoo!

Still having problems with frmAuctionItems of
Record
Source:
SELECT *
FROM tblAuctionItems
WHERE (((tblAuctionItems.AuctionID)=[Forms]!
[frmAuctionItems]![cboAuction]));

I have cboAuction:
SELECT tblAuctions.AuctionID,
tblAuctions.AuctionDate,
tblAuctions.AuctionName
FROM tblAuctions
ORDER BY tblAuctions.AuctionDate DESC;

As soon as I select an Auction, I get "Run-time
error '3058': Index or primary key cannot contain a
Null
value. And when I select debug, it sends me right
to
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub

You indicated that on the main portion, I need
AuctionItemID field of the table bound to a textbox
on
the form (which makes sense to me).
tblAuctionItems
has
CPK AuctionItemID and AuctionID so that
AuctionItemID
is
NOT an AutoNumber- just a long integer. I added
AuctionItemName to the table so that I don't have
to
expose AuctionItemID field value to the user. But
I
don't think I'm doing the AuctionItemID field of
the
table bound to a textbox correctly. I tried it two
ways:
I selected the "textbox" icon, and set the control
source
to AuctionItemID which doesn't seem any different
than
just dragging AuctionItemID field to the form. So
then I
tried the "textbox" icon and set it =
[AuctionItemID].
Did I do anything right? I don't want to enter
anything
in this invisible field, rather I want the user to
enter
into AuctionItemName. But how are AuctionItemID
and
AuctionItemName "linked"?

Then you said that if I see "cannot be Null" I
have 1
0f
3 problems (I'm so lucky it's probably 2 or 3 out
of
3!).
1) control bound to that field on my form so that I
can
enter a value (I don't want to enter a value,
correct?),
but I think something like this is the problem
2) not including the field in my form RecordSource-
don't
think this is the problem as I selected * from
tblAuctionItems.
3)LinkMaster/Child fields- don't think that's the
problem
as on sfrmAuctionItemContents, both LinkChildFields
and
LinkMasterFields are set to AuctionItemID.

Here's my table setup:
tblAuctionDonors
AuctionDonorID AutoNumber

tblAuctionGifts (CPK)
AuctionGiftsID Number (Long Integer); Required =
No
(don't know why)
AuctionDonorID Number (Long Integer); Required =
Yes

tblAuctionItemContents (CPK)
AuctionItemID Number (Long Integer); Required = No
AuctionGiftsID Number (Long Integer); Required =
Yes

tblAuctionItems (CPK)
AuctionItemID Number (Long Integer); Required = No
AuctionID Number (Long Integer): Required = Yes

tblAuctions
AcutionID AutoNumber

Thanks for the help and the fantastic explanations!
Sal

-----Original Message-----
Quick stepback here.

Autonumber fields are often used as primary key
fields.
But because they are
unique in and of themselves, they would be used as
single primary key field
(not a composite primary key field). Thus, if you
use
an
autonumber in a
table for the primary key, it should be the only
field
that is in the
primary key. (Autonumber fields are Long Integer
data
type.)

When a child table is using a field as a foreign
key
(meaning that it is
meant to be a joining field between the child and
the
parent tables), it
must have the same data type as the key in the
parent
table. Thus, if the
parent table contains MyID (an autonumber) as a
primary
key, and your child
table uses MyID for joining the tables, MyID in the
child table must by Long
Integer.

Composite primary keys are nothing more than a
combination of fields where
the combination of the values from those fields is
a
unique combination
(only one record in the table has that combination
of
values). Individually,
each field that is in the composite primary key may
have
the same value in
more than one record. For example, suppose my child
table contains two
fields: CarType and CarColor. Suppose that they
are
a
composite primary
key. That means that my table could contain these
records:
CarType CarColor
------- -------
Cadillac Red
Cadillac Yellow
Cadillac White
Pontiac Black
Pontiac Red
Impala While

Each combination is unique, even though some values
repeat in the records.

OK - now to the current issues.

When a field is part of a composite primary key,
that
field must have a
non-Null value in order to save the record. While
setting the Default Value
of the field to 0 (when it's a Long Integer data
type)
can avoid this error
occurring, it also masks the possibility that your
form
is not properly
writing a value to that field when you create a new
record. Thus, you may
think you're saving data with the correct value in
a
joining field, when in
reality they're all getting a value of 0, which
won't
join back to the
parent table (and then you'll wonder why you
can't "find" your data). So,
until you're sure that your form is working
correctly,
I
would leave the
Default Value as Null (or empty) for now.

The fact that you see the "cannot be Null" error
suggests that you don't
have a control bound to that field on your form so
that
you can enter a
value for that field; or it suggests that you're
not
including the field in
your form (or subform) RecordSource; or it suggests
that
your
LinkMasterFields and LinkChildFields properties are
not
set correctly for
the subform so that the form will write the value
into
that field for you.

This code:
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub

should not create any "duplicate value" problems
*unless* you've bound the
cboAuction control to a field in the form's
RecordSource. This control must
be unbound (the Control Source must be empty)
because
all you're using it
for is to navigate the form to the desired records.
If
you have a field name
in its ControlSource, then you're changing the
value
of
AuctionID in some
records in the tblAuctionItems table from what it
was
to
what you select --
and this will cause duplicate value problems (not
to
mention potentially
mess up your data).

I made an error in what I told you the RecordSource
for
the subform should
be (it does happen g ).

Change it to this:
SELECT tblAuctionItemContents.*
FROM tblAuctionItemContents;

Your combo box that will allow you to select the
AuctionGiftID should have a
Row Source query similar to this:
SELECT AuctionGiftID, AuctionGiftName, (other
fields) FROM AuctionGifts;
This combo box should be bound to AuctionGiftID
field
in
the subform's
RecordSource. The bound column of this combo box
should
be 1.

Now you should be able to select a gift so that it
can
be added to the
item's contents.

--

Ken Snell
MS ACCESS MVP


"SillySally"
wrote
in
message
bl...
Sorry- I wasn't clear. I was talking about a
combo
box on
the subform (I understand about navigating on the
main
form using *). These composite primary keys are
very
confusing to me (but I did change all of them to
long
integer). I'm having a couple of problems- first
with
the
tables. I was getting error messages that IDs
couldn't be
null, so I set the default values to 0 and then I
got
duplicate value violations.
Now that my CPK are long integers, should I have
a
default
value? And
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub
is giving me grief (duplicate values in the index
which
must be related to my table question).

2nd trauma
I created fmrAuctionItems with
sfrmAcutionItemContents. I
was creating a combo box on the subform, but now
somehow,
I'm unable to enter anything into the subform
(it's
all
grayed out). So I rebuilt the form and subform
again-
same thing. Which is frustrating because I
finally
see
where I'm going. I think I even built the
correct
combo
box for the subform before grayness took over.
Any
ideas
why can't I enter anything in the subform?

frmAuctionItems RecordSource:
SELECT *
FROM tblAuctionItems
WHERE (((tblAuctionItems.AuctionID)=[Forms]!
[frmAuctionItems]![cboAuction]));

sfrmAuctionItemContents RecordSource:
SELECT tblAuctionItemContents.*,
tblAuctionGifts.*
FROM tblAuctionItemContents INNER JOIN
tblAuctionGifts
ON
tblAuctionItemContents.AuctionGiftsID =
tblAuctionGifts.AuctionGiftsID;

Now, if I delete tblAcutionItemsContent from the
sfrm
and
just leave tblAuctionGits, the sql is fine. So
my
join
must be the problem? Although it's probably
related
to my
table woes.

Sorry to be such a pain! Sal



-----Original Message-----
You're still going in the right direction. What
you
need
to stop and think
about now is -- what am I wanting to do on this
form?
The
answer will then
tell you what you need to do.

The answer, from my perspective, is that you want
to
select or create an
auction item on the form, and then to assign
auction
gifts to that item.
Right?

So, on the main portion of frmAuctionItems, you
need
the
AuctionItemID field
of the table bound to a textbox on the form
(likely,
you're using an
autonumber field for this? so the textbox can be
hidden/invisible), and you
want a textbox to enter the name of a new auction
item.
Unless you plan to
expose the AuctionItemID field's value to the
user
(which
isn't necessary),
then you should have another field in
tblAuctionItems
that allows you to
give the record an identifying name. This is then
what
you display on the
form. Otherwise, if you want to use the
AuctionItemID
as
the identifying
value to the user, then display it in a textbox
that
is
not hidden.

Use the navigation buttons at bottom of form to
move
back
and forth between
existing records (auction items), or to create a
new
auction item (the *
button). No combo box is needed for this type of
design
(it's possible to
use a combo box for selecting the auction item
that
you
want, instead of
using navigation buttons -- you do that by using
an
unbound combo box that
has a Row Source that essentially is the same as
the
form's RecordSource
query, and then you would use code to move the
form
to
the selected item --
but let's leave that for the future when you're
comfortable with what you're
doing now).

In the subform, you would use a combo box to
select a
specific AuctionGift
to be a component of the AuctionItem being
displayed
on
the main form. It
should be bound to AuctionGiftID field in
tblAuctionItemContents. (Note:
this AuctionGiftID field should not be an
autonumber
field here; it should
be a Long Integer field so that its data type
matches
the
autonumber type
that you likely used for AuctionGiftID field in
tblAuctionGifts. Autonumber
field is used ONLY in the table where the field
value
is
the primary key
identifier (not a composite, but the only field
that
is
the primary key) for
that record; in the children table that join to
the
main
table via this key,
the field then is a Long Integer. You may have
this
set
up incorrectly in
your children tables.)

Oh, and yes, what you call "baskets" I am
calling "items". But I am going
one step beyond that. An individual item can be
composed
of a single gift,
and its contents would be that single
AuctionGiftID
(one
record in
tblAuctionItemContents table). Thus, if you have
a "baseball bat" up for
auction and that is a single entity, it should
have a
single record in
tblAuctionItemaContents table that shows that its
content
is a single gift.
Contrast that with a "basket" that is made up of
a
gift
certificate, candy,
and a travel voucher -- this item would have
three
records in
tblAuctionItemContents table. This structure
allows
you
to have a different
Name for an item from what it is named as a gift,
as
well
as any other
details that might differ from what you knew when
it
was
accepted as a gift
versus what you know/do when it becomes an item.
--

Ken Snell
MS ACCESS MVP




"SillySally"
wrote
in
message
news:12cd01c51920$c850bef0$a501280a@phx .gbl...
Thanks for the step-through instructions. I
now
have
frmAuctionItems with sfrmAuctionItemContents.
And
I
previously created frmAuctions so I can enter
each
Auction.

On frmAuctionItems, cboAuction works just
right. I
even
added a field =cboAuction.Column(2) to show the
Auction
name.

You said that in the detail section of the
form,
I
could
put in the controls from tblAuctionItems that I
want
to
see/edit. tblAuctionItems only contains the
CPK
built
from AuctionItemID and AuctionID. I moved all
the
other
fields to tblAuctionGifts. So that just leaves
the
subform.

I chose the Auction name from the combo box in
the
form
header and it worked fine. And then, nothing.
The
subform doesn't have anything in it, so I
thought
I'd
make a combo box associated with
AuctionItemName
from
tblAuctionGifts to pull in information since
tblAuctionGifts contains the details about each
donation.
I created cboItemName with a Row Source:

SELECT tblAuctionGifts.AuctionGiftsID,
tblAuctionGifts.AuctionItemName
FROM tblAuctionGifts;

I bound it to AuctionGiftID in tblAuctionGifts,
but
then
I got the dreaded: "control can't be edited ;
it's
bound
to AutoNumber
field 'tblAuctionGifts.AuctionGiftsID'.

So I'm not doing it right, but I do need (I
think)
to be
able to select gifts to assign to items (I
think
you
are
calling "items" what I'm think are "baskets").
So
in
this form, I think I want to create "baskets"
and
choose
which gifts goes into the basket, or not
create a
basket,
but select the gift to stand alone (like the
cheese).
Are we thinking the same thing?

Thanks for the patience and help, Sal


-----Original Message-----
Sounds as if you're making good progress.

I think I would design your form for items and
their
contents this way.

Create a form (name it sfrmAuctionItemContents)
whose
recordsource is
similar to this:

SELECT tblAuctionItemContents.*,
tblAuctionGifts.*
FROM tblAuctionItemContents INNER JOIN
tblAuctionGifts ON
tblAuctionItemContents.AuctionGiftID
=
tblAuctionGifts.AuctionGiftID;

Set up this form as a Continuous Forms format
(it
will
be the subform for
the next form). Put the desired controls on the
form
that you'll want to
see/edit when adding individual gifts to an
item.

Save and close this form.


Create a form (name it frmAuctionItems) whose
recordsource is similar to
this:

SELECT * FROM tblAuctionItems
WHERE [AuctionID]=[Forms]![frmAuctionItems]!
[cboAuction];

In the form's header, put a combo box (name it
cboAuction) whose Row Source
is similar to this:
SELECT AuctionID, AuctionName, AuctionDate
FROM tblAuctions ORDER BY AuctionDate DESC;

Use the AfterUpdate event of this combo box to
requery
the main form's
RecordSource:
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub

(If you don't know how to create this code to
run
from
an event, let me
know.)

In the detail section of this form (it should
be
set
for
Single View
format), put controls from the tblAuctionItems
table
that you will want to
see/edit.

In the detail section, put a subform control.
Use
sfrmAuctionItemContents as
the Source Object. Set the LinkChildFields and
LinkMasterFields to
AuctionItemID. Now the subform will be used to
enter
the
gifts that are part
of each item.

Save and close this form.


This setup will not let you create a new
auction.
You'll
need a separate
form that will let you create a new record in
tblAuctions table for a new
auction. Then that auction can be seen in the
above
form
and be available
for selection.


--

Ken Snell
MS ACCESS MVP




"SillySally"
wrote in
message
news:058701c518ef$7833acb0$a601280a@p hx.gbl...
Thanks for the clarification- before I
posted I
had
the
donor form/gifts subform correct, but then
got
confused
with items and gifts. So now I'm able to
enter
donors
and
their multiple donated gifts.

tblAuctionGifts
now contains date donated, value,
description,
min.
bid...

so I removed these type of fields from
tblAuctionItems
since tblAuctionItemContents has CPK to Items
and
Gifts.

I really do have an Auction table
tblAuctions
AuctionID
AuctionDate
AuctionName
AuctionLocation

In tblAuctionItems, I removed AuctionGiftsID
as
instructed.

Okay, then! So now I want to be able to list
Items
for
an
Auction as well as create baskets of items
for
an
auction. Once the item is assigned to a
basket,
I
don't
want it to be available as a "lone" item (but
of
course,
I may need to unassign it from a basket to
either
go
to a
different basket or stand alone).

I not really following what to do with
tblAuctionItems
and tblAuctionItemContents or how to create a
form
that
shows the items available and then create
baskets.
Not
much of a visionary, sadly.

Thanks for the guidance, Sal



-----Original Message-----
Answers inline....

--

Ken Snell
MS ACCESS MVP


"SillySally"

wrote
in
message
news:0d3a01c5186d$5fb3adc0
...
Thanks- hard to believe I'm still having
problems.
I have tables:

tblAuctionDonors
AuctionDonorID (PK)
FirstName
LastName

OK good.



tblAuctionGifts (I put the "key" symbol
next
to
both
the
ID fields which I hope gives me a composite
primary
key)
AuctionGiftsID
AuctionDonorID

Although this is what I posted in the
original
structure, I think you can
use just AuctionGiftsID as the primary key.
No
need to
have a composite
primary key now that I've thought about this
a
bit
more.
(And yes, one way
you can create a composite primary key is to
highlight
both fields and the
click the Key icon.) You also could add more
fields to
this table, such as
AuctionGiftDateDonated,
AuctionGiftDescription,
etc.



tblAuctionItems (CPK with the next 2 listed
IDs)
AuctionItemID
AuctionID
DateReceived
ItemValue
ShortDescription
MinimumBid
AuctionGiftsID (I added this myself,
think I
need
it;
or
maybe I should have added AuctionItemID to
AuctionGifts?)

Delete the AuctionGiftsID field from the
above
table.
AuctionGiftsID is used
in tblAuctionItemContents to relate an item
to
the
gift
(s) that compose that
item.




tblAuctionItemContents (CPK with the next 2
listed
IDs)
AuctionItemID
AuctionGiftsID

OK good.


You're missing the tblAuctions table, from
which
you
get
the value for
AuctionID in tblAuctionItems table.



Auction Donor Entry (form): Donor listing
(name,
address).
Record source is tblAuctionDonor.

So the main form is fine. But I'm having
problems
with
the subform. I tried to create a query
like
the
one
you
suggested:
SELECT tblAuctionGifts.AuctionDonorID,
tblAuctionItems.AuctionItemID,
tblAuctionItems.ItemValue,
tblAuctionItems.AuctionItemName,
tblAuctionItems.MinimumBid,
tblAuctionGifts.AuctionGiftsID,
FROM tblAuctionGifts INNER JOIN
tblAuctionItems
ON
tblAuctionGifts.AuctionGiftsID =
tblAuctionItems.AuctionGiftsID;

which seems like it has everything I need:
it
links
together DonorID, ItemID, and GiftsID.
Yet,
it
opens "grayed out" so that I can't enter
anything
into
it. I see that given the table setup, I
need
both
Gifts
and Items together to be able to link to
Donors. I
expect
this subform to allow me to list, in
datasheet
form,
the
items received from each donor.

The query that I'd posted is just an example.
It
will
not work for the donor
form and gift subform set up.

In this case, the subform should use a query
similar
to
this:

SELECT * FROM tblAuctionGifts;

Then you set the LinkMasterFields and
LinkChildFields
properties for the
subform control (the control on the main form
that
holds
the gifts subform)
to link the subform's records to the main
form's
records. In this case, set
both properties to AuctionDonorID. Then, as
you
select
a
donor on the main
form, the subform will show the records for
that
donor,
including the
ability to enter new records.




I'd like to get the first form working
before I
move
to
the other one. Thanks for your help, Sal



snipped for length



-----Original Message-----
What you would want is five tables (PK
=
primary
key;
CPK = composite
primary key)

tblAuctionDonors (the people who
donate
gifts)
AuctionDonorID (PK)
AuctionDonorName

tblAuctionGifts (the gifts you get
for
an
auction)
AuctionGiftID (CPK)
AuctionDonorID (CPK)

tblAuctions (the individual auctions)
AuctionID
AuctionName
AuctionDate

tblAuctionItems (the items that are
available
for
each
auction)
AuctionItemID (CPK)
AuctionID (CPK)
AuctionItemName

tblAuctionItemContents (the gifts that
are
in
each
item)
AuctionItemID (CPK)
AuctionGiftID (CPK)


.



.



.



.



.



.

Thanks! I think I've never had an unbound combo box with
just a RowSource instead. Makes me wonder what I've been
wonder what efforts I've been taking to get around doing
so!
Much better- thanks for you efforts, I appreciate it!
Sal

-----Original Message-----
I've looked at the database.

The "Record cannot contain a Null value" error is because
you had the
cboAuction combo box bound to the AuctionID field in the
main form's
RecordSource. This combo box must be unbound (its
ControlSource should be
empty). What was happening was that, after you selected
an auction item, the
form began to create a new record (when the form opens,
because there is no
selection initially in the AuctionID combo box, it is
starting with a record
ready to be created; because the combo box was bound,
your selection of an
auction began writing into a new record) and then the
focus moved to the
subform before you could enter other data for the main
form.

I've revised the frmAuctionItems form and returned the
database to you by
private email. I removed the AuctionID field from the
cboAuction control's
ControlSource, and I've added code for the form's Load
event to put the
focus into the combo box right away. That will help avoid
the focus going to
the subform when the form is opened.

Your thought about adding a field to tblAuctionGifts that
will allow you to
identify a specific auction for which that gift was
solicited is an ok idea.
Just don't set a relationship from it to tblAuctions
unless you will always
have the auction already established in the database
before you solicit the
gift.

--

Ken Snell
MS ACCESS MVP


"Ken Snell [MVP]"
wrote in message
...
Not sure what may be happening. If you can zip up your
database and email
it to me, I'll take a look as time permits. Be sure to
identify the forms,
tables, etc. that are pertinent.

My email address can be obtained by removing this is
not real from my
reply email address.
--

Ken Snell
MS ACCESS MVP


"SillySally" wrote
in message
...
The man with patience!
Thanks for the ponderings. The default value is still
not working.

I have cboAuction:
SELECT tblAuctions.AuctionID, tblAuctions.AuctionDate,
tblAuctions.AuctionName FROM tblAuctions ORDER BY
AuctionDate DESC;

I set AuctionID = [cboAuction]

I have text box with control source: AuctionItemID and
default value
=Nz(DMax("AuctionItemID","tblAuctionItems","Auctio nID
= "
& [cboAuction]),0)+1

All components of CPK are required in the tables. Hmm.
Thanks, Sal.

-----Original Message-----
Yes, what we need is to define the default value of the
AuctionID control in
your main form (forgot to tell you that); that is, the
textbox that is bound
to the AuctionID field (not the combo box that is in
the
form header).

Set the Default Value for this control to
=[cboAuction]

Now, when you select an auction from the combo box at
top of form, and you
start a new record, the AuctionID field will get the
value from the combo
box, and then the default value for the AuctionItemID
should work fine.

However, if you still see that the AuctionItemID value
is not being filled
in OR if it's getting an incorrect value, then what we
may want to do is to
scrap the use of Default Value for the AuctionID and
AuctionItemID fields,
and instead use a tiny amount of code in the form to
write the correct
values into those fields. But before we go there, let's
see if this works
for you.

Again, a reminder to be sure that you set the Required
property to Yes for
all the fields that together are the CPK in a table so
that those fields
cannot be empty in a record. This again will help to
ensure that the form is
doing what we want it to do.
--

Ken Snell
MS ACCESS MVP


"SillySally" wrote
in
message
. ..
Well, I think your mind's eye is fabulous!
The default value still isn't working. I think I
know
why, but don't understand it.

I looked at my 3 tables that have CPKs and here's
what
I
found (although I'll just tell you about
tblAuctionItems)

tblAuctionItems
AuctionItemID
AuctionID
AuctionItemName

When I look at the table in datasheet view, only 1
field
appears: AuctionItemName. So that AuctionID and
AuctionItemID aren't getting populated. Which leads
me
to believe that maybe I need to define the
relationships
between these tables (correct?). I did and that
didn't
help. Grr. Any ideas why I'm having such table
trauma?
Thanks, Sal

-----Original Message-----
The #Error error message indicates that something is
not
right with the
expression; as I don't have the db right here, I may
be
using a different
name for something than you are using.

Nz is a function that replaces a Null value with what
you use as the second
argument of the Nz function. In the example I gave
you,
a Null from the DMax
function's value will be replaced with a zero. So
what
the expression is
supposed to do is find the maximum value in the
tblAuctionItems for the
field AuctionItemID for the records that have a value
for AuctionID that
matches the one you selected in your combo box. . .

aha -- that is my error, I think. Use this expression
as
the default value
(need to use the combo box value!):
=Nz(DMax
("AuctionItemID", "tblAuctionItems", "AuctionID
= " & [cboAuction]),
0) + 1

Sorry about that... sometimes it gets a little
jumbled
in my "mind's eye"
when trying to "see" the setup there...

I'm not sure I'm following the sequence of events
that
you say you're not
liking, but yes, it may be related to the AfterUpdate
code that you are
trying to use. Go with the DefaultValue expression
above
for the
AuctionItemID control, and delete the AfterUpdate
code
that you have.

As for eliminating gifts from the combo box once
they've
been selected, yes,
this can be done. Unless you'll have thousands and
thousands of entries,
probably the easiest way is to change the Row Source
query for the
AuctionGiftID value in the subform to this:

SELECT AuctionGifts.AuctionGiftID,
AuctionGifts.AuctionGiftName, (other
fields) FROM AuctionGifts LEFT JOIN
AuctionItemContents
ON
AuctionGifts.AuctionGiftID=AuctionItemConten ts.Auctio
nG
if
tID WHERE
AuctionItemContents.AuctionGiftID Is Null;

This will "omit" any AuctionGift records whose ID
value
is already in a
record in the tblAuctionItemContents table.

Glad you're making progress.
--

Ken Snell
MS ACCESS MVP



"SillySally" wrote
in
message
. ..
Thanks for the advice- I added all of the ID fields
to
the form to see what was happening. I tried your
code
as
the default value and got "#Error".
So I tried:
Private Sub cboAuction_AfterUpdate()
AuctionItemID = Nz(DMax
("AuctionItemID", "tblAuctionItems", "AuctionID
= " &
[AuctionID]), 0) + 1
Me.Requery
End Sub

which seemed to work (tell me if I'm crazy), but I
think
it's causing problems as well. Could you explain
what
the Nz(DMax) statement means so I can figure out
how
to
use it as the default value?

I'm not loving this form. When I select an Auction
and
then give an ItemName- say Basket1- (assign an
AuctionItemID) so this is Record 1, and then add a
new
record and select an Auction, the form returns
Basket1
(Record 1) so I then hit the button to get to the
second record. But it's probably what happens
when I
try
things on my own with the after update.

Is there a way to tell the combo box that once a
gift
has
been selected, I don't want to be able to select it
again
unless I delete it from an item?

Thanks for getting me this far! Sal


-----Original Message-----
What I think is happening on your form (when you
select
an AuctionID from
the combo box) is that your current record on the
form
is never getting an
AuctionItemID value (you're not exposing it to the
user,
so the user isn't
entering a value for it; and you're not giving it a
value through your
form's programming), and as you're seeing, no field
in
a
composite primary
key index should be empty/Null. You'll need to
have a
way of giving the
AuctionItemID field a value. If you're not going to
expose it to the user,
then use the DefaultValue of the textbox to which
it's
bound to give it a
value for new records. An expression similar to
this
should work:
=Nz(DMax
("AuctionItemID", "tblAuctionItems", "AuctionID
= " &
[AuctionID]), 0) + 1

This will assign an incrementing number, beginning
at
1,
for each item for a
specific auction ID value, when you start a new
record
for an auction item
belonging to an auction.

In your table setups, you indicate that one of the
fields in each CPK pair
is marked as Required, and the other is not. I
would
mark both fields in
each CPK as Required; that way, the table will
enforce
that you have a value
in each field for each record.

--

Ken Snell
MS ACCESS MVP


"SillySally"
wrote
in
message
l...
Thanks for the CPK lesson- I appreciate it! And
good
news! sfrmAuctionItemsContents is working. The
new
Record Source and combo box work just as I
expected.
Woo
hoo!

Still having problems with frmAuctionItems of
Record
Source:
SELECT *
FROM tblAuctionItems
WHERE (((tblAuctionItems.AuctionID)=[Forms]!
[frmAuctionItems]![cboAuction]));

I have cboAuction:
SELECT tblAuctions.AuctionID,
tblAuctions.AuctionDate,
tblAuctions.AuctionName
FROM tblAuctions
ORDER BY tblAuctions.AuctionDate DESC;

As soon as I select an Auction, I get "Run-time
error '3058': Index or primary key cannot
contain a
Null
value. And when I select debug, it sends me right
to
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub

You indicated that on the main portion, I need
AuctionItemID field of the table bound to a
textbox
on
the form (which makes sense to me).
tblAuctionItems
has
CPK AuctionItemID and AuctionID so that
AuctionItemID
is
NOT an AutoNumber- just a long integer. I added
AuctionItemName to the table so that I don't have
to
expose AuctionItemID field value to the user.
But
I
don't think I'm doing the AuctionItemID field of
the
table bound to a textbox correctly. I tried it
two
ways:
I selected the "textbox" icon, and set the
control
source
to AuctionItemID which doesn't seem any different
than
just dragging AuctionItemID field to the form.
So
then I
tried the "textbox" icon and set it =
[AuctionItemID].
Did I do anything right? I don't want to enter
anything
in this invisible field, rather I want the user
to
enter
into AuctionItemName. But how are AuctionItemID
and
AuctionItemName "linked"?

Then you said that if I see "cannot be Null" I
have 1
0f
3 problems (I'm so lucky it's probably 2 or 3 out
of
3!).
1) control bound to that field on my form so
that I
can
enter a value (I don't want to enter a value,
correct?),
but I think something like this is the problem
2) not including the field in my form
RecordSource-
don't
think this is the problem as I selected * from
tblAuctionItems.
3)LinkMaster/Child fields- don't think that's the
problem
as on sfrmAuctionItemContents, both
LinkChildFields
and
LinkMasterFields are set to AuctionItemID.

Here's my table setup:
tblAuctionDonors
AuctionDonorID AutoNumber

tblAuctionGifts (CPK)
AuctionGiftsID Number (Long Integer); Required =
No
(don't know why)
AuctionDonorID Number (Long Integer); Required =
Yes

tblAuctionItemContents (CPK)
AuctionItemID Number (Long Integer); Required =
No
AuctionGiftsID Number (Long Integer); Required =
Yes

tblAuctionItems (CPK)
AuctionItemID Number (Long Integer); Required =
No
AuctionID Number (Long Integer): Required = Yes

tblAuctions
AcutionID AutoNumber

Thanks for the help and the fantastic
explanations!
Sal

-----Original Message-----
Quick stepback here.

Autonumber fields are often used as primary key
fields.
But because they are
unique in and of themselves, they would be used
as
single primary key field
(not a composite primary key field). Thus, if you
use
an
autonumber in a
table for the primary key, it should be the only
field
that is in the
primary key. (Autonumber fields are Long Integer
data
type.)

When a child table is using a field as a foreign
key
(meaning that it is
meant to be a joining field between the child and
the
parent tables), it
must have the same data type as the key in the
parent
table. Thus, if the
parent table contains MyID (an autonumber) as a
primary
key, and your child
table uses MyID for joining the tables, MyID in
the
child table must by Long
Integer.

Composite primary keys are nothing more than a
combination of fields where
the combination of the values from those fields
is
a
unique combination
(only one record in the table has that
combination
of
values). Individually,
each field that is in the composite primary key
may
have
the same value in
more than one record. For example, suppose my
child
table contains two
fields: CarType and CarColor. Suppose that they
are
a
composite primary
key. That means that my table could contain these
records:
CarType CarColor
------- -------
Cadillac Red
Cadillac Yellow
Cadillac White
Pontiac Black
Pontiac Red
Impala While

Each combination is unique, even though some
values
repeat in the records.

OK - now to the current issues.

When a field is part of a composite primary key,
that
field must have a
non-Null value in order to save the record. While
setting the Default Value
of the field to 0 (when it's a Long Integer data
type)
can avoid this error
occurring, it also masks the possibility that
your
form
is not properly
writing a value to that field when you create a
new
record. Thus, you may
think you're saving data with the correct value
in
a
joining field, when in
reality they're all getting a value of 0, which
won't
join back to the
parent table (and then you'll wonder why you
can't "find" your data). So,
until you're sure that your form is working
correctly,
I
would leave the
Default Value as Null (or empty) for now.

The fact that you see the "cannot be Null" error
suggests that you don't
have a control bound to that field on your form
so
that
you can enter a
value for that field; or it suggests that you're
not
including the field in
your form (or subform) RecordSource; or it
suggests
that
your
LinkMasterFields and LinkChildFields properties
are
not
set correctly for
the subform so that the form will write the value
into
that field for you.

This code:
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub

should not create any "duplicate value" problems
*unless* you've bound the
cboAuction control to a field in the form's
RecordSource. This control must
be unbound (the Control Source must be empty)
because
all you're using it
for is to navigate the form to the desired
records.
If
you have a field name
in its ControlSource, then you're changing the
value
of
AuctionID in some
records in the tblAuctionItems table from what it
was
to
what you select --
and this will cause duplicate value problems (not
to
mention potentially
mess up your data).

I made an error in what I told you the
RecordSource
for
the subform should
be (it does happen g ).

Change it to this:
SELECT tblAuctionItemContents.*
FROM tblAuctionItemContents;

Your combo box that will allow you to select the
AuctionGiftID should have a
Row Source query similar to this:
SELECT AuctionGiftID, AuctionGiftName, (other
fields) FROM AuctionGifts;
This combo box should be bound to AuctionGiftID
field
in
the subform's
RecordSource. The bound column of this combo box
should
be 1.

Now you should be able to select a gift so that
it
can
be added to the
item's contents.

--

Ken Snell
MS ACCESS MVP


"SillySally"
wrote
in
message
news:031801c5194e$9b3718f0$a401280a@phx. gbl...
Sorry- I wasn't clear. I was talking about a
combo
box on
the subform (I understand about navigating on
the
main
form using *). These composite primary keys
are
very
confusing to me (but I did change all of them
to
long
integer). I'm having a couple of problems-
first
with
the
tables. I was getting error messages that IDs
couldn't be
null, so I set the default values to 0 and
then I
got
duplicate value violations.
Now that my CPK are long integers, should I
have
a
default
value? And
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub
is giving me grief (duplicate values in the
index
which
must be related to my table question).

2nd trauma
I created fmrAuctionItems with
sfrmAcutionItemContents. I
was creating a combo box on the subform, but
now
somehow,
I'm unable to enter anything into the subform
(it's
all
grayed out). So I rebuilt the form and subform
again-
same thing. Which is frustrating because I
finally
see
where I'm going. I think I even built the
correct
combo
box for the subform before grayness took over.
Any
ideas
why can't I enter anything in the subform?

frmAuctionItems RecordSource:
SELECT *
FROM tblAuctionItems
WHERE (((tblAuctionItems.AuctionID)=[Forms]!
[frmAuctionItems]![cboAuction]));

sfrmAuctionItemContents RecordSource:
SELECT tblAuctionItemContents.*,
tblAuctionGifts.*
FROM tblAuctionItemContents INNER JOIN
tblAuctionGifts
ON
tblAuctionItemContents.AuctionGiftsID =
tblAuctionGifts.AuctionGiftsID;

Now, if I delete tblAcutionItemsContent from
the
sfrm
and
just leave tblAuctionGits, the sql is fine. So
my
join
must be the problem? Although it's probably
related
to my
table woes.

Sorry to be such a pain! Sal



-----Original Message-----
You're still going in the right direction. What
you
need
to stop and think
about now is -- what am I wanting to do on this
form?
The
answer will then
tell you what you need to do.

The answer, from my perspective, is that you
want
to
select or create an
auction item on the form, and then to assign
auction
gifts to that item.
Right?

So, on the main portion of frmAuctionItems, you
need
the
AuctionItemID field
of the table bound to a textbox on the form
(likely,
you're using an
autonumber field for this? so the textbox can
be
hidden/invisible), and you
want a textbox to enter the name of a new
auction
item.
Unless you plan to
expose the AuctionItemID field's value to the
user
(which
isn't necessary),
then you should have another field in
tblAuctionItems
that allows you to
give the record an identifying name. This is
then
what
you display on the
form. Otherwise, if you want to use the
AuctionItemID
as
the identifying
value to the user, then display it in a textbox
that
is
not hidden.

Use the navigation buttons at bottom of form to
move
back
and forth between
existing records (auction items), or to create
a
new
auction item (the *
button). No combo box is needed for this type
of
design
(it's possible to
use a combo box for selecting the auction item
that
you
want, instead of
using navigation buttons -- you do that by
using
an
unbound combo box that
has a Row Source that essentially is the same
as
the
form's RecordSource
query, and then you would use code to move the
form
to
the selected item --
but let's leave that for the future when you're
comfortable with what you're
doing now).

In the subform, you would use a combo box to
select a
specific AuctionGift
to be a component of the AuctionItem being
displayed
on
the main form. It
should be bound to AuctionGiftID field in
tblAuctionItemContents. (Note:
this AuctionGiftID field should not be an
autonumber
field here; it should
be a Long Integer field so that its data type
matches
the
autonumber type
that you likely used for AuctionGiftID field in
tblAuctionGifts. Autonumber
field is used ONLY in the table where the field
value
is
the primary key
identifier (not a composite, but the only field
that
is
the primary key) for
that record; in the children table that join to
the
main
table via this key,
the field then is a Long Integer. You may have
this
set
up incorrectly in
your children tables.)

Oh, and yes, what you call "baskets" I am
calling "items". But I am going
one step beyond that. An individual item can be
composed
of a single gift,
and its contents would be that single
AuctionGiftID
(one
record in
tblAuctionItemContents table). Thus, if you
have
a "baseball bat" up for
auction and that is a single entity, it should
have a
single record in
tblAuctionItemaContents table that shows that
its
content
is a single gift.
Contrast that with a "basket" that is made up
of
a
gift
certificate, candy,
and a travel voucher -- this item would have
three
records in
tblAuctionItemContents table. This structure
allows
you
to have a different
Name for an item from what it is named as a
gift,
as
well
as any other
details that might differ from what you knew
when
it
was
accepted as a gift
versus what you know/do when it becomes an
item.
--

Ken Snell
MS ACCESS MVP




"SillySally"
wrote
in
message
news:12cd01c51920$c850bef0$a501280a@ph x.gbl...
Thanks for the step-through instructions. I
now
have
frmAuctionItems with sfrmAuctionItemContents.
And
I
previously created frmAuctions so I can enter
each
Auction.

On frmAuctionItems, cboAuction works just
right. I
even
added a field =cboAuction.Column(2) to show
the
Auction
name.

You said that in the detail section of the
form,
I
could
put in the controls from tblAuctionItems
that I
want
to
see/edit. tblAuctionItems only contains the
CPK
built
from AuctionItemID and AuctionID. I moved
all
the
other
fields to tblAuctionGifts. So that just
leaves
the
subform.

I chose the Auction name from the combo box
in
the
form
header and it worked fine. And then,
nothing.
The
subform doesn't have anything in it, so I
thought
I'd
make a combo box associated with
AuctionItemName
from
tblAuctionGifts to pull in information since
tblAuctionGifts contains the details about
each
donation.
I created cboItemName with a Row Source:

SELECT tblAuctionGifts.AuctionGiftsID,
tblAuctionGifts.AuctionItemName
FROM tblAuctionGifts;

I bound it to AuctionGiftID in
tblAuctionGifts,
but
then
I got the dreaded: "control can't be edited ;
it's
bound
to AutoNumber
field 'tblAuctionGifts.AuctionGiftsID'.

So I'm not doing it right, but I do need (I
think)
to be
able to select gifts to assign to items (I
think
you
are
calling "items" what I'm think
are "baskets").
So
in
this form, I think I want to create "baskets"
and
choose
which gifts goes into the basket, or not
create a
basket,
but select the gift to stand alone (like the
cheese).
Are we thinking the same thing?

Thanks for the patience and help, Sal


-----Original Message-----
Sounds as if you're making good progress.

I think I would design your form for items
and
their
contents this way.

Create a form (name it
sfrmAuctionItemContents)
whose
recordsource is
similar to this:

SELECT tblAuctionItemContents.*,
tblAuctionGifts.*
FROM tblAuctionItemContents INNER JOIN
tblAuctionGifts ON
tblAuctionItemContents.AuctionGiftID
=
tblAuctionGifts.AuctionGiftID;

Set up this form as a Continuous Forms format
(it
will
be the subform for
the next form). Put the desired controls on
the
form
that you'll want to
see/edit when adding individual gifts to an
item.

Save and close this form.


Create a form (name it frmAuctionItems) whose
recordsource is similar to
this:

SELECT * FROM tblAuctionItems
WHERE [AuctionID]=[Forms]![frmAuctionItems]!
[cboAuction];

In the form's header, put a combo box (name
it
cboAuction) whose Row Source
is similar to this:
SELECT AuctionID, AuctionName,
AuctionDate
FROM tblAuctions ORDER BY AuctionDate
DESC;

Use the AfterUpdate event of this combo box
to
requery
the main form's
RecordSource:
Private Sub cboAuction_AfterUpdate()
Me.Requery
End Sub

(If you don't know how to create this code to
run
from
an event, let me
know.)

In the detail section of this form (it should
be
set
for
Single View
format), put controls from the
tblAuctionItems
table
that you will want to
see/edit.

In the detail section, put a subform control.
Use
sfrmAuctionItemContents as
the Source Object. Set the LinkChildFields
and
LinkMasterFields to
AuctionItemID. Now the subform will be used
to
enter
the
gifts that are part
of each item.

Save and close this form.


This setup will not let you create a new
auction.
You'll
need a separate
form that will let you create a new record in
tblAuctions table for a new
auction. Then that auction can be seen in the
above
form
and be available
for selection.


--

Ken Snell
MS ACCESS MVP




"SillySally"

wrote in
message
news:058701c518ef$7833acb0
...
Thanks for the clarification- before I
posted I
had
the
donor form/gifts subform correct, but then
got
confused
with items and gifts. So now I'm able to
enter
donors
and
their multiple donated gifts.

tblAuctionGifts
now contains date donated, value,
description,
min.
bid...

so I removed these type of fields from
tblAuctionItems
since tblAuctionItemContents has CPK to
Items
and
Gifts.

I really do have an Auction table
tblAuctions
AuctionID
AuctionDate
AuctionName
AuctionLocation

In tblAuctionItems, I removed
AuctionGiftsID
as
instructed.

Okay, then! So now I want to be able to
list
Items
for
an
Auction as well as create baskets of items
for
an
auction. Once the item is assigned to a
basket,
I
don't
want it to be available as a "lone" item
(but
of
course,
I may need to unassign it from a basket to
either
go
to a
different basket or stand alone).

I not really following what to do with
tblAuctionItems
and tblAuctionItemContents or how to
create a
form
that
shows the items available and then create
baskets.
Not
much of a visionary, sadly.

Thanks for the guidance, Sal



-----Original Message-----
Answers inline....

--

Ken Snell
MS ACCESS MVP


"SillySally"

wrote
in
message
news:0d3a01c5186d$5fb3adc0
...
Thanks- hard to believe I'm still having
problems.
I have tables:

tblAuctionDonors
AuctionDonorID (PK)
FirstName
LastName

OK good.



tblAuctionGifts (I put the "key" symbol
next
to
both
the
ID fields which I hope gives me a
composite
primary
key)
AuctionGiftsID
AuctionDonorID

Although this is what I posted in the
original
structure, I think you can
use just AuctionGiftsID as the primary key.
No
need to
have a composite
primary key now that I've thought about
this
a
bit
more.
(And yes, one way
you can create a composite primary key is
to
highlight
both fields and the
click the Key icon.) You also could add
more
fields to
this table, such as
AuctionGiftDateDonated,
AuctionGiftDescription,
etc.



tblAuctionItems (CPK with the next 2
listed
IDs)
AuctionItemID
AuctionID
DateReceived
ItemValue
ShortDescription
MinimumBid
AuctionGiftsID (I added this myself,
think I
need
it;
or
maybe I should have added AuctionItemID
to
AuctionGifts?)

Delete the AuctionGiftsID field from the
above
table.
AuctionGiftsID is used
in tblAuctionItemContents to relate an item
to
the
gift
(s) that compose that
item.




tblAuctionItemContents (CPK with the
next 2
listed
IDs)
AuctionItemID
AuctionGiftsID

OK good.


You're missing the tblAuctions table, from
which
you
get
the value for
AuctionID in tblAuctionItems table.



Auction Donor Entry (form): Donor listing
(name,
address).
Record source is tblAuctionDonor.

So the main form is fine. But I'm having
problems
with
the subform. I tried to create a query
like
the
one
you
suggested:
SELECT tblAuctionGifts.AuctionDonorID,
tblAuctionItems.AuctionItemID,
tblAuctionItems.ItemValue,
tblAuctionItems.AuctionItemName,
tblAuctionItems.MinimumBid,
tblAuctionGifts.AuctionGiftsID,
FROM tblAuctionGifts INNER JOIN
tblAuctionItems
ON
tblAuctionGifts.AuctionGiftsID =
tblAuctionItems.AuctionGiftsID;

which seems like it has everything I
need:
it
links
together DonorID, ItemID, and GiftsID.
Yet,
it
opens "grayed out" so that I can't enter
anything
into
it. I see that given the table setup, I
need
both
Gifts
and Items together to be able to link to
Donors. I
expect
this subform to allow me to list, in
datasheet
form,
the
items received from each donor.

The query that I'd posted is just an
example.
It
will
not work for the donor
form and gift subform set up.

In this case, the subform should use a
query
similar
to
this:

SELECT * FROM tblAuctionGifts;

Then you set the LinkMasterFields and
LinkChildFields
properties for the
subform control (the control on the main
form
that
holds
the gifts subform)
to link the subform's records to the main
form's
records. In this case, set
both properties to AuctionDonorID. Then, as
you
select
a
donor on the main
form, the subform will show the records for
that
donor,
including the
ability to enter new records.




I'd like to get the first form working
before I
move
to
the other one. Thanks for your help, Sal



snipped for length



-----Original Message-----
What you would want is five tables
(PK
=
primary
key;
CPK = composite
primary key)

tblAuctionDonors (the people who
donate
gifts)
AuctionDonorID (PK)
AuctionDonorName

tblAuctionGifts (the gifts you get
for
an
auction)
AuctionGiftID (CPK)
AuctionDonorID (CPK)

tblAuctions (the individual
auctions)
AuctionID
AuctionName
AuctionDate

tblAuctionItems (the items that are
available
for
each
auction)
AuctionItemID (CPK)
AuctionID (CPK)
AuctionItemName

tblAuctionItemContents (the gifts
that
are
in
each
item)
AuctionItemID (CPK)
AuctionGiftID (CPK)


.



.



.



.



.



.





.

"Ken Snell [MVP]" wrote:

What you would want is five tables (PK = primary key; CPK = composite
primary key)

tblAuctionDonors (the people who donate gifts)
AuctionDonorID (PK)
AuctionDonorName

tblAuctionGifts (the gifts you get for an auction)
AuctionGiftID (CPK)
AuctionDonorID (CPK)

tblAuctions (the individual auctions)
AuctionID
AuctionName
AuctionDate

tblAuctionItems (the items that are available for each auction)
AuctionItemID (CPK)
AuctionID (CPK)
AuctionItemName

tblAuctionItemContents (the gifts that are in each item)
AuctionItemID (CPK)
AuctionGiftID (CPK)

--

Ken Snell
MS ACCESS MVP


"SillySally" wrote in message
...
Hello. I just designed a new section of my db. We receive
donated auction items and want to track the AuctionGiver
and AuctionGift. We also want to create auction sheets
(that give the price, minimum bid, room for people to make
bids). The auction sheet has the description of the
auction item. All went well.

Until I realized that we often combine auction items to
create gift baskets (things that go well together like a
hotel stay and airline tickets, or small items that are
grouped together for greater value). We need to keep
these items separate in that we need to be able to track
which AuctionGiver gave which AuctionGift. But I'd also
like to create a "Basket" section where we could track
AuctionGifts lumped together.

I'm a little mystified how to do the "basket" thing. I'd
appreciate any suggestions. Thank you for your
consideration, Sal

"SillySally" wrote:

Hello. I just designed a new section of my db. We receive
donated auction items and want to track the AuctionGiver
and AuctionGift. We also want to create auction sheets
(that give the price, minimum bid, room for people to make
bids). The auction sheet has the description of the
auction item. All went well.

Until I realized that we often combine auction items to
create gift baskets (things that go well together like a
hotel stay and airline tickets, or small items that are
grouped together for greater value). We need to keep
these items separate in that we need to be able to track
which AuctionGiver gave which AuctionGift. But I'd also
like to create a "Basket" section where we could track
AuctionGifts lumped together.

I'm a little mystified how to do the "basket" thing. I'd
appreciate any suggestions. Thank you for your
consideration, Sal

"SillySally" wrote:

Ken, thanks for the clarification- I appreciate it and was
able to build the tables, but of course now I'm struggling
with the forms.

I created an AuctionDonor form that contains pertinent
donor information from the tblAuctionDonors (name,
address...). I originally had a subform that showed all
of the things that the donor had given including the
value, minimum bid, description. But now I'm confused
because the tblAuctionGifts that contains the gifts we get
for an auction doesn't seem to contain any of that
information, rather tblAuctionItems does.

I still must track the description of each item donated
and link it to a donor (so I can thank them properly for
their gifts and what-not). So I'm not sure what the Donor
form / items subform would look like.

I like the idea of tblAuctionItems to show items available
for each auction. Question: tblAuctionItemContents- can I
create a Basket1 for the given auction name (so that 5
years from now, I don't have to start at Basket 2,000) and
then tell the basket which auction items it holds?

I have the AuctionName form done (the easy one you know!).

I believe the third form would show both individual
auction items and basket that are composed of individual
items combined together. What would that look like?

Thanks for your patience, Sal

-----Original Message-----
Comments/answers inline...

--

Ken Snell
MS ACCESS MVP

"SillySally" wrote in
message
...
Thnaks for the help- I'll give it a try.
I'm a little confused about tblAuctionGifts,
tblAuctionItems and tblAuctionItemContents. Before I
had
AuctionID as a CPK in tblAuctionGifts.

tblAuctionGifts would contain separately an airline
ticket
and then a hotel room (from 2 different AuctionDonors).

Yes, tblAuctionGifts would contain one record for each
gift that is donated
for an auction. So in your example, you would have two
records.


tblAuctionItems would essentially link the AuctionGift
with those available for the correct auction (?) So, for
Gala 2005, both the ticket and the room are available.

Yes, this table contains all the "items" (whether
individual gifts that were
donated or your combined "baskets" that are composed of
many individual
gifts). So these are the items on which people are
actually bidding. And
this table allows you to store all auctions' items in one
table; each item
is associated to a specific auction.


Then tblAuctionItemContents links the AuctionGifts to
the
AuctionItems, so Basket1 could contain GiftA (ticket)
and
GiftB (room). Am I sort of getting it?

Yes, you are understanding correctly.


Also, only
tblAuctionDonors has a primary key- why is that?

All of the example tables that I posted contain a primary
key. CPK means
that the primary key consists of more than one field.
It's "composed" of
more than one field; thus, it's called a composite
primary key.
Alternatively, you can add a separate PK field, and then
you'd create a
unique index on the fields that I labeled with the CPK
designation.




I'll build the tables and will no doubt have form design
questions if you don't mind. I appreciate your help.

No problem. Good luck.


Thanks, Sal

-----Original Message-----
What you would want is five tables (PK = primary key;
CPK = composite
primary key)

tblAuctionDonors (the people who donate gifts)
AuctionDonorID (PK)
AuctionDonorName

tblAuctionGifts (the gifts you get for an auction)
AuctionGiftID (CPK)
AuctionDonorID (CPK)

tblAuctions (the individual auctions)
AuctionID
AuctionName
AuctionDate

tblAuctionItems (the items that are available for each
auction)
AuctionItemID (CPK)
AuctionID (CPK)
AuctionItemName

tblAuctionItemContents (the gifts that are in each
item)
AuctionItemID (CPK)
AuctionGiftID (CPK)

--

Ken Snell
MS ACCESS MVP


"SillySally" wrote in
message
.. .
Hello. I just designed a new section of my db. We
receive
donated auction items and want to track the
AuctionGiver
and AuctionGift. We also want to create auction
sheets
(that give the price, minimum bid, room for people to
make
bids). The auction sheet has the description of the
auction item. All went well.

Until I realized that we often combine auction items
to
create gift baskets (things that go well together
like a
hotel stay and airline tickets, or small items that
are
grouped together for greater value). We need to keep
these items separate in that we need to be able to
track
which AuctionGiver gave which AuctionGift. But I'd
also
like to create a "Basket" section where we could track
AuctionGifts lumped together.

I'm a little mystified how to do the "basket" thing.
I'd
appreciate any suggestions. Thank you for your
consideration, Sal


.



.

"Ken Snell [MVP]" wrote:

Comments/answers inline...

--

Ken Snell
MS ACCESS MVP

"SillySally" wrote in message
...
Thnaks for the help- I'll give it a try.
I'm a little confused about tblAuctionGifts,
tblAuctionItems and tblAuctionItemContents. Before I had
AuctionID as a CPK in tblAuctionGifts.

tblAuctionGifts would contain separately an airline ticket
and then a hotel room (from 2 different AuctionDonors).

Yes, tblAuctionGifts would contain one record for each gift that is donated
for an auction. So in your example, you would have two records.


tblAuctionItems would essentially link the AuctionGift
with those available for the correct auction (?) So, for
Gala 2005, both the ticket and the room are available.

Yes, this table contains all the "items" (whether individual gifts that were
donated or your combined "baskets" that are composed of many individual
gifts). So these are the items on which people are actually bidding. And
this table allows you to store all auctions' items in one table; each item
is associated to a specific auction.


Then tblAuctionItemContents links the AuctionGifts to the
AuctionItems, so Basket1 could contain GiftA (ticket) and
GiftB (room). Am I sort of getting it?

Yes, you are understanding correctly.


Also, only
tblAuctionDonors has a primary key- why is that?

All of the example tables that I posted contain a primary key. CPK means
that the primary key consists of more than one field. It's "composed" of
more than one field; thus, it's called a composite primary key.
Alternatively, you can add a separate PK field, and then you'd create a
unique index on the fields that I labeled with the CPK designation.




I'll build the tables and will no doubt have form design
questions if you don't mind. I appreciate your help.

No problem. Good luck.


Thanks, Sal

-----Original Message-----
What you would want is five tables (PK = primary key;
CPK = composite
primary key)

tblAuctionDonors (the people who donate gifts)
AuctionDonorID (PK)
AuctionDonorName

tblAuctionGifts (the gifts you get for an auction)
AuctionGiftID (CPK)
AuctionDonorID (CPK)

tblAuctions (the individual auctions)
AuctionID
AuctionName
AuctionDate

tblAuctionItems (the items that are available for each
auction)
AuctionItemID (CPK)
AuctionID (CPK)
AuctionItemName

tblAuctionItemContents (the gifts that are in each item)
AuctionItemID (CPK)
AuctionGiftID (CPK)

--

Ken Snell
MS ACCESS MVP


"SillySally" wrote in
message
...
Hello. I just designed a new section of my db. We
receive
donated auction items and want to track the AuctionGiver
and AuctionGift. We also want to create auction sheets
(that give the price, minimum bid, room for people to
make
bids). The auction sheet has the description of the
auction item. All went well.

Until I realized that we often combine auction items to
create gift baskets (things that go well together like a
hotel stay and airline tickets, or small items that are
grouped together for greater value). We need to keep
these items separate in that we need to be able to track
which AuctionGiver gave which AuctionGift. But I'd also
like to create a "Basket" section where we could track
AuctionGifts lumped together.

I'm a little mystified how to do the "basket" thing.
I'd
appreciate any suggestions. Thank you for your
consideration, Sal


.