Solved

use a variable as fieldname in recordset

Posted on 1998-10-23
5
215 Views
Last Modified: 2006-11-17
What I want to do is use a variable as the name for a field in a recordset. I've got a recordset called [export]. This recordset has 50 fields called [field1], [field2], , , [field50]. I use a variable called Counter. I don't know which data to put in which field before I start filling the table so I use the counter variable to place the data in a specific field.

Syntax I'm trying to use is lliek this:
dim counter
Set [ex] = dbs.openrecordset("export")
ex.addnew
if .... then
 ex!field & counter = "test"
 counter = counter + 1
end if
if .... then
 ex!field & counter = "test2"
 counter = counter + 1
end if
etc...

However this doen't work, does anyone know a sollution for this?
0
Comment
Question by:Sykey
  • 3
5 Comments
 
LVL 5

Accepted Solution

by:
kulikuli earned 100 total points
ID: 1966561
example:
myrecordset(field & cstr(counter))="test2"
0
 
LVL 5

Expert Comment

by:kulikuli
ID: 1966562
using your recordset named ex it would be like:
ex(field & cstr(counter))="test2"
or to explain a little more another example:
set rs=db.openrecordset("MyTable",dbopendynaset)
   rs.addnew
   for intCount=0 to rs.fields.count-1
      strFieldname="Field" & cstr(intCount)
      rs(strFieldname)="NewValue"
   next intCount
   rs.update

'the above procedure fills all fields in the new record with the value "NewValue"

Once again another example that is fieldname-independend:
rs.addnew
for intCount=0 to rs.fields.count-1
   rs.fields(intCount)="MyValue" 'All fields get filled with this value
next intCount
rs.update

Hope this helps,

kulikuli
0
 
LVL 2

Expert Comment

by:Poddy
ID: 1966563
i think you should consider normalising your data, too
0
 
LVL 5

Expert Comment

by:kulikuli
ID: 1966564
Poddy is right. It isn't a very good idea to have a table with fieldnames like you describe. Try normalizing your datamodel and apply the last example I provided.
0
 

Author Comment

by:Sykey
ID: 1966565
I agree about normalising my data, but this is for a temp-table and I don't know what data will be put in which field. This depends on the wishes of the user and can change every time..
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

823 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