Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
whill96205
Starting Member
46 Posts |
 Posted - 2004-06-30 : 17:23:43
|
| I'm not sure if this is an Access question or a SQL question... I apologize for being long-winded below; I just wanted to be sure to put everything into context.Okay -- think of the classic ORDER-PART example: a many-to-many relationship. Normally, you could set up an ORDER-DETAILS table to facilitate the relationship between them. Then, on an Access form for creating/modifying Orders, you could show the relevant data from the ORDER-DETAILS table in a subform, and users could add/delete Parts from the Order. Within the subform, the PartID field could be a combo box, wherein you select a Part from a dropdown list and the rest of the fields in the subform automatically populate with description, price, etc.I thought I could utilize a similar scenario for my situation: matching up QUOTES and LOTS in a many-to-many relationship. A QUOTE can reference multiple LOTS; a LOT can be associated with multiple QUOTES. (In this scenario, the QUOTE becomes an actual Order when an auction is won and shipping information gets added to the record.) However, unlike a PART which comes from a predefined list, a LOT (in my case) is an auction item, and the list is ever-changing. LOT data is stored in its own table, which will be constantly updated. It would sure be convenient if a user could select a LOT from a combo box dropdown list on our QUOTE form, but that would be an enormously long list, filtered with a query to boot (so that the user can't select LOTs that are have already been sold and shipped). Is there any practical way to do this?--Whill |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-30 : 19:07:33
|
| You can dynamically control the contents of a drop-down box quite easily.On opening a new QUOTE form, the LOT drop down can be filled with a query like this:SELECT * FROM LOT WHERE LOT NOT IN (SELECT LotID FROM QUOTE)Or something similar. |
 |
|
|
whill96205
Starting Member
46 Posts |
Posted - 2004-07-01 : 14:16:01
|
Okay.Now how about this...We will be storing LOT data and AUCTION data in separate tables, with LotID as a foreign key in the AUCTION table. For business reasons, if a LOT doesn't sell in its first AUCTION, then it can be re-listed, but we will still need to retain the data on the first AUCTION.I'd like to create an Acess form where the user can enter both LOT and AUCTION data side-by-side. If it is a new LOT, then the data entered on this screen should be related (that is, the AUCTION data will automatically be stored in a record with that LOT's LotID as a foreign key). Later, if the LOT gets placed in a new AUCTION, the user needs to be able to view the LOT/AUCTION data on this Access form, and perhaps click a button to "blank out" the AUCTION-related fields but leave the LOT data alone -- that is, initiate a new record in the AUCTION table that will also be related to this same LOT.Is it possible, from an Access form, to initiate a new record in one table but not another, if BOTH tables are displaying data on the form?Yes, another long-winded, newb-type situation for ya!  --Whill |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-07-01 : 17:55:41
|
| This level of complexity may require an unbound form. But at the very least, I'd say you need to create your own command buttons and logic to create this type of situation. Perhaps you could insert a blank auction record, link the Lot info and then tell the form to go to that record so the user could insert the other auction data.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
|
|
|
|
|
|
|
|
Subform - Combo Box - Query