Slowly but surely

[maceslin]

I am slowly getting the project designed like it should be but my
current problem is as follows:

I have frmAvailReports with cascading cbo (cboAvailReports and
cboSpecifcReports) and a view report command button. Selection within
cboSpecificReports as [forms]![frmAvailReports]![cboSpecificReports]
is the parameter in qryNumberedFleet.

frmAvailReports works great as a standalone form but as soon as I drop
it as a subform into another form and execute the command button I get
a popup with [forms]![frmAvailReports]![cboSpecificReports]. Any idea
why?

Part two
I was planning on using "if then else" to run differnet queries based
on cboAvailReports on execution of command buton. I am sure there is
some way to code the button to adjust the query so I don't have to
make all these queries. Some specific code would be great

Thanks

Dave

 

[Jeff Boyce]

When you "move" that form inside another as a subform, the "address" of the
referred-to combo box is no longer:

[Forms]![frmAvailReports]![cboSpecificReports]

Instead, the second combobox in the cascade will need to refer to:

[Forms]![YourParentForm]![yourSubFormControlName].[Form]![cboAvailReports]

Check the following KB article at Microsoft.com: 209099.

I don't fully understand the second part, but it sounds like you want to
have different queries run, depending on which report is selected. ?!why!?
If you are selecting reports, don't you want the report to run? If you are
basing your report on a query, simply selecting the report seems like it
would cause its query to run.

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[maceslin]

When you "move" that form inside another as a subform, the "address" of the
referred-to combo box is no longer:

[Forms]![frmAvailReports]![cboSpecificReports]

Instead, the second combobox in the cascade will need to refer to:

[Forms]![YourParentForm]![yourSubFormControlName].[Form]![cboAvailReports]

Check the following KB article at Microsoft.com: 209099.

I don't fully understand the second part, but it sounds like you want to
have different queries run, depending on which report is selected. ?!why!?
If you are selecting reports, don't you want the report to run? If you are
basing your report on a query, simply selecting the report seems like it
would cause its query to run.

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am slowly getting the project designed like it should be but my
current problem is as follows:

I have frmAvailReports with cascading cbo (cboAvailReports and
cboSpecifcReports) and a view report command button. Selection within
cboSpecificReports as [forms]![frmAvailReports]![cboSpecificReports]
is the parameter in qryNumberedFleet.

frmAvailReports works great as a standalone form but as soon as I drop
it as a subform into another form and execute the command button I get
a popup with [forms]![frmAvailReports]![cboSpecificReports]. Any idea
why?

Part two
I was planning on using "if then else" to run differnet queries based
on cboAvailReports on execution of command buton. I am sure there is
some way to code the button to adjust the query so I don't have to
make all these queries. Some specific code would be great

Dave- Hide quoted text -

- Show quoted text -

Further explanantion for part two. Each selection from
cboAvailReports points to a different field within the query with the
data from cboSpecificReports being the parameter to search. No matter
the fields selected form the two combo boxes they will be viewed in
either a common form or report. Hope this elaborates sufficiently.

Thanks for part 1

Dave

 

[Jeff Boyce]

Not sure if I'm visualizing your situation correctly...

From what I'm inferring, it sounds like you could dynamically build a SQL
statement for the underlying query, then use that as the source for the
?generic? report.

Regards

Jeff Boyce
Microsoft Office/Access MVP

When you "move" that form inside another as a subform, the "address" of
the
referred-to combo box is no longer:

[Forms]![frmAvailReports]![cboSpecificReports]

Instead, the second combobox in the cascade will need to refer to:


[Forms]![YourParentForm]![yourSubFormControlName].[Form]![cboAvailReports]

Check the following KB article at Microsoft.com: 209099.

I don't fully understand the second part, but it sounds like you want to
have different queries run, depending on which report is selected.
?!why!?
If you are selecting reports, don't you want the report to run? If you
are
basing your report on a query, simply selecting the report seems like it
would cause its query to run.

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am slowly getting the project designed like it should be but my
current problem is as follows:

