• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

Add records in a query-based datasheet form

In an Access project (Access front-end, SQL Server back-end)...

I have created a datasheet form based on a query that queries two tables.
This datasheet form is then used as a subform on another form.
I have designated one of the two tables as the Unique Table so that users can edit data within the subform.

Is there a way to allow users to also create new Unique Table records via the subform?

The subform's properties are set to allow additions, deletions, and edits, and the subform type is set to Updateable Snapshot.  Thought I'd done this before, but just can't duplicate it...

Thanks,
Galisteo8
0
Galisteo8
Asked:
Galisteo8
  • 3
  • 2
1 Solution
 
Brian CroweCommented:
It sounds like you are trying to create what SQL refers to as an updatable view.  This is only possible under certain conditions.  If you look up "CREATE VIEW" in books online and find "updatable" you will find the restrictions.


... it is updatable only if the following conditions are satisfied:

The select_statement has no aggregate functions in the select list and does not contain the TOP, GROUP BY, UNION (unless the view is a partitioned view as described later in this topic), or DISTINCT clauses. Aggregate functions can be used in a subquery in the FROM clause as long as the values returned by the functions are not modified. For more information, see Aggregate Functions.

select_statement has no derived columns in the select list. Derived columns are result set columns formed by anything other than a simple column expression, such as using functions or addition or subtraction operators.

The FROM clause in the select_statement references at least one table. select_statement must have more than non-tabular expressions, which are expressions not derived from a table. For example, this view is not updatable:
CREATE VIEW NoTable AS
SELECT GETDATE() AS CurrentDate,
       @@LANGUAGE AS CurrentLanguage,
       CURRENT_USER AS CurrentUser
0
 
Galisteo8Author Commented:
I need to re-phrase the way I have this subform set up...

Specifically, I have a query (or view) called qryBrandLots, and the datasheet subform's RecordSource is
SELECT * FROM qryBrandLots ORDER BY LotNum

Here is what qryBrandLots looks like:
----------------------------------------------------------------------
SELECT
LOT.Client,
LOT.LotNum,

LEFT(dbo.lot.lotnum,
    charindex('-', dbo.lot.lotnum + '-') - 1) AS Shipment,

LOT.Description,
LOT.ClientPrice,
LOT.LotPaid,

    Payback = CASE WHEN dbo.LOT.Payback = 0 THEN NULL
    ELSE dbo.LOT.Payback END,

    CheckDate = CASE WHEN dbo.AUCTION.RelistedYN = 1 THEN NULL
     ELSE dbo.LOT.RinnerCkDate END,

    CheckNum = CASE WHEN dbo.AUCTION.RelistedYN = 1 THEN NULL
     ELSE dbo.LOT.RinnerCkNum END,

AUCTION.WinBid,
AUCTION.EbayNum,
AUCTION.EndDate,

    PaidStatus = CASE WHEN dbo.AUCTION.PaidStatus = 1 THEN 'Yes'
     ELSE '' END,

    Refund = CASE WHEN dbo.AUCTION.RefundYN = 1 THEN 'Yes' ELSE
     '' END,

    Relist = CASE WHEN dbo.AUCTION.RelistedYN = 1 THEN 'Yes' ELSE
     '' END

FROM dbo.LOT LEFT JOIN
    dbo.AUCTION ON
    dbo.LOT.LotNum = dbo.AUCTION.LotNum
----------------------------------------------------------------------

Is CASE considered an aggregate function -- Would the presence of CASE statements in the query prevent updating?

OR... Would the fact that the subform queries qryBrandLots for its RecordSource, instead of just tapping qryBrandLots directly, be a problem that prevents updating?
0
 
Brian CroweCommented:
I believe that CASE would present a problem since there would be no way to determine how to update the source.
0
 
Galisteo8Author Commented:
I am thinking, then, that the main form should have a command button ("Add Lot") that will open a popup or modal form tied directly to the Lot table in which the user actually creates the new Lot record.  Upon closing this modal form, a) a new Lot record is created in the Lot table, and b) the main form and subform are updated.

Can you think of a simpler way?  I'm trying to make this as streamlined as possible for the users (who want this subform to look-and-feel like an Excel spreadsheet), and as non-code-heavy as I can for me.
0
 
Galisteo8Author Commented:
I've proceeded with the modal form approach, rather than adding records directly in a row of the datasheet, since that does not seem possible in my situation.  BriCrow, I tend to believe you are correct -- that the use of CASE in the query does prevent me from doing that.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now