By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

query by field heading

P: n/a
I have been looking for an answer to a problem and have found this
group and hope you can assist .

I have been re doing a data base I have made for a car club I am with
and have been trying to make a query that selects from a table as
desribed below ..

I have a table (Volunteer) that has a member field (memnumber)
and a number of fields that are headed in various categories and are
yes/no formated

I want to get a create a query / report by selecting members
that are "true" based on field category headings by selecting a
category as a variable, from a lookup table that has field /category
heading in it, rather than create a number of stand alone queries.
these field / categories may increase in the future

Can this be done easily or is at involved... ???
I am not an expert but have reasonable amount of "User"
knowledge

I am using Windows XP and Access 2002

Your help would be gratefully accepted
Nov 12 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
John,

Look at QueryDef in the Help file.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"John young" <ja*****@arach.net.au> wrote in message
news:82**************************@posting.google.c om...
I have been looking for an answer to a problem and have found this
group and hope you can assist .

I have been re doing a data base I have made for a car club I am with
and have been trying to make a query that selects from a table as
desribed below ..

I have a table (Volunteer) that has a member field (memnumber)
and a number of fields that are headed in various categories and are
yes/no formated

I want to get a create a query / report by selecting members
that are "true" based on field category headings by selecting a
category as a variable, from a lookup table that has field /category
heading in it, rather than create a number of stand alone queries.
these field / categories may increase in the future

Can this be done easily or is at involved... ???
I am not an expert but have reasonable amount of "User"
knowledge

I am using Windows XP and Access 2002

Your help would be gratefully accepted

Nov 12 '05 #2

P: n/a
Hi John,

I'm not real clear on the criteria that decides whether or not to include a
member in the results, so bear with me and my assumptions, while I attempt
to make this as flexible as possible.

In this situation let's say you want members displayed where either *one
field* OR *one of many fields* contains a "True " value. (ie. field names
"Ford", or "Chev", or "Dodge" contain a "True" value.)

This can be accomplished best (IMHO) by using a multi-select listbox to
specify criteria.

1.) Design an unbound main form with a list-box control containing a field
list.

Create this list-box using the following method:
a.) Choose the list-box tool from the toolbar, and place it on your
form.
b.) When the list-box wizard starts up ... hit the [Cancel] button ...
we want to do something that the wizard is not designed to do.
c.) Open the property sheet for the list-box (if not already open).
d.) Name: Lets call it "lstFieldList"
RowSource Type: Leave as "Table/Query" for now, but we'll change this in
a minute.
Multi Select: Choose "Extended"
e.) In the "Row Source" field, select the table (Volunteer) that
contains the Yes/No Fields.
f.) After selecting the table, click on the ellipse (...) to invoke the
Query Builder, and answer "Yes" when prompted.
g.) When the "Design View" QBE grid appears, select JUST the Yes/No
fields that you want to be displayed in the field list.
h.) If you like, you can confirm that the selected fields DO contain
either Yes or No values by opening the "query" in datasheet view.
i.) Now switch from "Design or "Datasheet" view to "SQL" View and insert
this string -- Bogus As '(All)', -- just after the word "SELECT" in your SQL
statement.

It should now look something like this:

SELECT Bogus As '(All)', Volunteers.Ford, Volunteers.Chev, Volunteers.Dodge
FROM Volunteers;

Note: The idea of adding an '(All)' to the field list is so that we can
decide to show all records in the table rather than JUST by criteria.

j.) Close the QBE grid. When prompted to "save the changes to the SQL
statement and update the property...", say Yes
k.) Now change the list-box's RowSource Type to "Field List"
l.) Select Form view, and check out the "lstFieldList" list-box. It should
now contain '(All)', followed by a list of the desired fields.

2.) Insert a bound subform control (again based on Volunteer table) using
the subform tool / wizard to select the desired fields that you'd like to
display in your results. Lets name this subform "sbfVolunteers"

3.) Seeing as how the subform is based on the Volunteers table, all records
will be displayed by default. We're going to change the subform's
recordsource to an SQL statement that we will build in VBA code:
************************************************** ***********
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
sRequerySubform
End Sub

