Check for a field being autonumber in a recordset

[TheDude53]

Hi All,

I've got a problem and thus far no MS help has been helpful (trying to act surprised) and was hoping that the wonderful people on tek tips could help (sucking up as much as possible) )

Simple problem really.

Got a recordset say:
strSQL = “SELECT * FROM tblThisTable”
Set rsCopyFrom = currentdb.openrecordset(strSQL)

I’m looping through the fields and setting various fields to the new values however I haven’t found a way to skip past autonumbers. I’m doing something like:

Do while iCount < rsCopyFrom.Fields.Count
Select Case rsCopyFrom.Fields(iCount).Name
Case “Alpha”
Case “Beta”
Case Else
End select
iCount = iCount + 1
Loop

You get the idea. Is there anyway to get to the autoNumber setting of a field? Type just returns a value indicating numeric, text etc.

Cheers one and all.

 

[rubbernilly]

I don't think that is possible...

The page is blocked by my firewall here at work so I can't be sure, but I believe that this is the page where I read that it was impossible:

aspadvice.com/blogs/andrewmooney/default.aspx

That being said, when I have had an autonumber field in a recordset, this is the way I have handled it.

First, build a query or an SQL statement for your rs object that renames the fields so that they match the field names of the recordset you are going to write to. Then write the fields through an iteration.

dim fld as DAO.Field, rsRead as DAO.Recordset
dim rsWrite as DAO.Recordset
dim strSQL as string

strSQL = "SELECT Field1 as Monkeys, Field2 as Whooptihumps, Field3 as Flapjabbers FROM tblThisTable"

set rsRead = CurrentDb.OpenRecordset(strSQL)
set rsWrite = CurrentDb.OpenRecordset("SELECT * FROM tblDestination")

For Each fld in rsWrite.Fields
if fld.Name <> "MyAutoNumberField" and not isNull(fld)then
rsWrite(fld.name) = rsRead(fld.name)
End if
next

set rsread = nothing
set rswrite = nothing
[/code]

You can see the test there for the field that was the autonumber... but I think it is even better to limit the destination recordset so that it does not include the autonumber fields in the first place. They'll take care of themselves, so there is no need to include them in your SQL select statement.

That way, you can be sure that every field that exists in your rsWrite recordset exists in the rsRead recordset, and once you mask/alias the rsRead fields to match rswrite, you know that you can write them across without worry of the autonumber field.

 

[TheDude53]

got it covered now thanks - reason i couldn't do it by name is because it's in a generic function for some mother of an application. can be done as follows:

If rsCopyFrom.Fields(iFieldCount).Attributes <> 17 Then

attributes of 17 is an autonumber with index