Advance Report in a form

[radiaz via AccessMonster.com]

I created a MS Database and everything works good except I'd like to enhance
the form where I keep all the reports.
Currently, each report,three different reports have a combo box and its
respective command button that says "Preview Report" When the user selects a
criteria from the combo box and clicks the "preview report" button, a report
pops up containing information on the selected criteria. ( One report at a
time)
SO, If you look at the form you will see three different combo boxes follow
by their respective command buttons. Everything works fine.
This is what I'd like to do, but I don't know how to do it.
I'd like to have three combo boxes (combo 1 containing Type information,
combo 2 containing function information, and combo 3-4 containing date ranges
- Row Source Type Table/Query) and only "one" command button. When a user
goes to the report form, I want the combo boxes to default to "ALL" so if the
"Preview Report" command button is clicked and nothing is selected from the
combo boxes, that person will get a report containing all the records that
are in the database.
If the user selects a criteria from either combo box 1 or combo box 2 or
combo box 3-4 and clicks the "Preview Report" command button, then he/she
will get a report based on the selected criteria. OR If the user then selects
criteria from all (combo 1, 2, 3), then, he she will get a report based on
the criteria selected on those three boxes.
Am I making any sense? I already have all the queries and they work well.
This might involve some visual basic coding and I'm not too familiar with it.
I'm learning though. Can you please help me how to code that particular
"command button"

Please I'd appreciate any help you can give me.

Rita

 

[strive4peace]

Hi Rita

Report Filters

It would be best to build a filter string for the report (as
opposed to imbedding a parameter in a query)--in this way,
you can use comboboxes and listboxes to make it easier for
the user to choose criteria and you can ignore criteria when
it has not been specified...

here is an example that tests criteria and builds a filter
string to use as a parameter in OpenReport

assuming you are in the code behind the ReportMenu form...

'---------------------------------------

'tell Access you are going to create a variable to hold text
dim mFilter as string

'initialize the variable
mFilter = ""

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text
& "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that
and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname]= #" &
me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
'if we alread have criteria, add AND to specify that
and more
if mfilter <> "" then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " &
me.controlname_for_number
end if


if len(mfilter) > 0 then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif
'---------------------------------------

I have used
TextFieldname to show how text needs to be delimited - with
single quote marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is
filled out.
If it is, we are going to see if we first need to add AND
(if the filter string already says something)
Then we are going to add the criteria for that filter


make sure that the referenced fields are in the underlying
recordset for the report.
Since a filter is applied on the recordset, they do not have
to be on the report object (unlike a form)

For a Date Range, you would do:

'---------------------------------------

If not IsNull(me.date1_controlname ) Then
mFilter = mFilter & "[DateFieldname]>= #" &
me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
'if we alread have criteria, add AND to specify that
and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname] <= #" &
me.controlname_for_date2 & "#"
end if
'---------------------------------------
***

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'---------------------------------------
if len(trim(nz(me.filter,""))) > 0 then
me.Label_Criteria.Caption = me.filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'---------------------------------------


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

 

[radiaz via AccessMonster.com]

Thanks Crystal for your response.

I tested one control and it worked fine. When I test another control I get an
error and when I debug it goes to this highlighted section "DoCmd.OpenReport
"rptmama1", acViewPreview, , mFilter"

This is the code I have so far,

Private Sub Command19_Click()

Dim mFilter As String

mFilter = ""

If Not IsNull(Me.cboType) Then
mFilter = "[tblStatutes.Type]= '" & Me.cboType & "'"
End If

If Not IsNull(Me.cboFunction) Then

If mFilter <> "" Then mFilter = mFilter & " AND "
mFilter = mFilter & "[tblStatutes.Function_Type]= '" & Me.cboFunction &
"'"
End If

If Len(mFilter) > 0 Then
DoCmd.OpenReport "rptmama1", acViewPreview, , mFilter
Else
DoCmd.OpenReport "rptmama1", acViewPreview
End If

One thing I noticed is that if a user selects criteria from cboType and
cbofunction and lets say there are no match in the database for that selected
criteria, nothing comes up and I get an error. I'd like for the user to
select from cboType, for example "Policy" and cboFunction "Right of Way", in
this case if you look at the sample below, the person will get 1 record
"index 05" but what if the person selects from cboType, for example "Policy"
and cboFunction "Environmental Manager" then there are no matching records
that meet that criteria, right? If that happens, can I just get instead all
the records with Type "Policy"? In this case a report will show having 4
records. This is what I'm trying to accomplish. In addition if the user does
not select anything from the combo boxes and clicks the command button, I'd
like for he/she to be able to get the same report to open up but in this case
to show all the records.

