Help with custom search form - should I use rec

[Manuel]

I have a search form that retrieves records in a subform based on criteria
entered into unbound text boxes on the main/parent form. The subform is tied
to a SQL recordset that contains a field called “Selectâ€. The field’s
datatype is Yes/No.

On the main form I have a button called “View Selected†with the following
code in the OnClick event:

Private Sub cmdViewSelected_Click()
On Error GoTo Err_cmdViewSelected_Click

Dim i As Integer

i = DCount("ProjectId", "ProjectTbl", "[Select] = " & True)

If i = 0 Then
MsgBox "No Record(s) Selected", vbInformation
Exit Sub
End If

DoCmd.OpenForm "MainForm", , , "[Select] = " & True

Exit_cmdViewSelected_Click:
Exit Sub

Err_cmdViewSelected_Click:
MsgBox Err.Description
Resume Exit_cmdViewSelected_Click

End Sub

Basically, the user has the option to select a subset of the records
returned by the search by clicking the “Select†checkbox and then clicking
the “View Selected†button, which opens another form (“Main Formâ€) that
displays more details.

I plan to clear the “Select†field via an Update query, inserted in both the
parent form’s OnClose event and in the above code.

DoCmd.RunSQL "UPDATE ProjectTbl SET ProjectTbl.[Select] = False WHERE
(((ProjectTbl.[Select])=True))"

The problem is that there are a number of users using the database and I see
the potential of one or more users checking the “Select†checkbox for
different records and then clicking the “View Selected†button and having
unexpected results/records returned in the detailed form (unexpected to the
user, since he/she just expects to have the records he/she selected display
in the detailed form).

Is there a better way to have the “View Selected†functionality within my
search form than the way I’m doing it? Perhaps by using the “clone†method
to clone the recordset or by creating another workspace. I’m just not sure
of where to being with the coding.

Your assistance is greatly appreciated!

Thanks,

Manuel

 

[Daryl S]

Manuel -

This is one of the few cases where you want a table in the front-end
application. This table would only contain two fields - the primary key of
the table with the data you are looking at and the [Select] field. The
[Select] field should have a default value of False. The [Select] field
should not be in the back-end table.

Then right before you open the form, delete all records from this table and
populate it with all the primary keys from the main table. Leave the
[Select] field out of this append query as it will default to False. Your
form should be based on a query that joins the local table with the back-end
table. The user will click on the SELECT checkboxes as you have in place,
and can run queries or subforms based on their own selections. You don't
need to worry about clearing the [Select] field if you are replacing the
records when the user opens the form.

The only downside to this is that the front-end applications may need to be
compacted/repaired occasionally if there are a lot of records involved.

Hope that helps!

--
Daryl S

I have a search form that retrieves records in a subform based on criteria
entered into unbound text boxes on the main/parent form. The subform is tied
to a SQL recordset that contains a field called “Selectâ€. The field’s
datatype is Yes/No.

On the main form I have a button called “View Selected†with the following
code in the OnClick event:

Private Sub cmdViewSelected_Click()
On Error GoTo Err_cmdViewSelected_Click

Dim i As Integer

i = DCount("ProjectId", "ProjectTbl", "[Select] = " & True)

If i = 0 Then
MsgBox "No Record(s) Selected", vbInformation
Exit Sub
End If

DoCmd.OpenForm "MainForm", , , "[Select] = " & True

Exit_cmdViewSelected_Click:
Exit Sub

Err_cmdViewSelected_Click:
MsgBox Err.Description
Resume Exit_cmdViewSelected_Click

End Sub

Basically, the user has the option to select a subset of the records
returned by the search by clicking the “Select†checkbox and then clicking
the “View Selected†button, which opens another form (“Main Formâ€) that
displays more details.

I plan to clear the “Select†field via an Update query, inserted in both the
parent form’s OnClose event and in the above code.

DoCmd.RunSQL "UPDATE ProjectTbl SET ProjectTbl.[Select] = False WHERE
(((ProjectTbl.[Select])=True))"

The problem is that there are a number of users using the database and I see
the potential of one or more users checking the “Select†checkbox for
different records and then clicking the “View Selected†button and having
unexpected results/records returned in the detailed form (unexpected to the
user, since he/she just expects to have the records he/she selected display
in the detailed form).

Is there a better way to have the “View Selected†functionality within my
search form than the way I’m doing it? Perhaps by using the “clone†method
to clone the recordset or by creating another workspace. I’m just not sure
of where to being with the coding.

Your assistance is greatly appreciated!

Thanks,

Manuel