INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Writing a function loop through a settings table and set TempVars
2

Writing a function loop through a settings table and set TempVars

Writing a function loop through a settings table and set TempVars

(OP)
Hello I have a settings table.

I would like to write a function which would cycle through all records ticked as enabled and create TempVars for each record.

My table is called SETTINGS

The name of the TempVar I would like to set is contained in the field named: Set_TempVars_Name

The Value that I want to set for this TempVars is in the field named: Set_Value

The name of the field to enable or disable whether a Tempvars is created is in field called: Set_Enabled

For example the first record would contain the following data:

Filed Name: Value:
Set_TempVars_Name TVars_Company_Name
Set_Value ABC Limited
Set_Enabled Yes

I would like the function to create a TempVars called TVars_Company_Name, with the value ABC Limited and cycle through all records which are marked Yes in the Set_enabled field.

Thank you for any help. Mark






RE: Writing a function loop through a settings table and set TempVars

(OP)
So after a long evening, I have written my first function.

To my surprise it works (although some names have changed since starting out).

I wonder if someone could comment on the function I have written - I am sure there will be ways to improve its reliabilty, etc.

I would appreciate comments and suggestions.

Thanks again - Mark

CODE -->

Public Function AddTV() As String

Dim rs As dao.Recordset
Dim SetTbl As String

SetTbl = "tbl_SA_SETTINGS"

Set rs = CurrentDb.OpenRecordset(SetTbl)

With rs

If Not .BOF And Not .EOF Then

.MoveLast
.MoveFirst

While (Not .EOF)

Debug.Print rs.Fields("set_ID")


' Check to make sure that a setting record value is present in the table record

If IsNull(rs.Fields("Set_Value_String").Value) = True And IsNull(rs.Fields("Set_Value_Number").Value) = True Then

MsgBox "DO NOT PROCEED - INFORM MANAGER - Setting table has no Setting at record: " & rs.Fields("set_ID")

Exit Function
End If


' Check to make sure that a setting record does not contain more than one value in the table record
' For instance to check a setting has not been entered in both Str Value and Number Value

If IsNull(rs.Fields("Set_Value_String").Value) = False And IsNull(rs.Fields("Set_Value_Number").Value) = False Then

MsgBox "DO NOT PROCEED - INFORM MANAGER - Setting table has more than one Setting at record: " & rs.Fields("set_ID")

Exit Function
End If


' Check to make sure that a Str setting record value is present and if so make TempVars with the Str Value

If IsNull(rs.Fields("Set_Value_String").Value) = False And rs.Fields("Set_Enabled").Value = True Then

TempVars.Add rs.Fields("Set_TempVars_Name").Value, rs.Fields("Set_Value_String").Value

End If


' Check to make sure that a Number setting record value is present and if so make TempVars with the Number Value

If IsNull(rs.Fields("Set_Value_Number").Value) = False And rs.Fields("Set_Enabled").Value = True Then

TempVars.Add rs.Fields("Set_TempVars_Name").Value, rs.Fields("Set_Value_Number").Value

End If


.MoveNext

Wend

End If

.Close

End With

Set rs = Nothing

End Function 

RE: Writing a function loop through a settings table and set TempVars

Looks good to me. You could add a field in your table "Value_Type" with choices "string", "numeric", "boolean" etc. Would make the logic a little simpler. Determine what type of value and then look for it in the correct field. Indenting your code will make it more readable especially with your nested loops. One thing is you have a "With RS", but within the with sometimes you still call out RS as in rs.fields..

Then

CODE -->

