Best Practice: Variables Vs Unbound Text boxes 4

[sub5]

Hi all

Is it always better to use variables to hold temporary data rather then putting the value in an unbound text field. I am thinking of three scenerios:
1. Creating records
2. As items which determine process eg which form to open next
3. As a criteria for a query (using a function in the case of a variable)

I would have said yes because it makes the process more obvious for maintenance ie you can easily search on a variable. But I might be wrong and I have little experience of item 3 and the potential pitfalls.

 

[jebry]

Hi!

Personally, I think it depends on the scope of the information. If the variable can be declared at the procedure or, at most, the form level then it would be fine to use. If you need a global variable I prefer to use a textbox to store the information.

I usually have a table called tblparameter in which I store constants that I need. And I have a form that is automatically opened when the Db starts which holds the information in textboxes. I put any unbound textboxes that I need on that form.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[hwkranger]

Sub5,

It's ussually a sticky situation when you use the phrase "always better.." There is almost always an exception to every rule.

For your situation I disagree with jebry. I would go with the statement that: It is almost always better to use a variable to store data rather than a text box.

1) variable data is smaller than a form object.
2) variable data can be managed by scope and excessibility. (public, private and at differing scopes).
3) as you stated a variable can be searched via ctrl+f
4) a variable isn't accessible by a user
5) a user can't see a variable
6) a variable's data isn't called via a collection or at run time (eg. forms("formname").object.value)

The biggest issue I see with using a variable is if you are running the project as an MDB and you get an error and hit "STOP". This will reset all your variable values.

I do much of the same of which you are doing. For Queries, I use a FUNCTION returning a value as the criteria for query, rather than a form. Why? It's easier to manage the code and project that way. If you change the name of the object on the form, then everywhere you used it you'll have to change it. Whereas, if the return source of your function changes, you just change the function.

I use variables or constants for almost everything.

For creating records, this is an example of my code -- i use a temporary object to hold the data when updating. When inserting I use an instance of the object.

Option Compare Database
Option Explicit

'*******************************************************
'*                   AUTOGENERATED CODE FOR                                *
'*                       tblBankListing CLASS
'*
'*                 BUILDER BY RANDALL VOLLEN                               *
'******************4/4/2006 2:17:21 PM***********************

'THIS CLASS USES CENTRALPROOFMA  NEEDS TO BE MODIFIED ON SINGLE
'DATABASE CONVERSION

'*************** BUISINESS DATA TYPE **********************'


Private Type biztblBankListing_TYPE
    mstrCoNumber As String
    mstrStateName As String
    mstrStateAbbrev As String
    mstrBalanceGroup As String
    mintBankID As Integer
End Type


Private Type biztblBankListing_STATE_TYPE
    CoNumber As Boolean
    StateName As Boolean
    StateAbbrev As Boolean
    BalanceGroup As Boolean
End Type


Private Const CLASS_NAME = "tblBankListing"
Private Const DATA_ACCESS = "CentralProofMA..tblBankListing"
Private adtbiztblBankListing_DATA As biztblBankListing_TYPE
Private adtbiztblBankListingBefore_DATA As biztblBankListing_TYPE
Private adtbiztblBankListing_STATE_DATA As biztblBankListing_STATE_TYPE
Private cn As New ADODB.Connection
Private blnFetched As Boolean




'*************** Let/Get Properties **********************'
'*      Change these to public if public access is required.               *


'******************************************
'*      COMMENTS:
'*
'******************************************
Public Property Let CoNumber(mstrCoNumber As String)
    adtbiztblBankListing_DATA.mstrCoNumber = mstrCoNumber
End Property
Public Property Get CoNumber() As String
    CoNumber = adtbiztblBankListing_DATA.mstrCoNumber
End Property
'******************************************
'*      COMMENTS:
'*
'******************************************
Public Property Let StateName(mstrStateName As String)
    adtbiztblBankListing_DATA.mstrStateName = mstrStateName
End Property
Public Property Get StateName() As String
    StateName = adtbiztblBankListing_DATA.mstrStateName
