473,325 Members | 2,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

alternatives to #if ... #else ..... #end if statement

Hi Team

The function below searches all the tables in a database. However, if
subsearch = true then it searches all the objects listed in a recordset
(which are all table names).

I thought to be really clever and use :

#if subsearch ... #else ..... #end if

statement to select which part should be compiled. But this is WRONG! I
learned in the help because it is only for conditional compiler constants,
the subsearch variable I have is from a form. Can anyone suggest an
alternative to the #if statement.

Note, I can not use the standard if statement, because that will not
compile.

Public Sub Tsearch(S As String, REC As Long, Subsearch As Boolean, linked As
Boolean)
'rec is the id for the group
Dim TBL As TableDef
Dim Fld As Field
'-------------------------------set basics
Set Dbs = CurrentDb
DXGID = REC
If linked Then
TYPID = TLn
Else
TYPID = Tn
End If
SS = S
'--------------delete the previous search entries-------------------
If Not PFDEL(Subsearch) Then Exit Sub
'---------------start loop: two options search 'if' in help for more
information
#If Subsearch Then
Set RST = Dbs.OpenRecordset("SELECT * FROM [" & TblNS & "] WHERE (["
& TblNGid & "]=" & REC & " AND [" & TblNTid & "] = " & TYPID & ";")
Do While Not RST.EOF
Set TBL = Dbs.TableDefs(RST.Fields("dc"))
#Else
For Each TBL In Dbs.TableDefs
#End If
'------------------actual search-----------------------------------------
ObjN = TBL.Name
If Left(ObjN, 1) = "~" Then GoTo looper
'--------check object name--------------------
Call PFONC
'--------search fields--------------------
For Each Fld In TBL.Fields
StbS = Fld.Name & " "
If InStr(1, StbS, SS, vbDatabaseCompare) > 0 Then
FldN = Fld.Name
Call PFADD
End If
Next Fld
'------------------end of actual
search-----------------------------------------
#If Subsearch Then
RST.MoveNext
Loop
#Else
looper:
Next TBL
#End If
Call PSFIN
End Sub

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004
Nov 13 '05 #1
5 3476
What error do you get when you try to compile with a standard If statement?

- Turtle

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:3E******************@news.xtra.co.nz...
Hi Team

The function below searches all the tables in a database. However, if
subsearch = true then it searches all the objects listed in a recordset
(which are all table names).

I thought to be really clever and use :

#if subsearch ... #else ..... #end if

statement to select which part should be compiled. But this is WRONG! I
learned in the help because it is only for conditional compiler constants,
the subsearch variable I have is from a form. Can anyone suggest an
alternative to the #if statement.

Note, I can not use the standard if statement, because that will not
compile.

Public Sub Tsearch(S As String, REC As Long, Subsearch As Boolean, linked As Boolean)
'rec is the id for the group
Dim TBL As TableDef
Dim Fld As Field
'-------------------------------set basics
Set Dbs = CurrentDb
DXGID = REC
If linked Then
TYPID = TLn
Else
TYPID = Tn
End If
SS = S
'--------------delete the previous search entries-------------------
If Not PFDEL(Subsearch) Then Exit Sub
'---------------start loop: two options search 'if' in help for more
information
#If Subsearch Then
Set RST = Dbs.OpenRecordset("SELECT * FROM [" & TblNS & "] WHERE ([" & TblNGid & "]=" & REC & " AND [" & TblNTid & "] = " & TYPID & ";")
Do While Not RST.EOF
Set TBL = Dbs.TableDefs(RST.Fields("dc"))
#Else
For Each TBL In Dbs.TableDefs
#End If
'------------------actual search-----------------------------------------
ObjN = TBL.Name
If Left(ObjN, 1) = "~" Then GoTo looper
'--------check object name--------------------
Call PFONC
'--------search fields--------------------
For Each Fld In TBL.Fields
StbS = Fld.Name & " "
If InStr(1, StbS, SS, vbDatabaseCompare) > 0 Then
FldN = Fld.Name
Call PFADD
End If
Next Fld
'------------------end of actual
search-----------------------------------------
#If Subsearch Then
RST.MoveNext
Loop
#Else
looper:
Next TBL
#End If
Call PSFIN
End Sub

