rst!record number (two word field problem) (2000)

Topic

New Reply

I’m trying to work out a problem with a corrupt record – can’t delete it, can’t append from the table to another. I thought that I’d write some code to loop through the table and append everything I can until it crashes, delete those records, and so on.

However, the record number field is called “Record Number” and my rst!Record Number statement doesn’t work, nor does rst!”Record Number”, or rst!(“Record Number”), etc.

How do you refer to a field name that has a space in it when dealing with recordsets?

Thanks!

Randy

Reply | Quote

Replies

I believe you should be able to use either of:
rst(“Record Number”) or RS![Record Number]
The first of these has round brackets ( ) the second square brackets [ ]
The first of these looks the same as one you say you tried and didn’t work.

An anternative is to refer to a field by its index – its position in the list of fields. If Record Number was the first field you could use: rd!fields(0) – that is a zero

Reply | Quote

John, thanks for the reply. When I try rst!(“Record Number”) it focuses on rst! and gives me a compile error: Type-declaration character does not match declared data type. When I try rst!([Record Number]) it focuses on [Record Number] and I get a compile error: External name not defined. I do have the Microsoft DAO 3.6 Object Library referenced.

Function GetTheRecords()
Dim db As DAO.database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(“Exceptions”)
MsgBox rst!(“Record Number”)
End Function

I’m doing this to try to salvage records from a corrupt table with about 44,000 records. Usually I can identify the corrupt record(s) by appending chunks of records and seeing where it crashes. In this case, although I can see the data in the records, I can’t append any records – not even one without the “Invalid Argument” error.

Thanks,

Randy

Reply | Quote

What happens if you try using a different field. It sounds to me like you error is not related to the field name, but something more general.

Reply | Quote

You don’t use the bang (!) when you put the field name inside parens. It’s just rst(“field name”) or rst![field name], not both. The first construction is shorthand for rst.Fields(“field name”).

Reply | Quote