Avatar of mdlp
mdlp
Flag 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
   Loop

Thank you experts

Microsoft AccessVisual Basic Classic

Avatar of undefined
Last Comment
mdlp

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
NotLogical

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
aikimark

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mdlp

ASKER
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.
Helen Feddema

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
      Else
         'Create an array of counties for this record
         strCounties = rstMaster![County]
         'Debug.Print "Counties: " & strCounties
         strCountyArray = Split(expression:=strCounties, _
            delimiter:=",", _
            limit:=-1, _
            Compare:=vbTextCompare)
         intUBound = UBound(strCountyArray)

         For intCountyNo = 0 To intUBound
            If strCountyArray(intCountyNo) = "" Or _
               strCountyArray(intCountyNo) = "Various statewide" Then
               GoTo NextRecord
            Else
               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.AddNew
            rstCounties![RecordID] = rstMaster![RecordID]
            rstCounties![CountyID] = Nz(DLookup(expr:="[CountyID]", _
               domain:="tlkpCounties", criteria:=strSearch))
            rstCounties.Update
         Next intCountyNo
      End If

NextRecord:
      rstMaster.MoveNext
   Loop
   
ErrorHandlerExit:
   rstMaster.Close
   rstCounties.Close
   
   Exit Sub

ErrorHandler:
   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, _
         Title:=strTitle
      GoTo ErrorHandlerExit
   Else
      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

mdlp

ASKER
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,
mdlp
Your help has saved me hundreds of hours of internet surfing.
fblack61