End Property
'******************************************
'*      COMMENTS:
'*
'******************************************
Public Property Let StateAbbrev(mstrStateAbbrev As String)
    adtbiztblBankListing_DATA.mstrStateAbbrev = mstrStateAbbrev
End Property
Public Property Get StateAbbrev() As String
    StateAbbrev = adtbiztblBankListing_DATA.mstrStateAbbrev
End Property
'******************************************
'*      COMMENTS:
'*
'******************************************
Public Property Let BalanceGroup(mstrBalanceGroup As String)
    adtbiztblBankListing_DATA.mstrBalanceGroup = mstrBalanceGroup
End Property
Public Property Get BalanceGroup() As String
    BalanceGroup = adtbiztblBankListing_DATA.mstrBalanceGroup
End Property
'******************************************
'*      COMMENTS:
'*
'******************************************
Public Property Let BankID(mintVal As Integer)
    adtbiztblBankListing_DATA.mintBankID = mintVal
End Property
Public Property Get BankID() As Integer
    BalanceGroup = adtbiztblBankListing_DATA.mintBankID
End Property
'******************************************
'*      COMMENTS:
'*  Loads data into the members
'******************************************
Public Sub FetchData()
    Dim rst As New ADODB.RecordSet
    rst.Open GetFetchSQL, cn, adOpenKeyset, adLockReadOnly
    If Not rst.EOF Then
    If Not IsNull(rst(0)) Then
        adtbiztblBankListing_DATA.mstrCoNumber = rst(0)
    End If
    If Not IsNull(rst(1)) Then
        adtbiztblBankListing_DATA.mstrStateName = rst(1)
    End If
    If Not IsNull(rst(2)) Then
        adtbiztblBankListing_DATA.mstrStateAbbrev = rst(2)
    End If
    If Not IsNull(rst(3)) Then
        adtbiztblBankListing_DATA.mstrBalanceGroup = rst(3)
    End If
    If Not IsNull(rst(4)) Then
        adtbiztblBankListing_DATA.mintBankID = rst(4)
    End If
    
    adtbiztblBankListingBefore_DATA = adtbiztblBankListing_DATA
    blnFetched = True
    End If
End Sub

'******************************************
'*      COMMENTS:
'*  Inserts the data from the members into the Database
'******************************************
Public Sub InsertRecord()
    cn.Execute GetInsertSQL()
Call Me.LogAction(15, CLASS_NAME & " Modified Item: " & adtbiztblBankListing_DATA.mstrCoNumber, , , "Insert")
End Sub

'******************************************
'*      COMMENTS:
'*  Updates the Database with the data from the members
'******************************************
Public Sub UpdateRecord()
    cn.Execute GetUpdateSQL()
Call Me.LogAction(15, CLASS_NAME & " Modified Item: " & adtbiztblBankListing_DATA.mstrCoNumber, , , "Update")
End Sub

'******************************************
'*      COMMENTS:
'*  Deletes a Record
'******************************************
Public Sub DeleteRecord()
    cn.Execute GetDeleteSQL()
Call Me.LogAction(15, CLASS_NAME & " Modified Item: " & adtbiztblBankListing_DATA.mstrCoNumber, , , "Delete")
End Sub

'******************************************
'*      COMMENTS:
'*  Returns the current Recordset
'******************************************
Public Function FetchRecord() As ADODB.RecordSet
    Dim rst As New ADODB.RecordSet
    rst.Open GetFetchSQL, cn, adOpenKeyset, adLockOptimistic
    Set FetchRecord = rst
Call Me.LogAction(10, CLASS_NAME & " Item: " & adtbiztblBankListing_DATA.mstrCoNumber, , , "Fetched Record")
End Function
'******************************************
'*      COMMENTS:
'*  Returns all the Records in the table
'******************************************
Public Function FetchAllRecordset() As ADODB.RecordSet
    Dim rst As New ADODB.RecordSet
    rst.Open GetFetchAllSQL, cn, adOpenKeyset, adLockOptimistic
    Set FetchAllRecordset = rst