Private Sub lstFieldList_AfterUpdate()
sRequerySubform
End Sub

Public Sub sRequerySubform()
'What I want to do here is build the RecordSource for the subform
'ON-THE-FLY...to allow for a variable WHERE statement.
'in order to alow the user to view:
' 1.) ALL records
' 2.) filtered by one field only
' 3.) filtered by multiple fields

Dim ctl As Control
Set ctl = Me.lstFieldList

Dim Msg As String
Dim MySQL As String

Dim CR As String
CR = vbCrLf

Dim varItm As Variant
Dim whr As String

'Build the SELECT portion of the SQL statement
MySQL = ""
MySQL = MySQL & "SELECT Volunteers.*"
MySQL = MySQL & " FROM Volunteers "

'Add a "safety" step which prevents the user from combining "All" with any
other criteria.
If ctl.ItemsSelected.Count > 1 And ctl.Selected(0) = True Then
Msg = ""
Msg = Msg & "You cannot select '(All)' along with " & CR
Msg = Msg & "any other criteria."
MsgBox (Msg)

ctl.Selected(0) = False
End If

'Build the WHERE portion
whr = "" 'initialize variable
If ctl.ItemsSelected.Count > 0 Then

For Each varItm In ctl.ItemsSelected

If Len(whr) > 0 Then
whr = whr & " OR " & ctl.ItemData(varItm) & "= True"
Else
whr = whr & ctl.ItemData(varItm) & "= True"
End If

Next varItm

End If

If Len(whr) > 0 Then
MySQL = MySQL & "WHERE (" & whr & ")"
End If

MySQL = MySQL & "; "
'Debug.Print MySQL

Me.sbfVolunteers.Form.RecordSource = MySQL
Set ctl = Nothing

End Sub
************************************************** **
After implementing all of the above, you should be able to select "Chev" and
have all of the "Chev = True" records displayed.
Press [Ctrl] and click on "Ford" ... the subform should then display all of
the "Chev = True" and "Ford=True" records, and so on!

The beauty of this approach is that you could add a field "Volvo" (ya,
right!) later on. All that you need do to add this to the field list in the
combo-box is to modify the listbox as in 1.g.) above.
--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"John young" <ja*****@arach.net.au> wrote in message
news:82**************************@posting.google.c om...
I have been looking for an answer to a problem and have found this
group and hope you can assist .

I have been re doing a data base I have made for a car club I am with
and have been trying to make a query that selects from a table as
desribed below ..

I have a table (Volunteer) that has a member field (memnumber)
and a number of fields that are headed in various categories and are
yes/no formated

I want to get a create a query / report by selecting members
that are "true" based on field category headings by selecting a
category as a variable, from a lookup table that has field /category
heading in it, rather than create a number of stand alone queries.
these field / categories may increase in the future

Can this be done easily or is at involved... ???
I am not an expert but have reasonable amount of "User"
knowledge

I am using Windows XP and Access 2002

Your help would be gratefully accepted

Nov 12 '05 #3

P: n/a
Don
I have just received your reply top my query and have
implemented it and it displays as you indicate ..

My Question may have been incomplete in my wording as what i
want to do is to create a query / report that gives me a list
/printout of all members (volunteers) who are listed as "True" for
the selected field name categorys ... eg "magazine" , "swap meet",
"parts" etc .... etc

The query will have reference to member number,name, phone no., email
from a members table along with the relevant "true" criteria from the
field selected by list box...
Hope this is clearer ...
Nov 12 '05 #4

P: n/a
Hi John,

Easy fix...

We'll just use the same "MySQL" string as the FilterName for the report.
This is what I like to call the WYSIWYG approach to reporting. :)
[What You See (displayed in the subform) Is What You Get] on the report.

1.) Move the "Dim MySQL As String" declaration to the form's declaration
section (like this) so that this string variable is available to all
procedures on this form.
*************************************
Option Compare Database
Option Explicit
Dim MySQL As String
*************************************

2.) I'm assuming that you have already designed a report, and have named it
"rptVolunteers"...
If you haven't, go ahead and create it based directly on the "Volunteers"
table. When you open it from the DB window it will show ALL records, but
when you open it from the command button on your form it will show just the
filtered records that are being displayed in the subform.

