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-07-21 : 16:51:44
|
| Hi,I have a form and subform in an Access Database Project (Access 2000, with a SQL Server 2000 database). The main form is a typical "order form", and the subform shows the contents of the order. The datasource for the subform is a View rather than a single table, since I need to display data from more than one table. The SQL statement that generates the View is:SELECT [ORDER].OrderID, [ORDER-DETAIL].EbayNum, AUCTION.Winner, LOT.LotNum, LOT.Description, LOT.WarehouseLocFROM dbo.AUCTION INNER JOIN dbo.[ORDER-DETAIL] ON dbo.AUCTION.EbayNum = dbo.[ORDER-DETAIL].EbayNum INNER JOIN dbo.[ORDER] ON dbo.[ORDER-DETAIL].OrderID = dbo.[ORDER].OrderID INNER JOIN dbo.LOT ON dbo.AUCTION.LotNum = dbo.LOT.LotNumIn the subform, users see the auction items that are associated with the order that is being viewed in the main form. I would like for users to be able to add auctions to an order by entering an EbayNum directly into the subform datasheet, and having the remaining data (LotNum, Description, Location) fill in automatically. I also expect that, when this happens, a new record is being created in the ORDER-DETAIL table (tblORDER-DETAIL, OrderID, EbayNum) using the EbayNum supplied by the user and the OrderID of the current order on the main form. Can these two things be done when the datasource for the subform is a View rather than a single table?Note: The View was created in SQL, not in Access.Thanks in advance! |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-21 : 19:35:46
|
| The easiest and most reliable method is to have: - A subform that is not editable - An item entry dialog box, in which case you can have complete control over how the information is entered.If you really want a subform that is editable, you will either need to invest in a third-party grid control or use temporary tables as I have previously suggested.Tim |
 |
|
|
whill96205
Starting Member
46 Posts |
Posted - 2004-07-22 : 12:53:05
|
| - A subform that is not editableWhich property or properties govern this?- An item entry dialog box, in which case you can have complete control over how the information is entered.Do you mean a separate control? Please explain.Also, does the fact that my subform is based on a View have any bearing on how I do any of this?Thanks,Whill |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-22 : 18:32:56
|
| A non-editable subform is a subform that is read-only. There are a number of ways of setting this:- using joined query as the source (and not specifying unique table)- setting any/all of the following form parameters: AllowAdd, AllowEdit, AllowDelete- Setting the form datasource type as Snapshot (only applicable to ADPs, as I recall)By dialog box I mean a completely separate form that is opened from the main form. It would most likely be opened as a modal form (look it up).Without wanting to sound like a smartarse, I think you should probably invest in a good Access book (Wrox ones are generally good). The app you're designing is a bit too complex for a newbie, and I fear that you'll never get a stable application unless you know what you're doing from the start. I have inherited a number of Access apps from programmers (and I use the word loosely) who clearly had no idea what they were doing.Tim |
|
|
|
whill96205
Starting Member
46 Posts |
Posted - 2004-07-28 : 10:54:40
|
| LOL Thanks... I think. :)You don't sound like a smartass. I appreciate the guidance.By the way, I did get the subform to be updateable, as far as creating a new record in the Order-Details table. My next task is to get a combobox on the EbayNum column so as to limit the list of "add-able" EbayNum values to only those that do not already have a winner associated with them.--Whill |
|
|
|
|
|
|
|
|
View-based subform ?'s