Select fields to be included in queries

[George]

Dear friends,

I have a relational database (Access 2003) and I need the user to be able to
select some fields from one or more tables to be included in a query, using a
form (The user will not have permissions for viewing/creating/mmodifying
tables, queries etc).

i.e. a sample of tables:

tbl_Employees (EmployeeID primary key, employeeName, EmployeeSurname,
dateofBirth, TelNos, Address fields etc)
tbl_EmployeesTransfers (TransferDate and BranchID primary keys)

The user must be able to see all those fields and he may wants to use only
the EmployeeID and the TransferDate to be shown in a query.

Any help will be highly appreciated.

Thanking you in advance,

GeorgeCY

 

[Tom Wickerath]

Hi George,

You need to use a technique called Query By Form (QBF). I have some QBF
samples available, if you'd like to take a look. These range from very simple
to somewhat more complex.

Very little VBA code / fairly simple:
http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip

This one shows how to "iterate" a multiselect list box:
http://www.accessmvp.com/TWickerath/downloads/elements.zip

A bit more advanced. This is a modified copy of the QBF sample that
is shown in Chapter 8 of Access 2000 Power Programming, written by
F. Scott Barker:
http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

Includes a Word document that helps explain the technique.
http://www.seattleaccess.org/downloads.htm
See the download "Query By Form"
Tom Wickerath, February 12, 2008

If I understand you correctly (?), your tbl_EmployeesTransfers would be a
child table in a one-to-many (1:M) relationship with the tbl_Employees table.
This means that if the criteria in your QBF form for a particular search was
based on the Employee table fields only, the subform used to display the
matching records would show the same employee name for each record in the
Transfers table. There is a way around this, but it gets a bit more
complicated. You can "concatenate" (ie. add together) the results from the
child form to display in one field in the QBF subform. This technique is
shown here:

http://www.mvps.org/access/modules/mdl0004.htm

But, first things first. Work on getting a functional QBF search form, even
if it displays the same employee name several times for each transfer record.
Then you can work on adding the finishing touch later, if you want the
subform to display only one record per employee.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Allen Browne]

If you know nothing about code, the simplest solution would be to let the
user create a query, using whatever fields they want.

Alternatively, you could create a form that has all the columns in
Datasheeet view. Then set the ColumnHidden property of the controls you wish
to hide in (say) Form_Open.

 

[George]

Thanks a lot Tom for your prompt answer,

What I need is to be able to have in a list all available field names from
my tables and to be able to select some of them to be included in a query.
Also, if possible to use criteria for each selected field.

Yes, the tbl_EmployeesTransfers is a child table related to tbl_Employees,
using EmployeeID.

Any thoughts?

GeorgeCY

Ο χÏήστης "Tom Wickerath" έγγÏαψε:

 

[Tom Wickerath]

Hi George,

What I need is to be able to have in a list all available field names from
my tables and to be able to select some of them to be included in a query.

When I design my QBF forms, I generally pick the fields that I believe are
the most appropriate for a user to perform a search with. Then I create a QBF
form that accomodates such a search. Have a look at the four samples that I
provided links to first, before deciding that you need a possible alternate
approach.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[George]

Thanks a lot both of you.

1. Examples supplied by Tom are very useful, but as mentioned before - is
there any way to show a map? of all tables/fields in a form so the user can
tick on any field and this to be included in the results of a query?

2. Since I will lock the database (the user will not be able to see or
create queries), How can I let the user create the required query?

GeorgeCY

Ο χÏήστης "Allen Browne" έγγÏαψε:

 

[Allen Browne]

Use something like this as the RowSource of a combo/list box to list of the
tables in your database:

SELECT MSysObjects.Name FROM MSysObjects
WHERE ((MSysObjects.Name Not Like "~*")
AND (MSysObjects.Type In (1,4,6,8)))
ORDER BY MSysObjects.Name;

To list the fields in a table, set the RowSourceType of another combo to
Field List.

 

[George]

Thanks a lot Allen,

I now have a combo box and a list box in my form

combo1 - Row Source Table/Query
SELECT MSysObjects.Name FROM MSysObjects WHERE (((Left([Name],4))<>"MSys")
And ((MSysObjects.Type)=1 Or (MSysObjects.Type)=6)) ORDER BY
MSysObjects.Name;

After update of this I have:

Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim strRecSource As String


Set db = CurrentDb()
Set tdf = db.TableDefs(Me.Combo1)


