setting form record source when form is opened

[deannamch]

I have a question about setting the record source of a form at the time the
form is opened.

Background: I need to clean up and modify an application. The application
currently only has one table and one template form for data entry. However a
separate copy of a selection query and a separate copy of the data entry form
have been created for each analyst in the office. The only difference
between the queries is the CurrentAnalyst criteria is set to one person’s
name. The only difference between the forms is the record source is set to a
different selection query. Currently the analysts open their copy of the
data entry form. They are able to apply and remove filters and do not lose
the initial criteria that limits the records to their own matters.

I didn’t set up the application this way and I would prefer not to have to
replicate changes across a dozen copies each time a change is made.

My first approach was to create a selection form where the user clicks a
button by their name. In the command click event I opened a copy of the form
where the record source is the table. I did this using the OpenForm method
of DoCmd and setting a WHERE condition. This works however the condition is
applied as a filter property. The user will lose the CurrentAnalyst criteria
if they remove the filter which is definitely not desirable.

Any suggestions would be appreciated for a way to proceed on being able to
keep the users limited to their own matters while still allowing them to
filter their own matters.

Thanks,
Deanna

 

[Jeanette Cunningham]

Set the form's recordsource using a query in the Load event of the form

Me.RecordSource = "theQuery"

will set the recordsource of the form. "theQuery" can be a saved query or a
SQL string.

Jeanette Cunningham

 

[deannamch]

Set the form's recordsource using a query in the Load event of the form

Me.RecordSource = "theQuery"

will set the recordsource of the form. "theQuery" can be a saved query or a
SQL string.

Jeanette Cunningham

I have a question about setting the record source of a form at the time the
form is opened.

[quoted text clipped - 34 lines]

Thanks,
Deanna

Thanks for the suggestion. I thought about trying this however I would still
need to maintain the dozen different queries where the only difference is one
selection criteria. We are making quite a few changes in the next 2 months
including adding fields. Each time a field is added it would have to be be
to all of the queries. I'm trying to eliminate that type of redundancy.

Deanna

 

[Jeanette Cunningham]

You can set a query string in code like this:

Dim strSQL as String
Dim strWhere as String

Const cstrStub = "SELECT FieldA.TheTable, FieldB.TheTable, FieldC.TheTable "
_
& "FROM TheTable WHERE "

strWhere = "UserName.TheTable = """ & Forms!frmHiddenForm!txtUserName & """
strSQL = cstrStub & strWhere

When the user opens the app, store their user name on a textbox on the
hidden form, which opens when the app opens, and stays open in hidden mode
all the time.
That way you can use the same query for each copy of the front end.

Jeanette Cunningham

Set the form's recordsource using a query in the Load event of the form

Me.RecordSource = "theQuery"

will set the recordsource of the form. "theQuery" can be a saved query or
a
SQL string.

Jeanette Cunningham

I have a question about setting the record source of a form at the time
the
form is opened.

[quoted text clipped - 34 lines]

Thanks,
Deanna

Thanks for the suggestion. I thought about trying this however I would
still
need to maintain the dozen different queries where the only difference is
one
selection criteria. We are making quite a few changes in the next 2
months
including adding fields. Each time a field is added it would have to be
be
to all of the queries. I'm trying to eliminate that type of redundancy.

Deanna