3.) Create a command button called "cmdOpenReport" using the button wizard
.... then just add " , MySQL " in the optional [filtername] parameter of the
OpenReport Method. (like this)
*************************************
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim stDocName As String

stDocName = "rptVolunteers"
DoCmd.OpenReport stDocName, acPreview, MySQL ' Added the " , MySQL "
here

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub
*************************************

--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
"John young" <ja*****@arach.net.au> wrote in message
news:82**************************@posting.google.c om...
Don
I have just received your reply top my query and have
implemented it and it displays as you indicate ..

My Question may have been incomplete in my wording as what i
want to do is to create a query / report that gives me a list
/printout of all members (volunteers) who are listed as "True" for
the selected field name categorys ... eg "magazine" , "swap meet",
"parts" etc .... etc

The query will have reference to member number,name, phone no., email
from a members table along with the relevant "true" criteria from the
field selected by list box...
Hope this is clearer ...

Nov 12 '05 #5

P: n/a
Don
thanks for your reply .... works well and I can use it ... but
unfortunatly not what i am after .....
i really want to produce report that has only selected field
information from the selection list box... with the field selected as
the 'variable' to display as heading on report and 'true' data below
....... so as you select a diffent category from list box it displays
only that data in report...

sorry for the confusion .....
Nov 12 '05 #6

P: n/a
Hi John,

If I'm finally "getting this" ... you only want the colmn headings in the
report to be visible only if they were the fields that you selected in
lstFieldNames? That sounds a little tougher, but probably still not
impossible. Is it important that they be positioned exactly where you want
them on this report? I guess that could be done too using each label and
fields .Left property, and then repositioning it later?

I'm thinking of building an array of selected fields, passing that to the
OpenArgs, then setting the report column's .Visible property to true for all
fields in the array. The other fields not in the array would get their
..visible property set to False. I'm thinking that the report's OnFormat
event is where this test should run.

I don't have time to mess with this today, maybe you could give that a try,
or at least confirm that I'm on the right track now?

Don

"John young" <ja*****@arach.net.au> wrote in message
news:82**************************@posting.google.c om...
Don
thanks for your reply .... works well and I can use it ... but
unfortunatly not what i am after .....
i really want to produce report that has only selected field
information from the selection list box... with the field selected as
the 'variable' to display as heading on report and 'true' data below
...... so as you select a diffent category from list box it displays
only that data in report...

sorry for the confusion .....

Nov 12 '05 #7

P: n/a
Hi Chuck,

That sounds like a cool tip, if I understand the concept correctly.

We're talking about basically assigning an "unbound" control's Control
Source property to whatever value I carry forward using the OpenArgs,
correct?
I imagine that you should be able to also assign the label's caption
property somehow too?

Would you mind explaining this a little more?

Regards,
Don
Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in message
news:e7********************************@4ax.com...

Hint: Use the Ordinal field position rather then the field's name as
the control's recordsource

Example: recordset.Fields(0)
On Wed, 05 May 2004 14:00:15 GMT, "Don Leverton"
<le****************@telusplanet.net> wrote:
If I'm finally "getting this" ... you only want the colmn headings in the
report to be visible only if they were the fields that you selected in
lstFieldNames? That sounds a little tougher, but probably still not
impossible. Is it important that they be positioned exactly where you wantthem on this report? I guess that could be done too using each label and
fields .Left property, and then repositioning it later?
I'm thinking of building an array of selected fields, passing that to the
OpenArgs, then setting the report column's .Visible property to true for allfields in the array. The other fields not in the array would get their
.visible property set to False. I'm thinking that the report's OnFormat
event is where this test should run.
I don't have time to mess with this today, maybe you could give that a try,or at least confirm that I'm on the right track now?


Nov 12 '05 #8

P: n/a
Hi Chuck,

Disregard my stupid question about the OpenArgs thing <blush>
It appears that Reports don't HAVE an OpenArgs ... aheh, heh, umm, ya,
heh-heh.