Call Me.LogAction(10, CLASS_NAME & "Item: " & adtbiztblBankListing_DATA.mstrCoNumber, , , "Fetched All Records")
End Function

Private Function GetFetchSQL() As String
    Dim strSQL As String
    strSQL = strSQL & "Select * from " & DATA_ACCESS & vbCrLf
    strSQL = strSQL & "Where CoNumber = " & adtbiztblBankListing_DATA.mstrCoNumber & vbCrLf
    GetFetchSQL = strSQL
End Function

'******************************************
'*      COMMENTS:
'*  Returns all the Records in the table
'*  For use in a Combo Box
'******************************************
Public Function FetchAllComboBoxRecordset() As ADODB.RecordSet
    Dim rst As New ADODB.RecordSet
    rst.Open GetFetchAllComboBoxSQL, cn, adOpenKeyset, adLockOptimistic
    Set FetchAllComboBoxRecordset = rst
    Call Me.LogAction(10, CLASS_NAME & "Item: " & adtbiztblBankListing_DATA.mstrCoNumber, , , "Fetched All Records View")
End Function

Private Function GetFetchAllComboBoxSQL() As String
    Dim strSQL As String
    strSQL = "Select CoNumber, StateName from " & DATA_ACCESS
    GetFetchAllComboBoxSQL = strSQL
End Function


'*** MUST EDIT INSERT WHEN USING AUTOINCREMENT ***
'* Edit this if auto numbers are used *
'******************************************
'*      COMMENTS:
'*  Builds the Insert SQL
'******************************************
Private Function GetInsertSQL() As String
    Dim strSQL As String
    strSQL = "Insert Into " & DATA_ACCESS & "("
    strSQL = strSQL & "[CoNumber],"
    strSQL = strSQL & "[StateName],"
    strSQL = strSQL & "[StateAbbrev],"
    strSQL = strSQL & "[BalanceGroup])"
    strSQL = strSQL & "Values( "
    strSQL = strSQL & SQLPrepWithQuote(CStr(adtbiztblBankListing_DATA.mstrCoNumber)) & ","
    strSQL = strSQL & SQLPrepWithQuote(CStr(adtbiztblBankListing_DATA.mstrStateName)) & ","
    strSQL = strSQL & SQLPrepWithQuote(CStr(adtbiztblBankListing_DATA.mstrStateAbbrev)) & ","
    strSQL = strSQL & SQLPrepWithQuote(CStr(adtbiztblBankListing_DATA.mstrBalanceGroup)) & ")"
    GetInsertSQL = strSQL
End Function

'******************************************
'*      COMMENTS:
'*  Builds the update SQL
'******************************************
Private Function GetUpdateSQL() As String
    Dim strSQL As String
    strSQL = "Update " & DATA_ACCESS & " SET "
    strSQL = strSQL & "[StateName] = "
    strSQL = strSQL & SQLPrepWithQuote(CStr(adtbiztblBankListing_DATA.mstrStateName)) & ", "
    strSQL = strSQL & "[StateAbbrev] = "
    strSQL = strSQL & SQLPrepWithQuote(CStr(adtbiztblBankListing_DATA.mstrStateAbbrev)) & ", "
    strSQL = strSQL & "[BalanceGroup] = "
    strSQL = strSQL & SQLPrepWithQuote(CStr(adtbiztblBankListing_DATA.mstrBalanceGroup))
strSQL = strSQL & " Where CoNumber= " & adtbiztblBankListing_DATA.mstrCoNumber
    GetUpdateSQL = strSQL
End Function

'******************************************
'*      COMMENTS:
'*  Builds the delete SQL
'******************************************
Private Function GetDeleteSQL() As String
    Dim strSQL As String
    strSQL = strSQL & "Delete   from " & DATA_ACCESS & vbCrLf
    strSQL = strSQL & "Where CoNumber = " & adtbiztblBankListing_DATA.mstrCoNumber & vbCrLf
    GetDeleteSQL = strSQL
End Function