For Each fld In tdf.Fields
strRecSource = strRecSource & fld.Name
strRecSource = strRecSource & "; "
Next


strRecSource = Left(strRecSource, Len(strRecSource) - 2)


Me.Combo2.RowSource = strRecSource
Me.Refresh


combo2 is a multi select list Box, where I can select more than one
available field.

Now, how I can have a query returning those selected fields?

GeorgeCY


Ο χÏήστης "Allen Browne" έγγÏαψε:

 

[Allen Browne]

I don't understand the need for that code.

Just set the combo's RowSourceType property to:
Field List
and use the AfterUpdate of your previous combo to set its RowSource to the
name of the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks a lot Allen,

I now have a combo box and a list box in my form

combo1 - Row Source Table/Query
SELECT MSysObjects.Name FROM MSysObjects WHERE (((Left([Name],4))<>"MSys")
And ((MSysObjects.Type)=1 Or (MSysObjects.Type)=6)) ORDER BY
MSysObjects.Name;

After update of this I have:

Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim strRecSource As String


Set db = CurrentDb()
Set tdf = db.TableDefs(Me.Combo1)


For Each fld In tdf.Fields
strRecSource = strRecSource & fld.Name
strRecSource = strRecSource & "; "
Next


strRecSource = Left(strRecSource, Len(strRecSource) - 2)


Me.Combo2.RowSource = strRecSource
Me.Refresh


combo2 is a multi select list Box, where I can select more than one
available field.

Now, how I can have a query returning those selected fields?

GeorgeCY


Ο χÏήστης "Allen Browne" έγγÏαψε:

Use something like this as the RowSource of a combo/list box to list of
the
tables in your database:

SELECT MSysObjects.Name FROM MSysObjects
WHERE ((MSysObjects.Name Not Like "~*")
AND (MSysObjects.Type In (1,4,6,8)))
ORDER BY MSysObjects.Name;

To list the fields in a table, set the RowSourceType of another combo to
Field List.

 

[George]

Dear Allen,

I have done that already.

So, I am able to select some fields from this list box. Then those fields
to be included into a new query and show all those records (or export them in
excel).

The user will not be able to create that query (in design view), so I need
that to be done as mentioned above

Ο χÏήστης "Allen Browne" έγγÏαψε:

I don't understand the need for that code.

Just set the combo's RowSourceType property to:
Field List
and use the AfterUpdate of your previous combo to set its RowSource to the
name of the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks a lot Allen,

I now have a combo box and a list box in my form

combo1 - Row Source Table/Query
SELECT MSysObjects.Name FROM MSysObjects WHERE (((Left([Name],4))<>"MSys")
And ((MSysObjects.Type)=1 Or (MSysObjects.Type)=6)) ORDER BY
MSysObjects.Name;

After update of this I have:

Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim strRecSource As String


Set db = CurrentDb()
Set tdf = db.TableDefs(Me.Combo1)


For Each fld In tdf.Fields
strRecSource = strRecSource & fld.Name
strRecSource = strRecSource & "; "
Next


strRecSource = Left(strRecSource, Len(strRecSource) - 2)


Me.Combo2.RowSource = strRecSource
Me.Refresh


combo2 is a multi select list Box, where I can select more than one
available field.

Now, how I can have a query returning those selected fields?

GeorgeCY


Ο χÏήστης "Allen Browne" έγγÏαψε:

Use something like this as the RowSource of a combo/list box to list of
the
tables in your database:

SELECT MSysObjects.Name FROM MSysObjects
WHERE ((MSysObjects.Name Not Like "~*")
AND (MSysObjects.Type In (1,4,6,8)))
ORDER BY MSysObjects.Name;

To list the fields in a table, set the RowSourceType of another combo to
Field List.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks a lot both of you.

1. Examples supplied by Tom are very useful, but as mentioned before -
is
there any way to show a map? of all tables/fields in a form so the user
can
tick on any field and this to be included in the results of a query?

2. Since I will lock the database (the user will not be able to see or
create queries), How can I let the user create the required query?

GeorgeCY

Ο χÏήστης "Allen Browne" έγγÏαψε:

If you know nothing about code, the simplest solution would be to let
the
user create a query, using whatever fields they want.

Alternatively, you could create a form that has all the columns in
Datasheeet view. Then set the ColumnHidden property of the controls
you
wish
to hide in (say) Form_Open.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dear friends,

