Compile error: Block If without If

[BruceM]

CaseNumber, OfficerNumber, and Incident are showing up in the string. Is
that the intention? The string is looking for records with the date
11/27/2005, a zero-length string in the Incident field, and nothing at all
in CaseNumber and OfficerNumber, which suggests that your test for IsNull is
not producing the expected results. Marshall's suggested code assumed that
CaseNumber and OfficerNumber are number fields. Is that accurate?
I will repeat what I have suggested before: search for one variable. Put
an apostrophe in front of every line of code from If Not
IsNull(tbxCaseNumber) Then and the End If just before Debug.Print. You are
now searching just for the date. Run the code. If it produces the expected
result, remove the apostrophes from the lines of code that test for
tbxCaseNumber, and run the code again. If there is a problem, stop right
there and solve it.
It would help to see the code you are actually using.
Regarding the record source table, I have lost track of what you are trying
to do. Are the fields in the string from the main form's record source?
However, the first step is to get the expected string. Right now it can't
work, no matter the record source.

 

[Marshall Barton]

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.

The structure of the SQL statement is ok, but the syntax is
still invalid. Note the part with
CaseNumber= AND
This indicates that tbxCaseNumber is Null. But, the code
explicitly tests for that so I guess that either the case
number text box is not named tbxCaseNumber or there is some
other confusion (e.g. you are typing "" on the text box).
The same kind of thing is happening with the text box,
tbxIncident.

Those two things lead me to wonder if the module with this
code is missing the statement:
OPTION EXPLICIT
at the top of the module. Make sure that line is there so
the VBA compiler can check for misspelled and undeclared
names.

I also see that you added quotes around the OfficerNumber
value. This is only appropriate if the field in the
**table** is type Text. OTOH, if the field is a numeric
type (Long, Integer, etc) then you need to get rid of the
quotes around the value:

Re book recommendations. With nearly 45 years of experience
as a programmer, I don't use books to learn a new language
so I can not make a meaningful suggestion. I am sure that
others can come up with a list though.

 

[rstiles]

Bruce and Marsh,

The Officer Number and Case Number are both text format which solved a
majority of the problems. I created this particular application this
way to avoid any complications when I importing everything over from
Paradox 3.5. The other part of the problem was the text box names,
there were a couple that I had thought I had changed but I didn't... I
changed them.

You were also right when it came to adding OPTION EXPLECIT, that solved
that problem.

Between the Debug.Print and testing each line by adding the apostrophe
I have managed to get the results that I want for the Officer, Officer
Number, and Incident text boxes...know I having fun again.

The only two that are still not showing any results are the date text
box and the Description text box. This is what is showing on the
debug.print for the date and incident searches:

SELECT CNDate, CaseNumber, Officer, OfficerNumber, Incident,
Description FROM tblCaseNumber WHERE ORDER BY CaseNumber DESC

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

I think that the desciption problem can be solved with changing it to a
wildcard. When this area was being used in Paradox they would enter
info like ...../......./....../......, etc. The only problem is I am
not sure where the place the wildcard characters for that particular
code.

Another problem that I noticed is that when I do a search and go to
close the form it wants to save the changes; I do not want this to
happen. Would I be right to say that I could place a code under the on
close property for this subform to prevent this from happening.

Here are some features that I would like to add, If you guys could
point me in the right direction:

I would like to add another code under my reset command button to clear
out the previous search.

I am also going to add another text to search for date ranges, if I am
correct I should just be able to add another date string and add the
additional code to my existing one.

