Recordset Fieldname Manipulation

[pastymann]

Hi There

I use code like the following to create virtual recordsets in my modules that allows me to manipulate data very easily and make my database very versatile.

=================================================
Set BOMdb = DBEngine.Workspaces(0).Databases(0)
Set Monthset = BOMdb.OpenRecordset("tblMonths", DB_OPEN_TABLE)

Monthset.MoveFirst
Do While Not Monthset.EOF
varMonthName = Monthset![MonthName]

Monthset.MoveNext
Loop
====================================================


However what I really would find useful is to be able to specify the fieldname (shown in red) from a variable.

I have tried replacing the fieldname with a variable in the usual format as below but it never works

varFieldname = "November"

varMonthName = Monthset![" & varFieldname & "]


Anybody have any suggestions on how I can do this as it would save a lot of lines of code.

Pastymann

 

[DCrake]

Monthset(MonthName)

 

[pastymann]

Hi David
I must be missing something here.
If I use your line in my code I get an error message of 'Item Not Found In This Collection'.

What am I doing wrong?


=============================================
Set BOMdb = DBEngine.Workspaces(0).Databases(0)
Set Monthset = BOMdb.OpenRecordset("tblMonths", DB_OPEN_TABLE)

varnewMonth = "NOV10"

Monthset.MoveFirst
Do While Not Monthset.EOF
varMonthName = Monthset![MonthName]
varMonthNameNew = Monthset(varnewMonth)
Monthset.MoveNext
Loop
==============================================

Pastyman

 

[DCrake]

What are the names in the table called?

How you have it you are replacing the variable name everytime you move to the next record.

What are the contents of the tblMonths?

 

[pastymann]

Hi David

My Table tblMonths has fields of NOV10, DEC10, JAN11 etc

I know my code probably doesn't read correct but it is a precis version of a mutch bigger block of code, which does what I want it to do but contains many bits of code that are duplicated.

I just used this to show the way I create and manipulate my database tables with code.

What I want to do is have a variable that I can dynamically change which corresponds to one of the fieldnames in my table. When the code runs, depending on what the variable is I can get the value from that record that corresponds to the fieldname select from the variable.

Put another way.
I want to be able to set a variable = NOV10
Open my recordset.
Move to a record and get data from the field named NOV10
Do something with it
Get the next record.

Then loop around and do the same for DEC10

Pastymann

 

[DCrake]

Sounds like to me that your data is very denormalised. What are you going to do i 5 years time. You should not be adding fields to a table in the fashion I suspect you are.

However if you are looking for a field via its name then you need

Rs.Fields.Name

 

[pastymann]

Hi David

Got there in the end.
My database is not really being used as a database.
It is being used to manipulate data easier than if I used Excel.
So more a tool than a datasbase.

In the end I needed to use the following line of code.

===========================================
Set BOMdb = DBEngine.Workspaces(0).Databases(0)
Set Monthset = BOMdb.OpenRecordset("tblBomForecast", DB_OPEN_TABLE)

varnewmonth = "FEB11"

Monthset.MoveFirst
Do While Not Monthset.EOF
varMonthName = Monthset![FEB11]
varMonthNameNew = Monthset.Fields(varnewmonth).Value

Monthset.MoveNext
Loop
============================================

Thanks for all your help.

 

[gemma-the-husky]

you can assemble a variable as shown below - similar syntax to referencing a control on a form

the basic syntax is either
rs!fieldname or
rs.fields("fieldname")

the former doesn't help, but the latter lends itself to constructs such as

rs.fields("Nov" & yearnum)

or even
dim fldname as string
fldname = "Nov" & yearnum
rs.fields(fldname)