Parameter form to report

A

Aviator

I have a DB that has a look up field for aircraft type that gets info from a
small seperate DB that contains many aircraft types. The main DB also has 25
check box fields that split an aircraft interior info different parts. When a
job is done, the work order number(primary key) is entered, along with the
aircraft type. The areas worked on during the job are checked, there could be
just one or as many as 25. I have a Form created that I want to use to obtain
the parameters for the report I created. The form contains one combo
box(Aircraft Type) and the 25 check boxes. I user would select the aircraft
type and one or many check boxes to return the work orders that were of that
aircraft type and work was completed in those specified areas. I have the
check box part working fine, have been unable to get the combo box to only
return results of that aircarft type. No errors, using access 2003. The
following is the code used on the form. Aly help would be greatly
appreciated.

Private Sub OK_Click()

Dim strWhere As String
Dim ctl As Control
strWhere = "" 'initialize the where clause
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strWhere = strWhere & "[" & ctl.Tag & "] or "
End If
End If
Next
If Len(strWhere) > 1 Then
'remove the last " or " from the where clause
strWhere = Left(strWhere, Len(strWhere) - 4)
Debug.Print "strWhere: " & strWhere
End If

'the final strWhere might look like
'[Airstairs/Entrance] or [Carpet] or [Cockpit Items] or [Complete
Refurbishment] or [Cup Holders] or [Curtain] or [Divan] or [Dye Job] or
[Entertainment] or [Galley] or [Headliner] or [Lavatory] or [Lighting] or
[Loncoin] or [Lower Side Walls] or [Painting] or [Repairs] or [Runner] or
[Seat Belts] or [Seats] or
or [Telephone] or [Various/Miscellaneous]
or [Veneer] or [Windowline]
'open the report based on the checked boxes
DoCmd.OpenReport "Type and Work Report", acViewPreview, , strWhere

End Sub

Private Sub Close_Click()
DoCmd.Close 'Close Form
End Sub
 
D

Duane Hookom

I think I asked a while back (not sure)
1) the name of the "on combo box"
2) the name of the aircraft type field in the report's record source
3) the data type of the aircraft type field in the report's record source
 
A

Aviator

1) Name of the combo box is Aircraft Type. Think you meant that, not sure
what "on combo box" means

2) Name of the Reports field is also Aircraft Type

3) I belive the data type of that field is Text Box.

Under properties for the report "Aircraft Type" text box, the title bar is
titled.
" Text Box:Aircraft Type "

Name.................Aircraft Type
Control Source....Aircraft Type
Format .............. blank
Tag................... blank
Smart Tag......... blank


Duane Hookom said:
I think I asked a while back (not sure)
1) the name of the "on combo box"
2) the name of the aircraft type field in the report's record source
3) the data type of the aircraft type field in the report's record source

--
Duane Hookom
MS Access MVP

Aviator said:
I have a DB that has a look up field for aircraft type that gets info from
a
small seperate DB that contains many aircraft types. The main DB also has
25
check box fields that split an aircraft interior info different parts.
When a
job is done, the work order number(primary key) is entered, along with the
aircraft type. The areas worked on during the job are checked, there could
be
just one or as many as 25. I have a Form created that I want to use to
obtain
the parameters for the report I created. The form contains one combo
box(Aircraft Type) and the 25 check boxes. I user would select the
aircraft
type and one or many check boxes to return the work orders that were of
that
aircraft type and work was completed in those specified areas. I have the
check box part working fine, have been unable to get the combo box to only
return results of that aircarft type. No errors, using access 2003. The
following is the code used on the form. Aly help would be greatly
appreciated.

Private Sub OK_Click()

Dim strWhere As String
Dim ctl As Control
strWhere = "" 'initialize the where clause
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strWhere = strWhere & "[" & ctl.Tag & "] or "
End If
End If
Next
If Len(strWhere) > 1 Then
'remove the last " or " from the where clause
strWhere = Left(strWhere, Len(strWhere) - 4)
Debug.Print "strWhere: " & strWhere
End If

