Parameter form to report

G

Guest

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
 
G

Guest

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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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
 
G

Guest

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
Click to expand...
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
Click to expand...
 
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
Click to expand...
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
Click to expand...
Click to expand...
 
G

Guest

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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

In an earlier post, I attempted to make you rename your combo box to use a
standard naming convention.
"If your combo box has a different name then rename it or change the code"
Apparently you didn't change the control name like I suggested.

You should kick your development up a notch and find (and use) a naming
convention that doesn't permit spaces in object names. Also, you should name
significant controls with standardized, significant names. If I intend to
refer to a combo box anywhere in code or queries or other, I name the combo
box beginning with "cbo". There are other prefixes for other types of
controls.
--
Duane Hookom
MS Access MVP

Aviator said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I changed the field names like stated, I didn't understand it that way
before, but I get it now. That makes more sense that way when writing code I
now understand. No longer get that error anymore. New one. When I run the
parameter form:

Runtime error '3075':
Missing ), ], or Item in query expression '(1=1 AND
[cboAircraftType]="Astra" AND ([Carpe))'.


Here is the current code I have written:
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 [cboAircraftType]=""" & _
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
 
G

Guest

not sure if it help. I noticed in the error and in the Immediate window, that
wether I selcet on check box or many, the last letter of the last check box
name is missing. Immediate window example:

1=1 AND [cboAircraftType]="Astra" AND ([Carpet]or[Dye Job]or[Galle)

Duane Hookom said:
In an earlier post, I attempted to make you rename your combo box to use a
standard naming convention.
"If your combo box has a different name then rename it or change the code"
Apparently you didn't change the control name like I suggested.

You should kick your development up a notch and find (and use) a naming
convention that doesn't permit spaces in object names. Also, you should name
significant controls with standardized, significant names. If I intend to
refer to a combo box anywhere in code or queries or other, I name the combo
box beginning with "cbo". There are other prefixes for other types of
controls.
--
Duane Hookom
MS Access MVP

Aviator said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Apparently you don't copy and paste the code that I send to you. You are
continually leaving out spaces. There are three missing spaces in the next
section of code from what I posted to you last night. Looking at your debug
result should make two of the spaces fairly obvious.

If ctl.Value = True Then
'add to the where clause
strOrs = strOrs & "[" & ctl.Tag & "]or"
End If

--
Duane Hookom
MS Access MVP

Aviator said:
not sure if it help. I noticed in the error and in the Immediate window,
that
wether I selcet on check box or many, the last letter of the last check
box
name is missing. Immediate window example:

1=1 AND [cboAircraftType]="Astra" AND ([Carpet]or[Dye Job]or[Galle)

Duane Hookom said:
In an earlier post, I attempted to make you rename your combo box to use
a
standard naming convention.
"If your combo box has a different name then rename it or change the
code"
Apparently you didn't change the control name like I suggested.

You should kick your development up a notch and find (and use) a naming
convention that doesn't permit spaces in object names. Also, you should
name
significant controls with standardized, significant names. If I intend to
refer to a combo box anywhere in code or queries or other, I name the
combo
box beginning with "cbo". There are other prefixes for other types of
controls.
--
Duane Hookom
MS Access MVP

Aviator said:
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.

:

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

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I did copy and paste. I went thru and double checked the code. All spaces are
in the place that yours are. I did not find the three spaces that your are
speaking of. I copied then paste the one you just sent, and it made no
change.

Duane Hookom said:
Apparently you don't copy and paste the code that I send to you. You are
continually leaving out spaces. There are three missing spaces in the next
section of code from what I posted to you last night. Looking at your debug
result should make two of the spaces fairly obvious.

If ctl.Value = True Then
'add to the where clause
strOrs = strOrs & "[" & ctl.Tag & "]or"
End If

--
Duane Hookom
MS Access MVP

Aviator said:
not sure if it help. I noticed in the error and in the Immediate window,
that
wether I selcet on check box or many, the last letter of the last check
box
name is missing. Immediate window example:

1=1 AND [cboAircraftType]="Astra" AND ([Carpet]or[Dye Job]or[Galle)

Duane Hookom said:
In an earlier post, I attempted to make you rename your combo box to use
a
standard naming convention.
"If your combo box has a different name then rename it or change the
code"
Apparently you didn't change the control name like I suggested.

You should kick your development up a notch and find (and use) a naming
convention that doesn't permit spaces in object names. Also, you should
name
significant controls with standardized, significant names. If I intend to
refer to a combo box anywhere in code or queries or other, I name the
combo
box beginning with "cbo". There are other prefixes for other types of
controls.
--
Duane Hookom
MS Access MVP

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.

:

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

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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I didn't add them back in my most recent reply. I'm not sure how the spaces
got lost during your copy and paste.
I expected you to go back to my reply 5/3/2006 9:43 PM. This is another
instance where you should be able to figure this out on your own. I even had
the following comment in my code:
'remove the last " or " from the where clause

--
Duane Hookom
MS Access MVP

Aviator said:
I did copy and paste. I went thru and double checked the code. All spaces
are
in the place that yours are. I did not find the three spaces that your are
speaking of. I copied then paste the one you just sent, and it made no
change.

Duane Hookom said:
Apparently you don't copy and paste the code that I send to you. You are
continually leaving out spaces. There are three missing spaces in the
next
section of code from what I posted to you last night. Looking at your
debug
result should make two of the spaces fairly obvious.

If ctl.Value = True Then
'add to the where clause
strOrs = strOrs & "[" & ctl.Tag & "]or"
End If

--
Duane Hookom
MS Access MVP

Aviator said:
not sure if it help. I noticed in the error and in the Immediate
window,
that
wether I selcet on check box or many, the last letter of the last check
box
name is missing. Immediate window example:

1=1 AND [cboAircraftType]="Astra" AND ([Carpet]or[Dye Job]or[Galle)

:

In an earlier post, I attempted to make you rename your combo box to
use
a
standard naming convention.
"If your combo box has a different name then rename it or change the
code"
Apparently you didn't change the control name like I suggested.

You should kick your development up a notch and find (and use) a
naming
convention that doesn't permit spaces in object names. Also, you
should
name
significant controls with standardized, significant names. If I intend
to
refer to a combo box anywhere in code or queries or other, I name the
combo
box beginning with "cbo". There are other prefixes for other types of
controls.
--
Duane Hookom
MS Access MVP

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.

:

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

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I went back and recopied and pasted the one from before. Not sure how it got
lost in translation the first time. It now works just as I wanted. Thank you
very much for your patients in helping me and putting up with my
shortcomings. Wouldn't have been able to do it without you.

Duane Hookom said:
I didn't add them back in my most recent reply. I'm not sure how the spaces
got lost during your copy and paste.
I expected you to go back to my reply 5/3/2006 9:43 PM. This is another
instance where you should be able to figure this out on your own. I even had
the following comment in my code:
'remove the last " or " from the where clause

--
Duane Hookom
MS Access MVP

Aviator said:
I did copy and paste. I went thru and double checked the code. All spaces
are
in the place that yours are. I did not find the three spaces that your are
speaking of. I copied then paste the one you just sent, and it made no
change.

Duane Hookom said:
Apparently you don't copy and paste the code that I send to you. You are
continually leaving out spaces. There are three missing spaces in the
next
section of code from what I posted to you last night. Looking at your
debug
result should make two of the spaces fairly obvious.

If ctl.Value = True Then
'add to the where clause
strOrs = strOrs & "[" & ctl.Tag & "]or"
End If

--
Duane Hookom
MS Access MVP

not sure if it help. I noticed in the error and in the Immediate
window,
that
wether I selcet on check box or many, the last letter of the last check
box
name is missing. Immediate window example:

1=1 AND [cboAircraftType]="Astra" AND ([Carpet]or[Dye Job]or[Galle)

:

In an earlier post, I attempted to make you rename your combo box to
use
a
standard naming convention.
"If your combo box has a different name then rename it or change the
code"
Apparently you didn't change the control name like I suggested.

You should kick your development up a notch and find (and use) a
naming
convention that doesn't permit spaces in object names. Also, you
should
name
significant controls with standardized, significant names. If I intend
to
refer to a combo box anywhere in code or queries or other, I name the
combo
box beginning with "cbo". There are other prefixes for other types of
controls.
--
Duane Hookom
MS Access MVP

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.

:

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

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
Click to expand...
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

Report based on form filter? 1
Filter Report by Form 2
Crosstab Query Building Report 2
Pass x number of parameters to a report from a form 2
Filter report based on list box and combo box 3
Need a form for Customer Range - can I copy the one I used for dat 1
Limit Report on two date fields 2
Form with Multiple Parameters 2

Top