"FieldName" is not an index in this table. Error 3800

[AUGuy]

This is the code i'm using, error line highlighted:

Option Compare Database
Public Function VerifyAcct(AcctNum As String)
Dim dbDatabase As Database
Dim rsAcctNumber As Recordset
Set dbDatabase = CurrentDb
Set rsAcctNumber = dbDatabase.OpenRecordset("CAM_Portfolio_Query", dbOpenTable)
With rsAcctNumber
[COLOR=red][B]    .Index = "Account-Number"
[/B][/COLOR]    .Seek "=", AcctNum
    If .NoMatch = True Then
        MsgBox "NotFound"
    Else
        MsgBox "Found"
    End If
End With
    
End Function

It is giving me Runtime 3800, "FieldName" is not an index in this table.
In the table CAM_Portfolio_Query, I set the [Account-Number] field to be both the key and indexed, with no duplicates allowed.

Yet i still get this error. Any thoughts?

Thanks,
Guy

 

[John Big Booty]

Wild guess mode; The embedded minus sign may be causing your problem; avoid using special charters in field and object names, limit yourself to alpha and numeric charters and the underscore (_)

 

[pbaldy]

I agree, and bracketing the field name may work around the problem (but option 1 is changing the name as John suggests). I would also point out that you're using a very inefficient method to test (or get the record to work with), as the whole table has to be loaded. I'd do this type of thing:

strSQL = "SELECT * FROM CAM_Portfolio_Query WHERE [Account-Number] = " & AcctNum
Set rsAcctNumber = dbDatabase.OpenRecordset(strSQL , dbOpenDynaset)
If rsAcctNumber.EOF Then
  'no records
Else
  'there's at least 1
End If

 

[AUGuy]

I agree, and bracketing the field name may work around the problem (but option 1 is changing the name as John suggests). I would also point out that you're using a very inefficient method to test (or get the record to work with), as the whole table has to be loaded. I'd do this type of thing:

strSQL = "SELECT * FROM CAM_Portfolio_Query WHERE [Account-Number] = " & AcctNum
[COLOR=red][B]Set rsAcctNumber = dbDatabase.OpenRecordset(strSQL , dbOpenDynaset)[/B][/COLOR]
If rsAcctNumber.EOF Then
  'no records
Else
  'there's at least 1
End If

I am getting "Too few parameters. Expected 1." error from the above highlighted line.

 

[gemma-the-husky]

do you have a function within CAM_PORTFOLIO_QUERY

I think you get this error when trying to iterate a recordset which includes a function.

 

[AUGuy]

do you have a function within CAM_PORTFOLIO_QUERY

I think you get this error when trying to iterate a recordset which includes a function.

Nope, the name is a bit misleading. CAM_PORTFOLIO_QUERY is actually the name of a table. It was existing in the database before i got a hold of it, and I just haven't renamed it yet.

 

[boblarson]

Is Account-Number a text field per chance?

 

[AUGuy]

Is Account-Number a text field per chance?

It is indeed.

 

[boblarson]

Then your SQL should be:

strSQL = "SELECT * FROM CAM_Portfolio_Query WHERE [Account-Number] = " & Chr(34) & AcctNum & Chr(34)

 

[AUGuy]

Then your SQL should be:

strSQL = "SELECT * FROM CAM_Portfolio_Query WHERE [Account-Number] = " & Chr(34) & AcctNum & Chr(34)

Ah yes of course...

i changed it, but I'm still getting the same error.

 

[boblarson]

Make sure that you don't ALSO have a query of the same name.

 

[AUGuy]

The only queries it has are AssignGuar,AttorneyDel,AttorneyDetail

 

[boblarson]

So, if you take off the Where clause does it open okay?

If so, I am thinking that you have this in the code:
[Account-Number]

but it might be off on the spelling. Look really close because a space or something can cause a problem. Try going to the table, go to design view and copy the name directly from that field and paste it in.

 

[AUGuy]

So, if you take off the Where clause does it open okay?

If so, I am thinking that you have this in the code:
[Account-Number]

but it might be off on the spelling. Look really close because a space or something can cause a problem. Try going to the table, go to design view and copy the name directly from that field and paste it in.

*Sigh*...

WHERE [A[B][U][COLOR=red]ccc[/COLOR][/U][/B]ount-Number] = " & Chr(34) & AcctNum & Chr(34)

I've been looking at this for close to an hour and didn't notice that.

Thanks again. I'm an idiot

 

[boblarson]

I'm an idiot

Nope, just a victim of something we've probably all suffered from (and I am no exception) - misspelled field names. I have one which has two letters reversed and also one where the person misspelled subsidies as subsities. So, I know.