Compile error: Block If without If

R

rstiles

Good morning everyone,

First off I need to thank Marsh for giving me the outline for the
following code, I would not have been able to continue with this
application without it.

I have entered this code under my search command button and after
writing it all I am getting "Compile error: Block If without If" when I
try to debug the code (see below). I have heard that Access 2003 has
done this in past when there is not a problem with the code. Would you
guys be willing to take a look...

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "SELECT
Date,CaseNumber,Officer,OfficerNumber,Incident,Description FROM
tblCaseNumber "
strSort = " ORDER BY CaseNumber, DESC"
If Not IsNull(tbxDate) Then
strWhere = strWhere & " AND Date=" _
& Format(tbxDate, "\#m\/d\/yyyy\#")
'date field
If Not IsNull(tbxCaseNumber) Then
strWhere = strWhere & " AND CaseNumber=""" & tbxCaseNumber &
"""" 'text
If Not IsNull(tbxOfficer) Then
strWhere = strWhere & " AND Officer=""" & tbxOfficer & """"
'text
If Not IsNull(tbxOfficerNumber) Then
strWhere = strWhere & " AND OfficerNumber=""" &
tbxOfficerNumber & """" 'text
If Not IsNull(tbxIncident) Then
strWhere = strWhere & " AND Incident=""" & tbxIncident & """"
'text
If Not IsNull(tbxDescription) Then
strWhere = strWhere & " AND Description=""" & tbxDescription &
"""" 'text
End If
Me.lstCNSearch.RowSource = strSQL & Mid(strWhere, 6) & strSort
End Sub

Thankyou,

Ron
 
A

Al Campagna

rstiles,
This appears to be a coding problem first. I will not try to determine if there is
abything else wrong in the logic of the code, but only deal with the IFs and EndIfs
Each IF that is opened , must be closed by it's own EndIF.
When you work with nested IFs, it's always good practice to use indentation to keep
your Ifs and EndIfs straight.
Your code abbreviated........ (6 Ifs)
If
If
If
If
If
If
End If
End If
End If
End If
End If
End If

Try that first, and then see where you are...
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."
 
K

Keith Wilby

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "SELECT
Date,CaseNumber,Officer,OfficerNumber,Incident,Description FROM
tblCaseNumber "
strSort = " ORDER BY CaseNumber, DESC"
If Not IsNull(tbxDate) Then
strWhere = strWhere & " AND Date=" _
& Format(tbxDate, "\#m\/d\/yyyy\#")
'date field
If Not IsNull(tbxCaseNumber) Then
strWhere = strWhere & " AND CaseNumber=""" & tbxCaseNumber &
"""" 'text
If Not IsNull(tbxOfficer) Then
strWhere = strWhere & " AND Officer=""" & tbxOfficer & """"
'text
If Not IsNull(tbxOfficerNumber) Then
strWhere = strWhere & " AND OfficerNumber=""" &
tbxOfficerNumber & """" 'text
If Not IsNull(tbxIncident) Then
strWhere = strWhere & " AND Incident=""" & tbxIncident & """"
'text
If Not IsNull(tbxDescription) Then
strWhere = strWhere & " AND Description=""" & tbxDescription &
"""" 'text
End If
Me.lstCNSearch.RowSource = strSQL & Mid(strWhere, 6) & strSort
End Sub
Click to expand...

Try "ElseIf" for all but the first clause, eg

If Not IsNull(Something) Then
Do Something
ElseIf Not IsNull(SomethingElse) Then
Do Something Else

.... etc.

HTH - Keith.
www.keithwilby.com
 
B

BruceM

Each If needs an End If:

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "SELECT
Date,CaseNumber,Officer,OfficerNumber,Incident,Description FROM
tblCaseNumber "
strSort = " ORDER BY CaseNumber, DESC"
If Not IsNull(tbxDate) Then
strWhere = strWhere & " AND Date=" _
& Format(tbxDate, "\#m\/d\/yyyy\#")
If Not IsNull(tbxCaseNumber) Then
strWhere = strWhere & " AND CaseNumber=""" & tbxCaseNumber & """"
If Not IsNull(tbxOfficer) Then
strWhere = strWhere & " AND Officer=""" & tbxOfficer & """"
If Not IsNull(tbxOfficerNumber) Then
strWhere = strWhere & " AND OfficerNumber=""" &
tbxOfficerNumber & """"
If Not IsNull(tbxIncident) Then
strWhere = strWhere & " AND Incident=""" & tbxIncident &
""""
If Not IsNull(tbxDescription) Then
strWhere = strWhere & " AND Description=""" &
tbxDescription & """"
End If
End If
End If
End If
End If
End If
Me.lstCNSearch.RowSource = strSQL & Mid(strWhere, 6) & strSort
End Sub

However, I'm not sure this is going to give you the result you want, since
from what I can tell strWhere will give you unusable results in at least
some cases. If tbxDate contains the value 1/1/2007 and the rest of the text
boxes are blank, Mid(strWhere, 6) = 007, which isn't any help in your select
statement. On 1/10/2007 the result will be 2007. On 10/10/2007 it will be
/2007. If the other text boxes contain values those will be appended to
strWhere. Also, Date is a reserved word, which should not be used for a
field name. If it is, the field name must be enclosed in square brackets.
However, it would be best to change the field name.
It looks as if strWhere is supposed to start with something like " WHERE ".
I expect you used Mid due to a misunderstanding about the somewhat puzzling
bit in Help about a Where statement without the Where, which doesn't apply
in the case of a SQL statement such as this. You need the WHERE.
Something like this may do the trick (using tbxDate as the example, and
assuming strWhere is already defined as "WHERE "):
strWhere = strWhere & "Date= " _
& Format(tbxDate, "\#m\/d\/yyyy\#") & " AND "
Add the " AND " to the end of each strWhere. At the end of the code, snip
off the last four characters:
strWhere = Left(strWhere,Len(strWhere) - 4)
strSort then doesn't need spaces before ORDER BY"

I'm not sure you have the quotes right for the text fields. It may need to
be something like:
"Officer =" & """" & tbxOfficer & """"
Or, with the AND at the end, maybe:
"Officer =" & """" & tbxOfficer & " AND """
Frankly, the double-double quote business still has me a bit puzzled, so
don't take this suggestion too literally.
This article may help provide some insight into SQL in VBA
http://www.mvps.org/access/forms/frm0001.htm
The article shows a way of defining """" as a constant, which can simplify
the coding, but is not strictly necessary.
 