---
Please immediately let us know (by phone or return email) if (a) this email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

Nov 13 '05 #2
It tells me that the if statement is not closed properly, but I think it
just gets confused with the recordset being started and all the other stuff
happening.
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.700 / Virus Database: 457 - Release Date: 06/06/2004
Nov 13 '05 #3
You have loops that begin in one IF statement and end in another. You may
need to break this down into two separate items, one in each part of a
single IF statement (i.e. repeating the midde portion in each part of a
single IF statement or placing the middle part in a separate function that
you can call from each part of the single IF statement). You are also
jumping into the middle of the second IF statement with your GoTo Looper
call.
If Left(ObjN, 1) = "~" Then GoTo looper What happens if you change this to
If Left(ObjN, 1) = "~" Then Next TBL

Also, while it shouldn't matter, sometime it does, try changing (in both
locations) If Subsearch Then to
If Subsearch = True Then

--
Wayne Morgan
MS Access MVP
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:3E******************@news.xtra.co.nz... Hi Team

The function below searches all the tables in a database. However, if
subsearch = true then it searches all the objects listed in a recordset
(which are all table names).

I thought to be really clever and use :

#if subsearch ... #else ..... #end if

statement to select which part should be compiled. But this is WRONG! I
learned in the help because it is only for conditional compiler constants,
the subsearch variable I have is from a form. Can anyone suggest an
alternative to the #if statement.

Note, I can not use the standard if statement, because that will not
compile.

Public Sub Tsearch(S As String, REC As Long, Subsearch As Boolean, linked As Boolean)
'rec is the id for the group
Dim TBL As TableDef
Dim Fld As Field
'-------------------------------set basics
Set Dbs = CurrentDb
DXGID = REC
If linked Then
TYPID = TLn
Else
TYPID = Tn
End If
SS = S
'--------------delete the previous search entries-------------------
If Not PFDEL(Subsearch) Then Exit Sub
'---------------start loop: two options search 'if' in help for more
information
#If Subsearch Then
Set RST = Dbs.OpenRecordset("SELECT * FROM [" & TblNS & "] WHERE ([" & TblNGid & "]=" & REC & " AND [" & TblNTid & "] = " & TYPID & ";")
Do While Not RST.EOF
Set TBL = Dbs.TableDefs(RST.Fields("dc"))
#Else
For Each TBL In Dbs.TableDefs
#End If
'------------------actual search-----------------------------------------
ObjN = TBL.Name
If Left(ObjN, 1) = "~" Then GoTo looper
'--------check object name--------------------
Call PFONC
'--------search fields--------------------
For Each Fld In TBL.Fields
StbS = Fld.Name & " "
If InStr(1, StbS, SS, vbDatabaseCompare) > 0 Then
FldN = Fld.Name
Call PFADD
End If
Next Fld
'------------------end of actual
search-----------------------------------------
#If Subsearch Then
RST.MoveNext
Loop
#Else
looper:
Next TBL
#End If
Call PSFIN
End Sub

---
Please immediately let us know (by phone or return email) if (a) this email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

Nov 13 '05 #4
Now you've told me the error message, I can see your problem clearly:

Do While Not RST.EOF
Set TBL = Dbs.TableDefs(RST.Fields("dc"))

There's no
Loop
at the end to tell Access where to revert to the Do.
Since it comes to the Else (or EndIf) statement before it finds a Loop, it
can't compile.

Add
Loop
after the two lines I cited, and you should be able to compile without your
conditionals.

- Turtle

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:kL******************@news.xtra.co.nz...
It tells me that the if statement is not closed properly, but I think it
just gets confused with the recordset being started and all the other stuff happening.
---
Please immediately let us know (by phone or return email) if (a) this email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.700 / Virus Database: 457 - Release Date: 06/06/2004

Nov 13 '05 #5
Thank you all for your comments on this post. I have solved it I think.
Incidentally, you will find an idea below for a function that allows you to
search for keywords through tables, queries, forms, reports and modules.
You can either search the whole database or the objects held in D-DXS

