Recordset Field Name

[gblack]

OK, what I am trying to do is to run through a table and change all the fields to upper case because I’m using Oracle and everything is upper case in our Database.

The issue is that there are 75 fields that I have to do this to. Luckily all the fields are text, so all I need to do is loop through the recordset and loop through each of the fields and use the UCase() function to change each value.

The issue is that I do not want to have to specifically identify each and every one of the 75 fields.

I’d like to use an index number, but it doesn’t seem to allow me to do so when I try to update the recordset.

I can use strCAPs = UCase(rs.Fields(i)) to find a value and change it to caps, but I can’t use rs!Fields(i) = strCAPs to change the value in my recordset to strCAPs.

Is there a way I can get the actual name of the field which is indexed. I tired rs.Fields(i).Name, but that gave me an error. So I don’t know what to do… can anyone help here?

 

[RoyVidar]

I don't understand. I thought both things you're saying doesn't work, should work. Could you post the code you're using and the error messages?

When looping the fields collection, remember it's indexed starting from zero, which means you need to stop at rs.fields.count-1.

 

[DJkarl]

OK, what I am trying to do is to run through a table and change all the fields to upper case because I’m using Oracle and everything is upper case in our Database.

The issue is that there are 75 fields that I have to do this to. Luckily all the fields are text, so all I need to do is loop through the recordset and loop through each of the fields and use the UCase() function to change each value.

The issue is that I do not want to have to specifically identify each and every one of the 75 fields.

I’d like to use an index number, but it doesn’t seem to allow me to do so when I try to update the recordset.

I can use strCAPs = UCase(rs.Fields(i)) to find a value and change it to caps, but I can’t use rs!Fields(i) = strCAPs to change the value in my recordset to strCAPs.

Is there a way I can get the actual name of the field which is indexed. I tired rs.Fields(i).Name, but that gave me an error. So I don’t know what to do… can anyone help here?

I would do it this way

Sub test()
Dim x As Integer
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("YourTableHere")

rs.MoveFirst

Do Until rs.EOF
    For x = 0 To rs.Fields.Count - 1
        rs.Edit
        rs(x) = UCase(rs(x))
        rs.Update
    Next x
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Sub

 

[gblack]

Hmm...

I tried using

Do Until rs.EOF
For x = 0 To rs.Fields.Count - 1
rs.Edit
rs(x) = UCase(rs(x))
rs.Update
Next x
rs.MoveNext

But it's giving me an error message at the rs.Edit line... telling me: "method or data member not found"

But that looks exactly like what I want it to do... if it'd just work like that!!!

 

[gblack]

oops...

No that code actually worked... my mistake.

Thanks Much!
Gary

 

[RoyVidar]

I think that error might be explained by Access having a hiccup over which library to use. In some versions, both ADO and DAO are selected as references, and then, when declaring something As Recordset, will make Access select the one highest in the reference list. Even if they share the same name, they are different, and will give either some type mismatch, the error you mention etc.

Therefore, one is often recommended to be explicit in the declarations, i e

dim rsD as DAO.Recordset
dim rsA as ADODB.Recordset

which will disambiguate this. Since ADO recordset doesn't have an .Edit method, I guess that's what you got the first time

 

[gemma-the-husky]

rs(x) thats the problem

its rs!fieldname

not sure if you can have rs.fields(x)

but you definitely cant have just rs(x)

 

[RoyVidar]

No, I don't think that is the problem, Gemma, they are all valid ways of referring to a field in a recordset by it's ordinal position. It's easy to try, take any table.

dim rs as dao.recordset
dim x as long
set rs = currentdb.openrecordset("mytable")
do while not rs.eof
    for x = 0 to rs.fields.count-1
        debug.print rs(x), rs.fields(x), rs.fields.item(x), rs.fields(x).value,
    next x
    debug.print
    rs.movenext
loop

 

[MarkK]

Since the recordset Edit and Update methods make the whole record available for changes, I'd traverse the fields inside a single Edit/Update operation...
I'd also use a DAO.Field variable to traverse the fields collection of the recordset...

Dim rst As DAO.Recordset
Dim fld As DAO.Field
Set rst = CurrentDb.OpenRecordset("YourTableHere")
With rst
  Do While Not .EOF
    .Edit
    For Each fld in .Fields
      If Not IsNumeric(fld) then fld = UCase(fld)
    Next fld
    .Update
    .Movenext
  Loop
  .Close
End With

 

[gemma-the-husky]

Roy

didnt know you could do that syntax

thanks