R

rstiles

Thankyou for your help,

The End If took care of the compile error but bruce was write and the
code did not work.

Maybe it will help to explain what I am attempting to do...

I have a tabbed form with four tabs, the first two are for data entry
and the last two tabs are for searching, the third tab is for the FCR
table and the last one for the Case Number table. All that I want to do
is create a basic search form -- one with unbound text boxes and a list
box for the results after the end user hits the command button.

Ron
 
R

rstiles

One last thing, I am using Access 2003 and the closest example of what
I would like would be a QBF exept having the results in the list box
instead of a new form. At one point I was thinking going that route but
in 2003 it appears it like the pop ups better which would not work for
my app.

Ron
 
F

fredg

Good morning everyone,

First off I need to thank Marsh for giving me the outline for the
following code, I would not have been able to continue with this
application without it.

I have entered this code under my search command button and after
writing it all I am getting "Compile error: Block If without If" when I
try to debug the code (see below). I have heard that Access 2003 has
done this in past when there is not a problem with the code. Would you
guys be willing to take a look...

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "SELECT
Date,CaseNumber,Officer,OfficerNumber,Incident,Description FROM
tblCaseNumber "
strSort = " ORDER BY CaseNumber, DESC"
If Not IsNull(tbxDate) Then
strWhere = strWhere & " AND Date=" _
& Format(tbxDate, "\#m\/d\/yyyy\#")
'date field
If Not IsNull(tbxCaseNumber) Then
strWhere = strWhere & " AND CaseNumber=""" & tbxCaseNumber &
"""" 'text
If Not IsNull(tbxOfficer) Then
strWhere = strWhere & " AND Officer=""" & tbxOfficer & """"
'text
If Not IsNull(tbxOfficerNumber) Then
strWhere = strWhere & " AND OfficerNumber=""" &
tbxOfficerNumber & """" 'text
If Not IsNull(tbxIncident) Then
strWhere = strWhere & " AND Incident=""" & tbxIncident & """"
'text
If Not IsNull(tbxDescription) Then
strWhere = strWhere & " AND Description=""" & tbxDescription &
"""" 'text
End If
Me.lstCNSearch.RowSource = strSQL & Mid(strWhere, 6) & strSort
End Sub

Thankyou,

Ron
Click to expand...

The other guys have told you about the lack of end if's in your code.

Regarding this line >> strSQL = "SELECT Date,CaseNumber, ...etc. <<
In addition to their remarks, Date is a reserved Access/VBA/Jet word
and should not be used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
 
M

Marshall Barton

Good morning everyone,

First off I need to thank Marsh for giving me the outline for the
following code, I would not have been able to continue with this
application without it.

I have entered this code under my search command button and after
writing it all I am getting "Compile error: Block If without If" when I
try to debug the code (see below). I have heard that Access 2003 has
done this in past when there is not a problem with the code. Would you
guys be willing to take a look...

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "SELECT
Date,CaseNumber,Officer,OfficerNumber,Incident,Description FROM
tblCaseNumber "
strSort = " ORDER BY CaseNumber, DESC"
If Not IsNull(tbxDate) Then
strWhere = strWhere & " AND Date=" _
& Format(tbxDate, "\#m\/d\/yyyy\#")
'date field
If Not IsNull(tbxCaseNumber) Then
strWhere = strWhere & " AND CaseNumber=""" & tbxCaseNumber &
"""" 'text
If Not IsNull(tbxOfficer) Then
strWhere = strWhere & " AND Officer=""" & tbxOfficer & """"
'text
If Not IsNull(tbxOfficerNumber) Then
strWhere = strWhere & " AND OfficerNumber=""" &
tbxOfficerNumber & """" 'text
If Not IsNull(tbxIncident) Then
strWhere = strWhere & " AND Incident=""" & tbxIncident & """"
'text
If Not IsNull(tbxDescription) Then
strWhere = strWhere & " AND Description=""" & tbxDescription &
"""" 'text
End If
Me.lstCNSearch.RowSource = strSQL & Mid(strWhere, 6) & strSort
End Sub
Click to expand...


Since each If (condition and result) is independent of the
other If block, you need to use this logic structure:

If
...
End If
If
...
End If
If
...
End If
....
 
B

BruceM

What is Date in this part of the expression? Is it a field?

If Not IsNull(tbxDate) Then
strWhere = strWhere & " AND Date=" _
& Format(tbxDate, "\#m\/d\/yyyy\#")

If it is a field, note what I and others have said about using Date as a
field name. Also, note what I said about defining strWhere, and about
putting AND at the end. Now simplify so that you are testing for a single
variable.

strWhere = "WHERE "
If Not IsNull(tbxDate) Then
strWhere = strWhere & " AND [Date] =" _
& Format(tbxDate, "\#m\/d\/yyyy\#") & " AND "
MsgBox strWhere
End If

Are you getting the expected result in the message box?

Is your intention to find all records with fields that match the current
record, or are you intending to search for a particular record based on
other criteria? Knowing that you have a four-tab form does little to
explain how your database is structured.
 
R

rstiles

Thankyou everyone for your comments and suggestions...for the most part
I believe I have changed what Have needed to, here is the code:

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "WHERE
CNDate,CaseNumber,Officer,OfficerNumber,Incident,Description FROM
tblCaseNumber "
strSort = "ORDER BY CaseNumber, DESC"
strWhere = "WHERE"
If Not IsNull(tbxDate) Then
strWhere = strWhere & " CNDate =" _
& Format(tbxDate, "\#m\/d\/yyyy\#") & " AND "
'date field
strWhere = Left(strWhere, Len(strWhere) - 4)
If Not IsNull(tbxCaseNumber) Then
strWhere = strWhere & "CaseNumber=" & """ & tbxCaseNumber &
" And """ "
If Not IsNull(tbxOfficer) Then
strWhere = strWhere & "Officer=" & """ & tbxOfficer & "
And """ 'text"
If Not IsNull(tbxOfficerNumber) Then
strWhere = strWhere & "OfficerNumber=" & """ &
tbxOfficerNumber " And """ "
If Not IsNull(tbxIncident) Then
strWhere = strWhere & "Incident=" & """ &
tbxIncident " And """ "
If Not IsNull(tbxDescription) Then
strWhere = strWhere & "Description=" & """
& tbxDescription " And """ "
End If
End If
End If
End If
End If
End If
Me.lstCNSearch.RowSource = strSQL & Mid(strWhere, 6) & strSort
End Sub

