How use DLookup within VBA

I seem to have gotten what I wanted from earlier question, I have created below a table where I want to store two strings:
Tbllinkstring, see attached table

Now I want to use DLookup to be pick the string from the table, how do I use the IF and THEN and ELSE from the code below so that it evaluate and use the correct string, do not worry about the rest, I will take care of the rest, see the shaded area in the attached table:

Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function
For me this will be the best solution because before deploying the application I will be required only to change one of the links above in the table called Tbllinkstring to the correct host name, this means that I will still maintain my existing laptop links which allow me to compile and make an ACCDE copy without an error. Once the application is deployed to a new host name again it will simply drop my existing links after evaluating and use the new one. The advantage is that the users will not be required to select the correct link at all, the system will handle that for them.
On the shaded areas I want to use the Dlookup and IF THEN & ELSE, any Idea here????

Regards

Chris
Table-links.docx
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
Personally, I would use a database property to store a value (Debugging) to indicate whether I am in debug or deploy mode.  Then I would use that value in the dlookup:
set db = currentdb
set prp = db.Creatproperty("Debugging", dbBoolean, true)
db.property.append prp

Open in new window

Once you have created this property, you can simply change it like:
currentdb.property("Debugging") = False

Open in new window

Then in your DLookup I would use:
Dim strCriteria as string
strCriteria = "[ID] = " & iif(currentdb.Property("Debugging"), 2, 1)
constr = DLookup("LinkString", "tblLinkString", strcriteria)

Open in new window

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Personally, I would use a database property to store a value (Debugging) to indicate whether I am in debug or deploy mode.

 That or call a function, which can remain un-changed when you call it, but the checks it makes to see if your in debug mode can take many forms:

1. A database property
2. A flag in a table
3. A file on disk
4. A registry entry

 and may be one or more of them.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Just so you have an idea, the function below currently is checking for a disk file, but I could include additional checks.  Also, it accepts an optional argument so I can force debug mode if I want to.

Jim.

Function DebugMode(Optional varMode As Variant) As Integer

      ' Determines if app is running in debug mode.
      ' Debug mode is set by placing a .txt file
      ' in the database directory with a name of
      ' <database name>_Debug.txt
      ' Or by calling this routine with the optional argument.

          Dim strFileName As String
          Dim intLen As Integer

10        On Error GoTo DebugMode_Error

20        If Nz(gvarDebugMode, "") = "" Then
30            On Error GoTo 0
40            Err = 0
50            strFileName = left(CurrentDb.Name, Len(CurrentDb.Name) - 4) & "_Debug.txt"
60            intLen = Len(Dir(strFileName))
70            If (Not Err And intLen > 0) Then
80                gvarDebugMode = True
90            Else
100               gvarDebugMode = False
110           End If
120           On Error GoTo DebugMode_Error
130       End If

140       If Not IsMissing(varMode) Then
150           gvarDebugMode = varMode
160       End If

DebugMode_Exit:
170       On Error Resume Next

180       DebugMode = Nz(gvarDebugMode, False)

190       Exit Function

DebugMode_Error:
200       gvarDebugMode = Null
210       Resume DebugMode_Exit

End Function
SolarWinds® Network Configuration Manager (NCM)

SolarWinds Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

Fabrice LambertConsultingCommented:
That or call a function, which can remain un-changed when you call it, but the checks it makes to see if your in debug mode can take many forms:

 1. A database property
 2. A flag in a table
 3. A file on disk
 4. A registry entry

  and may be one or more of them.
In my opinion, it is better to use a conditional compilation directive,, so you can write different behavior fo your functionalities without touching too much on the project, neither test too many variables or objects.

You can set a directive from the VB IDE, Tools menu ==> Project propertiesSans-titre.pngSample code:
Public function InputString() As String
#If DEBUG_MODE Then
    InputString = "My Hard coded string."
#Else
    InputString = InputBox("Please, enter a string")
#End If
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In my opinion, it is better to use a conditional compilation directive,, so you can write different behavior fo your functionalities without touching too much on the project, neither test too many variables or objects.

 Bit too inflexible for me.  I like to be able to jump into debug mode on my apps in a number of ways.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I'm also left wondering where that would leave you with the compile state as your actually altering which lines of your code are getting compiled.

Jim.
Fabrice LambertConsultingCommented:
I'm also left wondering where that would leave you with the compile state as your actually altering which lines of your code are getting compiled.
The compile state reset whenever you change the directive's state (exactly like when you touch anything on your project).
I'm not sure to see what you have in mind here.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The compile state reset

 Just that; every time you change the constant, your actually altering the lines of code that get executed and breaking the compile state.  

 I don't see any upside to doing a debug flag this way.

Jim.
Dale FyeOwner, Dev-Soln LLCCommented:
I've used conditional compilation directives in the past.  The only time I generally change it is when I'm ready to deploy my application, and I'm going to do a new compile (and possibly make a new accde) before I deploy anyway.  But I just find that storing a db property, or storing the value in a table is more convenient.

Then, during application startup, I generally load that value into a tempvar so that I can use it wherever I need to in my application.

Dale
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Accounting

From novice to tech pro — start learning today.