select case rs!valueType
    case "String"
      is not isnull(rs!"set_value_String") then
        TempVars.Add rs.Fields("Set_TempVars_Name").Value, rs.Fields("Set_Value_String").Value
      else
        MsgBox "DO NOT PROCEED - INFORM MANAGER - Setting table does not have a string value for: " & rs.Fields("set_ID"
        exit function
      end if
    case "Numeric"
      same for numeric
    case "Boolean"
    ...
    case else
  end select 

RE: Writing a function loop through a settings table and set TempVars

(OP)
Thank you for your reply.

If I added an extra column called value-type as suggested, and stored the format
Type in there (ie date, currency etc), would I then be able to just have one text field for storing the value and format the value when I created the TempVars?

Would this work or would I come across problems with formatting a text field to the various data types?

Many thanks for thoughts.

Regards Mark.

RE: Writing a function loop through a settings table and set TempVars

You could. Look at the vba datatype conversion functions

CODE -->

CBool(number)            Boolean        
CByte(expression)        Byte        
CCur(expression)         Currency        
CDate(expression)        Date        
CDbl(expression)         Double        
CInt(expression)         Integer        
CLng(expression)         Long        
CSng(expression)         Single        
CStr(expression)         String        
CVar(expression)         Variant 


So to do this the single field "Set_Value" needs to be text. Then you can add values like

CODE -->

True
  1.2
  1,000
  4/19/2015
  Blue 

Also you then need to check, not just that there is a value but the correct type of value to be converted.

Example

CODE -->

select case rs!valueType
    case "String"
      iF not isnull(rs!"set_value") then
        TempVars.Add rs.Fields("Set_TempVars_Name").Value, rs.Fields("Set_Value").Value
      else
      ..
      end if
    case "Double"
      iF isNumeric(rs!"set_value") then
        TempVars.Add rs.Fields("Set_TempVars_Name").Value, cdbl(rs.Fields("Set_Value").Value)
      else
      ..
      end if
   case "Date"
      if isDate(rs!"set_value") then
        TempVars.Add rs.Fields("Set_TempVars_Name").Value, cdate(rs.Fields("Set_Value").Value)
      else
      ..
      end if
    case else
  end select 

I do not think VBA has an isBoolean, but you can roll your own.

CODE -->

Private Function IsBoolean(rvarValue As Variant) As Boolean
' Procedure: IsBoolean
'
' Description: test a variant to determine if it is boolean or not
'--
   
   Const cstrProcedure = "IsBoolean"
   'Dim blnResult As Boolean
   On Error GoTo HandleError
   
   'blnResult = False
   'this code yields false positives
   '    blnResult = False
   '    If IsNumeric(rvarValue) Then
   '        If rvarValue = CLng(True) Or rvarValue = CLng(False) Then
   '            blnResult = True
   '        End If
   '    Else
   '        blnResult = False
   '    End If
   If VarType(rvarValue) = vbBoolean Then
       IsBoolean = True
   Else
       IsBoolean = False
   End If
HandleExit:
  Exit Function
HandleError:
   'Insert error handling code here
   Resume HandleExit
End Function 

RE: Writing a function loop through a settings table and set TempVars

(OP)
Thank you for your comments.

As I am only just learning functions, I may not have understood all the advice, but I have modified my code below and would be grateful for further comments/ recommendations. I have inserted a Value_Data_Type column in my settings table. This has been a good move as it now allows me to ensure that a setting is entered by using the required setting at table level.

New code below - many thanks Mark

CODE -->

Public Function Set_TempVars() As String

    Dim rs As dao.Recordset
    Dim SetTbl As String

    SetTbl = "tbl_SA_SETTINGS"

    Set rs = CurrentDb.OpenRecordset(SetTbl)

With rs

    If Not .BOF And Not .EOF Then

    .MoveLast
    .MoveFirst

While (Not .EOF)


 'Check to make sure that the field Set_Value_Data_Type contains a value when Set_Enabled is TRUE

  If IsNull(rs.Fields("Set_Value_Data_Type").Value) = True _
    And rs.Fields("Set_Enabled").Value = True Then
    MsgBox "DO NOT PROCEED - INFORM MANAGER - Settings table has no Setting Value Data Type set at record: " & rs.Fields("Set_ID")
    Exit Function
  End If


 'Check to make sure that the field Set_Value contains a value when Set_Enabled is TRUE

  If IsNull(rs.Fields("Set_Value").Value) = True _
  And rs.Fields("Set_Enabled").Value = True Then
    MsgBox "DO NOT PROCEED - INFORM MANAGER - Setting table has no Setting Value at record: " & rs.Fields("set_ID")
    Exit Function
  End If


' Set the TempVars when Value Data Type is a STRING VALUE
        
  If (rs.Fields("Set_Value_Data_Type").Value) = "String_Value" _
    And IsNull(rs.Fields("Set_Value").Value) = False _
    And (rs.Fields("Set_Enabled").Value) = True Then
    TempVars.Add (rs.Fields("Set_TempVars_Name").Value), (rs.Fields("Set_Value").Value)
  
  End If

' Set the TempVars when Value Data Type is a GENERAL NUMBER VALUE

    If (rs.Fields("Set_Value_Data_Type").Value) = "General_Number_Value" _
    And IsNull(rs.Fields("Set_Value").Value) = False _
    And (rs.Fields("Set_Enabled").Value) = True Then
    TempVars.Add (rs.Fields("Set_TempVars_Name").Value), Format((rs.Fields("Set_Value").Value), "General Number")
    Debug.Print rs.Fields("Set_Value")
    End If
    

' Set the TempVars when Value Data Type is a CURRENCY VALUE

    If (rs.Fields("Set_Value_Data_Type").Value) = "Currency_Value" _
    And IsNull(rs.Fields("Set_Value").Value) = False _
    And (rs.Fields("Set_Enabled").Value) = True Then
    TempVars.Add (rs.Fields("Set_TempVars_Name").Value), Format((rs.Fields("Set_Value").Value), "Currency")
    Debug.Print rs.Fields("Set_Value")
    End If
    
' Set the TempVars when Value Data Type is a SHORT DATE VALUE

    If (rs.Fields("Set_Value_Data_Type").Value) = "Short_Date_Value" _
    And IsNull(rs.Fields("Set_Value").Value) = False _
    And (rs.Fields("Set_Enabled").Value) = True Then
    TempVars.Add (rs.Fields("Set_TempVars_Name").Value), Format((rs.Fields("Set_Value").Value), "Short Date")
    Debug.Print rs.Fields("Set_Value")
    End If
    
' Set the TempVars when Value Data Type is a TRUE/FALSE VALUE

    If (rs.Fields("Set_Value_Data_Type").Value) = "True/False_Value" _
    And IsNull(rs.Fields("Set_Value").Value) = False _
    And (rs.Fields("Set_Enabled").Value) = True Then
    TempVars.Add (rs.Fields("Set_TempVars_Name").Value), Format((rs.Fields("Set_Value").Value), "True/False")
    Debug.Print rs.Fields("Set_Value")
    End If
    
   
.MoveNext

Wend

End If

.Close

End With

Set rs = Nothing

End Function 

RE: Writing a function loop through a settings table and set TempVars

(OP)
I altered the code as the variable formats were not correct:

The code below is what I ended up with and seems to work fine.

Thanks for all your help

Mark

CODE -->

Public Function Set_TempVars() As String

    Dim rs As dao.Recordset
    Dim SetTbl As String

    SetTbl = "tbl_SA_SETTINGS"

    Set rs = CurrentDb.OpenRecordset(SetTbl)

With rs

    If Not .BOF And Not .EOF Then

    .MoveLast
    .MoveFirst

While (Not .EOF)


 'Check to make sure that the field Set_Value_Data_Type contains a value when Set_Enabled is TRUE

  If IsNull(rs.Fields("Set_Value_Data_Type").Value) = True _
    And rs.Fields("Set_Enabled").Value = True Then
    MsgBox "DO NOT PROCEED - INFORM MANAGER - Settings table has no Setting Value Data Type set at record: " & rs.Fields("Set_ID")
    Exit Function
  End If


 'Check to make sure that the field Set_Value contains a value when Set_Enabled is TRUE

  If IsNull(rs.Fields("Set_Value").Value) = True _
  And rs.Fields("Set_Enabled").Value = True Then
    MsgBox "DO NOT PROCEED - INFORM MANAGER - Setting table has no Setting Value at record: " & rs.Fields("set_ID")
    Exit Function
  End If


' Set the TempVars when Value Data Type is a STRING VALUE
        
  If (rs.Fields("Set_Value_Data_Type").Value) = "String_Value" _
    And IsNull(rs.Fields("Set_Value").Value) = False _
    And (rs.Fields("Set_Enabled").Value) = True Then
    TempVars.Add (rs.Fields("Set_TempVars_Name").Value), CStr(rs.Fields("Set_Value").Value)
    Debug.Print rs.Fields("Set_Value")
  End If

' Set the TempVars when Value Data Type is a NUMBER VALUE

  If (rs.Fields("Set_Value_Data_Type").Value) = "Number_Value" _
    And IsNull(rs.Fields("Set_Value").Value) = False _
    And (rs.Fields("Set_Enabled").Value) = True Then
    TempVars.Add (rs.Fields("Set_TempVars_Name").Value), CDbl(rs.Fields("Set_Value").Value)
    Debug.Print rs.Fields("Set_Value")
  End If
    

' Set the TempVars when Value Data Type is a CURRENCY VALUE

  If (rs.Fields("Set_Value_Data_Type").Value) = "Currency_Value" _
    And IsNull(rs.Fields("Set_Value").Value) = False _
    And (rs.Fields("Set_Enabled").Value) = True Then
    TempVars.Add (rs.Fields("Set_TempVars_Name").Value), CCur(rs.Fields("Set_Value").Value)
    Debug.Print rs.Fields("Set_Value")
  End If
    
' Set the TempVars when Value Data Type is a DATE VALUE

  If (rs.Fields("Set_Value_Data_Type").Value) = "Date_Value" _
    And IsNull(rs.Fields("Set_Value").Value) = False _
    And (rs.Fields("Set_Enabled").Value) = True Then
    TempVars.Add (rs.Fields("Set_TempVars_Name").Value), CDate(rs.Fields("Set_Value").Value)
    Debug.Print rs.Fields("Set_Value")
  End If
    
' Set the TempVars when Value Data Type is a TRUE/FALSE VALUE

  If (rs.Fields("Set_Value_Data_Type").Value) = "True/False_Value" _
    And IsNull(rs.Fields("Set_Value").Value) = False _
    And (rs.Fields("Set_Enabled").Value) = True Then
    TempVars.Add (rs.Fields("Set_TempVars_Name").Value), CBool(rs.Fields("Set_Value").Value)
    Debug.Print rs.Fields("Set_Value")
  End If
    
   
.MoveNext

Wend

End If

.Close

End With

Set rs = Nothing

End Function 


RE: Writing a function loop through a settings table and set TempVars

You do have a Function. Functions usually accept a parameter (or parameters) but they don’t have to - and they return a value. Yours should return a value As String, but it does not. So what you have is just a Sub.

There is also a question of scope. Yours is Public. Do you need to have it accessed anywhere in your app? Or just from one particular place in your app, like in the start-up form? If so, you may just need a Private Sub Set_TempVars()

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Writing a function loop through a settings table and set TempVars

(OP)
Ahh thank you. So should it be a Public Sub instead of a Public function?

I really just need the routine to run at start up, but I need to access the TempVars values throughout all of the database.

I thought I would run it via the Autoexec Macro and also (to be double sure it runs), run it on the on open event of the main menu form.

On this basis should it be a public or private sub?

Many thanks, Mark

RE: Writing a function loop through a settings table and set TempVars

>run it on the on open event of the main menu form.

In this case I would make it as a Private Sub in your main menu form.

> I need to access the TempVars values throughout all of the database.

Your Sub could be Private, and it can populate TempVars that are public / global.

Don't confuse the scope of the Sub/Function with the scope of the variables.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close