I need a form to select the items to be presented in the report.

D

dd

I posted this problem in m.p.a.queries, but the responses turned into a form
coding question and thus I post it here.

I have a building inspection database with condition details of numerous
sites
I have a query which selects a specific site using [Like]&*

I have a condition survey report based on this query.
Instead of a query asking for a site, I want a form which lists all the
available sites; allows the user to pick specific sites from the list, and
then produce the report for each of the picked sites.

Regards
Dylan Dawson
 
S

Sam

I have a condition survey report based on this query.
Instead of a query asking for a site, I want a form which lists all the
available sites; allows the user to pick specific sites from the list, and
then produce the report for each of the picked sites.
Click to expand...


Hi Dylan,

Until someone offers a neater solution, you can try this:

1. On your form, build a combo box that will let the user choose a site
from a list.

2. Amend your query so that for the field 'Site' you set the criteria
to

=
![comboBoxControlName]

3. Use a wizard to create a button to launch the report.

The user will select a site, click the 'Print' button which will launch
your report based on yoru query, which has a where clause on the site
field...

I think think this is what you are asking for ;) If not slap me around
a little, and re phrase your request.

I am always happy to try and help!

Kindest Regards,
Sam
 
D

dd

Sam,

I tried this with a listbox, but it doesn't pass my selection to the query.
Also, I thought a listbox would let me select more than one Site, but it
doesn't.
Any ideas?

Dylan

I have a condition survey report based on this query.
Instead of a query asking for a site, I want a form which lists all the
available sites; allows the user to pick specific sites from the list, and
then produce the report for each of the picked sites.
Click to expand...


Hi Dylan,

Until someone offers a neater solution, you can try this:

1. On your form, build a combo box that will let the user choose a site
from a list.

2. Amend your query so that for the field 'Site' you set the criteria
to

=
![comboBoxControlName]

3. Use a wizard to create a button to launch the report.

The user will select a site, click the 'Print' button which will launch
your report based on yoru query, which has a where clause on the site
field...

I think think this is what you are asking for ;) If not slap me around
a little, and re phrase your request.

I am always happy to try and help!

Kindest Regards,
Sam
 
S

SteveS

Hi Dylan,

Maybe this is what you are looking for.... at least it is another way. <g>

On a form, create an unbound list box and two buttons. One button will print
the reports and the second will clear the selected items in the list box.


---LIST BOX---

On the "Other" tab,
1) change the Multi Select" property to 'Simple'.
2) change the name of the list box to "mslbxSites".
(my naming convention:: "ms" = MultiSelect; "lbx" = listbox and "Sites" is
the field you are selecting)


On the 'Data' tab, set the row source to look like this:

SELECT DISTINCT SurveyData.[Site]
FROM Test Cases
ORDER BY SurveyData.[Site];


---FIRST BUTTON---
Name one button "btnPrintReport". Set the caption of this button to "Print
Report" (no quotes).
Paste the following code in the click event:

' ***********
Dim vItm As Variant
Dim strCriteria As String

strCriteria = ""
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = Trim(Me!mslbxSites.ItemData(vItm))

'!!!!!! change "ReportName" to the name of your report. !!!!!!!!!
DoCmd.OpenReport "ReportName", , , "[Site] Like '" & strCriteria & "*'"

Next vItm
' *****************

---REPORT-----
In the report query remove the "Where" clause. The query should look like
this:

SELECT SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element,
SurveyData.Condition, SurveyData.PriorityRef, SurveyData.[Revenue Cost],
SurveyData.[Revenue Comments], SurveyData.[Remaining Life],
SurveyData.[Capital Cost], SurveyData.[Capital Comments]
FROM SurveyData
ORDER BY SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element;



---SECOND BUTTON---
Name the second button "btnClearSelections".
Set the caption of this button to "Clear Selections" (no quotes).
Paste the following code in the click event:

' **************
Dim n As Long

For n = 0 To Me!mslbxSites.ListCount
Me!mslbxSites.Selected(n) = False
Next
' **************

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


dd said:
Sam,

I tried this with a listbox, but it doesn't pass my selection to the query.
Also, I thought a listbox would let me select more than one Site, but it
doesn't.
Any ideas?