Index Name Type Function
01 " Policy Survey and Mapping
02 "" Policy Survey and Mapping
03 "" Policy Survey and Mapping
04 """ Procedure Environmental Manager
05 "" Policy Right Away

Another thing the combo boxes are being populated from the tables tblType and
tblFunction_Type through its rowsources "Table/Query"
I'd like to add the word "All" to the combo boxes and make that the default.
So when a user opens up the report form, the combo boxes will show "ALL". If
the user clicks on the command button, then he/she will get all records. I
he/she starts selecting criteria from the combo boxes, then he/she will be
narrowing their search.

Is that possible?

Thanks again

Hi Rita

Report Filters

It would be best to build a filter string for the report (as
opposed to imbedding a parameter in a query)--in this way,
you can use comboboxes and listboxes to make it easier for
the user to choose criteria and you can ignore criteria when
it has not been specified...

here is an example that tests criteria and builds a filter
string to use as a parameter in OpenReport

assuming you are in the code behind the ReportMenu form...

'---------------------------------------

'tell Access you are going to create a variable to hold text
dim mFilter as string

'initialize the variable
mFilter = ""

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text
& "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that
and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname]= #" &
me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
'if we alread have criteria, add AND to specify that
and more
if mfilter <> "" then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " &
me.controlname_for_number
end if

if len(mfilter) > 0 then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif
'---------------------------------------

I have used
TextFieldname to show how text needs to be delimited - with
single quote marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is
filled out.
If it is, we are going to see if we first need to add AND
(if the filter string already says something)
Then we are going to add the criteria for that filter

make sure that the referenced fields are in the underlying
recordset for the report.
Since a filter is applied on the recordset, they do not have
to be on the report object (unlike a form)

For a Date Range, you would do:

'---------------------------------------

If not IsNull(me.date1_controlname ) Then
mFilter = mFilter & "[DateFieldname]>= #" &
me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
'if we alread have criteria, add AND to specify that
and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname] <= #" &
me.controlname_for_date2 & "#"
end if
'---------------------------------------
***

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'---------------------------------------
if len(trim(nz(me.filter,""))) > 0 then
me.Label_Criteria.Caption = me.filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'---------------------------------------

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

I created a MS Database and everything works good except I'd like to enhance
the form where I keep all the reports.

[quoted text clipped - 26 lines]

Rita

 

[strive4peace]

put this code behind your report

'-------------
Private Sub Report_NoData(Cancel As Integer)
MsgBox "Report Has No Data", , "No Data"
Cancel = True
End Sub
'--------------

This will cause an error flag in the calling routine after
you see the msgbox if there is no data -- for now, just
ignore it. Once we get the other problem resolved, I will
tell you how to handle that.

this
mFilter = "[tblStatutes.Type]= '" & Me.cboType & "'"
needs to change to this
mFilter = "[Type]= '" & Me.cboType & "'"

likewise, this
mFilter = mFilter & "[tblStatutes.Function_Type]= '" &
Me.cboFunction & "'"
to this
mFilter = mFilter & "[Function_Type]= '" & Me.cboFunction & "'"

IF you indeed did have 2 fields of the same name on the
report and needed to preface them with the tablename to
distinguish them, you would bracket the tablename and
bracket the fieldname

[tblStatutes].[Function_Type]

If you have no spaces or special characters, the brackets
aren't necessary...

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Thanks Crystal for your response.

I tested one control and it worked fine. When I test another control I get an
error and when I debug it goes to this highlighted section "DoCmd.OpenReport
"rptmama1", acViewPreview, , mFilter"

This is the code I have so far,

Private Sub Command19_Click()

Dim mFilter As String

mFilter = ""

If Not IsNull(Me.cboType) Then
mFilter = "[tblStatutes.Type]= '" & Me.cboType & "'"
End If

If Not IsNull(Me.cboFunction) Then

If mFilter <> "" Then mFilter = mFilter & " AND "
mFilter = mFilter & "[tblStatutes.Function_Type]= '" & Me.cboFunction &
"'"
End If

If Len(mFilter) > 0 Then
DoCmd.OpenReport "rptmama1", acViewPreview, , mFilter
Else
DoCmd.OpenReport "rptmama1", acViewPreview
End If

One thing I noticed is that if a user selects criteria from cboType and
cbofunction and lets say there are no match in the database for that selected
criteria, nothing comes up and I get an error. I'd like for the user to
select from cboType, for example "Policy" and cboFunction "Right of Way", in
this case if you look at the sample below, the person will get 1 record
"index 05" but what if the person selects from cboType, for example "Policy"
and cboFunction "Environmental Manager" then there are no matching records
that meet that criteria, right? If that happens, can I just get instead all
the records with Type "Policy"? In this case a report will show having 4
records. This is what I'm trying to accomplish. In addition if the user does
not select anything from the combo boxes and clicks the command button, I'd
like for he/she to be able to get the same report to open up but in this case
to show all the records.

Index Name Type Function
01 " Policy Survey and Mapping
02 "" Policy Survey and Mapping
03 "" Policy Survey and Mapping
04 """ Procedure Environmental Manager
05 "" Policy Right Away

Another thing the combo boxes are being populated from the tables tblType and
tblFunction_Type through its rowsources "Table/Query"
I'd like to add the word "All" to the combo boxes and make that the default.
So when a user opens up the report form, the combo boxes will show "ALL". If
the user clicks on the command button, then he/she will get all records. I
he/she starts selecting criteria from the combo boxes, then he/she will be
narrowing their search.

Is that possible?

Thanks again

Hi Rita

Report Filters

It would be best to build a filter string for the report (as
opposed to imbedding a parameter in a query)--in this way,
you can use comboboxes and listboxes to make it easier for
the user to choose criteria and you can ignore criteria when
it has not been specified...

here is an example that tests criteria and builds a filter
string to use as a parameter in OpenReport

assuming you are in the code behind the ReportMenu form...

'---------------------------------------

'tell Access you are going to create a variable to hold text
dim mFilter as string

'initialize the variable
mFilter = ""

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text
& "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that
and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname]= #" &
me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
'if we alread have criteria, add AND to specify that
and more
if mfilter <> "" then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " &
me.controlname_for_number
end if

if len(mfilter) > 0 then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif
'---------------------------------------

I have used
TextFieldname to show how text needs to be delimited - with
single quote marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is
filled out.
If it is, we are going to see if we first need to add AND
(if the filter string already says something)
Then we are going to add the criteria for that filter

make sure that the referenced fields are in the underlying
recordset for the report.
Since a filter is applied on the recordset, they do not have
to be on the report object (unlike a form)

For a Date Range, you would do:

'---------------------------------------

If not IsNull(me.date1_controlname ) Then
mFilter = mFilter & "[DateFieldname]>= #" &
me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
'if we alread have criteria, add AND to specify that
and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname] <= #" &
me.controlname_for_date2 & "#"
end if
'---------------------------------------
***

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'---------------------------------------
if len(trim(nz(me.filter,""))) > 0 then
me.Label_Criteria.Caption = me.filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'---------------------------------------

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

I created a MS Database and everything works good except I'd like to enhance
the form where I keep all the reports.

[quoted text clipped - 26 lines]

Rita

 

[radiaz via AccessMonster.com]

Crystal,

I already had this on the "On No Data" Report,
Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ErrorHandler

MsgBox "There are currently no records", vbOKOnly, _
"Title of Message Box"

Cancel = True

ExitHandler:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume ExitHandler

End Sub

I did have similar field names in the report. I'm pulling information from
two tables.
I changed the code to this,

Private Sub Command19_Click()

Dim mFilter As String

'initialize the variable
mFilter = ""


If Not IsNull(Me.cboType) Then
mFilter = "[tblStatutes].Type = '" & Me.cboType & "'"
End If

If Not IsNull(Me.cboFunction) Then

If mFilter <> "" Then mFilter = mFilter & " AND "
mFilter = mFilter & "[tblStatutes].[Function_Type]= '" & Me.cboFunction &
"'"
End If


If Len(mFilter) > 0 Then
DoCmd.OpenReport "rptmama1", acViewPreview, , mFilter
Else
DoCmd.OpenReport "rptmama1", acViewPreview
End If

Again, If I select from one cbo box a criteria, it works. but if I select the
same criteria again and then select another criteria in another cbo box, and
there are no matching records, I get the above error " MsgBox "There are
currently no records", vbOKOnly, _
"Title of Message Box"

But that's not true. There are records. I should still get the records from
the first selection. When I close the small error window, I get a " run time
error 2501, the open report action was canceled, end- debug-help.
If I click on the debug, it takes me to "DoCmd.OpenReport "rptmamatest",
acViewPreview, , mFilter"

