Where clause not working

I need some help with a Where clause in a SQL statement.

What I am trying to do is use a value that is displayed in a text box on my form as the filter criteria in a list box.

On my first form I have the user select his username from a combo box. He is then prompted for a password. Once he gets past the password form, it allows him access into the main data entry form. Once he's in, the UserID he selected from the first form is automatically displayed in a textbox known as [Text689]. This text box is set to Enabled.False so it can't be changed and it's control source is set to the table as UserID.

I have another listbox setup on the main form as a record selector. This is where it gets confusing. I can get records with no problem, what I can't figure out is how to limit the records the user can select, to what he actually has created. I want to filter his selection options based on his UserID.

I have tried everything I can think of and nothing seems to work. I think the answer lies in the where clause of my SQL statement but it continues to elude me.

example:

Select tblFTEP.[Key#],tblFTEP.UserID,tblFTEP.TraineeID, tblFTEP.DOR,tblFTEP.Date
From tbl FTEP
Where tblFTEP.UserID = Text689 ;

What I want the where condition to say is, show only those records that match the UserID that is displayed in Text689 on the main form.

Any ideas? I am just about ready to throw this laptop out the window!

I have posted this in the Access help Thread but have recieved no responses. Is it even possible to do this?

hi

to be frank i dont understand ASP and what i would have done in case of VB is put the query in double quotes like this

"Select tblFTEP.[Key#],tblFTEP.UserID,tblFTEP.TraineeID, tblFTEP.DOR,tblFTEP.Date
From tbl FTEP
Where tblFTEP.UserID ='" & Text689.text & "';"

please try this and let me know whether it worked.

Date is a reserved word and must be surrounded by [] brackets.

Thank you both for replying .

Unfortunately neither method helped.....

I'm beginning to lose hope that there is a solution to this. I've been after this problem for two weeks and am still beating my head against the wall!

I wonder if I am violating some rule on what you can use as criteria?

When I put brackets around Date in SQL View , Access just ignored them and changed it back to what it wanted.

Select tblFTEP.[Key #], tblFTEP.UserID, tblFTEP.TraineeID, tblFTEP.DOR, tblFTEP.Date From tblFTEP
Where (((tblFTEP.UserID) = 'Text689'));

Is what it is showing me.

When I try to select a record there is nothing there.

Am I violating some SQL rule , or am I just doing things the hard way?

Got some help from SBaxter. He suggested I requery my list. Shazaam ! It worked! I felt really lttle after that. The little details will get ya every time.

Thanks for your help Evil doing moderator.

The problem ended up not being the SQL Statement but the fact I did not requery my list box. I set my listbox up to requery when it opened and son of a gun the sucker worked! I Can't take credit though SBaxter pointed out this little detail that has eluded me for two weeks. I feel much better now, and I wanted to thank you for your help as well. I'm still learning this stuff and sometimes I feel like I just wanna drive a bus over a cliff!