Dylan

I have a condition survey report based on this query.
Instead of a query asking for a site, I want a form which lists all the
available sites; allows the user to pick specific sites from the list, and
then produce the report for each of the picked sites.
Click to expand...


Hi Dylan,

Until someone offers a neater solution, you can try this:

1. On your form, build a combo box that will let the user choose a site
from a list.

2. Amend your query so that for the field 'Site' you set the criteria
to

=
![comboBoxControlName]

3. Use a wizard to create a button to launch the report.

The user will select a site, click the 'Print' button which will launch
your report based on yoru query, which has a where clause on the site
field...

I think think this is what you are asking for ;) If not slap me around
a little, and re phrase your request.

I am always happy to try and help!

Kindest Regards,
Sam
Click to expand...
 
D

dd

Thanks Steve,

This is along the lines of what I'm trying to do. Can I preview the report
rather than print it?

Regards
Dylan

"SteveS" <limbim53 at yahoo dot com> wrote in message
Hi Dylan,

Maybe this is what you are looking for.... at least it is another way. <g>

On a form, create an unbound list box and two buttons. One button will print
the reports and the second will clear the selected items in the list box.


---LIST BOX---

On the "Other" tab,
1) change the Multi Select" property to 'Simple'.
2) change the name of the list box to "mslbxSites".
(my naming convention:: "ms" = MultiSelect; "lbx" = listbox and "Sites" is
the field you are selecting)


On the 'Data' tab, set the row source to look like this:

SELECT DISTINCT SurveyData.[Site]
FROM Test Cases
ORDER BY SurveyData.[Site];


---FIRST BUTTON---
Name one button "btnPrintReport". Set the caption of this button to "Print
Report" (no quotes).
Paste the following code in the click event:

' ***********
Dim vItm As Variant
Dim strCriteria As String

strCriteria = ""
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = Trim(Me!mslbxSites.ItemData(vItm))

'!!!!!! change "ReportName" to the name of your report. !!!!!!!!!
DoCmd.OpenReport "ReportName", , , "[Site] Like '" & strCriteria &
"*'"

Next vItm
' *****************

---REPORT-----
In the report query remove the "Where" clause. The query should look like
this:

SELECT SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element,
SurveyData.Condition, SurveyData.PriorityRef, SurveyData.[Revenue Cost],
SurveyData.[Revenue Comments], SurveyData.[Remaining Life],
SurveyData.[Capital Cost], SurveyData.[Capital Comments]
FROM SurveyData
ORDER BY SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element;



---SECOND BUTTON---
Name the second button "btnClearSelections".
Set the caption of this button to "Clear Selections" (no quotes).
Paste the following code in the click event:

' **************
Dim n As Long

For n = 0 To Me!mslbxSites.ListCount
Me!mslbxSites.Selected(n) = False
Next
' **************

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


dd said:
Sam,

I tried this with a listbox, but it doesn't pass my selection to the
query.
Also, I thought a listbox would let me select more than one Site, but it
doesn't.
Any ideas?

Dylan

I have a condition survey report based on this query.
Instead of a query asking for a site, I want a form which lists all the
available sites; allows the user to pick specific sites from the list,
and
then produce the report for each of the picked sites.
Click to expand...


Hi Dylan,

Until someone offers a neater solution, you can try this:

1. On your form, build a combo box that will let the user choose a site
from a list.

2. Amend your query so that for the field 'Site' you set the criteria
to

=
![comboBoxControlName]

3. Use a wizard to create a button to launch the report.

The user will select a site, click the 'Print' button which will launch
your report based on yoru query, which has a where clause on the site
field...

I think think this is what you are asking for ;) If not slap me around
a little, and re phrase your request.

I am always happy to try and help!

Kindest Regards,
Sam
Click to expand...
 
S

SteveS

Dylan,

I just noticed an error in the code for the list box in the FROM line. Sorry.
It should be:

SELECT DISTINCT SurveyData.[Site]
FROM SurveyData ' <<== change to SurveyData
ORDER BY SurveyData.[Site];



About the preview

Add a check box to the form. Name the check box "ckPreview". Set the Default
value to TRUE.

