is there a non-data entry mode?

[Guest]

I'd like to use a form merely for allowing people to look up information.

they'd enter a value, and various queries could be executed from their input
and various values reported. I don't want them adding anything, just
entering bits to be used in the query.

 

[Ken Snell [MVP]]

Best way to do this is to lock all the controls on the form. You can do this
in the form's design view.

If you're not using a combo box as the "search" selector, nor a textbox as
the "search" enterer, then you can set the form's AllowEdits,
AllowDeletions, and AllowAdditions properties to No in the form's design
view.

 

[Bob Howard]

First, build a Query for the universe of data that can be seen by the
user --- all fields from all related tables (even fields that you may not
want displayed but which you may need access to in the code behind the form.

Second, create a Form with unbound controls representing the various data
fields the users can query against and other questions you want them to
answer that affect the result. Set the RecordSource of this Form to the
Query you built, and set AllowEdits=Yes, DataEntry=No, AllowDeletions=No,
AllowFilters=Yes, AllowAdditions=No. You'll need AllowEdits=Yes to allow
the user to merely enter data into the form --- this doesn't mean the user
will actually be able to change the data in the tables because the fields
used to display the data will be locked,

Third, put a button on this Form, and create an OnClick event for the
button. In the OnClick event, put code to examine all the various conotrols
where the user may have entered data on the form. From this, build an SQL
filter (basically, an SQL WHERE clause without the word "WHERE" at the
beginning). You may need to build this piece by piece with appropriate AND
clauses, etc. Put the whole thing together into a string variable (such as
strFilter).

Next within the OnClick event, set the filter by executing the following:

DoCmd.AppyFilter , strFilter

[and note the presence of the comma as the SQL you built is the second
parameter, the first being absent]

Immedately following this, tell Access to requery the data behind the form:

Me.Requery

The records now available to the Form are only those that meet the
characteristics of the filter.

You'll need locked/disabled controls on the form for actually displaying the
result. The user can then scroll through the result.

Bob.

 

[Guest]

Your response seems quite well-informed, and I really appreciate your taking
the time to help me out. Perhaps you can break it down even farther for
someone very new (not to programming, but to Access and VB)?

0. I don't know what creating a form with unbound controls means.

1. I am concerned about gathering ALL the data I'll ever need in a single
query. I need to be able to process the results of the first query in order
to build a second, third, indefinite query to accumulate the information.
I'll have to process it a little too.

2. I tried setting the form properties as you say below, but in "Form view"
mode, I still can't enter anything in the important field. (it's a text box,
dragged directly from the field list of the query from which the form data is
based). I can view the values of the various fields by using the arrows at
the bottom left to progress through the records.

3. I added a button and the code, but it seems to fail when i click it...
it jumps to some VB-looking code and indicates a problem where thefunction is
declared.
It says: "Method or Data member not found" and points at the line "Private
Sub FindRecordsMain_Click()"

4. I don't know what this means:

You'll need locked/disabled controls on the form for actually displaying the
result.

thanks again for your help, any further detail would be great.

-e

First, build a Query for the universe of data that can be seen by the
user --- all fields from all related tables (even fields that you may not
want displayed but which you may need access to in the code behind the form.

Second, create a Form with unbound controls representing the various data
fields the users can query against and other questions you want them to
answer that affect the result. Set the RecordSource of this Form to the
Query you built, and set AllowEdits=Yes, DataEntry=No, AllowDeletions=No,
AllowFilters=Yes, AllowAdditions=No. You'll need AllowEdits=Yes to allow
the user to merely enter data into the form --- this doesn't mean the user
will actually be able to change the data in the tables because the fields
used to display the data will be locked,

Third, put a button on this Form, and create an OnClick event for the
button. In the OnClick event, put code to examine all the various conotrols
where the user may have entered data on the form. From this, build an SQL
filter (basically, an SQL WHERE clause without the word "WHERE" at the
beginning). You may need to build this piece by piece with appropriate AND
clauses, etc. Put the whole thing together into a string variable (such as
strFilter).

Next within the OnClick event, set the filter by executing the following:

DoCmd.AppyFilter , strFilter

[and note the presence of the comma as the SQL you built is the second
parameter, the first being absent]

Immedately following this, tell Access to requery the data behind the form:

Me.Requery

The records now available to the Form are only those that meet the
characteristics of the filter.

You'll need locked/disabled controls on the form for actually displaying the
result. The user can then scroll through the result.

Bob.

I'd like to use a form merely for allowing people to look up information.

they'd enter a value, and various queries could be executed from their input
and various values reported. I don't want them adding anything, just
entering bits to be used in the query.

 

[Bob Howard]

Here are some answer that I hope are helpful...

0. An unbound control is a control (like a text control) that has no
Coontrol Source (i.e., it's not specifically related --- by Access --- to a
database field).

1. The query I"m referring to is an Access query, which is a logical view
of the data that is physically represented by the tables themselves. Within
your program (form) you'll be using the result of the information the user
enters to only present to the user the subset of the data thus requested.

2. The problem is that the textbox you created is bound to the database
since you dragged it from the list of query fields. To create a field for
the user to merely enter input data for youor program, create a new textbox
by using the textbox tool from the toolbox. Remember this is a field that
you want the user to type something into --- you'll use it in the VBA code
behind the form.

3. The "VB looking code" is where you'll need to add some VB code to set
the conditions for having only the subset of data shown to the user. I see
from your response that you're not familiar with VB, so this will need to
take more explanation with an example.

Assuming there is only one field you need to ask the user, the following 4
lines of VBA code does the following:

1) Defines a variable called strFilter where you'll build an SQL filter
2) Builds the SQL filter directing that only records from [field name]
within [table name] whose value equals the value entered by the user into
the field on the form called FindRecordsMain (a value entered by the user)
3) Applies that filter to the form
4) Tells Access to requery the data behind the form so that only the
selected records are now viewed