The table D-DXS holds a list of objects
Fields:
ID - autonumber
Dc = text, name of object
D-DXG-ID = linked to D-DXG.ID
D-TYP-ID = linked to D-TYP.ID, a table which lists the
object types
Fld = text field, which stores informatio on where in the
object the string is found (e.g.a control in a form)

The table D-DXG holds a list of searches
ID autonumber
D = name of search
MEM = extra info on the search

Option Compare Database
Option Explicit
Const TblNS = "D-DXS"
Const TblNGid = "D-DXG-ID"
Const TblNTid = "D-TYP-ID"
Const Qn = 5
Const Fn = -32768
Const Rn = -32764
Const Mn = -32761
Const Tn = 1 'not linked tables
Const TLn = 6 'linked
Dim SS As String
Dim TYPID As Long
Dim DXGID As Long
Dim ObjN As String
Dim FldN As String
Dim Msg As String
Dim RST As Dao.Recordset
Dim Dbs As Database
Dim StbS As String 'the string to be searched
Public Sub Tsearch(S As String, REC As Long, Subsearch As Boolean, linked As
Boolean)
'rec is the id for the group
Dim Tbl As TableDef
'-------------------------------set basics
Set Dbs = CurrentDb
DXGID = REC
If linked Then
TYPID = TLn
Else
TYPID = Tn
End If
SS = S
'--------------delete the previous search entries-------------------
If Not PFDEL(Subsearch) Then Exit Sub
'---------------start loop: two options
If Not Subsearch Then
For Each Tbl In Dbs.TableDefs
Call PSTBL(Tbl)
Next Tbl
Else
Set RST = Dbs.OpenRecordset("SELECT * FROM [" & TblNS & "] WHERE (["
& TblNGid & "]=" & REC & " AND [" & TblNTid & "] = " & TYPID & ";")
Do While Not RST.EOF
Set Tbl = Dbs.TableDefs(RST.Fields("dc"))
Call PSTBL(Tbl)
RST.MoveNext
Loop
End If
finalise:
Call PSFIN
End Sub
Public Sub Qsearch(S As String, REC As Long, Subsearch As Boolean)
Dim QDF As QueryDef
Dim Sqls As String
Dim Dbs As Database
Dim RST As Dao.Recordset
Dim Msg As String
'-------------------------------set basics
Set Dbs = CurrentDb
DXGID = REC
TYPID = Qn
SS = S
'--------------delete the previous search entries-------------------
If Not PFDEL(Subsearch) Then Exit Sub
'---------------start loop: two options search 'if' in help for more
information
If Not Subsearch Then
For Each QDF In Dbs.QueryDefs
Call PSQDF(QDF)
Next QDF
Else
Set RST = Dbs.OpenRecordset("SELECT * FROM [" & TblNS & "] WHERE (["
& TblNGid & "]=" & REC & " AND [" & TblNTid & "] = " & TYPID & ";")
Do While Not RST.EOF
Set QDF = Dbs.QueryDefs(RST.Fields("dc"))
Call PSQDF(QDF)
RST.MoveNext
Loop
End If
finalise:
Call PSFIN
End Sub
Public Sub FRsearch(S As String, REC As Long, Subsearch As Boolean, IsReport
As Boolean)
Dim Ctr As Container
Dim Doc As Document
'-------------------------------set basics
Set Dbs = CurrentDb
DXGID = REC
If IsReport Then TYPID = Rn Else TYPID = Fn
SS = S
'--------------delete the previous search entries-------------------
If Not PFDEL(Subsearch) Then Exit Sub
'---------------start loop: two options search 'if' in help for more
information
If Not Subsearch Then
If IsReport Then
Set Ctr = Dbs.Containers!Reports
Else
Set Ctr = Dbs.Containers!Forms
End If
For Each Doc In Ctr.Documents
ObjN = Doc.Name
Call PSOBJ(IsReport)
Next Doc
Else
Set RST = Dbs.OpenRecordset("SELECT * FROM [" & TblNS & "] WHERE (["
& TblNGid & "]=" & REC & " AND [" & TblNTid & "] = " & TYPID & ";")
Do While Not RST.EOF
ObjN = RST.Fields("dc")
Call PSOBJ(IsReport)
RST.MoveNext
Loop
End If
finalise:
Call PSFIN
End Sub

