If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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) . . . . . . |
#22
|
|||
|
|||
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) . . . . . . |
#23
|
|||
|
|||
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) . . . . . . |
#24
|
|||
|
|||
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) . . . . . . . |
#25
|
|||
|
|||
"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 |
#26
|
|||
|
|||
"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 |
#27
|
|||
|
|||
"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 . . |
#28
|
|||
|
|||
"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 . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Who owns the copyright on graphic design layouts prepared in MS Wo | Karen | General Discussion | 4 | February 1st, 2005 07:01 AM |
How can I customise an entire design set | David | Publisher | 2 | November 12th, 2004 09:43 AM |
design master problem | J. Vermeer | General Discussion | 0 | September 8th, 2004 03:23 PM |
Action queries changing when reopened in design view | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 12:34 AM |
opening a document so it is NOT in design mode | Brad Pears | New Users | 1 | May 3rd, 2004 09:13 PM |