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.
-----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>
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.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>
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>
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" <
[email protected]>
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" <
[email protected]>
wrote
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
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"
