Syntax error

[BonnieG]

I was wondering if someone could help me please?

I have a few tables, one of which stores a record of changes made to user accounts, and some of those changes are linked to a "multichange" table for when I have made the same change to multiple accounts.

What I am trying to do is select the change_id from my changes table, with a limit of 1 as I only want one result (it will always be the same) but I am using this query and getting a syntax error telling me I have a missing operator.

SQL_SELECT = "SELECT change_id from dbo_user_change where username = '" & username & "' AND change_type = 'new account' LIMIT 1"
Set qresults = CurrentDb.OpenRecordset(SQL_SELECT)
Do While Not qresults.EOF
multichange_id = qresults.Fields(1)
qresults.MoveNext
Loop
qresults.Close

'Username' is a field on my form. Does anyone know where I'm going wrong please? I am not entirely sure this is the best way of getting this information...

 

[pr2-eugin]

I do not think Access uses LIMIT.. Try

SELECT [COLOR=Red][B]TOP 1[/B][/COLOR].........

Saying that.. Why do you even need this condition? Should the Query not return only one ID as per the UserName.. In other words why is your UserName not Unique?

 

[BonnieG]

Thank you. I've tried that and I'm getting "Item not found in collection" now... hmmm... any ideas?

This happens whether I include the "TOP 1" or remove it entirely...

 

[pr2-eugin]

I think fields is Zero bound.. So try.. Fields(0) or simply.. Fields("change_id") or Fields!change_id

 

[BonnieG]

That did the trick! Thank you so much!

 

[pr2-eugin]

Glad you have it sorted.. Good Luck..