'the final strWhere might look like
'[Airstairs/Entrance] or [Carpet] or [Cockpit Items] or [Complete
Refurbishment] or [Cup Holders] or [Curtain] or [Divan] or [Dye Job] or
[Entertainment] or [Galley] or [Headliner] or [Lavatory] or [Lighting] or
[Loncoin] or [Lower Side Walls] or [Painting] or [Repairs] or [Runner] or
[Seat Belts] or [Seats] or
or [Telephone] or
[Various/Miscellaneous]
or [Veneer] or [Windowline]
'open the report based on the checked boxes
DoCmd.OpenReport "Type and Work Report", acViewPreview, , strWhere

End Sub

Private Sub Close_Click()
DoCmd.Close 'Close Form
End Sub
 
D

Duane Hookom

1) I mis-quoted your statement "one combo box". So I was asking for the name
of the combo box on your form that you only mentioned as "one combo box".
2) It is appropriate when referring to field names in a posting (especially
field names with spaces) to place [ ] around them like [Aircraft Type].
3) There is no field type "Text Box". A text box is a control on a form or
report. A field has a data type like Text or Numeric or Date or Autonumber
....

Let's assume your combo box on your form is named "cboAircraftType" and the
field in the report is [Aircraft Type] and this field is a text field. If
your combo box has a different name then rename it or change the code.
Your code would then might be something like listed below. If not, open your
debug window and find the mistake and fix it.

Private Sub OK_Click()

Dim strWhere As String
Dim strOrs as String
Dim ctl As Control
strWhere = "1=1 " 'initialize the where clause
If Not IsNull(Me.cboAircraftType) Then
strWhere = strWhere & " AND [Aircraft Type] ="" & _
me.cboAircraftType & """ "
End If
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strOrs = strOrs & "[" & ctl.Tag & "] or "
End If
End If
Next
If Len(strOrs) > 1 Then
'remove the last " or " from the where clause
strOrs = Left(strOrs, Len(strOrs) - 4)
strWhere = strWhere & " AND (" & strOrs & ")"
End If
debug.Print strWhere
'the final strWhere might look like
'[Airstairs/Entrance] or [Carpet] or [Cockpit Items]
'open the report based on the checked boxes
DoCmd.OpenReport "Type and Work Report", acViewPreview, , strWhere

End Sub

Private Sub Close_Click()
DoCmd.Close 'Close Form
End Sub

Aviator said:
1) Name of the combo box is Aircraft Type. Think you meant that, not sure
what "on combo box" means

2) Name of the Reports field is also Aircraft Type

3) I belive the data type of that field is Text Box.

Under properties for the report "Aircraft Type" text box, the title bar is
titled.
" Text Box:Aircraft Type "

Name.................Aircraft Type
Control Source....Aircraft Type
Format .............. blank
Tag................... blank
Smart Tag......... blank


Duane Hookom said:
I think I asked a while back (not sure)
1) the name of the "on combo box"
2) the name of the aircraft type field in the report's record source
3) the data type of the aircraft type field in the report's record source

--
Duane Hookom
MS Access MVP

Aviator said:
I have a DB that has a look up field for aircraft type that gets info
from
a
small seperate DB that contains many aircraft types. The main DB also
has
25
check box fields that split an aircraft interior info different parts.
When a
job is done, the work order number(primary key) is entered, along with
the
aircraft type. The areas worked on during the job are checked, there
could
be
just one or as many as 25. I have a Form created that I want to use to
obtain
the parameters for the report I created. The form contains one combo
box(Aircraft Type) and the 25 check boxes. I user would select the
aircraft
type and one or many check boxes to return the work orders that were of
that
aircraft type and work was completed in those specified areas. I have
the
check box part working fine, have been unable to get the combo box to
only
return results of that aircarft type. No errors, using access 2003. The
following is the code used on the form. Aly help would be greatly
appreciated.

Private Sub OK_Click()

Dim strWhere As String
Dim ctl As Control
strWhere = "" 'initialize the where clause
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strWhere = strWhere & "[" & ctl.Tag & "] or "
End If
End If
Next
If Len(strWhere) > 1 Then
'remove the last " or " from the where clause
strWhere = Left(strWhere, Len(strWhere) - 4)
Debug.Print "strWhere: " & strWhere
End If

'the final strWhere might look like
'[Airstairs/Entrance] or [Carpet] or [Cockpit Items] or [Complete
Refurbishment] or [Cup Holders] or [Curtain] or [Divan] or [Dye Job] or
[Entertainment] or [Galley] or [Headliner] or [Lavatory] or [Lighting]
or
[Loncoin] or [Lower Side Walls] or [Painting] or [Repairs] or [Runner]
or
[Seat Belts] or [Seats] or
or [Telephone] or
[Various/Miscellaneous]
or [Veneer] or [Windowline]
'open the report based on the checked boxes
DoCmd.OpenReport "Type and Work Report", acViewPreview, , strWhere

End Sub

Private Sub Close_Click()
DoCmd.Close 'Close Form
End Sub
 
A

Aviator

Made the changes you suggested, it is pasted at the end. I received a
Compile Error: after typing the 7th line of the "OK" code. It turned the
line red and said "Expected:Expression"
The line was me.cboAircraftType & """ "