Now change code for the button named "btnPrintReport" to this:

' **************
Dim vItm As Variant
Dim strCriteria As String
Dim intView As Integer
Dim intWindowMode As Integer

strCriteria = ""
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = Trim(Me!mslbxSites.ItemData(vItm))

If ckPreview Then
intView = acPreview
intWindowMode = acDialog
Else
intView = acViewNormal
intWindowMode = acWindowNormal
End If

'!!!!!! change "ReportName" to the name of your report. !!!!!!!!!
DoCmd.OpenReport "ReportName", intView, , "[Site] Like '" &
strCriteria & "*'", intWindowMode


Next vItm
' **************

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


dd said:
Thanks Steve,

This is along the lines of what I'm trying to do. Can I preview the report
rather than print it?

Regards
Dylan

"SteveS" <limbim53 at yahoo dot com> wrote in message
Hi Dylan,

Maybe this is what you are looking for.... at least it is another way. <g>

On a form, create an unbound list box and two buttons. One button will print
the reports and the second will clear the selected items in the list box.


---LIST BOX---

On the "Other" tab,
1) change the Multi Select" property to 'Simple'.
2) change the name of the list box to "mslbxSites".
(my naming convention:: "ms" = MultiSelect; "lbx" = listbox and "Sites" is
the field you are selecting)


On the 'Data' tab, set the row source to look like this:

SELECT DISTINCT SurveyData.[Site]
FROM Test Cases
ORDER BY SurveyData.[Site];


---FIRST BUTTON---
Name one button "btnPrintReport". Set the caption of this button to "Print
Report" (no quotes).
Paste the following code in the click event:

' ***********
Dim vItm As Variant
Dim strCriteria As String

strCriteria = ""
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = Trim(Me!mslbxSites.ItemData(vItm))

'!!!!!! change "ReportName" to the name of your report. !!!!!!!!!
DoCmd.OpenReport "ReportName", , , "[Site] Like '" & strCriteria &
"*'"

Next vItm
' *****************

---REPORT-----
In the report query remove the "Where" clause. The query should look like
this:

SELECT SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element,
SurveyData.Condition, SurveyData.PriorityRef, SurveyData.[Revenue Cost],
SurveyData.[Revenue Comments], SurveyData.[Remaining Life],
SurveyData.[Capital Cost], SurveyData.[Capital Comments]
FROM SurveyData
ORDER BY SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element;



---SECOND BUTTON---
Name the second button "btnClearSelections".
Set the caption of this button to "Clear Selections" (no quotes).
Paste the following code in the click event:

' **************
Dim n As Long

For n = 0 To Me!mslbxSites.ListCount
Me!mslbxSites.Selected(n) = False
Next
' **************

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


dd said:
Sam,

I tried this with a listbox, but it doesn't pass my selection to the
query.
Also, I thought a listbox would let me select more than one Site, but it
doesn't.
Any ideas?

Dylan

I have a condition survey report based on this query.
Instead of a query asking for a site, I want a form which lists all the
available sites; allows the user to pick specific sites from the list,
and
then produce the report for each of the picked sites.
Click to expand...


Hi Dylan,

Until someone offers a neater solution, you can try this:

1. On your form, build a combo box that will let the user choose a site
from a list.

2. Amend your query so that for the field 'Site' you set the criteria
to

=
![comboBoxControlName]

3. Use a wizard to create a button to launch the report.

The user will select a site, click the 'Print' button which will launch
your report based on yoru query, which has a where clause on the site
field...

I think think this is what you are asking for ;) If not slap me around
a little, and re phrase your request.

I am always happy to try and help!

Kindest Regards,
Sam
Click to expand...
Click to expand...
 
D

dd

Thanks Steve, this is excellent

I can apply your code to the other reports so that the users can select
locations and categories of defect, etc.

It would be usefull if I could collate these into a single report, can you
translate this into VBA for me as well?

Many thanks
Dylan

"SteveS" <limbim53 at yahoo dot com> wrote in message
Dylan,

I just noticed an error in the code for the list box in the FROM line.
Sorry.
It should be:

SELECT DISTINCT SurveyData.[Site]
FROM SurveyData ' <<== change to SurveyData
ORDER BY SurveyData.[Site];



