Solved

use a variable as fieldname in recordset

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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 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…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

792 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