Am I confusing you? I'm sorry!

Appreciate your responses,

Rita

put this code behind your report

'-------------
Private Sub Report_NoData(Cancel As Integer)
MsgBox "Report Has No Data", , "No Data"
Cancel = True
End Sub
'--------------

This will cause an error flag in the calling routine after
you see the msgbox if there is no data -- for now, just
ignore it. Once we get the other problem resolved, I will
tell you how to handle that.

this
mFilter = "[tblStatutes.Type]= '" & Me.cboType & "'"
needs to change to this
mFilter = "[Type]= '" & Me.cboType & "'"

likewise, this
mFilter = mFilter & "[tblStatutes.Function_Type]= '" &
Me.cboFunction & "'"
to this
mFilter = mFilter & "[Function_Type]= '" & Me.cboFunction & "'"

IF you indeed did have 2 fields of the same name on the
report and needed to preface them with the tablename to
distinguish them, you would bracket the tablename and
bracket the fieldname

[tblStatutes].[Function_Type]

If you have no spaces or special characters, the brackets
aren't necessary...

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Thanks Crystal for your response.

[quoted text clipped - 186 lines]

 

[strive4peace]

Hi Rita,

change
[tblStatutes].[Function_Type]
to
[Function_Type]

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Crystal,

I already had this on the "On No Data" Report,
Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ErrorHandler

MsgBox "There are currently no records", vbOKOnly, _
"Title of Message Box"

Cancel = True

ExitHandler:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume ExitHandler

End Sub

I did have similar field names in the report. I'm pulling information from
two tables.
I changed the code to this,

Private Sub Command19_Click()

Dim mFilter As String

'initialize the variable
mFilter = ""


If Not IsNull(Me.cboType) Then
mFilter = "[tblStatutes].Type = '" & Me.cboType & "'"
End If

If Not IsNull(Me.cboFunction) Then

If mFilter <> "" Then mFilter = mFilter & " AND "
mFilter = mFilter & "[tblStatutes].[Function_Type]= '" & Me.cboFunction &
"'"
End If


If Len(mFilter) > 0 Then
DoCmd.OpenReport "rptmama1", acViewPreview, , mFilter
Else
DoCmd.OpenReport "rptmama1", acViewPreview
End If

Again, If I select from one cbo box a criteria, it works. but if I select the
same criteria again and then select another criteria in another cbo box, and
there are no matching records, I get the above error " MsgBox "There are
currently no records", vbOKOnly, _
"Title of Message Box"

But that's not true. There are records. I should still get the records from
the first selection. When I close the small error window, I get a " run time
error 2501, the open report action was canceled, end- debug-help.
If I click on the debug, it takes me to "DoCmd.OpenReport "rptmamatest",
acViewPreview, , mFilter"

Am I confusing you? I'm sorry!

Appreciate your responses,

Rita

put this code behind your report

'-------------
Private Sub Report_NoData(Cancel As Integer)
MsgBox "Report Has No Data", , "No Data"
Cancel = True
End Sub
'--------------

This will cause an error flag in the calling routine after
you see the msgbox if there is no data -- for now, just
ignore it. Once we get the other problem resolved, I will
tell you how to handle that.

this
mFilter = "[tblStatutes.Type]= '" & Me.cboType & "'"
needs to change to this
mFilter = "[Type]= '" & Me.cboType & "'"

likewise, this
mFilter = mFilter & "[tblStatutes.Function_Type]= '" &
Me.cboFunction & "'"
to this
mFilter = mFilter & "[Function_Type]= '" & Me.cboFunction & "'"

IF you indeed did have 2 fields of the same name on the
report and needed to preface them with the tablename to
distinguish them, you would bracket the tablename and
bracket the fieldname

[tblStatutes].[Function_Type]

If you have no spaces or special characters, the brackets
aren't necessary...

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Thanks Crystal for your response.

[quoted text clipped - 186 lines]

Rita

 

[radiaz via AccessMonster.com]

