REPOST - Combo box selection to give Specific Information to Query

[LeslieJ via AccessMonster.com]

Hello all,

My deadline is Friday, and this is one of the last problems I have - please
help!!!

I have a parameter form called frmDocbyDept that filters for the report
Documents by Department. On frmDocbyDept there is a combo box called cboDept,

an invisible text box called txtDept (after the combo box is updated, it
sends the dept id to txtDept which is what the query searches for), and an ok
and cancel button. cboDept consists of fields from the table Department
Holders, and the first column is hidden that shows the identifier, and the
second column shows the department name. When the ok button is clicked the
report is opened up and shows only the documents for the department that was
selected.

There is one department, Logistics, that is actually made up of 4 other
departments now. And documents will now be associated with all five
identifiers. What I would like to be able to do is this:
When Logistics is selected in the combo box, I would like for the text box to
say to search for the five department id's in the query. However, when I
make the text box say 58 or 30 or 33 or 35 or 36 (the five department id's),
the query does not correctly interpret the information and gives me a blank
report.

Every other department is straight forward, and works as expected.

I would appreciate it if anyone has any ideas on how to make the text box
correctly transfer the information over to the query. Thank you in advance.

The SQL for the query is as follows:

SELECT [Document Information].[Document Identifier], [Document Information].
[Document Number], [Document Version Information].[Document Revision],
[Document Version Information].[Document Amendment], [Document Version
Information].[Document Name], [Document Holder Information].[Department
Holder Identifier], [Document Holder Information].Department, [Document
Distribution Information].[Distribution Number], [Document Version
Information].[Document Status Type], [Document Version Information].[Initial
version]
FROM (([Document Information] INNER JOIN [Document Version Information] ON
[Document Information].[Document Identifier] = [Document Version Information].

[Document ID]) INNER JOIN [Document Distribution Information] ON [Document
Version Information].[Version Identifier] = [Document Distribution
Information].[Document Version ID]) INNER JOIN [Document Holder Information]
ON [Document Distribution Information].[Department Holders Abbreviations] =
[Document Holder Information].[Department Abbreviation]
WHERE ((([Document Holder Information].[Department Holder Identifier])=[Forms]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[Klatuu]

I would suggest you change your approach to how you are filtering the
report. Rather than the query filtering on the value of a hidden text box on
the form, use the Where argument of the OpenReport method to to do the
filtering. In this case it is necessary because of the difference in how you
have to do the filtering because of the one department.

So, remove the filtering from the report's record source. Then in the
command button's Click event where you open the report set the criteria:


Dim strWhere As String

If Me.cboDept.Column(1) = "Logistics" Then
strWhere = "Document Holder Information].[Department Holder
Identifier] IN( 58, 30, 33, 35, 36)"
Else
strWhere = "Document Holder Information].[Department Holder
Identifier] = " & Me.cboDept
End If

Docmd.OpenReport "MyReportName", , , strWhere
--
Dave Hargis, Microsoft Access MVP

Hello all,

My deadline is Friday, and this is one of the last problems I have - please
help!!!

I have a parameter form called frmDocbyDept that filters for the report
Documents by Department. On frmDocbyDept there is a combo box called cboDept,

an invisible text box called txtDept (after the combo box is updated, it
sends the dept id to txtDept which is what the query searches for), and an ok
and cancel button. cboDept consists of fields from the table Department
Holders, and the first column is hidden that shows the identifier, and the
second column shows the department name. When the ok button is clicked the
report is opened up and shows only the documents for the department that was
selected.

There is one department, Logistics, that is actually made up of 4 other
departments now. And documents will now be associated with all five
identifiers. What I would like to be able to do is this:
When Logistics is selected in the combo box, I would like for the text box to
say to search for the five department id's in the query. However, when I
make the text box say 58 or 30 or 33 or 35 or 36 (the five department id's),
the query does not correctly interpret the information and gives me a blank
report.

Every other department is straight forward, and works as expected.

I would appreciate it if anyone has any ideas on how to make the text box
correctly transfer the information over to the query. Thank you in advance.

The SQL for the query is as follows:

SELECT [Document Information].[Document Identifier], [Document Information].
[Document Number], [Document Version Information].[Document Revision],
[Document Version Information].[Document Amendment], [Document Version
Information].[Document Name], [Document Holder Information].[Department
Holder Identifier], [Document Holder Information].Department, [Document
Distribution Information].[Distribution Number], [Document Version
Information].[Document Status Type], [Document Version Information].[Initial
version]
FROM (([Document Information] INNER JOIN [Document Version Information] ON
[Document Information].[Document Identifier] = [Document Version Information].

[Document ID]) INNER JOIN [Document Distribution Information] ON [Document
Version Information].[Version Identifier] = [Document Distribution
Information].[Document Version ID]) INNER JOIN [Document Holder Information]
ON [Document Distribution Information].[Department Holders Abbreviations] =
[Document Holder Information].[Department Abbreviation]
WHERE ((([Document Holder Information].[Department Holder Identifier])=[Forms]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[LeslieJ via AccessMonster.com]

Hi Klatuu!

Thank you for your response. I was trying to figure out how to do it that
way, thank you!

However, now when I press the ok button, the report viewer opens, but no
report appears. I've tried other departments too. I have taken out the
filter in the query. All my references are correct. It compiles fine (I
know that doesn't always mean anything just letting you know :]). Do you
have any thoughts about what might be happening?

Thank you again.

I would suggest you change your approach to how you are filtering the
report. Rather than the query filtering on the value of a hidden text box on
the form, use the Where argument of the OpenReport method to to do the
filtering. In this case it is necessary because of the difference in how you
have to do the filtering because of the one department.

So, remove the filtering from the report's record source. Then in the
command button's Click event where you open the report set the criteria:

Dim strWhere As String

If Me.cboDept.Column(1) = "Logistics" Then
strWhere = "Document Holder Information].[Department Holder
Identifier] IN( 58, 30, 33, 35, 36)"
Else
strWhere = "Document Holder Information].[Department Holder
Identifier] = " & Me.cboDept
End If

Docmd.OpenReport "MyReportName", , , strWhere

Hello all,

[quoted text clipped - 48 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[Klatuu]

Not knowing your data well enough, it might be a data type issue. The code I
posted assumed the field you are filtering on is a numeric field. If that is
not correct, you would have to change syntax for text fields.

Also, I don't know for sure which column in you combo is the bound column
containing the ID. I assume it would be the first column. I also assumed
the name is in the second column of the combo. Note that there is a
confusing little fact about combo boxes. the Bound Column property is 1
based. That is when you enter 1 in the property, it referes to the first
column; however, the columns collection is 0 based, so the first column is
addressed as .Column(0)
When You address just the combo name:
=Me.MyCombo
It will return the value in the Bound column.

Not sure what your problem might be, but those are some things to look at.
--
Dave Hargis, Microsoft Access MVP

Hi Klatuu!

Thank you for your response. I was trying to figure out how to do it that
way, thank you!

However, now when I press the ok button, the report viewer opens, but no
report appears. I've tried other departments too. I have taken out the
filter in the query. All my references are correct. It compiles fine (I
know that doesn't always mean anything just letting you know :]). Do you
have any thoughts about what might be happening?

Thank you again.

I would suggest you change your approach to how you are filtering the
report. Rather than the query filtering on the value of a hidden text box on
the form, use the Where argument of the OpenReport method to to do the
filtering. In this case it is necessary because of the difference in how you
have to do the filtering because of the one department.

So, remove the filtering from the report's record source. Then in the
command button's Click event where you open the report set the criteria:

Dim strWhere As String

If Me.cboDept.Column(1) = "Logistics" Then
strWhere = "Document Holder Information].[Department Holder
Identifier] IN( 58, 30, 33, 35, 36)"
Else
strWhere = "Document Holder Information].[Department Holder
Identifier] = " & Me.cboDept
End If

Docmd.OpenReport "MyReportName", , , strWhere

Hello all,

[quoted text clipped - 48 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[LeslieJ via AccessMonster.com]

I am filtering on a numeric field - it's an autonumber field. The bound
column is the first column like you explained - the first (hidden) column is
the autonumber field, and the second column (the one that appears in the
combo box) is the name attached to the autonumber field.

I just closed out of the database and tried to run the report again. It came
up with a run time error 2585 and it highlights the row "DoCmd.OpenReport
"Documents by Department", , , strWhere"

I appreciate your help so much - thank you!

Not knowing your data well enough, it might be a data type issue. The code I
posted assumed the field you are filtering on is a numeric field. If that is
not correct, you would have to change syntax for text fields.

Also, I don't know for sure which column in you combo is the bound column
containing the ID. I assume it would be the first column. I also assumed
the name is in the second column of the combo. Note that there is a
confusing little fact about combo boxes. the Bound Column property is 1
based. That is when you enter 1 in the property, it referes to the first
column; however, the columns collection is 0 based, so the first column is
addressed as .Column(0)
When You address just the combo name:
=Me.MyCombo
It will return the value in the Bound column.

Not sure what your problem might be, but those are some things to look at.

Hi Klatuu!

[quoted text clipped - 34 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[Klatuu]

Okay this is a bit strange. Can you please post back with the code for the
event where you open the report and SQL of you report's record source, please?

Also, do you have any events firing in the report itself?
--
Dave Hargis, Microsoft Access MVP

I am filtering on a numeric field - it's an autonumber field. The bound
column is the first column like you explained - the first (hidden) column is
the autonumber field, and the second column (the one that appears in the
combo box) is the name attached to the autonumber field.

I just closed out of the database and tried to run the report again. It came
up with a run time error 2585 and it highlights the row "DoCmd.OpenReport
"Documents by Department", , , strWhere"

I appreciate your help so much - thank you!

Not knowing your data well enough, it might be a data type issue. The code I
posted assumed the field you are filtering on is a numeric field. If that is
not correct, you would have to change syntax for text fields.

Also, I don't know for sure which column in you combo is the bound column
containing the ID. I assume it would be the first column. I also assumed
the name is in the second column of the combo. Note that there is a
confusing little fact about combo boxes. the Bound Column property is 1
based. That is when you enter 1 in the property, it referes to the first
column; however, the columns collection is 0 based, so the first column is
addressed as .Column(0)
When You address just the combo name:
=Me.MyCombo
It will return the value in the Bound column.

Not sure what your problem might be, but those are some things to look at.

Hi Klatuu!

[quoted text clipped - 34 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[LeslieJ via AccessMonster.com]

The SQL for the query is:
SELECT [Document Information].[Document Identifier], [Document Information].
[Document Number], [Document Version Information].[Document Revision],
[Document Version Information].[Document Amendment], [Document Version
Information].[Document Name], [Document Holder Information].[Department
Holder Identifier], [Document Holder Information].Department, [Document
Distribution Information].[Distribution Number], [Document Version
Information].[Document Status Type], [Document Version Information].[Initial
version]
FROM (([Document Information] INNER JOIN [Document Version Information] ON
[Document Information].[Document Identifier] = [Document Version Information].
[Document ID]) INNER JOIN [Document Distribution Information] ON [Document
Version Information].[Version Identifier] = [Document Distribution
Information].[Document Version ID]) INNER JOIN [Document Holder Information]
ON [Document Distribution Information].[Department Holders Abbreviations] =
[Document Holder Information].[Department Abbreviation]
WHERE ((([Document Version Information].[Document Status Type])=1));

(The where condition in the SQL is to make sure documents returned are
current)

The Report code is:
Option Compare Database
Option Explicit
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If

End Function
Private Sub Report_Close()
DoCmd.Close acForm, "frmDocbyDept"
End Sub


Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open frmUnreturned Dialog
DoCmd.OpenForm "frmDocbyDept", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("frmDocbyDept") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False

End Sub

As you can see the only other thing that is in the code for the report, is to
close the form when the report is closed.

Thank you again for helping a green neck like me!

Okay this is a bit strange. Can you please post back with the code for the
event where you open the report and SQL of you report's record source, please?

Also, do you have any events firing in the report itself?

I am filtering on a numeric field - it's an autonumber field. The bound
column is the first column like you explained - the first (hidden) column is

[quoted text clipped - 28 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[Klatuu]

I think the code in the Open event is conflicting with the code where you are
opening the report (code which I wanted to see, but you did not include).

If you want to close the form, put your code in the report's close event
instead. that is what I do.
From one of my reports:

Private Sub Report_Close()
If CurrentProject.AllForms("frmRptProjectSummary").IsLoaded Then
DoCmd.Close acForm, "frmRptProjectSummary", acSaveNo
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

The SQL for the query is:
SELECT [Document Information].[Document Identifier], [Document Information].
[Document Number], [Document Version Information].[Document Revision],
[Document Version Information].[Document Amendment], [Document Version
Information].[Document Name], [Document Holder Information].[Department
Holder Identifier], [Document Holder Information].Department, [Document
Distribution Information].[Distribution Number], [Document Version
Information].[Document Status Type], [Document Version Information].[Initial
version]
FROM (([Document Information] INNER JOIN [Document Version Information] ON
[Document Information].[Document Identifier] = [Document Version Information].
[Document ID]) INNER JOIN [Document Distribution Information] ON [Document
Version Information].[Version Identifier] = [Document Distribution
Information].[Document Version ID]) INNER JOIN [Document Holder Information]
ON [Document Distribution Information].[Department Holders Abbreviations] =
[Document Holder Information].[Department Abbreviation]
WHERE ((([Document Version Information].[Document Status Type])=1));

(The where condition in the SQL is to make sure documents returned are
current)

The Report code is:
Option Compare Database
Option Explicit
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If

End Function
Private Sub Report_Close()
DoCmd.Close acForm, "frmDocbyDept"
End Sub


Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open frmUnreturned Dialog
DoCmd.OpenForm "frmDocbyDept", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("frmDocbyDept") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False

End Sub

As you can see the only other thing that is in the code for the report, is to
close the form when the report is closed.

Thank you again for helping a green neck like me!

Okay this is a bit strange. Can you please post back with the code for the
event where you open the report and SQL of you report's record source, please?

Also, do you have any events firing in the report itself?

I am filtering on a numeric field - it's an autonumber field. The bound
column is the first column like you explained - the first (hidden) column is

[quoted text clipped - 28 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[LeslieJ via AccessMonster.com]

I'm sorry Klatuu, I'm getting a bit muddled.

The code for opening the report is this:

Private Sub OkCmdButton_Click()
Dim strWhere As String

If Me.cbodept.Column(0) = 58 Then
strWhere = "[Document Holder Information].[Department Holder
Identifier]IN(58,30,33,35,36)"
Else
strWhere = "[Document Holder Information].[Department Holder
Identifier] = " & Me.cbodept
End If

DoCmd.OpenReport "Documents by Department", , , strWhere
End Sub

I think the code in the Open event is conflicting with the code where you are
opening the report (code which I wanted to see, but you did not include).

If you want to close the form, put your code in the report's close event
instead. that is what I do.
From one of my reports:

Private Sub Report_Close()
If CurrentProject.AllForms("frmRptProjectSummary").IsLoaded Then
DoCmd.Close acForm, "frmRptProjectSummary", acSaveNo
End If
End Sub

The SQL for the query is:
SELECT [Document Information].[Document Identifier], [Document Information].

[quoted text clipped - 69 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[Klatuu]

Is it still getting the error?

--
Dave Hargis, Microsoft Access MVP

I'm sorry Klatuu, I'm getting a bit muddled.

The code for opening the report is this:

Private Sub OkCmdButton_Click()
Dim strWhere As String

If Me.cbodept.Column(0) = 58 Then
strWhere = "[Document Holder Information].[Department Holder
Identifier]IN(58,30,33,35,36)"
Else
strWhere = "[Document Holder Information].[Department Holder
Identifier] = " & Me.cbodept
End If

DoCmd.OpenReport "Documents by Department", , , strWhere
End Sub

I think the code in the Open event is conflicting with the code where you are
opening the report (code which I wanted to see, but you did not include).

If you want to close the form, put your code in the report's close event
instead. that is what I do.
From one of my reports:

Private Sub Report_Close()
If CurrentProject.AllForms("frmRptProjectSummary").IsLoaded Then
DoCmd.Close acForm, "frmRptProjectSummary", acSaveNo
End If
End Sub

The SQL for the query is:
SELECT [Document Information].[Document Identifier], [Document Information].

[quoted text clipped - 69 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[LeslieJ via AccessMonster.com]

Yes (

Is it still getting the error?

I'm sorry Klatuu, I'm getting a bit muddled.

[quoted text clipped - 32 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[Klatuu]

Just as a test, try running the report without the strWhere. Le'ts see if we
can break it down to what is causing the problem.
--
Dave Hargis, Microsoft Access MVP

Yes (

Is it still getting the error?

I'm sorry Klatuu, I'm getting a bit muddled.

[quoted text clipped - 32 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[LeslieJ via AccessMonster.com]

I'll take out the strWhere - it still hung, no error, but nothing else either
(

Oh my goodness, I just put the strWhere back in place, and opened the form
first, instead of the report first - and it worked perfectly! It filtered
like I had expected, and it still opened all the other departments that I
asked of it.

Do you have any insight as to why it works when the form is opened first so
that I don't do this to myself in the future?

Thank you for all your help, and your patience. Really truly.

Just as a test, try running the report without the strWhere. Le'ts see if we
can break it down to what is causing the problem.

Yes (

[quoted text clipped - 5 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[Klatuu]

What form are we talking about here?
The one the opens the report, or is there another form.
Now I am the confused one.

--
Dave Hargis, Microsoft Access MVP

I'll take out the strWhere - it still hung, no error, but nothing else either
(

Oh my goodness, I just put the strWhere back in place, and opened the form
first, instead of the report first - and it worked perfectly! It filtered
like I had expected, and it still opened all the other departments that I
asked of it.

Do you have any insight as to why it works when the form is opened first so
that I don't do this to myself in the future?

Thank you for all your help, and your patience. Really truly.

Just as a test, try running the report without the strWhere. Le'ts see if we
can break it down to what is causing the problem.

Yes (

[quoted text clipped - 5 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[LeslieJ via AccessMonster.com]

Oh I'm so sorry - it's the form that opens the report.

What form are we talking about here?
The one the opens the report, or is there another form.
Now I am the confused one.

I'll take out the strWhere - it still hung, no error, but nothing else either
(

[quoted text clipped - 16 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));

 

[Klatuu]

Well, yeah. I don't understand how you were opening the report if the formt
that opens the report wasn't open?

--
Dave Hargis, Microsoft Access MVP

Oh I'm so sorry - it's the form that opens the report.

What form are we talking about here?
The one the opens the report, or is there another form.
Now I am the confused one.

I'll take out the strWhere - it still hung, no error, but nothing else either
(

[quoted text clipped - 16 lines]

![frmDocbyDept]![txtDepartment]) AND (([Document Version Information].
[Document Status Type])=1));