Solved

Creating Variable Name From Another Variable

Posted on 2013-11-20
9
264 Views
Last Modified: 2013-11-22
I have to read rows from a table into variable names and then compare them to the next row in the table. So I want to read the rows into a recordset, set a variable equal to each field in the recordset and then read the next row and compare the recordset values to the established field values from the previous row.

There's a large number of fields, and the number of fields - and underlying table structure - could change over time. So rather than hard code the variable names like this:

Dim strVariableName As String

I want to maintain a table with the Field Names and Variable Names and then use a recordset from THAT table to examine the table with the actual data in it. So I need to know how to write a command that does this:

Dim rsTableStructure!FieldName As String

But I need to be able to REFER BACK TO THAT "rsTableStructure!FieldName" to examine its contents later on.

Is there a way to do this or do I need to dream up some kind of array structure.

Thanks.
0
Comment
Question by:Buck_Beasom
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 39664148
An array would be a much better idea, plus you can't really do what you want.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39664200
IF you are using Access 2007 or higher, you can use TempVars:
http://blogs.office.com/b/microsoft-access/archive/2010/09/27/power-tip-maximize-the-user-of-tempvars-in-access-2007-and-2010.aspx

The code might look something like this for defining the names of the TempVars:
dim fld as Field
dim rs as dao.recordset

set rs = Currentdb.OpenRecordset "SELECT * FROM YourTable"

For each Fld in rs.Fields
    TempVars.Add(fld.Name,fld.Value)
next
rs.MoveNext

Do until rs.EOF
For each Fld in rs.Fields
    if rs.Fld.Value = Tempvars(fld.Name) then
           msgbox fld.Name & " is the same"
   else
           msgbox fld.Name & " has changed"
   end if
Next
rs.movenext
loop

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39664221
I agree, an array is the way to go.

Access has multiple ways to refer to columns in a recordset.  This is good and bad.  Bad in that it can confuse people who have never before seen that particular reference style and good in that it gives you a lot of flexibility.

Rather than using column names, you can use column numbers.  I haven't done this and my network is down at the moment so I don't have any database I can play with.  I don't know if the column collection is a zero-based array or not so watch out for that.  The first column of the table might be 0.

So, you would create a table with the column numbers in it.  I would include a second column with the names just because it will help me with debugging and aid future developers who step in after you leave.  But you don't use the name column, you use the number column.  That allows you to control the loop and use the column number as a variable.  You would use the same number to address the array.  Don't worry about there being gaps in the array.  Column 12 of the table should match to column 12 of the array.  It doesn't matter if you are only using columns 1, 5, 6, 7, and 27.  load the requested column numbers into an array and use that array to pull out the "index" for the recordset fields collection and the array of variables.

Post back the code you end up with in case anyone else might have a use for it.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39665127
It would be much easier to use clone of the recordset.
Public Function CheckRecord( _
  ByVal strTable As String, _
  ByVal strId As String, _
  ByVal lngId As Long) _
  As Boolean

  Dim dbs     As DAO.Database
  Dim rst     As DAO.Recordset
  Dim rstChk  As DAO.Recordset
  Dim fld     As DAO.Field
  Dim strFld  As String
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Select * From " & strTable & " Order By " & strId & ";")
  Set rstChk = rst.Clone
  
  rst.FindFirst "" & strId & " = " & lngID & ""
  rstChk.FindFirst "" & strId & " = " & lngID & ""
  rstChk.MoveNext
  ' Compare records.
  For Each fld In rst.Fields
      With fld
          strFld = .Name
          If strFld = strId Then
              ' Ignore the ID.
          Else
              ' Compare logic.    
              If .Value = rstChk.Fields(strFld).Value Then
                  ' Something
              End If
          End If
      End With
  Next
  rst.Close
  rstChk.Close

  Set fld = Nothing
  Set rstChk = Nothing
  Set rst = Nothing
  Set dbs = Nothing
  
End Function

Open in new window

NB: Air code. Add checks for NoMatch, EOF, etc.
/gustav
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:PatHartman
ID: 39665876
Yes, you could use a recordset clone but you would do what old timers call a 2-file match which is not what is shown.

Going through an entire recordset looking for matches using FindFirst would be very inefficient.  With this method you are reading one recordset sequentially and going back to the beginning of the second recordset each time and reading sequentially until you find a match.

With a 2-file match you use ordered recordsets so that both recordsets are in the same order.  Then you start by reading a record from each set and compare them.  Depending on whether the controlling fields are =, <, or > tells you what to do and dictates which set will be advanced next.

If setA = setB
    "match code"
    setA.MoveNext
    setB.MoveNext
Else
    If setA > setB
        setB.MoveNext
   Else
        setA.MoveNext
   End If
End If

This process is very efficient since it reads through each set sequentially and only once.
0
 

Author Comment

by:Buck_Beasom
ID: 39667099
I've wrestled with these various suggestions and I think some combination of them will work, but I still need a little help picking out the right pieces.

I assemble my "Field List" SQL with this:

strFieldListSQL = "SELECT FieldList.FieldName FROM FieldList"

Set rsFieldList = dbDatabase.OpenRecordset(strFieldListSQL)
'Now I have a recordset with all of the fields that are in the target data table

rsFieldList.MoveLast
lngLastFieldListRow = rsFieldList.RecordCount
rsFieldList.MoveFirst      
'Used below in the SQL assembly so I know the last field

strMasterSQL = "SELECT "

'This assembles the Main SQL using the Field List Recordset
Do Until rsFieldList.EOF
    strMasterSQL = strMasterSQL & "[DataFile].[" & rsFieldList & "]"
    If rsFieldList < lngLastFieldListRow Then
        strMasterSQL = strMasterSQL & ", "
    Else
       'Leave the comma off the last one
       strMasterSQL = strMasterSQL & " "
    End If
    rsFieldList.MoveNext
Loop 'rsFieldList

strMasterSQL = strMasterSQL & "FROM [DataFile] "
'Now I have the entire SQL assembled, one field at a time, using the rsFieldList field names

'Now I pull a record from the master table like this:

Set rsDataList = dbDatabase.OpenRecordset(strMasterSQL)

Do Until rsDataList.EOF
    rsDatamartFieldList.MoveFirst
    Do Until rsDatamartFieldList.EOF
        'HERE'S THE QUESTION ***************************
        strVariable = rsDataList!WHAT DO I PUT HERE????
        '*********************************************
        rsDatamartFieldList.MoveNext
    Loop
    rsDataList.MoveNext
Loop

Obviously I would be doing more than just assigning the value to a variable (actually, I am writing it into an Excel workbook) but the core question is, how do I refer to the field name in the rsDataList recordset when that field name is, itself, a value from ANOTHER recordset?

Normally I would use "rsDataList!Field1" (if "Field1" was explicitly named.) But what I am really trying to do is something like:

rsDataList![rsFieldList!FieldName]

although that won't work, obviously.

If I use the TempVar.Add, can I reuse the same TempVar over and over as I only need it at the specific moment I am referring to the field in the rsDataList recordset?

Seems there should be some simple solution - analgous to using "Form.Controls" when you want to refer to a control name that is a variable rather than explicitly referring to the control on a form.

Any suggestions will be appreciated.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39667343
You are making it too complicated.

> There's a large number of fields, and the number of fields - and
> underlying table structure - could change over time.

That's why you should read these dynamically, and with a recordset you have these ready on hand including both there names and values:

  For Each fld In rst.Fields
      With fld
          strFld = .Name
          If strFld = strId Then
              ' Ignore the ID.
          Else
              ' Compare logic.    
              If .Value = rstChk.Fields(strFld).Value Then
                  ' Something
              End If
          End If
      End With
  Next

And the easy way to control this is simply to loop the recordset. Unless you have millions of records, looping a DAO recordset is quite fast, so the simple method I sketched should work.

However, none of us have an exact idea of what you really want to do, so it is hard to be more specific.

/gustav
0
 

Author Closing Comment

by:Buck_Beasom
ID: 39668823
Well, it probably seemed too complicated because I wasn't doing a very good job of explaining everything ELSE I am trying to do here.

But you DID manage to provide the single line of code that solved all my problems, to wit:

rstChk.Fields(strFld).Value

The rstChk.FIELDS syntax accomplished the same thing I described in the "Form.Controls" analogy, i.e. allowing the specific field within the recordset to be referred to as a variable rather than explicitly.

Many thanks!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39668843
You are welcome!

/gustav
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now