variable in a recordset field

Liv Manto

Registered User.
Local time
Today, 23:44
Joined
Apr 26, 2001
Messages
266
I am trying to reuse a procedure for different tablenames



my procedure is like this.
_______________
dim rst as dao.recordset
set rst = db.openrecordset("table1")

rst ("AFieldnameinTable1") = 1
_______________

IF:

rst ("AFieldnameinTable1") = 1

I want to replace ("AFieldnameinTable1") with a variable name

SO:

Set Fieldname = "AFieldnameinTable1"
rst (Fieldname) = 1

Last Line -- rst (Fieldname) = 1 ---DOES NOT WORK

it says:

ITEM not found in this collection:


IT DOESNT SEE IT AS :

rst ("AFieldnameinTable1")

BUT AS :

rst (Fieldname)


I TRIED PUTTING IT AS :

"rst (" & Fieldname & ")"

SAYS SYNTAX ERROR



Any techniques you can share, please?
 
Last edited:
Not to sure what your trying to do, but have you tried.

"rst ('" & Fieldname & "')"
so then it should see it as rst('AFieldnameinTable1')

--
GeK
 
I tried that.

My mistake, that was supposed to be the syntax in my original post.

I took that after SQL syntax with a variable.

Tried this too.

Rst("'" & Fieldname & "'")

Still Error.

What I am trying to do is reuse a procedure with different tables with different field names.


I am thinking of going by

rst(1) so instead of variable just go by the sequence. I just have to make sure that all my fields in my tables have the same sequence.

BUT if you have any other idea, please help.
 
Last edited:
The following code works (although in and of itself not terribly useful - run it twice and both fields in every record ends up with value of zero - but that is not really the point here....)

it may help you to work out where you are going wrong...

Code:
Public Sub MySub()
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblYourTableName", dbOpenTable)
    
    rst.MoveFirst
    Do While Not rst.EOF
        Call MySubSub(rst, "MyFirstFieldName")
        Call MySubSub(rst, "MyOtherFieldName")
        rst.MoveNext
    Loop 'while not rst.eof
    
End Sub

Public Sub MySubSub(rst As DAO.Recordset, szFieldName As String)
    rst.Edit
    If rst.Fields(szFieldName).Value > 1 Then
        rst.Fields(szFieldName).Value = rst.Fields(szFieldName) * -1
    Else
        rst.Fields(szFieldName).Value = 0
    End If
    rst.Update
End Sub

AC97 / WIN2K

HTH

Regards

John.
 

Users who are viewing this thread

Back
Top Bottom