Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
Moss100

Moss100

Technical User
Aug 10, 2004
586
GB
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






 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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


 
Upvote 0 Downvote
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 [tt]Private Sub Set_TempVars()[/tt]


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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
>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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Bubba002
  • Question
WHERE clause in the Set command of an db.OpenRecordset method
Replies
16
Views
383
dhookom
dhookom
OCM
  • Locked
  • Question
Populate access table from excel
Replies
6
Views
801
OCM
OCM
renigar
  • Question
Access 365: Search LastName in one table to put EmployeeID in another
Replies
8
Views
425
renigar
renigar
TheresAlwaysAWay
  • Locked
  • Question
Can Access be used to generate and respond to text messages? 1
Replies
6
Views
764
TheresAlwaysAWay
TheresAlwaysAWay
MikeRei
  • Locked
  • Question
Help taking horizontal data and displaying it vertically (continuously)
2
Replies
27
Views
753
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top