Hi all,
I have modified a code I saw here online that was perfect for my search form (I belive it is from Bob Larson), however it isn't working properly and I have looked into it for a about 5 hours now and can't figure it out.

When I run the code, it is popping up an input box when I have it reading the combo box on the form. I have NO idea why it is happening. I am guessing it is something simple.

I have attached a sample database for you to review.

Thanks in advance!
Larry

The form "seems" to work without error.

What code gave you the error? Where is said code located? What is the name of the subroutine where the error occurs? Which line in the subroutine?

Hi George!

if you look at the form in design view, click the report button, it is under the onclick().

If you pick a last name then pick the dates Jan 1 - Apr 30, click the HD report button. (this is where it happens) an input box appears. It happens like that on all the combo boxes(3) I have.



Private Sub cmdOpenHDrpt_Click()
On Error GoTo Err_Command6_Click

Dim stDocName As String
Dim stWhere As String
Dim stDates As String
Dim blnTrim As Boolean

If Not IsNull(Me.cmbLastName) Then
stWhere = " [LName] = " & Me.cmbLastName & " AND "
blnTrim = True
End If

If Not IsNull(Me.cmbSubject) Then
stWhere = stWhere & "[subject] = " & Me.cmbSubject & " AND"
blnTrim = True
End If

If IsNull(Me.txtHDSelectDate1) And Me.txtHDSelectDate1 = "" Then
If Not IsNull(Me.txtHDSelectDate1) And Me.txtHDSelectDate1 <> "" Then
stWhere = stWhere & " [DateOfContact] >= " & Me.txtHDSelectDate1
blnTrim = False
End If
Else
If IsNull(Me.txtHDSelectDate2) And Me.txtHDSelectDate2 = "" Then
If Not IsNull(Me.txtHDSelectDate2) And Me.txtHDSelectDate2 <> "" Then
stWhere = stWhere & " [DateOfcontact] <= " & Me.txtHDSelectDate2 & "#"
blnTrim = False
End If
Else
If (Not IsNull(Me.txtHDSelectDate1) And Me.txtHDSelectDate2 <> "") And (Not IsNull(Me.txtHDSelectDate2) Or Me.txtHDSelectDate1 <> "") Then
stWhere = stWhere & "[DateofContact] Between # " & Me.txtHDSelectDate1 & "# AND #" & Me.txtHDSelectDate2 & "#"
blnTrim = False
End If
End If
End If


stDocName = "rptHDComments_LarryTest"
DoCmd.OpenReport stDocName, acPreview, , stWhere


Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox err.Description
Resume Exit_Command6_Click
End Sub


Thanks for looking into it!

Sorry, I didn't see the button at the bottom. I have a small screen.

That's not an input box. It is a customized error box.

Please change the line:
stWhere = stWhere & "[subject] = " & Me.cmbSubject & " AND"

to
stWhere = " [LName] = '" & Me.cmbLastName & "' AND "

The stWhere variable does not have a space between the "AND" and the next where condition and all strings should be delimited by "'".

Change:

stWhere = stWhere & "[subject] = " & Me.cmbSubject & " AND "
to:
stWhere = stWhere & "[subject] = '" & Me.cmbSubject & "' AND "

Again, it needs the "'" delimiter.

Last is a problem with your dates. I'm not a date expert and find the topic boring, so I will defer the date problem to someone else. Generally, (after I removed the superflous space from the command that builds the string) the date in the "where" clause ends up being:
[DateofContact] Between #1/1/2008# AND #4/30/2008#

This seems right to me but, like I said, I'm not a date expert.

Thanks George...:D

I believe that may have worked. I'll test it more tonight and tomorrow and let you know how it is working.

Good! Glad I could help.