Private Sub OK_Click()

Dim strWhere As String
Dim strOrs As String
Dim ctl As Control
strWhere = "1=1" 'initialize the where clause
If Not IsNull(Me.Aircraft_Type) Then
strWhere = strWhere & " AND [Aircraft Type]=""&_"
me.cboAircraftType & """ "
End If
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strOrs = strOrs & "[" & ctl.Tag & "]or"
End If
End If
Next
If Len(strOrs) > 1 Then
'remove the last " or " from the where clause
strOrs = Left(strOrs, Len(strOrs) - 4)
strWhere = strWhere & " AND (" & strOrs & ")"
End If
Debug.Print strWhere
'the final strWhere might look like
'[Airstairs/Entrance] or [Carpet] or [Cockpit Items] or [Complete
Refurbishment] or [Cup Holders] or [Curtain] or [Divan] or [Dye Job] or
[Entertainment] or [Galley] or [Headliner] or [Lavatory] or [Lighting] or
[Loncoin] or [Lower Side Walls] or [Painting] or [Repairs] or [Runner] or
[Seat Belts] or [Seats] or
or [Telephone] or [Various/Miscellaneous]
or [Veneer] or [Windowline]
'open the report based on the checked boxes
DoCmd.OpenReport "Type and Work Report", acViewPreview, , strWhere

End Sub

Private Sub Close_Click()
DoCmd.Close 'Close Form
End Sub
 
D

Duane Hookom

Try this. Make sure there is a space between & and _. You seemed to have
lost some spaces elsewhere in the code.

Private Sub OK_Click()

Dim strWhere As String
Dim strOrs As String
Dim ctl As Control
strWhere = "1=1" 'initialize the where clause
If Not IsNull(Me.Aircraft_Type) Then
strWhere = strWhere & " AND [Aircraft Type]="'" & _
me.cboAircraftType & """ "
End If
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strOrs = strOrs & " [" & ctl.Tag & "] or "
End If
End If
Next
If Len(strOrs) > 1 Then
'remove the last " or " from the where clause
strOrs = Left(strOrs, Len(strOrs) - 4)
strWhere = strWhere & " AND (" & strOrs & ")"
End If
Debug.Print strWhere
'the final strWhere might look like
'[Airstairs/Entrance] or [Carpet] or [Cockpit Items] or ...
'open the report based on the checked boxes
DoCmd.OpenReport "Type and Work Report", acViewPreview, , strWhere

End Sub

Private Sub Close_Click()
DoCmd.Close 'Close Form
End Sub
 
A

Aviator

I put the space in, and received a compile error:
Expected: end of statement
The 6th and 7th line now turn red, except for the _ which I just put a space
before. The "me" gets highlighted by the error message.

Private Sub OK_Click()