Bruce, here a copy of my current code with the updated changes:

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(tbxDateCNS) Then
strWhere = strWhere & " AND CNDate " = Format(tbxDateCNS,
"\#m\/d\/yyyy\#")
End If
If Not IsNull(tbxCaseNumberCNS) Then
strWhere = strWhere & " AND CaseNumber=""" & tbxCaseNumberCNS &
""" "
End If
If Not IsNull(tbxOfficerCNS) Then
strWhere = strWhere & " AND Officer=""" & tbxOfficerCNS & """ "
End If
If Not IsNull(tbxOfficerNumberCNS) Then
strWhere = strWhere & " AND OfficerNumber=""" &
tbxOfficerNumberCNS & """ "
End If
If Not IsNull(tbxIncidentTypeCNS) Then
strWhere = strWhere & " AND Incident=""" & tbxIncidentTypeCNS &
""" "
End If
If Not IsNull(tbxDescriptionCNS) Then
strWhere = strWhere & " AND Description=""" & tbxDescriptionCNS
& """ "
End If
Debug.Print strSQL & " WHERE " & Mid(strWhere, 6) & strSort
Me.lstCNSearch.RowSource = strSQL & " WHERE " & Mid(strWhere, 6) &
strSort
End Sub

Overview: Officer, Officer Number, and Case Number are all working
exactly as I want them to. Date and Description are not showing any
results (see debug.print above).

To the both of you and everyone else, thank you for being patient with
me during this learning process, you're help is so appeciated.

Ron

 

[BruceM]

For this line:
strWhere = strWhere & " AND CNDate " = Format(tbxDateCNS, "\#m\/d\/yyyy\#")
try instead:
strWhere = strWhere & " AND CNDate = #" & Me.tbxDateCNS & "#"
I don't think you need to format in this case. If you do, I believe you
need to double the quotes around the format.

I believe Description is a reserved word. Try surrounding it with square
brackets: [Description]
For lists of reserved words:
http://office.microsoft.com/en-us/access/HP011353121033.aspx
http://support.microsoft.com/kb/248738/EN-US/
http://support.microsoft.com/default.aspx?scid=kb;en-us;209187
The third link shows Description as a reserved word. It is a property, but
I couldn't say just how it is used in VBA.

 

[rstiles]

One thing I forgot to ask,

Would it be possible to add a double click event for the results in the
list box to display them in the unbound text boxes above? Since they
are unbound I don't think so but I am really not sure.

 

[BruceM]

I'm not following the question. The list box has some number of columns,
and as many rows as the row source SQL produces. Are you using the list box
to select records? Do you intend to click on one of the selections and go
to the corresponding record?

You can use a command button's Click event to clear the unbound text boxes:
Me.tbxTextBoxName = Null
Repeat for the other text box names.

Did you have any success with the suggestions I made in the previous post?

 

[rstiles]

I tried [Description] and I am getting the same result as before.
Changing the date code worked. Thank you Bruce.

 

[BruceM]

Sometimes code that didn't work seems to get "stuck" even if it is
corrected. If it is easy enough to change the field name, do so, then
retype that section of code. If changing the field name would be difficult,
try retyping the code anyhow, with [Description] in brackets. Also, try
Me.tbxDescriptionCNS. After you type Me and the dot you should see a list
of choices. Make sure tbxDescriptionCNS is one of those choices. If it is
not, it's time for some detective work (verify the control name, check the
spelling, etc.).

 

[Marshall Barton]

The Officer Number and Case Number are both text format which solved a
majority of the problems. I created this particular application this
way to avoid any complications when I importing everything over from
Paradox 3.5. The other part of the problem was the text box names,
there were a couple that I had thought I had changed but I didn't... I
changed them.

You were also right when it came to adding OPTION EXPLECIT, that solved
that problem.

Between the Debug.Print and testing each line by adding the apostrophe
I have managed to get the results that I want for the Officer, Officer
Number, and Incident text boxes...know I having fun again.

The only two that are still not showing any results are the date text
box and the Description text box. This is what is showing on the
debug.print for the date and incident searches:

SELECT CNDate, CaseNumber, Officer, OfficerNumber, Incident,
Description FROM tblCaseNumber WHERE ORDER BY CaseNumber DESC

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

I think that the desciption problem can be solved with changing it to a
wildcard. When this area was being used in Paradox they would enter
info like ...../......./....../......, etc. The only problem is I am
not sure where the place the wildcard characters for that particular
code.

Another problem that I noticed is that when I do a search and go to
close the form it wants to save the changes; I do not want this to
happen. Would I be right to say that I could place a code under the on
close property for this subform to prevent this from happening.

Here are some features that I would like to add, If you guys could
point me in the right direction:

I would like to add another code under my reset command button to clear
out the previous search.

I am also going to add another text to search for date ranges, if I am
correct I should just be able to add another date string and add the
additional code to my existing one.

Bruce, here a copy of my current code with the updated changes:

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(tbxDateCNS) Then
strWhere = strWhere & " AND CNDate " = Format(tbxDateCNS,
"\#m\/d\/yyyy\#")
End If
If Not IsNull(tbxCaseNumberCNS) Then
strWhere = strWhere & " AND CaseNumber=""" & tbxCaseNumberCNS &
""" "
End If
If Not IsNull(tbxOfficerCNS) Then
strWhere = strWhere & " AND Officer=""" & tbxOfficerCNS & """ "
End If
If Not IsNull(tbxOfficerNumberCNS) Then
strWhere = strWhere & " AND OfficerNumber=""" &
tbxOfficerNumberCNS & """ "
End If
If Not IsNull(tbxIncidentTypeCNS) Then
strWhere = strWhere & " AND Incident=""" & tbxIncidentTypeCNS &
""" "
End If
If Not IsNull(tbxDescriptionCNS) Then
strWhere = strWhere & " AND Description=""" & tbxDescriptionCNS
& """ "
End If
Debug.Print strSQL & " WHERE " & Mid(strWhere, 6) & strSort
Me.lstCNSearch.RowSource = strSQL & " WHERE " & Mid(strWhere, 6) &
strSort
End Sub

Overview: Officer, Officer Number, and Case Number are all working
exactly as I want them to. Date and Description are not showing any
results (see debug.print above).

The problem with the date is that the = sign crawled out of
the quotes:
strWhere = strWhere & " AND CNDate = "
Format(tbxDateCNS,"\#m\/d\/yyyy\#")

You can match any part of the description by using:

strWhere = strWhere & " AND Description LIKE ""*" &
tbxDescriptionCNS & "*"" "

If you only want the description to match all the text
between slashes (pay careful attentention to where I placed
the quotes and ampersands):

strWhere = strWhere & " AND ""/"" & Description & ""/"" LIKE
""*/" & tbxDescriptionCNS & "/*"" "

Note that when using Like, users can enter wildcard
characters in tbxDescriptionCNS. This can be a powerful
feature or a source of great confusion depending on your
user's understanding of these things. The confusion may be
compounded if a description might contain any of the
wildcard characters.

Sorry, I just noticed that I made a mistake for the
situation when all the text boxes have nothing entered. To
guard against this situation, change these lines to:

Debug.Print strSQL & (" WHERE " + Mid(strWhere, 6)) &
strSort
Me.lstCNSearch.RowSource = strSQL & (" WHERE " +
Mid(strWhere, 6)) & strSort

*****************************************************************
The problem of the search strings being saved is a serious
issue. These search text boxes ***MUST*** be unbound (i.e.
empty ControlSource). According to what I think you've said
so far, the form's Record Source should also be empty.
*****************************************************************

To clear the search text boxes, add a button to the form and
use code like this in its Click event procedure:

Me.tbxDateCNS = Null
Me.tbxCaseNumberCNS = Null
Me.tbxOfficerCNS = Null
Me.tbxOfficerNumberCNS = Null
Me.tbxIncidentTypeCNS = Null
Me.tbxDescriptionCNS = Null

Adding the ability to search for a range of dates is more
complicated than just adding another If block. You need to
specify how you want to deal with situations where only one
of the two date text boxes is specified, if the start date
is after the end date, etc. I suggest that you get all the
other parts working before tackling any new features.

 

[rstiles]

With the improvements that you both have mentioned everything is
turning out really nice and smooth, I even have the FCR search side
finsihed and is working well.

There are only a couple of bugs to work out before I work on some other
additions. Here are the bugs:

When the end user uses the reset command button I would like it the
clear the list box as well. I have played around with a little by
adding me.lstCNSearch = Null and me.lstCNSearch.rowsource = Null to the
rest of the list and of course that does not want to work.

Is there a way to clear the row source when either the user uses the
reset command or when they close the application so it does not save
the last search. What I am trying to avoid is when the end user closes
the form I don't want the message box popping up asking them if they
would like the save the changes to such and such form, I think this
would confuse them.

Instead of using the table, do you see a problem using a query instead.
I would like to make a couple of column location changes in the list
boxes. I could make the changes from the tables but I am not fond of
that idea, too many chances of screwing something up.

Ron

 

[BruceM]

I believe you can clear the list box with "" instead of Null in the
expression. I don't recall that I have tried to modify the row source in
that way, but I expect it would work as well.
Not only is it not a problem to change the order of columns in the query (or
the order of the fields in the query's SQL) to change the order of fields in
the list box, it is the best way to do so. I don't think modifying the
table would make a difference unless maybe the row source SQL includes
SELECT *, and even then it's not the way to proceed. The row source is SQL
in any case. If the row source is a named query you can modify that;
otherwise click the three dots on the right side of Row Source in the list
box property sheet, and make the changes there.

 

[Marshall Barton]

With the improvements that you both have mentioned everything is
turning out really nice and smooth, I even have the FCR search side
finsihed and is working well.

There are only a couple of bugs to work out before I work on some other
additions. Here are the bugs:

When the end user uses the reset command button I would like it the
clear the list box as well. I have played around with a little by
adding me.lstCNSearch = Null and me.lstCNSearch.rowsource = Null to the
rest of the list and of course that does not want to work.

Is there a way to clear the row source when either the user uses the
reset command or when they close the application so it does not save
the last search. What I am trying to avoid is when the end user closes
the form I don't want the message box popping up asking them if they
would like the save the changes to such and such form, I think this
would confuse them.

Instead of using the table, do you see a problem using a query instead.
I would like to make a couple of column location changes in the list
boxes. I could make the changes from the tables but I am not fond of
that idea, too many chances of screwing something up.

To echo Bruce, clear the list box:
Me.lstCNSearch.RowSource = ""

You are already using a query as the list box's row source,
that's what this whole thread is about. To rearrange the
columns, change the line strSQL = "SELECT . . . " to have
the fields in whatever order you want. Just remember to
adjust the list box's ColumnWidths and BoundColumn
properties accordingly.

---------------------------------------------------------------------------
During normal user activities, you should NOT be prompted to
save changes to forms when you close a form or close Access.
Most likely the prompt is because you are making changes to
the form's design and you do need to save them. OTOH, you
might have some code that is doing something funny. To
determine which of those is happening, close out of Access,
then open your app, do a couple of searches and close the
form. If you get the prompt, start looking for some code
cause. If you do not get the prompt, all is normal and you
can stop worrying about it.

 

[rstiles]

"" worked, thanks the SQL info, that helps quite a bit...

 

[rstiles]

Now that I have this worked out, what would be the best approach to
adding a date range (2nd box Null, search single date in first box -
1st box null, msg box please enter a date).

I know that we talked about very briefly but in the list box I was
thinking about adding a double click command so that the end user can
edit a record that they have searched for, how difficult would this be.

The last thing that I can think of at the moment, what is the best
approach to print the records in the results list box? Can a source for
a report be a list box? Better yet if I am using multi-select is there
a way just to print the records that are selected?

Thanks,
Ron

 

[Marshall Barton]

Now that I have this worked out, what would be the best approach to
adding a date range (2nd box Null, search single date in first box -
1st box null, msg box please enter a date).

Change the If block for the date to something like:

If Not IsNull(tbxDateCNS) Then
If IsNull(tbxEndDateCNS) Then
strWhere = strWhere & " AND CNDate = " & _
Format(tbxDateCNS, "\#m\/d\/yyyy\#")
Else
strWhere = strWhere & " AND CNDate Between " & _
Format(tbxDateCNS, "\#m\/d\/yyyy\#") & _
" And " & Format(tbxEndDateCNS, "\#m\/d\/yyyy\#")
End If
ElseIf Not IsNull(tbxEndDateCNS) Then
MsgBox "Either enter a start date or clear the end date"
End If

I know that we talked about very briefly but in the list box I was
thinking about adding a double click command so that the end user can
edit a record that they have searched for, how difficult would this be.

I don't think this aspect makes sense with a multi select
list box. For a single select list box, this is simple.
Assuming you want to open another form to display/edit the
selected record, just use the list box's value in the
OpenForm method's WhereCondition argument.

DoCmd.OpenForm "[other form]", _
WhereConfition:= "[key field]=" & Me.lstCNSearch

The last thing that I can think of at the moment, what is the best
approach to print the records in the results list box? Can a source for
a report be a list box? Better yet if I am using multi-select is there
a way just to print the records that are selected?

Here's the general idea:

Dim varItem As Variant
Dim strWhere As String

For Each varItem In Me.lstCNSearch.ItemsSelected
strWhere = "," & Me.lstCNSearch.ItemData(varItem)
Next varItem
DoCmd.OpenReport "[name of report]", acViewPreview, _
WhereConfition:= "[key field] IN(" & Mid(strWhere,2) & ")"

 

[Ron S]

March,

I am working this part of my application in order to open the other
form editing

DoCmd.OpenForm "[other form]", _
WhereConfition:= "[key field]=" & Me.lstCNSearch

When I run the WhereCondition part of this code I am getting an
"Missing Operator" error.
This is what I am using:

DoCmd.OpenForm "frmCNSearchEdit", WhereCondition:="[CNID]=" &
Me.lstCNSearch

Any Ideas?

 

[Perry]

Either use it with parameter names like:
DoCmd.OpenForm FormName:= _
"frmCNSearchEdit", _
WhereCondition:="[CNID]=" & Me.lstCNSearch

Or use the designated parameter sequence, without the parameter names like:
docmd.OpenForm "frmCNSearchEdit",,,"[CNID]=" & Me.lstCNSearch
(note the comma's)

Krgrds,
Perry

 

[Marshall Barton]

I am working this part of my application in order to open the other
form editing

DoCmd.OpenForm "[other form]", _
WhereConfition:= "[key field]=" & Me.lstCNSearch

When I run the WhereCondition part of this code I am getting an
"Missing Operator" error.
This is what I am using:

DoCmd.OpenForm "frmCNSearchEdit", "[CNID]=" &
Me.lstCNSearch

That message implies that the list box value is a string
with funny characters in it. Most likely this means that
you have specified the wrong column in the list box's
BoundColumn property.

OTOH, if the list box is set up correctly and CNID is a Text
field, then the value needs to be in quotes:

DoCmd.OpenForm "frmCNSearchEdit", _
WhereCondition:= "[CNID]=""" & Me.lstCNSearch & """"

 

[Ron S]

Perry,

Thankyou for your suggestions, I tried your idea and I am getting the
same result with the same error.

March,

I worked with your updated code as well and know I am getting this
error:
Run-time error: '2501':
The OpenForm Action was cancelled

 

[Marshall Barton]

Perry,

Thankyou for your suggestions, I tried your idea and I am getting the
same result with the same error.

March,

I worked with your updated code as well and know I am getting this
error:
Run-time error: '2501':
The OpenForm Action was cancelled

That error usually means what it says. Look at the form's
Open and Load event procedures and try to find where you
have
Cancel = True
or
DoCmd.CancelEvent
and then try to figure out why you have it there.