I'm investigating the "CreateReportControl" Function as I type this. It
looks kind of interesting...
Don

"Don Leverton" <My*****@Telus.Net> wrote in message
news:Hffmc.2872$uN4.1045@clgrps12...
Hi Chuck,

That sounds like a cool tip, if I understand the concept correctly.

We're talking about basically assigning an "unbound" control's Control
Source property to whatever value I carry forward using the OpenArgs,
correct?
I imagine that you should be able to also assign the label's caption
property somehow too?

Would you mind explaining this a little more?

Regards,
Don
Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in message
news:e7********************************@4ax.com...

Hint: Use the Ordinal field position rather then the field's name as
the control's recordsource

Example: recordset.Fields(0)
On Wed, 05 May 2004 14:00:15 GMT, "Don Leverton"
<le****************@telusplanet.net> wrote:
If I'm finally "getting this" ... you only want the colmn headings in thereport to be visible only if they were the fields that you selected in
lstFieldNames? That sounds a little tougher, but probably still not
impossible. Is it important that they be positioned exactly where you wantthem on this report? I guess that could be done too using each label andfields .Left property, and then repositioning it later?
I'm thinking of building an array of selected fields, passing that to theOpenArgs, then setting the report column's .Visible property to true
for
allfields in the array. The other fields not in the array would get their
.visible property set to False. I'm thinking that the report's OnFormat
event is where this test should run.
I don't have time to mess with this today, maybe you could give that a try,or at least confirm that I'm on the right track now?


Nov 12 '05 #9

P: n/a
Don, Chuck
looks like i have created a headache .. and hoped my explantion
of what i am trying was put forward plainly....
to try and make it clearer....
if i create a standard query from volunteer table with only two
fields, say "Mem No" and "Magazine"
and set the criteria for selection as showing 'Mem No' where
'Magazine' is 'True' ( all category fields are yes/no or true/false )
I get a list of Mem No's that meet this requirement.
If I change the field from "magazine" to "Parts", I get a list of
member no's that meet this requirement.. and so it goes on for each of
the 'categories' in the table .......
This can make for a large number of stand alone queries and
"reports" that have to be pre made ...
What i am looking for is to be able to select the category from a
list of field headings (categories) as previosly shown and for the
query / report to reflect that info.... this basically gives me one
query and one report structure to display those members who meet the
criteria for any given category (field heading)...
note: this query would be liked to 'members' table(by mem
no)to display name and contact as well..

hope this clears the fog ...
cheers john
Nov 12 '05 #10

P: n/a
Hi again...

OK... I think I have it!
Keep all of the old code... I don't recall making any changes to it
Add a command button, and put this code in it's On Click.

You Can select just one field ie "Magazine" or whatever, or you can pick
several still if you like. The report will be generated showing the selected
field name(s) along with a checkbox control to indicate a "True" value.

The report will open in design view, showing the selected field and
checkbox. Switch to Preview view. If it's what you like... print it.
When you're finished printing, close the report. DON'T bother to save it...
It simply recreates itself based on the selection.

Gotta go!
**************************************************

Private Sub cmdCreateReport_Click()
sRequerySubform

Dim rpt As Report

Dim MyCtl As Control 'Probably could have used this for various control
types.
Dim ctlTextBox As Control
Dim ctlListBox As Control
Dim ctlLabel As Control
Dim ctlCheckBox As Control
Dim varItm As Variant
Dim strField As String
Dim intDataX As Integer, intDataY As Integer
Dim intLabelX As Integer, intLabelY As Integer

Set ctlListBox = Me.lstFieldList
' Create new report with tblCustomers as its record source.
Set rpt = CreateReport
rpt.RecordSource = MySQL 'Yup same good old SQL string
rpt.Section(0).Height = 500
rpt.Section(4).Height = 300
rpt.Width = 9360 '6.5 x 1440 twips/inch

