Link to home
Start Free TrialLog in
Avatar of mdlp
mdlpFlag for United States of America

asked on

how can I create a Variable on the fly

This is much like my last question, I am sorry, for asking questions that I should be able to find the answers to, but I do not quite know the correct descriptors to get them.  

So I have 20 variables;
dim  sP1FieldName ,  sP2FieldName,  sP3FieldName  to sP20...
dim i as integer
i = 1

Do While i < 21
 sCurrValue = getDateField(i)
   ' getDateField() is a function that gets the date for field 1 this all works correctly
   ' now I want to recreate the correct variable that I created above with the text
   'and the i number

 "sP" & str(i) & "FieldName"  = sCurrValue  'This is where I am having the problem

   i = i + 1

Thank you experts

Avatar of NotLogical
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of aikimark
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mdlp


Thank you experts, I see that this can work, but I am trying to get my hands around the use of an array and not a variable, it is a reflection on my skill set not on your answers.  Please do not post any other answers until I can get this to work in my code.  I hate splitting point because it always seems that I get it wrong.

I am back and working on this project this morning.
Here are a few examples of using arrays:
Public Sub WriteCountiesToLinkTable()
'Created by Helen Feddema 24-May-2009
'Last modified 25-May-2009

On Error GoTo ErrorHandler
   Dim intCountyNo As Integer
   Dim intUBound As Integer
   Dim rstMaster As DAO.Recordset
   Dim rstCounties As DAO.Recordset
   Dim strCounties As String
   Dim strCountyArray() As String
   Dim strSearch As String
   Dim strCountyName As String
   'Clear table
   'strSQL = "DELETE * FROM tlnkCounties;"
   'DoCmd.SetWarnings False
   'DoCmd.RunSQL strSQL
   Set dbs = CurrentDb
   Set rstMaster = dbs.OpenRecordset("tblMasterDatabase")
   Set rstCounties = dbs.OpenRecordset("tlnkCounties")
   'Get counties from each record in master table, and write
   'them to link table
   ReDim strCountyArray(76)
   Do While Not rstMaster.EOF
      If Nz(rstMaster![County]) = "" Then
         GoTo NextRecord
         'Create an array of counties for this record
         strCounties = rstMaster![County]
         'Debug.Print "Counties: " & strCounties
         strCountyArray = Split(expression:=strCounties, _
            delimiter:=",", _
            limit:=-1, _
         intUBound = UBound(strCountyArray)

         For intCountyNo = 0 To intUBound
            If strCountyArray(intCountyNo) = "" Or _
               strCountyArray(intCountyNo) = "Various statewide" Then
               GoTo NextRecord
               strCountyName = Trim(strCountyArray(intCountyNo))
               'Debug.Print "County name: " & strCountyName
            End If
            strSearch = "[COUNTY] = " & Chr(39) _
               & strCountyName & Chr(39)
            'Debug.Print "Search string: " & strSearch
            Debug.Print "About to add a record for Record ID " _
               & rstMaster![RecordID] & " and County ID " _
               & DLookup(expr:="[CountyID]", _
               domain:="tlkpCounties", criteria:=strSearch)
            rstCounties![RecordID] = rstMaster![RecordID]
            rstCounties![CountyID] = Nz(DLookup(expr:="[CountyID]", _
               domain:="tlkpCounties", criteria:=strSearch))
         Next intCountyNo
      End If

   Exit Sub

   If Err.Number = 3058 Then
      strTitle = "Name wrong"
      strPrompt = "Please check the county name; " & strCountyName _
         & " not found in counties table"
      MsgBox prompt:=strPrompt, _
         buttons:=vbExclamation + vbOKOnly, _
      GoTo ErrorHandlerExit
      MsgBox "Error No: " & Err.Number _
         & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
End Sub

Public Sub SplitSKUs(strSKUs As String)

   Dim strSKUArray() As String
   Dim intUBound As Integer
   Dim i As Integer
   'Set up array of split items
   strSKUArray = Split(strSKUs, "*", -1, vbTextCompare)
   intUBound = UBound(strSKUArray)
   For i = 0 To intUBound
      Debug.Print strSKUArray(i)
   Next i
End Sub

Open in new window

Avatar of mdlp


Thank you, I hate to say it but I could not get it to work for me, but you have brought arrays back to the "front of mind" for me.  I have used arrays in the past but only to pass data through.

Helen thank you for the additional information, but since I posted I feel it would be wrong to award you points even thought you gave great input.  

Thank you all  again,