About the preview

Add a check box to the form. Name the check box "ckPreview". Set the Default
value to TRUE.

Now change code for the button named "btnPrintReport" to this:

' **************
Dim vItm As Variant
Dim strCriteria As String
Dim intView As Integer
Dim intWindowMode As Integer

strCriteria = ""
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = Trim(Me!mslbxSites.ItemData(vItm))

If ckPreview Then
intView = acPreview
intWindowMode = acDialog
Else
intView = acViewNormal
intWindowMode = acWindowNormal
End If

'!!!!!! change "ReportName" to the name of your report. !!!!!!!!!
DoCmd.OpenReport "ReportName", intView, , "[Site] Like '" &
strCriteria & "*'", intWindowMode


Next vItm
' **************

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


dd said:
Thanks Steve,

This is along the lines of what I'm trying to do. Can I preview the report
rather than print it?

Regards
Dylan

"SteveS" <limbim53 at yahoo dot com> wrote in message
Hi Dylan,

Maybe this is what you are looking for.... at least it is another way. <g>

On a form, create an unbound list box and two buttons. One button will
print
the reports and the second will clear the selected items in the list box.


---LIST BOX---

On the "Other" tab,
1) change the Multi Select" property to 'Simple'.
2) change the name of the list box to "mslbxSites".
(my naming convention:: "ms" = MultiSelect; "lbx" = listbox and "Sites" is
the field you are selecting)


On the 'Data' tab, set the row source to look like this:

SELECT DISTINCT SurveyData.[Site]
FROM Test Cases
ORDER BY SurveyData.[Site];


---FIRST BUTTON---
Name one button "btnPrintReport". Set the caption of this button to "Print
Report" (no quotes).
Paste the following code in the click event:

' ***********
Dim vItm As Variant
Dim strCriteria As String

strCriteria = ""
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = Trim(Me!mslbxSites.ItemData(vItm))

'!!!!!! change "ReportName" to the name of your report. !!!!!!!!!
DoCmd.OpenReport "ReportName", , , "[Site] Like '" & strCriteria &
"*'"

Next vItm
' *****************

---REPORT-----
In the report query remove the "Where" clause. The query should look like
this:

SELECT SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element,
SurveyData.Condition, SurveyData.PriorityRef, SurveyData.[Revenue Cost],
SurveyData.[Revenue Comments], SurveyData.[Remaining Life],
SurveyData.[Capital Cost], SurveyData.[Capital Comments]
FROM SurveyData
ORDER BY SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element;



---SECOND BUTTON---
Name the second button "btnClearSelections".
Set the caption of this button to "Clear Selections" (no quotes).
Paste the following code in the click event:

' **************
Dim n As Long

For n = 0 To Me!mslbxSites.ListCount
Me!mslbxSites.Selected(n) = False
Next
' **************

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


dd said:
Sam,

I tried this with a listbox, but it doesn't pass my selection to the
query.
Also, I thought a listbox would let me select more than one Site, but it
doesn't.
Any ideas?

Dylan

I have a condition survey report based on this query.
Instead of a query asking for a site, I want a form which lists all
the
available sites; allows the user to pick specific sites from the list,
and
then produce the report for each of the picked sites.
Click to expand...


Hi Dylan,

Until someone offers a neater solution, you can try this:

1. On your form, build a combo box that will let the user choose a site
from a list.

2. Amend your query so that for the field 'Site' you set the criteria
to

=
![comboBoxControlName]

3. Use a wizard to create a button to launch the report.

The user will select a site, click the 'Print' button which will launch
your report based on yoru query, which has a where clause on the site
field...

I think think this is what you are asking for ;) If not slap me around
a little, and re phrase your request.

I am always happy to try and help!

Kindest Regards,
Sam
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why has this stopped working 5
Listbox outputs multiple reports - collating into one report 5
Expandable list in report? 2
Form set the Visibility of a Label in a Report? 6
Seearch criteria in a question to be presented in a report 5
query to show where no data in a stated column 0
Selecting in combo box rather than using, Like [enter] & "*" 4
Transferring the filter from a datasheet form to a report. 4

Top