Solved

use a variable as fieldname in recordset

Posted on 1998-10-23
5
214 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

920 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

13 Experts available now in Live!

Get 1:1 Help Now