'Section(0) - Detail Section
'Section(1) - Report Header
'Section(2) - Report Footer
'Section(3) - Page Header
'Section(4) - Page Footer
'Section(5) - Group Level 1 Header
'Section(6) - Group Level 1 Footer
'Section(7) - Group Level 2 Header
'(all sizes in twips)... 1 twip = 1/1440 of an inch
'CreateReportControl(reportname, controltype[, section[, parent[,
columnname[, left[, top[, width[, height]]]]]]])
'Set up the page header section (which is pretty "Plain Vanilla")
'------------------------------------------------------------------
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acPageHeader, , , 100,
100)
ctlLabel.Caption = "Whatever you want to call this report."
ctlLabel.Height = 720
ctlLabel.Width = 7000
ctlLabel.FontName = "Times New Roman"
ctlLabel.FontSize = 18
Set MyCtl = CreateReportControl(rpt.Name, acLine, acPageHeader, , , 0, 840,
9360)

'----------------------- Create the "fixed" label / textbox
fields --------------------------

Set ctlTextBox = CreateReportControl(rpt.Name, acTextBox, acDetail, ,
"FirstName", 1000, 100)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acDetail,
ctlTextBox.Name, "FirstName", 100, 100)

Set ctlTextBox = CreateReportControl(rpt.Name, acTextBox, acDetail, ,
"LastName", 1000, 400)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acDetail,
ctlTextBox.Name, "LastName", 100, 400)

'----------------------- Create the "variable" label / textbox
fields --------------------------
' Initialize positioning values for the first pair of variable controls.
intLabelX = 2880
intLabelY = 100
intDataX = 3880
intDataY = 100
If ctlListBox.ItemsSelected.Count > 0 Then

For Each varItm In ctlListBox.ItemsSelected
strField = ctlListBox.ItemData(varItm)
Set ctlCheckBox = CreateReportControl(rpt.Name, acCheckBox, , ,
strField, intDataX, intDataY)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, ,
ctlCheckBox.Name, strField, intLabelX, intLabelY)

intLabelX = intLabelX + 1440 'each label is 1" apart
'intLabelY = 100
intDataX = intDataX + 1440 'each checkbox is also 1" apart
'intDataY = 100
Next varItm

End If

' Restore report
DoCmd.Restore

End Sub
************************************************** **********

"Don Leverton" <le****************@telusplanet.net> wrote in message
news:K4hmc.25419$U75.10328@edtnps89...
I'm investigating the "CreateReportControl" Function as I type this. It
looks kind of interesting...
Don

Nov 12 '05 #11

P: n/a
ja*****@arach.net.au (John young) wrote in
news:82**************************@posting.google.c om:
Don, Chuck
looks like i have created a headache .. and hoped my
explantion
of what i am trying was put forward plainly....
to try and make it clearer....
if i create a standard query from volunteer table with only
two
fields, say "Mem No" and "Magazine"
and set the criteria for selection as showing 'Mem No'
where
'Magazine' is 'True' ( all category fields are yes/no or
true/false )
I get a list of Mem No's that meet this requirement.
If I change the field from "magazine" to "Parts", I get a
list of
member no's that meet this requirement.. and so it goes on for
each of the 'categories' in the table .......
This can make for a large number of stand alone queries
and
"reports" that have to be pre made ...
What i am looking for is to be able to select the category
from a
list of field headings (categories) as previosly shown and
for the query / report to reflect that info.... this basically
gives me one query and one report structure to display those
members who meet the criteria for any given category (field
heading)...
note: this query would be liked to 'members' table(by
mem
no)to display name and contact as well..

hope this clears the fog ...
cheers john


Now I understand what you are after.

How about this approach.

Create the single report. Make the report title an unbound
textbox, called say txtTitle.

Now create an unbound form with an option group of possible
criteria, plus a command button labeled "Print it!"

you need to write a little bit of code that sets up your criteria
and passes them to the report.

The doCmd.openReport <<where clause>>
allows setting the criteria to the report as a filter.

So you need to code for each option of the group.

Since you are using boolean fields, the statements are simple.

Private sub PrintIt_Click()
Dim stWhereClause as string
Public stRptTitle as string
..
' the real name of your option group replaces ReportOptions.
Select Case ReportOptions
case 1
stWhereclause = "Magazine"
stReportTitle = "Magazine Subscribers"
case 2
stWhereClause = "Parts"
stReportTitle = "List of PArts People"
end select.