'******************************************
'*      COMMENTS:
'*  Builds the return all records SQL
'******************************************
Private Function GetFetchAllSQL() As String
    Dim strSQL As String
    strSQL = "Select * from " & DATA_ACCESS
        GetFetchAllSQL = strSQL
End Function

Private Sub Class_Initialize()
    Set cn = dbcnn
End Sub

Private Sub Class_Terminate()
    Set cn = Nothing
End Sub


'******************************************
'*      COMMENTS:
'*  Returns True if Dirty (Data has changed), False if not
'******************************************
Public Function Dirty() As Boolean
    If adtbiztblBankListing_DATA.mstrCoNumber <> adtbiztblBankListingBefore_DATA.mstrCoNumber Then
        Dirty = True
    ElseIf adtbiztblBankListing_DATA.mstrStateName <> adtbiztblBankListingBefore_DATA.mstrStateName Then
        Dirty = True
    ElseIf adtbiztblBankListing_DATA.mstrStateAbbrev <> adtbiztblBankListingBefore_DATA.mstrStateAbbrev Then
        Dirty = True
    ElseIf adtbiztblBankListing_DATA.mstrBalanceGroup <> adtbiztblBankListingBefore_DATA.mstrBalanceGroup Then
        Dirty = True
    ElseIf adtbiztblBankListing_DATA.mintBankID <> adtbiztblBankListingBefore_DATA.mintBankID Then
        Dirty = True
    End If
End Function


'******************************************
'*      COMMENTS:
'*  Private Logging Function, uses the Logging class
'******************************************
Public Sub LogAction(Level As Integer, strLogText As String, Optional strErrorKey As String, Optional iStatus As Integer, Optional strAction As String, Optional strTag As String)
    'Dim objLog As New clsLog
    'Call objLog.FastLog(Level, strLogText, strErrorKey, iStatus, strAction, strTag)
    'Set objLog = Nothing
End Sub

Randall Vollen
National City Bank Corp.

 

[JoeAtWork]

As a person who's inherited a number of applications that use unbound textboxes to store temporary data, I urge you to use variables whenever possible. I can't tell you how frustrating it is to try to follow the logic of code that is built this way.

Readability:
If EmpNumber is a textbox, I can't look up what type of data should be in it (string, number, etc.). Also, it's value is often updated by referencing another control, so I can't tell by looking at the code how and when (let alone why) it gets changed.

Form Design:
Imagine how difficult it is when you want to change the design of the form, and you have to keep moving around a couple dozen "Visible=False" textboxes because they are in the way. So much needless clutter!

Application Design:
Controls should be for presentation of data, period. The applications I have inherited would be very hard to migrate to another design environment, for instance VB6, because the UI and the code are so closely coupled together.

Usually the reason that people do this is to support other poor design concepts. In the apps I'm supporting, it's because the programmer wanted an easy way to access internal values in queries (i.e. so they could do Field=Forms!MyForm!MyTextbox).

 

[sub5]

Hi, where I have a reference to a form control in a query I am replacing it with a variable using this code:

In the criteria section of the Query:

GetstrVar()

In an independant module:

Public strVar as string

Public Function GetstrVar() as string
GetstrVar = strVar
End Function

This works fines. However the query and strVar are only used in one form and when I put the code in the form's module the query won't open and the listbox referencing it as it's source doesn't display any records.

Is it possible to change the code to put it in the form module?

 

[JoeAtWork]

It might not be working because GetstrVar might be getting called before the form is fully loaded. Perhaps you could requery the listbox in the form's Load event?

 

[sub5]

Hi Joe, no that didn't make a difference, I tried going into the listbox and pressing F9 but to no avail.

 

[SuryaF]

I think you need to reffer it with the Forms!FrmName!GetstrVar

 

[jebry]

Hi!

SuryaF is correct. If you are calling a form module from somewhere other than the form itself then you must reference through the form object. If you are using it run a parameter query then it needs to be in a global module and not a form module. Since you are using the query to set up a list box you could just build the query dynamically and set the rowsource of the list box to the SQL.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst