Results 1 to 4 of 4
  1. #1
    graviz is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2009
    Posts
    48

    VBA - Use a table for an array instead of hard coding values

    I'm trying to improve my coding and have created a function to loop through an array of values to see if a particular field contains them. Right now I have them hard coded as seen below. I'd like the array to read from a table if possible. I created a table called "REF_IHS_Projects" and the column is called "ID" that contains the values below.

    Code:
    Function IHS_Project_Levels(Project As String) As Integer
    
    Dim CodeArray, i As Integer
           CodeArray = Array("sh.p.smart home", "sh.p.supply chain", "sh.p.wfm / ofs", "sh.x.perficient", "sh.x.wfm/ofs", "sh.x.crafted", "sh.x.d2c.interstellar", "sh.x.dbdevelopment", "sh.x.kryptonite", "sh.x.perficient", "sh.x.transformers")
           For i = 0 To UBound(CodeArray)
              If InStr(Project, CodeArray(i)) Then
                    IHS_Project_Levels = 1
                   Exit Function
              End If
           Next
           IHS_Project_Levels = 0
    
    
    End Function
    I've googled using the ADODB recordset function but can't figure out how to modify my code above. Thanks in advance!

  2. #2
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Use a DAO.Recordset in place of array:
    Code:
    Function IHS_Project_Levels(Project As String) As Integer
        Dim rs As DAO.Recordset
        Dim intRet As Integer
    
        Set rs = CurrentDb.OpenRecordset("SELECT [ID] FROM [REF_IHS_Projects];", dbOpenForwardOnly)
        With rs
            While (Not .EOF) And (intRet = 0)
                If InStr(1, Project, .Fields("ID")) > 0 Then
                    intRet = 1
                End If
                .MoveNext
            Wend
            .Close
        End With
        Set rs = Nothing
        IHS_Project_Levels = intRet
    End Function

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I wouldn't loop the recordset, I'd include a WHERE clause that looked for the input value. If the recordset is empty return 0, otherwise 1. You could even use a DCount().
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 05-25-2018, 05:56 PM
  2. Replies: 2
    Last Post: 01-23-2018, 04:51 PM
  3. Hard Coding
    By zashaikh in forum Forms
    Replies: 7
    Last Post: 10-30-2017, 01:50 PM
  4. Replies: 4
    Last Post: 03-31-2017, 08:35 AM
  5. Replies: 3
    Last Post: 06-25-2015, 12:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums