S
SillySally
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","AuctionID = "
& [cboAuction]),0)+1
All components of CPK are required in the tables. Hmm.
Thanks, Sal.
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","AuctionID = "
& [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>
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
not-----Original Message-----
The #Error error message indicates that something is
right with theasexpression; 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
the default valueabove(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
for thethey'veAuctionItemID control, and delete the AfterUpdate code that you have.
As for eliminating gifts from the combo box once
been selected, yes,AuctionGifts.AuctionGiftName, (otherthis 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,iffields) FROM AuctionGifts LEFT JOIN AuctionItemContents ON
AuctionGifts.AuctionGiftID=AuctionItemContents.AuctionG
tID WHEREinAuctionItemContents.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" <[email protected]> wrote
messagecodeThanks for the advice- I added all of the ID fields to
the form to see what was happening. I tried your
asinthe 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
aarecomposite 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>
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
avaluecomposite 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
ofform,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>
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>
in
message
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
Iforcould
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" <[email protected]>
wrote in
message
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
anauction. 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"nextwrote
in
message
[email protected]...
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
toYet,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.
itforopens "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
anauction)
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)
.
.
.
.
.
.