I have frmAvailReports with cascading cbo (cboAvailReports and
cboSpecifcReports) and a view report command button. Selection within
cboSpecificReports as [forms]![frmAvailReports]![cboSpecificReports]
is the parameter in qryNumberedFleet.

frmAvailReports works great as a standalone form but as soon as I drop
it as a subform into another form and execute the command button I get
a popup with [forms]![frmAvailReports]![cboSpecificReports]. Any idea
why?

Part two
I was planning on using "if then else" to run differnet queries based
on cboAvailReports on execution of command buton. I am sure there is
some way to code the button to adjust the query so I don't have to
make all these queries. Some specific code would be great

Dave- Hide quoted text -

- Show quoted text -

Further explanantion for part two. Each selection from
cboAvailReports points to a different field within the query with the
data from cboSpecificReports being the parameter to search. No matter
the fields selected form the two combo boxes they will be viewed in
either a common form or report. Hope this elaborates sufficiently.

Thanks for part 1

Dave

 

[maceslin]

Not sure if I'm visualizing your situation correctly...

From what I'm inferring, it sounds like you could dynamically build a SQL
statement for the underlying query, then use that as the source for the
?generic? report.

Regards

Jeff Boyce
Microsoft Office/Access MVP

When you "move" that form inside another as a subform, the "address" of
the
referred-to combo box is no longer:
[Forms]![frmAvailReports]![cboSpecificReports]
Instead, the second combobox in the cascade will need to refer to:
[Forms]![YourParentForm]![yourSubFormControlName].[Form]![cboAvailReports]
Check the following KB article at Microsoft.com: 209099.
I don't fully understand the second part, but it sounds like you want to
have different queries run, depending on which report is selected.
?!why!?
If you are selecting reports, don't you want the report to run? If you
are
basing your report on a query, simply selecting the report seems like it
would cause its query to run.
What am I missing?
Regards
Jeff Boyce
Microsoft Office/Access MVP

I am slowly getting the project designed like it should be but my
current problem is as follows:
I have frmAvailReports with cascading cbo (cboAvailReports and
cboSpecifcReports) and a view report command button. Selection within
cboSpecificReports as [forms]![frmAvailReports]![cboSpecificReports]
is the parameter in qryNumberedFleet.
frmAvailReports works great as a standalone form but as soon as I drop
it as a subform into another form and execute the command button I get
a popup with [forms]![frmAvailReports]![cboSpecificReports]. Any idea
why?
Part two
I was planning on using "if then else" to run differnet queries based
on cboAvailReports on execution of command buton. I am sure there is
some way to code the button to adjust the query so I don't have to
make all these queries. Some specific code would be great
Thanks
Dave- Hide quoted text -
- Show quoted text -

Further explanantion for part two. Each selection from
cboAvailReports points to a different field within the query with the
data from cboSpecificReports being the parameter to search. No matter
the fields selected form the two combo boxes they will be viewed in
either a common form or report. Hope this elaborates sufficiently.

Thanks for part 1

Dave- Hide quoted text -

- Show quoted text -

That is correct- any suggestions?

Back to part one of problem
Have inserted following code in criteria of query
forms![frmBasicReports]![frmAvailReports].Form![cboSpecificReports]

cboSpecificReports is filled with a Case Select statement

The command button on the subform now executes the report bu there is
no data (should be three lines). Any suggestions? I believe my
subform control name is as listed but not sure where exaclty you get
this (I pulled from form prooperties, name which matches source object)

 

[Jeff Boyce]

Could you post back with some of the code you are using?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[maceslin]

Could you post back with some of the code you are using?

Regards

Jeff Boyce
Microsoft Office/Access MVP

<b>Here is the code for Case select in frmAvailReports</B>

Private Sub cboAvailReports_AfterUpdate()
On Error Resume Next
cboSpecificReports = Null
Select Case cboAvailReports.Value

Case "Status"
cboSpecificReports.ColumnCount = "3"
cboSpecificReports.BoundColumn = "1"
cboSpecificReports.ColumnWidths = "1;0;0"
cboSpecificReports.RowSource = "tblStatusChoices" 'need the first
column in table