Bruce,

To answer your question on what I want to do...if someone enters
"Smith" into the name text box, then I would like all of the Smith's
displayed in the list box from the CN table; if they enter "Smith" and
"John" and "7/14/2006" it will pull up John Smith if he was contacted
on 7/14/2006; and I can not forget if they enter 7/14/2006, it will
pull all of the records from that date.

I hope that this was what you were asking about.

Thankyou again everyone for all of your time.

Ron
 
R

rstiles

Thankyou everyone for your comments and suggestions...for the most part
I believe I have changed what Have needed to, here is the code:

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "WHERE
CNDate,CaseNumber,Officer,OfficerNumber,Incident,Description FROM
tblCaseNumber "
strSort = "ORDER BY CaseNumber, DESC"
strWhere = "WHERE"
If Not IsNull(tbxDate) Then
strWhere = strWhere & " CNDate =" _
& Format(tbxDate, "\#m\/d\/yyyy\#") & " AND "
'date field
strWhere = Left(strWhere, Len(strWhere) - 4)
If Not IsNull(tbxCaseNumber) Then
strWhere = strWhere & "CaseNumber=" & """ & tbxCaseNumber &
" And """ "
If Not IsNull(tbxOfficer) Then
strWhere = strWhere & "Officer=" & """ & tbxOfficer & "
And """ 'text"
If Not IsNull(tbxOfficerNumber) Then
strWhere = strWhere & "OfficerNumber=" & """ &
tbxOfficerNumber " And """ "
If Not IsNull(tbxIncident) Then
strWhere = strWhere & "Incident=" & """ &
tbxIncident " And """ "
If Not IsNull(tbxDescription) Then
strWhere = strWhere & "Description=" & """
& tbxDescription " And """ "
End If
End If
End If
End If
End If
End If
Me.lstCNSearch.RowSource = strSQL & Mid(strWhere, 6) & strSort
End Sub