Here are the four lines of code:

Dim strilter As String
strFilter = "[table name].[field name] = Me!FindRecordsMain"
DoCmd.ApplyFilter , strFilter
Me.Requery

At this point, the form has its hands on only the selected subset of data.

4. The controls on the form displaying the data fields that you wish the
user to view (the ones that are bound to the query as they each have a
Control Source pointing to the query) will now show the data values, one
record at a time. Since I assume you only want to allow the user to SEE the
data and not change it. To prevent them from changing any of the data, go
into each of these bound controls and set the property to Enabled=No and
Locked=Yes. The user will be able to see the data but not change it.

Hope this helps (pardon spelling errors...) !

Bob

Your response seems quite well-informed, and I really appreciate your taking
the time to help me out. Perhaps you can break it down even farther for
someone very new (not to programming, but to Access and VB)?

0. I don't know what creating a form with unbound controls means.

1. I am concerned about gathering ALL the data I'll ever need in a single
query. I need to be able to process the results of the first query in order
to build a second, third, indefinite query to accumulate the information.
I'll have to process it a little too.

2. I tried setting the form properties as you say below, but in "Form view"
mode, I still can't enter anything in the important field. (it's a text box,
dragged directly from the field list of the query from which the form data is
based). I can view the values of the various fields by using the arrows at
the bottom left to progress through the records.

3. I added a button and the code, but it seems to fail when i click it...
it jumps to some VB-looking code and indicates a problem where thefunction is
declared.
It says: "Method or Data member not found" and points at the line "Private
Sub FindRecordsMain_Click()"

4. I don't know what this means:

You'll need locked/disabled controls on the form for actually displaying the
result.

thanks again for your help, any further detail would be great.

-e

First, build a Query for the universe of data that can be seen by the
user --- all fields from all related tables (even fields that you may not
want displayed but which you may need access to in the code behind the form.

Second, create a Form with unbound controls representing the various data
fields the users can query against and other questions you want them to
answer that affect the result. Set the RecordSource of this Form to the
Query you built, and set AllowEdits=Yes, DataEntry=No, AllowDeletions=No,
AllowFilters=Yes, AllowAdditions=No. You'll need AllowEdits=Yes to allow
the user to merely enter data into the form --- this doesn't mean the user
will actually be able to change the data in the tables because the fields
used to display the data will be locked,

Third, put a button on this Form, and create an OnClick event for the
button. In the OnClick event, put code to examine all the various conotrols
where the user may have entered data on the form. From this, build an SQL
filter (basically, an SQL WHERE clause without the word "WHERE" at the
beginning). You may need to build this piece by piece with appropriate AND
clauses, etc. Put the whole thing together into a string variable (such as
strFilter).

Next within the OnClick event, set the filter by executing the following:

DoCmd.AppyFilter , strFilter

[and note the presence of the comma as the SQL you built is the second
parameter, the first being absent]

Immedately following this, tell Access to requery the data behind the form:

Me.Requery

The records now available to the Form are only those that meet the
characteristics of the filter.

You'll need locked/disabled controls on the form for actually displaying the
result. The user can then scroll through the result.

Bob.

I'd like to use a form merely for allowing people to look up information.

they'd enter a value, and various queries could be executed from their input
and various values reported. I don't want them adding anything, just
entering bits to be used in the query.