variables to replace strings

[garret]

Heres just a simple question that I was curious to ask after noticing
this pattern in almost every code sequence created by MS Access as well
as listed by the MVPs here:

Dim strDocName As String
Dim strWhere As String
strDocName = "rptSomeReport"
strWhere = "[RunID]=" & me!RunID
DoCmd.OpenReport strDocName, acPreview, , strWhere

Why use strDocName and strWhere instead of just inserting
"rptSomeReport"...isn't this just a waste of variable space? And this
also limits the use of the strWhere if you have multiple code sequences
in one procedure (you wouldn't have strWhere2, etc.).

 

[Roger Carlson]

Partly, this is because the Button Wizard does that when you create an Open
Report or Open Form button. But I use this throughout my code. The reason
is two-fold:

1) It's neater and cleaner. It makes the OpenReport line more readable and
keeps the line from being too long.
2) It helps in the debugging process. If something isn't working properly,
I can stick a Debug.Print line in there and see what string is actually
created. Like this:
Dim strDocName As String
Dim strWhere As String
strDocName = "rptSomeReport"
Debug.Print strDocName
strWhere = "[RunID]=" & me!RunID
Debug.Print strWhere
DoCmd.OpenReport strDocName, acPreview, , strWhere

This is also why I will most often do this:
Dim strSQL As String
strSQL = "Delete * from MyTable"
db.Execute strSQL

rather than
db.Execute "Delete * from MyTable"

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[fredg]

Heres just a simple question that I was curious to ask after noticing
this pattern in almost every code sequence created by MS Access as well
as listed by the MVPs here:

Dim strDocName As String
Dim strWhere As String
strDocName = "rptSomeReport"
strWhere = "[RunID]=" & me!RunID
DoCmd.OpenReport strDocName, acPreview, , strWhere

Why use strDocName and strWhere instead of just inserting
"rptSomeReport"...isn't this just a waste of variable space? And this
also limits the use of the strWhere if you have multiple code sequences
in one procedure (you wouldn't have strWhere2, etc.).

Good question.
The code is created by the Command button wizard, with a 'one size
fits all' mentality.
Personally, I would use
DoCmd.OpenReport "rptSomeReport",acViewPreview, , "RunID] = " &
[Me![Run]
to make the code easier to read and follow.

Using variable does make sense if you are selecting which report or
criteria to use at runtime.
If Me!Option = 1 Then
stDocName = "Report1"
stWhere = "[RunID] = " & Me![RunID]
Else
stDocName = "Report2"
stWhere = "[EmployeeName] = '" & Me![EmployeeName] & "'"
End If
Docmd.OpenReport stDocName, acViewPreview, , stWhere