asked on

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

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Galisteo8

ASKER

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?

Brian Crowe

I believe that CASE would present a problem since there would be no way to determine how to update the source.

Galisteo8

ASKER

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.

Galisteo8

ASKER

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.