Public Sub Msearch(S As String, REC As Long, Subsearch As Boolean)
Dim ModO As AccessObject
'--------------set basics------------------------------------
Set Dbs = CurrentDb
DXGID = REC
TYPID = Mn
SS = S
'--------------delete the previous search entries-------------------
If Not PFDEL(Subsearch) Then Exit Sub
'--------------cycle through modules-------------------------------------
For Each ModO In Application.CurrentProject.AllModules
ObjN = ModO.Name
Call PFSMO(ModO.Name, SS)
looper:
Next ModO
finalise:
Call PSFIN
End Sub

Private Function PFADD()
'add new entries to D-DXG
Msg = Msg & vbCrLf & ObjN & " (" & FldN & ")"
If Nz(FldN, "") <> "" Then FldN = FldN & ", "
Call FRUNSQL("UPDATE [D-DXS] SET [D-DXS].FLD = [FLD] & '" & FldN & "'
WHERE ((([D-DXS].Dc)='" & ObjN & "') AND (([D-DXS].[D-TYP-ID])=" & TYPID &
") AND (([D-DXS].[D-DXG-ID])=" & DXGID & "));")
Call FRUNSQL("INSERT INTO [D-DXS] ( Dc, [D-DXG-ID], [D-TYP-ID], [FLD] )
SELECT '" & ObjN & "' AS v1, " & DXGID & " AS v2, " & TYPID & " AS v3, '" &
FldN & "' AS v4;")
xit:
Exit Function
Err:
Resume xit
End Function

Private Function PFDEL(Subsearch As Boolean) As Boolean
'deletes entries from D-DXS
PFDEL = True
If Subsearch Then
GoTo xit
Else
If FRSHOW(206) Then
Call FRUNSQL("DELETE [" & TblNS & "].* FROM [" & TblNS & "]
WHERE ([" & TblNGid & "]=" & DXGID & " And [" & TblNTid & "] = " & TYPID &
");")
Else
PFDEL = False
End If
End If
xit:
Exit Function
Err:
Resume xit
End Function
Private Function PFONC()
'object name check: see if the string perhaps occurs in the name of the
object itself
If InStr(1, ObjN, SS, vbDatabaseCompare) > 0 Then
FldN = ""
Call PFADD
End If
xit:
Exit Function
Err:
Resume xit
End Function
Private Sub PSFIN()
If Msg = "" Then Msg = SS & " WAS NOT FOUND"
MsgBox "FOUND IN THE FOLLOWING: " & vbCr &
"________________________________" & Msg
Forms("D-SEA").Requery
End Sub
Private Function PFSMO(MdlN As String, SS As String)
Dim Mdl As Module
Dim SLine As Long, SCol As Long 'start line and column (character)
Dim ELine As Long, ECol As Long 'end line and column (character)
'--------------------select case: form, report or module
Select Case TYPID
Case Fn
If Not Forms(ObjN).HasModule Then Exit Function
Set Mdl = Forms(ObjN).Module
Case Rn
If Not Reports(ObjN).HasModule Then Exit Function
Set Mdl = Reports(ObjN).Module
Case Else
DoCmd.OpenModule MdlN
Set Mdl = Modules(MdlN)
DoCmd.Close acModule, MdlN, acSaveNo
'----------check name itself
Call PFONC
End Select
'--------------search the module
If Mdl.find(SS, SLine, SCol, ELine, ECol) Then
FldN = "(ModuleLine: " & SLine & ")"
PFADD
End If
End Function
Private Sub PSTBL(Tbl As TableDef)
Dim Fld As Field
'-------------------------------------------
ObjN = Tbl.Name
If Left(ObjN, 1) = "~" Then Exit Sub
'--------check object name--------------------
Call PFONC
'--------search fields--------------------
For Each Fld In Tbl.Fields
StbS = Fld.Name & " "
If InStr(1, StbS, SS, vbDatabaseCompare) > 0 Then
FldN = Fld.Name
Call PFADD
End If
Next Fld
End Sub
Private Sub PSQDF(QDF As QueryDef)
'------------------actual search-----------------------------------------
ObjN = QDF.Name
If Left(ObjN, 1) = "~" Then Exit Sub
'---check name-------------------------
Call PFONC
'---check sqls-------------------------------
StbS = QDF.SQL
If InStr(1, StbS, SS, vbDatabaseCompare) > 0 Then
Call PFADD
End If
End Sub

Private Sub PSOBJ(IsReport As Boolean)
Dim OBJ As Object
Dim Ctl As Control
Dim ppt As Property
'---------------cater for forms/reports that do not need to be
searched-------------
If Left(ObjN, 1) = "X" Then Exit Sub
If Left(ObjN, 1) = "D" Then Exit Sub
If DCount("[ID]", "[D-TAB]", "[D-TAB]![Dc]='" & Left(ObjN, 5) & "'") > 0
Then Exit Sub
If Left(ObjN, 1) = "~" Then Exit Sub
'---open report/form-------------------------------
If IsReport Then
DoCmd.OpenReport ObjN, acViewDesign, , , acHidden
Set OBJ = Reports(ObjN)
Else
DoCmd.OpenForm ObjN, acDesign, "", "", acFormPropertySettings,
acHidden, ""
Set OBJ = Forms(ObjN)
End If
'---check name and module-------------------------
Call PFONC
Call PFSMO("", SS)
'---------------------------------------search form/report
StbS = ""
For Each ppt In OBJ.Properties
If property_check(ppt.Name) <> False Then
StbS = StbS & ppt.Value
End If
Next ppt
If InStr(1, StbS, SS, vbDatabaseCompare) > 0 Then
FldN = "object properties"
Call PFADD
End If
'---------------------------------------search controls
StbS = ""
For Each Ctl In OBJ.Controls
For Each ppt In Ctl.Properties
If property_check(ppt.Name) <> False Then
StbS = StbS & ppt.Value
End If
Next ppt
If InStr(1, StbS, SS, vbDatabaseCompare) > 0 Then
FldN = Ctl.Name
Call PFADD
End If
Next Ctl
'------------------close
form/report-----------------------------------------
If IsReport Then
DoCmd.Close acReport, ObjN, acSaveNo
Else
DoCmd.Close acForm, ObjN, acSaveNo
End If
End Sub

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.700 / Virus Database: 457 - Release Date: 06/06/2004
Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Michael Chermside | last post by:
I (Michael Chermside) wrote: > a brace and either way it throws the logic off. I created a > bug in one of > my programs recently just by adding logging. It looked like this: > > if...
27
by: Ron Adam | last post by:
There seems to be a fair amount of discussion concerning flow control enhancements lately. with, do and dowhile, case, etc... So here's my flow control suggestion. ;-) It occurred to me (a...
3
by: Patrice | last post by:
Hi, I need to do multi-conditional statements like below, but this error is displayed : Expected 'End' /myFilepath, line x else response.write(arrCorpo(sparam,sdiv)) end if I don't...
43
by: Steven T. Hatton | last post by:
Now that I have a better grasp of the scope and capabilities of the C++ Standard Library, I understand that products such as Qt actually provide much of the same functionality through their own...
19
by: GMKS | last post by:
Hello all, I have 13 check boxes on a form. I am trying to check all the check boxes to determine if they are true or false when I close the form. At present only the first IF...Then...Else...
16
by: RC | last post by:
I have some code that needs to know if an int variable is one of several possible values. What is a better way of performing the test than stacking a bunch of case: in a switch statement? I also...
3
by: Amy | last post by:
Hi, I have 6 If Then Else statements I was supposed to write. I did so but I know that they have to be wrong because they all look the same. Could someone take a look at them and point me in the...
8
by: werner | last post by:
Hi! I don't want to use eval() in order to parse a user-supplied formula. What alternatives do I have? PHP has no standard functionality for tokenizing or parsing expressions in this regard. ...
32
by: cj | last post by:
Another wish of mine. I wish there was a way in the Try Catch structure to say if there wasn't an error to do something. Like an else statement. Try Catch Else Finally. Also because I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.