Coding Problem

[Stacey]

I recently recieved some help from Allen regarding another issue and I am
trying oto incorporate the code he gave me to perform another task...however
it doesn't seem to work. Can someone please take a look at the code and tell
me what I am doing wrong. Thanks in advance!

Private Sub Command27_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
With Me.List12
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.List21
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command27_Click"
End If
End Sub

 

[strive4peace]

Initialize variables
---


Hi Stacey,

what is the value of strDelim ? you have not initialized it.

for text:
strDelim = "'"

for numbers:
strDelim = ""

for dates:
strDelim = "#"

you also need to initalize your string variables:
strWhere1 = ""
strWhere2 = ""
strWhere3 = ""

it is also better to test the length of a string instead of the value...

instead of

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If

try this:
If len(trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
end if

If len(trim(strWhere2)) > 0 Then
If len(trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
end if

~~~

and one last note:

it is a good idea to change the NAME property of controls before you use
them in code.

for instance:
Command27 --> cmdEntitlementReport

then, the procedure devclaration line should change from
Private Sub Command27_Click()
to
Private Sub cmdEntitlementReport_Click()


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I recently recieved some help from Allen regarding another issue and I am
trying oto incorporate the code he gave me to perform another task...however
it doesn't seem to work. Can someone please take a look at the code and tell
me what I am doing wrong. Thanks in advance!

Private Sub Command27_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
With Me.List12
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.List21
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command27_Click"
End If
End Sub

 

[Stacey]

Hi Crystal...thanks for helping! Ok, I followed your suggestions, and now I
get a data mismatch...any recommendations? see below for the code.

Private Sub cmdEntitlementReport_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
strDelim = ""
With Me.Bkname
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.Conumber
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If Len(Trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
End If

If Len(Trim(strWhere2)) > 0 Then
If Len(Trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
End If
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdEntitlementReport_Click"
End If

End Sub

Initialize variables
---


Hi Stacey,

what is the value of strDelim ? you have not initialized it.

for text:
strDelim = "'"

for numbers:
strDelim = ""

for dates:
strDelim = "#"

you also need to initalize your string variables:
strWhere1 = ""
strWhere2 = ""
strWhere3 = ""

it is also better to test the length of a string instead of the value...

instead of

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If

try this:
If len(trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
end if

If len(trim(strWhere2)) > 0 Then
If len(trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
end if

~~~

and one last note:

it is a good idea to change the NAME property of controls before you use
them in code.

for instance:
Command27 --> cmdEntitlementReport

then, the procedure devclaration line should change from
Private Sub Command27_Click()
to
Private Sub cmdEntitlementReport_Click()


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I recently recieved some help from Allen regarding another issue and I am
trying oto incorporate the code he gave me to perform another task...however
it doesn't seem to work. Can someone please take a look at the code and tell
me what I am doing wrong. Thanks in advance!

Private Sub Command27_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
With Me.List12
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.List21
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command27_Click"
End If
End Sub

 

[strive4peace]

Error Handler, Compile code
---

Hi Stacey,

this needs another statement (End If):

If Len(Trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "

End If

Which statement is causing the problem? I see you have an error handler
at the bottom, but you have not set the procedure up to go there...
While I am developing, I like to make the error handler go to the line
that caused the problem so I can see where it is. Stop will stop the
code and Resume goes back to the offending line. When code Stops, press
F8 to execute one statement at a time.

Add an error handler to your code

put this at the top of your program, right after the procedure
declaration (skip a line first for better readability)

'~~~~~~~~~~~~~~~~~~~~~~
'set up Error Handler
On Error GoTo Proc_Err

'~~~~~~~~~~~~~~~~~~~~~~
... then your statements
'~~~~~~~~~~~~~~~~~~~~~~

put this at the end of the program

'~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
On Error Resume Next
'release object variables if any
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit

'~~~~~~~~~~~~~~~~~~~~~~

where
ProcedureName is the name of your procedure so you can identify what
code the problem is in when you see the error

The line labels do not matter (Proc_Exit:, Proc_Err, I like to use the
same ones all the time -- they only have to be unique within a procedure.

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


***********************************************

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal...thanks for helping! Ok, I followed your suggestions, and now I
get a data mismatch...any recommendations? see below for the code.

Private Sub cmdEntitlementReport_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
strDelim = ""
With Me.Bkname
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.Conumber
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If Len(Trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
End If

If Len(Trim(strWhere2)) > 0 Then
If Len(Trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
End If
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdEntitlementReport_Click"
End If

End Sub

Initialize variables
---


Hi Stacey,

what is the value of strDelim ? you have not initialized it.

for text:
strDelim = "'"

for numbers:
strDelim = ""

for dates:
strDelim = "#"

you also need to initalize your string variables:
strWhere1 = ""
strWhere2 = ""
strWhere3 = ""

it is also better to test the length of a string instead of the value...

instead of

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If

try this:
If len(trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
end if

If len(trim(strWhere2)) > 0 Then
If len(trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
end if

~~~

and one last note:

it is a good idea to change the NAME property of controls before you use
them in code.

for instance:
Command27 --> cmdEntitlementReport

then, the procedure devclaration line should change from
Private Sub Command27_Click()
to
Private Sub cmdEntitlementReport_Click()


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I recently recieved some help from Allen regarding another issue and I am
trying oto incorporate the code he gave me to perform another task...however
it doesn't seem to work. Can someone please take a look at the code and tell
me what I am doing wrong. Thanks in advance!

Private Sub Command27_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
With Me.List12
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.List21
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command27_Click"
End If
End Sub

 

[Stacey]

Hi Crystal,

I figured out what the problem was...I was treating a number in a text
field as a number in a number field. Thanks for taking the time to explain
how to debug a program...it really helped in isolating the problem. The code
was driving me crazy and you help me to break it down and understand each
part. Again, thank you!

Stacey

Error Handler, Compile code
---

Hi Stacey,

this needs another statement (End If):

If Len(Trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "

End If

Which statement is causing the problem? I see you have an error handler
at the bottom, but you have not set the procedure up to go there...
While I am developing, I like to make the error handler go to the line
that caused the problem so I can see where it is. Stop will stop the
code and Resume goes back to the offending line. When code Stops, press
F8 to execute one statement at a time.

Add an error handler to your code

put this at the top of your program, right after the procedure
declaration (skip a line first for better readability)

'~~~~~~~~~~~~~~~~~~~~~~
'set up Error Handler
On Error GoTo Proc_Err

'~~~~~~~~~~~~~~~~~~~~~~
... then your statements
'~~~~~~~~~~~~~~~~~~~~~~

put this at the end of the program

'~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
On Error Resume Next
'release object variables if any
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit

'~~~~~~~~~~~~~~~~~~~~~~

where
ProcedureName is the name of your procedure so you can identify what
code the problem is in when you see the error

The line labels do not matter (Proc_Exit:, Proc_Err, I like to use the
same ones all the time -- they only have to be unique within a procedure.

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


***********************************************

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal...thanks for helping! Ok, I followed your suggestions, and now I
get a data mismatch...any recommendations? see below for the code.

Private Sub cmdEntitlementReport_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
strDelim = ""
With Me.Bkname
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.Conumber
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If Len(Trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
End If

If Len(Trim(strWhere2)) > 0 Then
If Len(Trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
End If
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdEntitlementReport_Click"
End If

End Sub

Initialize variables
---


Hi Stacey,

what is the value of strDelim ? you have not initialized it.

for text:
strDelim = "'"

for numbers:
strDelim = ""

for dates:
strDelim = "#"

you also need to initalize your string variables:
strWhere1 = ""
strWhere2 = ""
strWhere3 = ""

it is also better to test the length of a string instead of the value...

instead of
If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If

try this:
If len(trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
end if

If len(trim(strWhere2)) > 0 Then
If len(trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
end if

~~~

and one last note:

it is a good idea to change the NAME property of controls before you use
them in code.

for instance:
Command27 --> cmdEntitlementReport

then, the procedure devclaration line should change from
Private Sub Command27_Click()
to
Private Sub cmdEntitlementReport_Click()


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Stacey wrote:
I recently recieved some help from Allen regarding another issue and I am
trying oto incorporate the code he gave me to perform another task...however
it doesn't seem to work. Can someone please take a look at the code and tell
me what I am doing wrong. Thanks in advance!

Private Sub Command27_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
With Me.List12
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.List21
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command27_Click"
End If
End Sub

 

[Stacey]

Crystal, one last question....how would I go about adding another combo box
code?

Hi Crystal,

I figured out what the problem was...I was treating a number in a text
field as a number in a number field. Thanks for taking the time to explain
how to debug a program...it really helped in isolating the problem. The code
was driving me crazy and you help me to break it down and understand each
part. Again, thank you!

Stacey

Error Handler, Compile code
---

Hi Stacey,

this needs another statement (End If):

If Len(Trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "

End If

Which statement is causing the problem? I see you have an error handler
at the bottom, but you have not set the procedure up to go there...
While I am developing, I like to make the error handler go to the line
that caused the problem so I can see where it is. Stop will stop the
code and Resume goes back to the offending line. When code Stops, press
F8 to execute one statement at a time.

Add an error handler to your code

put this at the top of your program, right after the procedure
declaration (skip a line first for better readability)

'~~~~~~~~~~~~~~~~~~~~~~
'set up Error Handler
On Error GoTo Proc_Err

'~~~~~~~~~~~~~~~~~~~~~~
... then your statements
'~~~~~~~~~~~~~~~~~~~~~~

put this at the end of the program

'~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
On Error Resume Next
'release object variables if any
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit

'~~~~~~~~~~~~~~~~~~~~~~

where
ProcedureName is the name of your procedure so you can identify what
code the problem is in when you see the error

The line labels do not matter (Proc_Exit:, Proc_Err, I like to use the
same ones all the time -- they only have to be unique within a procedure.

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


***********************************************

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal...thanks for helping! Ok, I followed your suggestions, and now I
get a data mismatch...any recommendations? see below for the code.

Private Sub cmdEntitlementReport_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
strDelim = ""
With Me.Bkname
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.Conumber
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If Len(Trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
End If

If Len(Trim(strWhere2)) > 0 Then
If Len(Trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
End If
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdEntitlementReport_Click"
End If

End Sub

:

Initialize variables
---


Hi Stacey,

what is the value of strDelim ? you have not initialized it.

for text:
strDelim = "'"

for numbers:
strDelim = ""

for dates:
strDelim = "#"

you also need to initalize your string variables:
strWhere1 = ""
strWhere2 = ""
strWhere3 = ""

it is also better to test the length of a string instead of the value...

instead of
If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If

try this:
If len(trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
end if

If len(trim(strWhere2)) > 0 Then
If len(trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
end if

~~~

and one last note:

it is a good idea to change the NAME property of controls before you use
them in code.

for instance:
Command27 --> cmdEntitlementReport

then, the procedure devclaration line should change from
Private Sub Command27_Click()
to
Private Sub cmdEntitlementReport_Click()


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Stacey wrote:
I recently recieved some help from Allen regarding another issue and I am
trying oto incorporate the code he gave me to perform another task...however
it doesn't seem to work. Can someone please take a look at the code and tell
me what I am doing wrong. Thanks in advance!

Private Sub Command27_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
With Me.List12
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.List21
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command27_Click"
End If
End Sub

 

[strive4peace]

you're welcome, Stacey happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal,

I figured out what the problem was...I was treating a number in a text
field as a number in a number field. Thanks for taking the time to explain
how to debug a program...it really helped in isolating the problem. The code
was driving me crazy and you help me to break it down and understand each
part. Again, thank you!

Stacey

Error Handler, Compile code
---

Hi Stacey,

this needs another statement (End If):

If Len(Trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "

End If

Which statement is causing the problem? I see you have an error handler
at the bottom, but you have not set the procedure up to go there...
While I am developing, I like to make the error handler go to the line
that caused the problem so I can see where it is. Stop will stop the
code and Resume goes back to the offending line. When code Stops, press
F8 to execute one statement at a time.

Add an error handler to your code

put this at the top of your program, right after the procedure
declaration (skip a line first for better readability)

'~~~~~~~~~~~~~~~~~~~~~~
'set up Error Handler
On Error GoTo Proc_Err

'~~~~~~~~~~~~~~~~~~~~~~
... then your statements
'~~~~~~~~~~~~~~~~~~~~~~

put this at the end of the program

'~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
On Error Resume Next
'release object variables if any
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit

'~~~~~~~~~~~~~~~~~~~~~~

where
ProcedureName is the name of your procedure so you can identify what
code the problem is in when you see the error

The line labels do not matter (Proc_Exit:, Proc_Err, I like to use the
same ones all the time -- they only have to be unique within a procedure.

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


***********************************************

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal...thanks for helping! Ok, I followed your suggestions, and now I
get a data mismatch...any recommendations? see below for the code.

Private Sub cmdEntitlementReport_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
strDelim = ""
With Me.Bkname
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.Conumber
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If Len(Trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
End If

If Len(Trim(strWhere2)) > 0 Then
If Len(Trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
End If
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdEntitlementReport_Click"
End If

End Sub

:

Initialize variables
---


Hi Stacey,

what is the value of strDelim ? you have not initialized it.

for text:
strDelim = "'"

for numbers:
strDelim = ""

for dates:
strDelim = "#"

you also need to initalize your string variables:
strWhere1 = ""
strWhere2 = ""
strWhere3 = ""

it is also better to test the length of a string instead of the value...

instead of
If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If

try this:
If len(trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
end if

If len(trim(strWhere2)) > 0 Then
If len(trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
end if

~~~

and one last note:

it is a good idea to change the NAME property of controls before you use
them in code.

for instance:
Command27 --> cmdEntitlementReport

then, the procedure devclaration line should change from
Private Sub Command27_Click()
to
Private Sub cmdEntitlementReport_Click()


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Stacey wrote:
I recently recieved some help from Allen regarding another issue and I am
trying oto incorporate the code he gave me to perform another task...however
it doesn't seem to work. Can someone please take a look at the code and tell
me what I am doing wrong. Thanks in advance!

Private Sub Command27_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
With Me.List12
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.List21
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command27_Click"
End If
End Sub

 

[strive4peace]

building OpenReport Where parameter
---

Hi Stacey,

Actually, my approach to this would be a little different than what you
have done as I would not use a string variable for each criteria -- just
reuse the same string for each listbox case and add to the report
criteria string as things are specified.

here is a generic example that tests criteria and builds a filter string
to use as a parameter in OpenReport

assuming you are in the code behind the ReportMenu form...

'~~~~~~~~~~~~~~~~~~

'tell Access you are going to create a variable to hold text
dim mFilter as string

'initialize the variable
mFilter = ""

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.text_controlname & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname]= #" _
& me.date_controlname & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.numeric_controlname
end if

'----------------------- listbox
dim varItem as Variant _
, mListWhere as string
mListWhere = ""

For Each varItem In me.listbox_controlname.ItemsSelected

'delete the line that doesn't apply

'for text
mListWhere = mListWhere & "'" _
& me.listbox_controlname.ItemData(varItem) & "', "

'for numbers
mListWhere = mListWhere _
& me.listbox_controlname.ItemData(varItem) & ", "

Next varItem

if len(mListWhere) > 0 then
mListWhere = "[Field_Name] IN (" & mListWhere

'remove comma and space from the end and add a parenthesis
mListWhere = left(mListWhere,len(mListWhere)-2)) & ")"

'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "

mfilter = mfilter & mListWhere
end if
'-----------------------

DoCmd.OpenReport "ReportName", acViewPreview, , mfilter

'~~~~~~~~~~~~~~~~~~

I have used
TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

for listbox criteria, a loop is done through the selected items and the
criteria is listed and seperated by commas

'~~~~~~~~~~~~~~~~~~
each time, we are testing to see if a filter control is filled out.
If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
the first condition tested, obviously, does not have anything in the
filter string yet <smile>

make sure that the referenced fields are in the underlying recordset for
the report.
Since a filter is applied on the recordset, they do not have to be on
the report object (unlike a form)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = mFilter & "[DateFieldname]>= #" _
& me.date1_controlname & "#"
end if

If not IsNull(me.date2_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname] <= #" _
& me.date2_controlname & "#"
end if
'~~~~~~~~~~~~~~~~~~

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~
if len(trim(nz(me.filter,""))) > 0 then
me.Label_Criteria.Caption = me.filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~

hopefully, you can adapt this logic to your question -- if not, we can
help you further



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Crystal, one last question....how would I go about adding another combo box
code?

Hi Crystal,

I figured out what the problem was...I was treating a number in a text
field as a number in a number field. Thanks for taking the time to explain
how to debug a program...it really helped in isolating the problem. The code
was driving me crazy and you help me to break it down and understand each
part. Again, thank you!

Stacey

Error Handler, Compile code
---

Hi Stacey,

this needs another statement (End If):

If Len(Trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
End If

Which statement is causing the problem? I see you have an error handler
at the bottom, but you have not set the procedure up to go there...
While I am developing, I like to make the error handler go to the line
that caused the problem so I can see where it is. Stop will stop the
code and Resume goes back to the offending line. When code Stops, press
F8 to execute one statement at a time.

Add an error handler to your code

put this at the top of your program, right after the procedure
declaration (skip a line first for better readability)

'~~~~~~~~~~~~~~~~~~~~~~
'set up Error Handler
On Error GoTo Proc_Err

'~~~~~~~~~~~~~~~~~~~~~~
... then your statements
'~~~~~~~~~~~~~~~~~~~~~~

put this at the end of the program

'~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
On Error Resume Next
'release object variables if any
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit

'~~~~~~~~~~~~~~~~~~~~~~

where
ProcedureName is the name of your procedure so you can identify what
code the problem is in when you see the error

The line labels do not matter (Proc_Exit:, Proc_Err, I like to use the
same ones all the time -- they only have to be unique within a procedure.

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


***********************************************

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Stacey wrote:
Hi Crystal...thanks for helping! Ok, I followed your suggestions, and now I
get a data mismatch...any recommendations? see below for the code.

Private Sub cmdEntitlementReport_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
strDelim = ""
With Me.Bkname
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.Conumber
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If Len(Trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
End If

If Len(Trim(strWhere2)) > 0 Then
If Len(Trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
End If
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdEntitlementReport_Click"
End If

End Sub

:

Initialize variables
---


Hi Stacey,

what is the value of strDelim ? you have not initialized it.

for text:
strDelim = "'"

for numbers:
strDelim = ""

for dates:
strDelim = "#"

you also need to initalize your string variables:
strWhere1 = ""
strWhere2 = ""
strWhere3 = ""

it is also better to test the length of a string instead of the value...

instead of
If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If

try this:
If len(trim(strWhere1)) > 0 Then
strWhere3 = strWhere1
end if

If len(trim(strWhere2)) > 0 Then
If len(trim(strWhere3)) > 0 Then
strWhere3 = strWhere3 & " AND "
strWhere3 = strWhere3 & strWhere2
end if

~~~

and one last note:

it is a good idea to change the NAME property of controls before you use
them in code.

for instance:
Command27 --> cmdEntitlementReport

then, the procedure devclaration line should change from
Private Sub Command27_Click()
to
Private Sub cmdEntitlementReport_Click()


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Stacey wrote:
I recently recieved some help from Allen regarding another issue and I am
trying oto incorporate the code he gave me to perform another task...however
it doesn't seem to work. Can someone please take a look at the code and tell
me what I am doing wrong. Thanks in advance!

Private Sub Command27_Click()
Dim strReport As String
Dim strWhere1 As String
Dim varItem As Variant
Dim strWhere2 As String
Dim strWhere3 As String
Dim strDelim As String
strReport = "entitlements"
With Me.List12
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Bank_Number] IN (" & Left$(strWhere1, lngLen) & ")"
End If
With Me.List21
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[companyno] IN (" & Left$(strWhere2, lngLen) & ")"
End If

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command27_Click"
End If
End Sub