Bruce,

To answer your question on what I want to do...if someone enters
"Smith" into the name text box, then I would like all of the Smith's
displayed in the list box from the CN table; if they enter "Smith" and
"John" and "7/14/2006" it will pull up John Smith if he was contacted
on 7/14/2006; and I can not forget if they enter 7/14/2006, it will
pull all of the records from that date.

I hope that this was what you were asking about.

Thankyou again everyone for all of your time.

Ron
 
M

Marshall Barton

Thankyou everyone for your comments and suggestions...for the most part
I believe I have changed what Have needed to, here is the code:

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "WHERE
CNDate,CaseNumber,Officer,OfficerNumber,Incident,Description FROM
tblCaseNumber "
strSort = "ORDER BY CaseNumber, DESC"
strWhere = "WHERE"
If Not IsNull(tbxDate) Then
strWhere = strWhere & " CNDate =" _
& Format(tbxDate, "\#m\/d\/yyyy\#") & " AND "
'date field
strWhere = Left(strWhere, Len(strWhere) - 4)
If Not IsNull(tbxCaseNumber) Then
strWhere = strWhere & "CaseNumber=" & """ & tbxCaseNumber &
" And """ "
If Not IsNull(tbxOfficer) Then
strWhere = strWhere & "Officer=" & """ & tbxOfficer & "
And """ 'text"
If Not IsNull(tbxOfficerNumber) Then
strWhere = strWhere & "OfficerNumber=" & """ &
tbxOfficerNumber " And """ "
If Not IsNull(tbxIncident) Then
strWhere = strWhere & "Incident=" & """ &
tbxIncident " And """ "
If Not IsNull(tbxDescription) Then
strWhere = strWhere & "Description=" & """
& tbxDescription " And """ "
End If
End If
End If
End If
End If
End If
Me.lstCNSearch.RowSource = strSQL & Mid(strWhere, 6) & strSort
End Sub

Bruce,

To answer your question on what I want to do...if someone enters
"Smith" into the name text box, then I would like all of the Smith's
displayed in the list box from the CN table; if they enter "Smith" and
"John" and "7/14/2006" it will pull up John Smith if he was contacted
on 7/14/2006; and I can not forget if they enter 7/14/2006, it will
pull all of the records from that date.