Case "Numbered Fleet"
cboSpecificReports.BoundColumn = "2"
cboSpecificReports.ColumnCount = "2"
cboSpecificReports.ColumnWidths = "0;1"
cboSpecificReports.RowSource = "tblNumbered" 'need the 2nd column in
table

' Case "Solution Location"
' cboSpecificReports.BoundColumn = "1"
' cboSpecificReports.ColumnCount = "8"
' cboSpecificReports.ColumnWidths = "1;0;0;0;0;0;0;0"
' cboSpecificReports.RowSource = "tblSolutionLocation"

' Case "Overdue"
' cboSpecificReports.BoundColumn = "1"
' cboSpecificReports.ColumnCount = "2"
' cboSpecificReports.ColumnWidths = "1;0"
' cboSpecificReports.RowSource = "qry"

Case "DOTMLPF"
cboSpecificReports.BoundColumn = "1"
cboSpecificReports.ColumnCount = "2"
cboSpecificReports.ColumnWidths = "1;0"
cboSpecificReports.RowSource = "tblDOTMLPF" 'need the first column

Case "Core Capability"
cboSpecificReports.BoundColumn = "1"
cboSpecificReports.ColumnCount = "2"
cboSpecificReports.ColumnWidths = "1;0"
cboSpecificReports.RowSource = "tblCore"
' Case "Approval"

Case Else
MsgBox "Something is wrong!"
End Select
End Sub

<B> Here is code for opening report in frmAvailReports</B>
Private Sub cboPreviewReports_Click()
On Error GoTo Err_cboPreviewReports_Click

Dim stDocName As String

stDocName = "rptNumberedFleet"
DoCmd.OpenReport stDocName, acPreview

Exit_cboPreviewReports_Click:
Exit Sub

Err_cboPreviewReports_Click:
MsgBox Err.Description
Resume Exit_cboPreviewReports_Click

End Sub

<b>Here is the code for the query</B>