I currently have four tables in my DB, tblType (It contains types like
"Manual",Handbook","Policy". One column PK. I has no ID Column.
tblType

Type (PK) ----"Manual",Handbook","Policy".

Same thing in the tblFunction. It only has one column, a text column with
information about Functions like "Planning & Conceptional Engineering",
"Preliminary Estimates", "Survey & Mapping". I also have two other tables but
those are not important right now.

The tblFunction table looks like this;

FunctionTYpe (PK)

and tblDocument
Document_Id (PK)
Document_Name
Document_Type
Document_Function
Abstract

and tblReferences

References_ID
Document_ID
Type
Function
Title
Notes
Abstract

Anyway I tried to do as you explained earlier, but is not working. If I leave
it like [Type] or [Function] , I get an error because I'm pulling the same
fieldnames from different tables. tblStatutes and tblReference both have a
field call Type and Function, so I have to specify the tables too.

Here is the coding ,

Private Sub Command19_Click()

Dim mFilter As String

'initialize the variable
mFilter = ""


If Not IsNull(Me.cboType) Then
mFilter = "[tblStatutes].[Type] = '" & Me.cboType & "'"
End If

If Not IsNull(Me.cboFunction) Then

If mFilter <> "" Then mFilter = mFilter & " AND "
mFilter = mFilter & "[tblStatute].[Function_Type]= '" & Me.cboFunction &
"'"
End If

If not IsNull(me.txtDate1) Then
mFilter = mFilter & "[Last_Date_Reviewed]>= #" &
me.txtDate1 & "#"
end if

If not IsNull(me.txtDate2) Then

if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[Last_Reviewed_Date] <= #" &
me.txtDate2 & "#"
end if


If Len(mFilter) > 0 Then
DoCmd.OpenReport "rptmama1", acViewPreview, , mFilter
Else
DoCmd.OpenReport "rptmama1", acViewPreview
End If
End Sub


This is what I want to achieve
When an user opens up the form frmReport, he/she will see

This

cboType ---when opened - "All" "Manual",Handbook","Policy".

cboFunction ---when opened--
"All" "Planning & Conceptional Engineering", "Preliminary Estimates",
"Survey & Mapping".

txtDate1 --- txtDate2 ---If used, user will enter a "date" or a "date
between date"

"Preview Report" command button - click event

This are the things that could happen;

A user might just click the "Preview Report" command button without selecting
any criteria from the cbo boxes-- if that happens, i'd like for Access to
open up "rptmama1"
The report will show all the records that are in the database.

If an user selects criteria from cboType, for example "Manual" and clicks the
"Preview Report" command button, then he/she will get the "rptmama1" to show
all the records that have "Manual" as a type.

If an user selects criteria from cbotype for example "Manual" and then
selects criteria from cbofunction for example "Planning & Conceptional
Engineering", I'd like for access to find the records that meet that criteria.
In case there are no matches, I want Access to instead give me all the
records that have "Manual" as a type and ViceVersa if there no records with
"Manual" as a type but then there are records with "Planning & Conceptional
Engineering" as a function, then Access should open up rptmama1 and show all
the recors with "Planning & Conceptional Engineering" on it. ( It sounds like
I need to incorporate "OR" somewhere in the code--- Give me this or give me
that

The above code works fine if I test

--one combo box and Access finds records with whatever chosen criteria, if
not I get the "On No Data error event" on the report, and then I get the
"error runtime error 2501 and if I click debug, Access takes me to "DoCmd.
OpenReport "rptmama1", acViewPreview, , mFilter"


--two combo boxes and Access finds records with whatever chosen criteria on
the two combo boxes, If there are no records found then I get the "On No Data
error event" on the report, and then I get the "error runtime error 2501 and
if I click debug, Access takes me to "DoCmd.OpenReport "rptmama1",
acViewPreview, , mFilter"

---If a put in 3/01/2006 and 6/03/2006 and leave the other cbo boxes alone,
when the command button is clicked, I get an error

The funny thing is that this does not look complicated at all, I just don't
know what I'm doing wrong.

Sorry for all the confusion.


Rita

Hi Rita,

change
[tblStatutes].[Function_Type]
to
[Function_Type]

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Crystal,

[quoted text clipped - 114 lines]

 

[strive4peace]

It is best to create an Autonumber field in each table and
call it SomethingID

TYPE is a reserved word and should not be used for a
fieldname -- you can qualify it like DocType. You should
use DocTypeID to relate tables, not a text field.

If you have 2 fields with the same names from different
tables and you need to distinginuish them, it is best to
give them an ALIAS like

SELECT ..., tblStatutes.DocTypeID as statDocTypeID, ...

then, make the Name property of the control the same as the
ControlSource

To store an ID and show the text, here is what you can do in
the combobox:


Name --> statDocTypeID
ControlSource --> statDocTypeID
RowSource --> SELECT DocTypeID, DocType FROM Tablename ORDER
BY DocType
BoundColumn --> 1
ColumnCount --> 2
columnWidths --> 0;2 (etc for however many columns you have
-- the ID column will be hidden)
ListWidth --> 2 (should add up to the sum of the column widths)

statDocTypeID will be stored in the form RecordSource while
showing you names from another table...

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

I currently have four tables in my DB, tblType (It contains types like
"Manual",Handbook","Policy". One column PK. I has no ID Column.
tblType

Type (PK) ----"Manual",Handbook","Policy".

Same thing in the tblFunction. It only has one column, a text column with
information about Functions like "Planning & Conceptional Engineering",
"Preliminary Estimates", "Survey & Mapping". I also have two other tables but
those are not important right now.

The tblFunction table looks like this;

FunctionTYpe (PK)

and tblDocument
Document_Id (PK)
Document_Name
Document_Type
Document_Function
Abstract

and tblReferences

References_ID
Document_ID
Type
Function
Title
Notes
Abstract

Anyway I tried to do as you explained earlier, but is not working. If I leave
it like [Type] or [Function] , I get an error because I'm pulling the same
fieldnames from different tables. tblStatutes and tblReference both have a
field call Type and Function, so I have to specify the tables too.

Here is the coding ,

Private Sub Command19_Click()

Dim mFilter As String

'initialize the variable
mFilter = ""


If Not IsNull(Me.cboType) Then
mFilter = "[tblStatutes].[Type] = '" & Me.cboType & "'"
End If

If Not IsNull(Me.cboFunction) Then

If mFilter <> "" Then mFilter = mFilter & " AND "
mFilter = mFilter & "[tblStatute].[Function_Type]= '" & Me.cboFunction &
"'"
End If

If not IsNull(me.txtDate1) Then
mFilter = mFilter & "[Last_Date_Reviewed]>= #" &
me.txtDate1 & "#"
end if

If not IsNull(me.txtDate2) Then

if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[Last_Reviewed_Date] <= #" &
me.txtDate2 & "#"
end if


If Len(mFilter) > 0 Then
DoCmd.OpenReport "rptmama1", acViewPreview, , mFilter
Else
DoCmd.OpenReport "rptmama1", acViewPreview
End If
End Sub


This is what I want to achieve
When an user opens up the form frmReport, he/she will see

This

cboType ---when opened - "All" "Manual",Handbook","Policy".

cboFunction ---when opened--
"All" "Planning & Conceptional Engineering", "Preliminary Estimates",
"Survey & Mapping".

txtDate1 --- txtDate2 ---If used, user will enter a "date" or a "date
between date"

"Preview Report" command button - click event

This are the things that could happen;

A user might just click the "Preview Report" command button without selecting
any criteria from the cbo boxes-- if that happens, i'd like for Access to
open up "rptmama1"
The report will show all the records that are in the database.

If an user selects criteria from cboType, for example "Manual" and clicks the
"Preview Report" command button, then he/she will get the "rptmama1" to show
all the records that have "Manual" as a type.

If an user selects criteria from cbotype for example "Manual" and then
selects criteria from cbofunction for example "Planning & Conceptional
Engineering", I'd like for access to find the records that meet that criteria.
In case there are no matches, I want Access to instead give me all the
records that have "Manual" as a type and ViceVersa if there no records with
"Manual" as a type but then there are records with "Planning & Conceptional
Engineering" as a function, then Access should open up rptmama1 and show all
the recors with "Planning & Conceptional Engineering" on it. ( It sounds like
I need to incorporate "OR" somewhere in the code--- Give me this or give me
that

The above code works fine if I test

--one combo box and Access finds records with whatever chosen criteria, if
not I get the "On No Data error event" on the report, and then I get the
"error runtime error 2501 and if I click debug, Access takes me to "DoCmd.
OpenReport "rptmama1", acViewPreview, , mFilter"


--two combo boxes and Access finds records with whatever chosen criteria on
the two combo boxes, If there are no records found then I get the "On No Data
error event" on the report, and then I get the "error runtime error 2501 and
if I click debug, Access takes me to "DoCmd.OpenReport "rptmama1",
acViewPreview, , mFilter"

---If a put in 3/01/2006 and 6/03/2006 and leave the other cbo boxes alone,
when the command button is clicked, I get an error

The funny thing is that this does not look complicated at all, I just don't
know what I'm doing wrong.

Sorry for all the confusion.


Rita

Hi Rita,

change
[tblStatutes].[Function_Type]
to
[Function_Type]

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Crystal,

[quoted text clipped - 114 lines]

Rita