Docmd.Openreport "MyReport",acPreview",,stwhereclause

End Sub

Now for the final bit. Open your report in design mode and open
the Report Header's Format event.

Add the statement
me.txtTitle = stReportTitle
Bob Quintal


Nov 12 '05 #12

P: n/a
Don..
sorry for delay in reply ....
thanks for the help to date and all works well as you have
indicated.... I have tried to join two tables to give me a complete
query with name and phone , email as a field to display in report
..... and this did not like it as it came up with an error saying it
didnt like the join .......
I then created another table to use as a testing table and included
name phone email as field names along with the current yes/no fields..
these fields appear in the sub form correctly after a slight
redesign of form but i can not get the report to display any of the
text fields..... have tried with name field and it produces "#name" in
space allocated for it ....
have changed the obvious parts of code criteria to reflect table
field names from source table but the report does not print preview
correctly ..
(only using "name" as testing procedure)
(originally i have two tables table 1 has name and contact
details and table 2 has category fields [yes/no] and tables are
joined by memnumber)
Nov 12 '05 #13

P: n/a
Hi John

See comments inserted below

"John young" <ja*****@arach.net.au> wrote in message
news:82*************************@posting.google.co m...
Don..
sorry for delay in reply ....
thanks for the help to date and all works well as you have
indicated.... --------------------------------------------------
So you are now doing the CreateReport thing? I'll assume so.
I have tried to join two tables to give me a complete
query with name and phone , email as a field to display in report
.... and this did not like it as it came up with an error saying it
didnt like the join ....... -----------------------------
You created a new query or modified the SQL statement that builds the
"query" in code?
1.) Un-comment the 'Debug.Print MySQL line (by removing the apostrophe) ,
and run the code ...
2.) Open the Immediate window and copy the resulting SQL string
3.) Design a new query, then switch to it's SQL View.
4.) Paste in the SQL string that you've copied from the Immediate Window
5.) Attempt to switch to the query's Datasheet View.
6.) If there is a problem with the SQL, Access will display a somewhat
informative message, and should even highlight the offending portion of the
SQL string for you.
7.) Change whatever you think needs to be changed, and test the query again
by attempting to switch to Datasheet View.
8.) If / when you get to the point that the Datasheet view is displayed
without errors, change the "MySQL" string to match, and run the code again.
9.) If everything works, re-comment the 'Debug.Print MySQL line
I then created another table to use as a testing table and included
name phone email as field names along with the current yes/no fields.. -----------------------------
I'd advise you not to use "Name" as a fieldname because "Name" is a reserved
word ... use "MemberName" (or something similar) instead.
these fields appear in the sub form correctly after a slight
redesign of form but i can not get the report to display any of the
text fields..... -----------------------------
The textbox and label will not appear in the report unless you specifically
create them in the CreateReport code ... remember, we are creating a report
in code, but not saving it ... right?

You will have to do this kind of operation for EACH textbox / label that
you want to see on the report. You will also have to decide if this is going
to be a "fixed" or "variable" control combination and calculate the position
(in twips) of where you would like each control to be placed.

Set ctlTextBox = CreateReportControl(rpt.Name, acTextBox, acDetail, ,
"FirstName", 1000, 100)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acDetail,
ctlTextBox.Name, "FirstName", 100, 100)
have tried with name field and it produces "#name" in space allocated for it ....
have changed the obvious parts of code criteria to reflect table
field names from source table but the report does not print preview
correctly ..
(only using "name" as testing procedure)
(originally i have two tables table 1 has name and contact
details and table 2 has category fields [yes/no] and tables are
joined by memnumber)


If ALL of the above is still not succesful, I'd be willing to have a look at
your work directly ... if you'd like.
If so, please "zip" up a copy containing some sample data, (use fake names
if privacy is a concern) and e-mail it to me. I'm sure you can figure out my
e-mail address from my signature. Please don't send the file as an MDB,
because Outlook will reject it as "potentially hostile". If you can't "zip"
it, you'll have to change the extension from MDB to something else like .TXT
or .DON
--
HTH,
Don Leverton
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
Nov 12 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.