Dim strWhere As String
Dim strOrs As String
Dim ctl As Control
strWhere = "1=1" 'initialize the where clause
If Not IsNull(Me.Aircraft_Type) Then
6th strWhere = strWhere & " AND [Aircraft Type]="'" & _
7th me.cboAircraftType & """ "
End If
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strOrs = strOrs & " [" & ctl.Tag & "] or "
End If
End If
Next
If Len(strOrs) > 1 Then
'remove the last " or " from the where clause
strOrs = Left(strOrs, Len(strOrs) - 4)
strWhere = strWhere & " AND (" & strOrs & ")"
End If
Debug.Print strWhere
'the final strWhere might look like
'[Airstairs/Entrance] or [Carpet] or [Cockpit Items] or ...
'open the report based on the checked boxes
DoCmd.OpenReport "Type and Work Report", acViewPreview, , strWhere

End Sub

Private Sub Close_Click()
DoCmd.Close 'Close Form
End Sub
 
D

Duane Hookom

Try replace a single quote with a double after the second "=". You should
learn how these expressions are written so that you can trouble-shoot my
typos.

If Not IsNull(Me.Aircraft_Type) Then
strWhere = strWhere & " AND [Aircraft Type]=""" & _
me.cboAircraftType & """ "
End If

--
Duane Hookom
MS Access MVP

Aviator said:
I put the space in, and received a compile error:
Expected: end of statement
The 6th and 7th line now turn red, except for the _ which I just put a
space
before. The "me" gets highlighted by the error message.

Private Sub OK_Click()

Dim strWhere As String
Dim strOrs As String
Dim ctl As Control
strWhere = "1=1" 'initialize the where clause
If Not IsNull(Me.Aircraft_Type) Then
6th strWhere = strWhere & " AND [Aircraft Type]="'" & _
7th me.cboAircraftType & """ "
End If
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strOrs = strOrs & " [" & ctl.Tag & "] or "
End If
End If
Next
If Len(strOrs) > 1 Then
'remove the last " or " from the where clause
strOrs = Left(strOrs, Len(strOrs) - 4)
strWhere = strWhere & " AND (" & strOrs & ")"
End If
Debug.Print strWhere
'the final strWhere might look like
'[Airstairs/Entrance] or [Carpet] or [Cockpit Items] or ...
'open the report based on the checked boxes
DoCmd.OpenReport "Type and Work Report", acViewPreview, , strWhere

End Sub

Private Sub Close_Click()
DoCmd.Close 'Close Form
End Sub
 
A

Aviator

Yes I do need to learn this expressions, I think I may find me an advance
class on this stuff. I find this stuff quit interesting, want to learn it
myself.
That fixed that error. When I ran ran the parameter form, another compile
error:
"Method or data member not found"
It highlighted the .cboAircraftType portion on the 7th line. I tried
changing it to
..cboAircraft_Type, no luck.

Duane Hookom said:
Try replace a single quote with a double after the second "=". You should
learn how these expressions are written so that you can trouble-shoot my
typos.

If Not IsNull(Me.Aircraft_Type) Then
strWhere = strWhere & " AND [Aircraft Type]=""" & _
me.cboAircraftType & """ "
End If

--
Duane Hookom
MS Access MVP

Aviator said:
I put the space in, and received a compile error:
Expected: end of statement
The 6th and 7th line now turn red, except for the _ which I just put a
space
before. The "me" gets highlighted by the error message.

Private Sub OK_Click()

Dim strWhere As String
Dim strOrs As String
Dim ctl As Control
strWhere = "1=1" 'initialize the where clause
If Not IsNull(Me.Aircraft_Type) Then
6th strWhere = strWhere & " AND [Aircraft Type]="'" & _
7th me.cboAircraftType & """ "
End If
For Each ctl In Me.Controls 'loop through all controls on form
'find the check boxes
If ctl.ControlType = acCheckBox Then
'find the true check boxes
If ctl.Value = True Then
'add to the where clause
strOrs = strOrs & " [" & ctl.Tag & "] or "
End If
End If
Next
If Len(strOrs) > 1 Then
'remove the last " or " from the where clause
strOrs = Left(strOrs, Len(strOrs) - 4)
strWhere = strWhere & " AND (" & strOrs & ")"
End If
Debug.Print strWhere
'the final strWhere might look like
'[Airstairs/Entrance] or [Carpet] or [Cockpit Items] or ...
'open the report based on the checked boxes
DoCmd.OpenReport "Type and Work Report", acViewPreview, , strWhere

End Sub

Private Sub Close_Click()
DoCmd.Close 'Close Form
End Sub
 

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

Top