Runtime error '3251' ???

[AccessChap]

Hi guys and gals,

Got a weird problem so really hoping someone has seen this before and can lend a hand

I have recently split a database into a front end and a back end for multi-user reasons. In the front end the database contains all the code of the back end but none of the data. Instead there are linked tables pointing to the back end database's tables to keep the front end to a sensible size. Since making this change I now get the above runtime error on this piece of code:

Function Validate_User_Exist()

' Open database tables to be updated (Build recordsets)

Set db = CurrentDb()
Set rstUser = db.OpenRecordset("tbl_User", dbOpenSnapshot)

Validate_User_Exist = False

' Errors on the line below
rstUser.Index = "PrimaryKey"

Do While Not rstUser.EOF

rstUser.Seek "=", Form_User_Create.Textbox_UserID
If rstUser.NoMatch = False Then
msg = MsgBox("User already created", vbOKOnly + vbInformation,
"Autoline Security Database")
Validate_User_Exist = True
Exit Function
End If
rstUser.MoveNext
Loop
End Function

Prior to splitting out the database this code worked with no issues. Anyone have any idea what is going on please? I'm stuck on this

TIA

Andy

 

[Guus2005]

Do While Not rstUser.EOF
    rstUser.Seek "=", Form_User_Create.Textbox_UserID
    If rstUser.NoMatch = False Then
        msg = MsgBox("User already created", vbOKOnly + vbInformation,
               "Autoline Security Database")
        Validate_User_Exist = True
        Exit Function
    End If
    rstUser.MoveNext
Loop

i would rewrite this part to

if isnull(dlookup("User_ID","tbl_User","User_ID=" & Form_User_Create.Textbox_UserID)) then
    msgbox "New User"
else
    msgbox "User already exists."
endif

But there are many more variations.

Short:

Validate_User_Exist =isnull(dlookup("User_ID","tbl_User","User_ID=" & Form_User_Create.Textbox_UserID))

I am not sure why you loop through the recordset when SEEK didn't find an occurence in the recordset? Doesn't seem right.

 

[RuralGuy]

Are you sure all of this works with a Snapshot cursor? How about trying a DynaSet instead?

 

[gemma-the-husky]

RUN TIME ERROR Bin SPLIT database

you get the run time error in a SPLIT database, because the indexed SEEK methods are not valid IN split databases - you have to use FIND methods instead, which are not quite as efficient (but still adequate)

you could open the backend directly, and then use seek

otherwise you can use the dlookups etc, as already explained.

 

[AccessChap]

Are you sure all of this works with a Snapshot cursor? How about trying a DynaSet instead?

No dice I'm afraid, the code still errors on the "rstUser.Index" line. Thanks anyway,

Andy

 

[AccessChap]

Fixed! I am officially much happier now

This is the snippet of code which allowed me to avoid using the .Index line and now all is well:

Validate_User_Exist =isnull(dlookup("User_ID","tbl_User","User_ID=" & Form_User_Create.Textbox_UserID))

Many thanks for the advice, I wouldn't have got this by myself

Andy

 

[RuralGuy]

Thanks for posting back with your success.