I hope that this was what you were asking about.
Click to expand...


Did you see my other reply? The If blocks should NOT be
nested.
 
R

rstiles

Marshall said:
Did you see my other reply? The If blocks should NOT be
nested.
Click to expand...

Thankyou March for reminding me...just did that as well and still no
results :-(
 
M

Marshall Barton

Thankyou March for reminding me...just did that as well and still no
results :-(
Click to expand...


It would help if you added:
Debug.Print trSQL & Mid(strWhere, 6) & strSort
right before you set the RowSource.

From an earlier post, I see that you are assigning strSQL
using the word WHERE when you should have the word SELECT.
There may be other issues, but the Debug.Print should make
them easier to spot.

If you are still having problems, post your current code and
explain what the results were. "still no results" just
doesn't provide any clues about what's happening.
 
R

rstiles

Marsh,

When I enter the search criteria into the text box and hit the search
command button the results are not showing up in the listbox below. To
make things easier I am placing a link below the show you a screen shot
of this particular page.

http://www.freeimagehost.eu/image/2334b917816

Here is the code as well...

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "WHERE
CNDate,CaseNumber,Officer,OfficerNumber,Incident,Description FROM
tblCaseNumber "
strSort = "ORDER BY CaseNumber, DESC"
strWhere = "WHERE"
If Not IsNull(tbxDate) Then
strWhere = strWhere & " CNDate =" _
& Format(tbxDate, "\#m\/d\/yyyy\#") & " AND "
'date field
strWhere = Left(strWhere, Len(strWhere) - 4)
End If
If Not IsNull(tbxCaseNumber) Then
strWhere = strWhere & "CaseNumber=" & """ & tbxCase# & " And
""" "
End If
If Not IsNull(tbxOfficer) Then
strWhere = strWhere & "Officer=" & """ & tbxOfficer & " And """
"
End If
If Not IsNull(tbxOfficerNumber) Then
strWhere = strWhere & "OfficerNumber=" & """ & tbxOfficerNumber
" And """ "
End If
If Not IsNull(tbxIncident) Then
strWhere = strWhere & "Incident=" & """ & tbxIncident " And """
"
End If
If Not IsNull(tbxDescription) Then
strWhere = strWhere & "Description=" & """ & tbxDescription "
And """ "
End If
Debug.Print trSQL & Mid(strWhere, 6) & strSort
Me.lstCNSearch.RowSource = strSQL & Mid(strWhere, 6) & strSort

End Sub

When I go to debug the case number string apparently it has a problem
but I have double and triple checked it and can not find what is wrong
there.
 
R

rstiles

One last thought...

Would it be easier to use a filter code for what I am trying to do.
 
M

Marshall Barton

When I enter the search criteria into the text box and hit the search
command button the results are not showing up in the listbox below. To
make things easier I am placing a link below the show you a screen shot
of this particular page.

http://www.freeimagehost.eu/image/2334b917816

Here is the code as well...

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "WHERE
CNDate,CaseNumber,Officer,OfficerNumber,Incident,Description FROM
tblCaseNumber "
strSort = "ORDER BY CaseNumber, DESC"
strWhere = "WHERE"
If Not IsNull(tbxDate) Then
strWhere = strWhere & " CNDate =" _
& Format(tbxDate, "\#m\/d\/yyyy\#") & " AND "
'date field
strWhere = Left(strWhere, Len(strWhere) - 4)
End If
If Not IsNull(tbxCaseNumber) Then
strWhere = strWhere & "CaseNumber=" & """ & tbxCase# & " And
""" "
End If
If Not IsNull(tbxOfficer) Then
strWhere = strWhere & "Officer=" & """ & tbxOfficer & " And """
"
End If
If Not IsNull(tbxOfficerNumber) Then
strWhere = strWhere & "OfficerNumber=" & """ & tbxOfficerNumber
" And """ "
End If
If Not IsNull(tbxIncident) Then
strWhere = strWhere & "Incident=" & """ & tbxIncident " And """
"
End If
If Not IsNull(tbxDescription) Then
strWhere = strWhere & "Description=" & """ & tbxDescription "
And """ "
End If
Debug.Print trSQL & Mid(strWhere, 6) & strSort
Me.lstCNSearch.RowSource = strSQL & Mid(strWhere, 6) & strSort

End Sub

When I go to debug the case number string apparently it has a problem
but I have double and triple checked it and can not find what is wrong
there.
Click to expand...


That code looks like a conglomeration of code from several
different people and there are numerous typos and syntax
errors. There's no way that could come close to working.

What did the Debug.Print show? If you had looked at the
Immediate window, you would have seen that the SQL statement
was a mess.

You really need to understand each line of code that you are
trying to use. Studying it and manually (with pencil and
paper) executing it is a good way to learn about all this
stuff. Doing that while single stepping through the code
and working to understand any differences from what you
thought should have happened is a great learning experience.
If you don't know what someone is talking about (break mode,
single step, etc), check VBA Help. If your'e still lost,
try to explain your confusion and ask here for a
clarification.

I'll try to rewrite your code, but don't just copy paste it
and expect it to work without you developing a thorough
understanding of how and why it's supposed to work. Since I
can't duplicate your database, there may be errors, but you
should be able to deduce any changes that might be needed.

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "SELECT CNDate, CaseNumber, Officer, " & _
"OfficerNumber, Incident, Description " & _
"FROM tblCaseNumber "
strSort = " ORDER BY CaseNumber DESC"

If Not IsNull(tbxDate) Then
strWhere = strWhere & " AND CNDate =" _
& Format(tbxDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(tbxCaseNumber) Then
strWhere = strWhere & " AND CaseNumber=" _
& tbxCaseNumber
End If
If Not IsNull(tbxOfficer) Then
strWhere = strWhere & " AND Officer=""" _
& tbxOfficer & """ "
End If
If Not IsNull(tbxOfficerNumber) Then
strWhere = strWhere & " AND OfficerNumber= _
& tbxOfficerNumber
End If
If Not IsNull(tbxIncident) Then
strWhere = strWhere & " AND Incident=""" _
& tbxIncident & """ "
End If
If Not IsNull(tbxDescription) Then
strWhere = strWhere & " AND Description=""" _
& tbxDescription & """ "
End If
Debug.Print strSQL & " WHERE " & Mid(strWhere, 6) & strSort
Me.lstCNSearch.RowSource = _
strSQL & " WHERE " & Mid(strWhere, 6) & strSort

End Sub
 
R

rstiles

Marsh,

Thankyou for your suggestions. While I have taken a look at the code
that you have done for me, I am breaking it down bit by bit to gain an
even greater understanding.

I did place this in and made a few changes, mainly taking out the
line/code continuation.

Right now all that is happening when I enter what I want to search for
is the list box staying blank or if I have the row source set in the
list box properties, it goes blank.

When I debug the code it is checking out ok, know in the imediate
window I am getting this result when I try a search and in this case I
was hoping to get all the results for officer 303.

SELECT CNDate, CaseNumber, Officer, OfficerNumber, Incident,
Description FROM tblCaseNumber WHERE CaseNumber= AND
OfficerNumber="303" AND Incident="" ORDER BY CaseNumber DESC

One last question, when it comes to learning VBA what is one of the
better books to learn from? I already have Access 2003 for Dumies and
started the VBA chapter a couple of days ago.
 
B

BruceM

You say the Debug.Print shows satisfactory results. Does the list box show
the expected results if you type the result of Debug.Print directly into its
Row Source?
 
R

rstiles

Actually I was wrong, and before I go further I should make sure that
my understanding of debug.print is correct. When I run this code the
results of debug.print are in the imediate window. If that is correct,
this is what is showing up when I am searching for 11/27/2005:

SELECT CNDate, CaseNumber, Officer, OfficerNumber, Incident,
Description FROM tblCaseNumber WHERE CNDate =#11/27/2005# AND
CaseNumber= AND OfficerNumber= AND Incident="" ORDER BY CaseNumber
DESC

This also showing up in the row source for the list box properties as
well with no results just a blank screen.

Does it matter that the subforms record source property is pointing to
the table?

Ron
 

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

Compile error: Argument not optional 3
Error on modified Allenbrowne SearchForm 6
Create Report On the Fly 1
Extra ) error in search form 4
Modified Allenbrowne Search Form displays error 1
Allen Brownes ECount() 5
Require a field in search form 1
runtime error 3075 2

Top