Select multiple (or randome) rows from a form (1 Viewer)

Guys,

I am new to MSAccess programming so need some help here.

I have a form where data is being displayed and currently user choose any row and assign to a investigator. This is very time consuming so they want mass select or random select and assign it a investigator. I tried to provide checkbox in the detail section. but what happens is in preview, I check the box but all checkboxes are selected. I do not want that. If I choose one then that should be selected and not other checkboxes.

Please help.

This is the normal behavior for an unbound control in Access. You have to bind the checkbox to a field in the underlying record in order for it not to persist thru all records.

In this situation you need to

1. Bind the checkbox to a field in the record
2. Tick the checkbox for the desired records
3. Do your assignment based on the selected records
4. Re-set the checkboxes back to an non-ticked state

An alternative approach would be to populate a Listbox with the records and set its Multi Select Property to either Simple or Extended. I think Extended is the one for your stated needs. Then assign the selected records to an investigator, as desired.

Linq ;0)>

Thanks for the quick reply.

The data source of the form is view, so i had added a dummy column with space as value. (' ' as qcselect). I bound the checkbox with the dummy column. Now the issue when I preview still all checkbox is selected and when I try to uncheck it says it is not editable. I checked the properties and made sure 'Enabled' is Yes. Not sure if is really bound.

Any idea.

Appreciate your help.

Sorry, but your response really makes no sense at all.

The data source of the form is view

Click to expand...

The Record Source of a Form, what you're calling the data source, is either a Table or a Query based on one or more Tables, not a 'view.'

The 'dummy column' you've created has to be an Unbound control, and as I've explained, these kinds of controls will display the value of the control in the current record in the control of all records! You have to bind this column to a field in the underlying Table or Query that your Form is based on, in order for it to be checked/non-checked on a record by record basis.

Hi

Now, I have added and bound the column to checkbox control. but am unable to check/uncheck the column as it says record is not editable.

Reason could be datasource of the form is query called "qptQCselect". The description of "qptQCSelect" is select * from AlertSelect_V.
Here AlertSelect_V is the view and it contains "Select a, b, c from table a, table b where clause" I have selected new column "d" as part of AlertSelect_V and trying to use as checkbox for multiple select. but am unable to edit this column to check/uncheck.

Query "qptQCselect" is being used in the code as user can choose many other options to filter out or sort form data as ad-hoc. So where clause can be modified based on user's selection like this.

CurrentDb.QueryDefs("qptQCSelect").SQL = "SELECT * FROM AlertSelect_UI_V WHERE <<>> "


please help.

Please help..

anandsbr said:

Please help..

Click to expand...

Please show us the complete SQL for query qptQCSelect
Also, could you please show us the Table design for the tables involved?
and for AlertSelect_V

What version of Access are you using?

What exactly are you trying to accomplish? Sounds like you want to use a form to identify cases, then assign an investigator to a case or cases.

Here it is

qptQCSelect SQL is
-------
SELECT * FROM AlertSelect_UI_V WHERE 1=2

AlertSelect_V SQL CODE is
--------
/****** Object: View [dbo].[AlertSelect_UI_V] Script Date: 01/29/2010 08:27:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create view [dbo].[AlertSelect_UI_V] AS

SELECT RVW.QCReviewId, A.AlertId, RVW.CaseNumber, c.caseid RVW.QCReviewerId, STF.LastName + ', ' + LEFT(STF.FirstName, 1), '') AS ReviewerName, RVW.ReviewTypeId, RT.StateRollup, RT.QCType, RT.ReviewType, QCSampleDate, RVW.QCStart, RVW.QCFinalDate, RVW.QCApproved, RVW.RowVersion, RVW.ChecklistVersion, dbo.LOB.Description AS LOB, c.SecondaryWorkgroup, c.EventSubtype, RVW.AcctOpenClosedNonSAR
FROM dbo.QCReview AS RVW INNER JOIN
dbo.ReviewType_V AS RT ON RVW.ReviewTypeId = RT.ReviewTypeId
LEFT OUTER JOIN dbo.Alert AS A ON A.AlertSurrogateId = RVW.AlertSurrogateId
LEFT OUTER JOIN
dbo.LOB ON dbo.LOB.LobSurrogateId = A.LOBSurrogateId LEFT OUTER JOIN
dbo.AlertHistory AS AHO ON RVW.OriginalDispositionHistoryId = AHO.AlertHistoryId LEFT OUTER JOIN
dbo.AssignedAlert AS AA ON RVW.AlertSurrogateId = AA.AlertSurrogateId LEFT OUTER JOIN
dbo.ScoreGroup AS SG ON AA.ScoreGroupId = SG.ScoreGroupId LEFT OUTER JOIN
dbo.Staff AS STF ON RVW.QCReviewerId = STF.StaffId LEFT OUTER JOIN
dbo.Staff AS STI ON RVW.InvestigatorId = STI.StaffId LEFT OUTER JOIN
dbo.ProcessEventLog AS PEL ON RVW.ProcessEventLogId = PEL.ProcessEventLogId LEFT OUTER JOIN
dbo.SamplePopulationLog2 AS spl2 ON spl2.SourceRecordIdType = 'CaseId' AND spl2.SampleProcessEventLogId = RVW.ProcessEventLogId AND
RVW.CaseNumber = spl2.CaseNumber LEFT OUTER JOIN
dbo.[Case] AS c ON CASE WHEN IsNumeric(spl2.SourceRecordid) = 1 THEN CAST(spl2.SourceRecordid AS integer) ELSE NULL END = c.CaseId
WHERE (RVW.QCReviewId NOT IN
(SELECT DISTINCT QCReviewId
FROM dbo.QCReview
WHERE (ReviewedPriorToDatabase = 'Y') OR
(IsInvalid = 'Yes'))) AND ((RVW.QCApproved = 'N') OR
((RVW.QCApproved = 'Y') AND (CAST(CONVERT(VarChar(10), PEL.EndTime, 101) AS DateTime) BETWEEN CONVERT(varchar, DATEADD(m, - 4,
GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))))


Here RVW.QCReview is the main table and I try to update QCReviewerid, QCFlag and rowversion (Timestamp). This has been working fine. but after i have introduced a new column RVW.AcctOpenClosedNonSAR in the view which already exists in the QCreview table, am unable to do an update. This column i want to use as checkbox so users can choose multiple alerts or cases and assign to userid (QCreviewerId)

What version of Access are you using? Access 2007 / SQL Server 2008

What exactly are you trying to accomplish? Sounds like you want to use a form to identify cases, then assign an investigator to a case or cases.

Ans: Trying to provide a checkbox (using newly introduced column RVW.AcctOpenClosedNonSAR) to user so they can choose multiple alerts or cases and assign to investigator.