I have a relational database (Access 2003) and I need the user to be
able
to
select some fields from one or more tables to be included in a
query,
using a
form (The user will not have permissions for
viewing/creating/mmodifying
tables, queries etc).

i.e. a sample of tables:

tbl_Employees (EmployeeID primary key, employeeName,
EmployeeSurname,
dateofBirth, TelNos, Address fields etc)
tbl_EmployeesTransfers (TransferDate and BranchID primary keys)

The user must be able to see all those fields and he may wants to
use
only
the EmployeeID and the TransferDate to be shown in a query.

Any help will be highly appreciated.

Thanking you in advance,

GeorgeCY

 

[Allen Browne]

George, I dont' think I want to go down the path you are taking here.

You could create a form (or open one in design view), and CreateControl for
the text boxes you need. After you save the form, you can then open it in
the desired view. What you are doing, in essence, is what the form wizard
does. A disadvantage of using design view is that you cannot create an MDE.

IMHO, it would be better to create a query that you will use to show the
user (assuming Datasheet view is okay.) You can then dynamically assign the
SQL property of the QueryDef, and OpenQuery to show that to the user.

Alternatively, create a form for each table (as you normally would), so that
each form can show each field. Hide the fields the user doesn't want to see.

While it might be possible to create some kind of generic unbound form with
lots of text boxes on it, and then assign its RecordSource and the Control
Source of each control you want to use and hide the other controls, I can't
imagine the result being very satisfactory.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,

I have done that already.

So, I am able to select some fields from this list box. Then those fields
to be included into a new query and show all those records (or export them
in
excel).

The user will not be able to create that query (in design view), so I need
that to be done as mentioned above

Ο χÏήστης "Allen Browne" έγγÏαψε:

I don't understand the need for that code.

Just set the combo's RowSourceType property to:
Field List
and use the AfterUpdate of your previous combo to set its RowSource to
the
name of the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks a lot Allen,

I now have a combo box and a list box in my form

combo1 - Row Source Table/Query
SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],4))<>"MSys")
And ((MSysObjects.Type)=1 Or (MSysObjects.Type)=6)) ORDER BY
MSysObjects.Name;

After update of this I have:

Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim strRecSource As String


Set db = CurrentDb()
Set tdf = db.TableDefs(Me.Combo1)


For Each fld In tdf.Fields
strRecSource = strRecSource & fld.Name
strRecSource = strRecSource & "; "
Next


strRecSource = Left(strRecSource, Len(strRecSource) - 2)


Me.Combo2.RowSource = strRecSource
Me.Refresh


combo2 is a multi select list Box, where I can select more than one
available field.

Now, how I can have a query returning those selected fields?

GeorgeCY


Ο χÏήστης "Allen Browne" έγγÏαψε:

Use something like this as the RowSource of a combo/list box to list
of
the
tables in your database:

SELECT MSysObjects.Name FROM MSysObjects
WHERE ((MSysObjects.Name Not Like "~*")
AND (MSysObjects.Type In (1,4,6,8)))
ORDER BY MSysObjects.Name;

To list the fields in a table, set the RowSourceType of another combo
to
Field List.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks a lot both of you.

1. Examples supplied by Tom are very useful, but as mentioned
before -
is
there any way to show a map? of all tables/fields in a form so the
user
can
tick on any field and this to be included in the results of a query?

2. Since I will lock the database (the user will not be able to see
or
create queries), How can I let the user create the required query?

GeorgeCY

Ο χÏήστης "Allen Browne" έγγÏαψε:

If you know nothing about code, the simplest solution would be to
let
the
user create a query, using whatever fields they want.

Alternatively, you could create a form that has all the columns in
Datasheeet view. Then set the ColumnHidden property of the controls
you
wish
to hide in (say) Form_Open.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dear friends,

I have a relational database (Access 2003) and I need the user to
be
able
to
select some fields from one or more tables to be included in a
query,
using a
form (The user will not have permissions for
viewing/creating/mmodifying
tables, queries etc).

i.e. a sample of tables:

tbl_Employees (EmployeeID primary key, employeeName,
EmployeeSurname,
dateofBirth, TelNos, Address fields etc)
tbl_EmployeesTransfers (TransferDate and BranchID primary keys)

The user must be able to see all those fields and he may wants to
use
only
the EmployeeID and the TransferDate to be shown in a query.

Any help will be highly appreciated.

Thanking you in advance,

GeorgeCY