SELECT tblBasicData.[Lesson IDPK], tblBasicData.RapID,
tblBasicData.DateObserved, tblBasicData.Title,
tblBasicData.ClassificationFK, tblBasicData.[Operation/exercise],
tblBasicData.CoreCapabilityFK, tblBasicData.ObervingCommand,
tblBasicData.NTA, tblBasicData.HyperlinkToLesson,
tblComments.Lesson_IDFK, tblComments.DOTLMPF_ChoiceFK,
tblComments.Date_Entered, tblComments.statusFK, tblComments.solution,
tblComments.Solution_LocationFK, tblComments.Responsible_IndividualFK,
tblComments.Review_Date, tblBasicData.NumberedFleetFK,
tblNumbered.Numbered_Fleet
FROM tblNumbered INNER JOIN (tblBasicData INNER JOIN tblComments ON
tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK) ON
tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK
WHERE (((tblNumbered.Numbered_Fleet)="forms![frmBasicData]!
[frmAvailReports].Form![cboSpecificReports]"));

I am very new to using VB and SQL staments, always been happy with
Acces but relaize the potnetial behind and am trying to learn with
assisitance form people liek yu

Thanks

 

[Jeff Boyce]

I haven't used the approach you appear to be using.

I can offer what I've done...

I create a table that holds the Access report name, a user-friendly report
name, a description, a query name, and perhaps a category and a Show/Hide
column. For each report I want to run, I put an entry in that table.

Then I create a form on which the users "order" a report. A combo box lets
them select the report they want. If there are a large number, first I have
them select a category from a combo box, then use that to filter the reports
by category selected.

If they will need to use selection criteria to "customize" any given report,
I add unbound controls on that order form to collect the selection criteria,
and refer to that form's controls in the query accompanying the report
selected.

If this isn't a viable approach in your situation, I'd suggest that you
re-post a new message in the newsgroups, perhaps the .reports 'group. A lot
of the folks who could help might not read far enough down this thread...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Could you post back with some of the code you are using?

Regards

Jeff Boyce
Microsoft Office/Access MVP

<b>Here is the code for Case select in frmAvailReports</B>

Private Sub cboAvailReports_AfterUpdate()
On Error Resume Next
cboSpecificReports = Null
Select Case cboAvailReports.Value

Case "Status"
cboSpecificReports.ColumnCount = "3"
cboSpecificReports.BoundColumn = "1"
cboSpecificReports.ColumnWidths = "1;0;0"
cboSpecificReports.RowSource = "tblStatusChoices" 'need the first
column in table

Case "Numbered Fleet"
cboSpecificReports.BoundColumn = "2"
cboSpecificReports.ColumnCount = "2"
cboSpecificReports.ColumnWidths = "0;1"
cboSpecificReports.RowSource = "tblNumbered" 'need the 2nd column in
table

' Case "Solution Location"
' cboSpecificReports.BoundColumn = "1"
' cboSpecificReports.ColumnCount = "8"
' cboSpecificReports.ColumnWidths = "1;0;0;0;0;0;0;0"
' cboSpecificReports.RowSource = "tblSolutionLocation"

' Case "Overdue"
' cboSpecificReports.BoundColumn = "1"
' cboSpecificReports.ColumnCount = "2"
' cboSpecificReports.ColumnWidths = "1;0"
' cboSpecificReports.RowSource = "qry"

Case "DOTMLPF"
cboSpecificReports.BoundColumn = "1"
cboSpecificReports.ColumnCount = "2"
cboSpecificReports.ColumnWidths = "1;0"
cboSpecificReports.RowSource = "tblDOTMLPF" 'need the first column

Case "Core Capability"
cboSpecificReports.BoundColumn = "1"
cboSpecificReports.ColumnCount = "2"
cboSpecificReports.ColumnWidths = "1;0"
cboSpecificReports.RowSource = "tblCore"
' Case "Approval"

Case Else
MsgBox "Something is wrong!"
End Select
End Sub

<B> Here is code for opening report in frmAvailReports</B>
Private Sub cboPreviewReports_Click()
On Error GoTo Err_cboPreviewReports_Click

Dim stDocName As String

stDocName = "rptNumberedFleet"
DoCmd.OpenReport stDocName, acPreview

Exit_cboPreviewReports_Click:
Exit Sub

Err_cboPreviewReports_Click:
MsgBox Err.Description
Resume Exit_cboPreviewReports_Click

End Sub

<b>Here is the code for the query</B>

SELECT tblBasicData.[Lesson IDPK], tblBasicData.RapID,
tblBasicData.DateObserved, tblBasicData.Title,
tblBasicData.ClassificationFK, tblBasicData.[Operation/exercise],
tblBasicData.CoreCapabilityFK, tblBasicData.ObervingCommand,
tblBasicData.NTA, tblBasicData.HyperlinkToLesson,
tblComments.Lesson_IDFK, tblComments.DOTLMPF_ChoiceFK,
tblComments.Date_Entered, tblComments.statusFK, tblComments.solution,
tblComments.Solution_LocationFK, tblComments.Responsible_IndividualFK,
tblComments.Review_Date, tblBasicData.NumberedFleetFK,
tblNumbered.Numbered_Fleet
FROM tblNumbered INNER JOIN (tblBasicData INNER JOIN tblComments ON
tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK) ON
tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK
WHERE (((tblNumbered.Numbered_Fleet)="forms![frmBasicData]!
[frmAvailReports].Form![cboSpecificReports]"));

I am very new to using VB and SQL staments, always been happy with
Acces but relaize the potnetial behind and am trying to learn with
assisitance form people liek yu

Thanks

 

[maceslin]

I haven't used the approach you appear to be using.

I can offer what I've done...

I create a table that holds the Access report name, a user-friendly report
name, a description, a query name, and perhaps a category and a Show/Hide
column. For each report I want to run, I put an entry in that table.

Then I create a form on which the users "order" a report. A combo box lets
them select the report they want. If there are a large number, first I have
them select a category from a combo box, then use that to filter the reports
by category selected.

If they will need to use selection criteria to "customize" any given report,
I add unbound controls on that order form to collect the selection criteria,
and refer to that form's controls in the query accompanying the report
selected.

If this isn't a viable approach in your situation, I'd suggest that you
re-post a new message in the newsgroups, perhaps the .reports 'group. A lot
of the folks who could help might not read far enough down this thread...

Regards

Jeff Boyce
Microsoft Office/Access MVP

<b>Here is the code for Case select in frmAvailReports</B>

Private Sub cboAvailReports_AfterUpdate()
On Error Resume Next
cboSpecificReports = Null
Select Case cboAvailReports.Value

Case "Status"
cboSpecificReports.ColumnCount = "3"
cboSpecificReports.BoundColumn = "1"
cboSpecificReports.ColumnWidths = "1;0;0"
cboSpecificReports.RowSource = "tblStatusChoices" 'need the first
column in table

Case "Numbered Fleet"
cboSpecificReports.BoundColumn = "2"
cboSpecificReports.ColumnCount = "2"
cboSpecificReports.ColumnWidths = "0;1"
cboSpecificReports.RowSource = "tblNumbered" 'need the 2nd column in
table

' Case "Solution Location"
' cboSpecificReports.BoundColumn = "1"
' cboSpecificReports.ColumnCount = "8"
' cboSpecificReports.ColumnWidths = "1;0;0;0;0;0;0;0"
' cboSpecificReports.RowSource = "tblSolutionLocation"

' Case "Overdue"
' cboSpecificReports.BoundColumn = "1"
' cboSpecificReports.ColumnCount = "2"
' cboSpecificReports.ColumnWidths = "1;0"
' cboSpecificReports.RowSource = "qry"

Case "DOTMLPF"
cboSpecificReports.BoundColumn = "1"
cboSpecificReports.ColumnCount = "2"
cboSpecificReports.ColumnWidths = "1;0"
cboSpecificReports.RowSource = "tblDOTMLPF" 'need the first column

Case "Core Capability"
cboSpecificReports.BoundColumn = "1"
cboSpecificReports.ColumnCount = "2"
cboSpecificReports.ColumnWidths = "1;0"
cboSpecificReports.RowSource = "tblCore"
' Case "Approval"

Case Else
MsgBox "Something is wrong!"
End Select
End Sub

<B> Here is code for opening report in frmAvailReports</B>
Private Sub cboPreviewReports_Click()
On Error GoTo Err_cboPreviewReports_Click

Dim stDocName As String

stDocName = "rptNumberedFleet"
DoCmd.OpenReport stDocName, acPreview

Exit_cboPreviewReports_Click:
Exit Sub

Err_cboPreviewReports_Click:
MsgBox Err.Description
Resume Exit_cboPreviewReports_Click

End Sub

<b>Here is the code for the query</B>

SELECT tblBasicData.[Lesson IDPK], tblBasicData.RapID,
tblBasicData.DateObserved, tblBasicData.Title,
tblBasicData.ClassificationFK, tblBasicData.[Operation/exercise],
tblBasicData.CoreCapabilityFK, tblBasicData.ObervingCommand,
tblBasicData.NTA, tblBasicData.HyperlinkToLesson,
tblComments.Lesson_IDFK, tblComments.DOTLMPF_ChoiceFK,
tblComments.Date_Entered, tblComments.statusFK, tblComments.solution,
tblComments.Solution_LocationFK, tblComments.Responsible_IndividualFK,
tblComments.Review_Date, tblBasicData.NumberedFleetFK,
tblNumbered.Numbered_Fleet
FROM tblNumbered INNER JOIN (tblBasicData INNER JOIN tblComments ON
tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK) ON
tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK
WHERE (((tblNumbered.Numbered_Fleet)="forms![frmBasicData]!
[frmAvailReports].Form![cboSpecificReports]"));

I am very new to using VB and SQL staments, always been happy with
Acces but relaize the potnetial behind and am trying to learn with
assisitance form people liek yu

Thanks- Hide quoted text -

- Show quoted text -

Thanks- let me look at your approach and go from there!!