How to link Access List Box row Source SQL query to a form field

Hi Experts,

I've got a list box on a form in which I want to show a list of related values filtered by a value on the form.

The form field is fldItemID in a form called MyFormName

I've tried setting the row source as

"SELECT tblItems.fldID, tblItems.Name FROM tblItems WHERE tblItems.fldID = " & me.fldItemID & " ORDER BY tbltems.Name;"

I've also tried using forms!MyFormName!fldItemID as the value but that doesnt work either.

What am i doing wrong.

(Access 2010 Project database)

Thanks!
jmsjmsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
The query needs to be changed to :

SELECT tblItems.fldID, tblItems.Name FROM tblItems WHERE tblItems.fldID = Forms!yourmainform!fldItemID  ORDER BY tbltems.Name

You can't reference form/report fields using "Me" except in the class module for the form/report.  So, you have to use the external reference style.

PS "Name" is a poor choice as a column name since it is the name of a property and you will end up with issues using VBA unless you are extremely careful.  Best practice is to avoid column names that are reserved words for VBA, Access, and SQL as well as avoiding embedded spaces and special characters.  It actually sounds harder than it is.  The easiest solution is to use compound names and either CamelCase or the_underscore to separate the words.
0
jmsjmsAuthor Commented:
I've tried this and it still doesnt work.  You sure this works in an Access Project Database?  I'm linking to a SQL backend?

(Name - Yep agree. I've changed the names of the fields to make it easier to read.)
0
jmsjmsAuthor Commented:
Setting the rowsource programmatically and using the following criteria bit for the WHERE clause works

=" & Forms!MyFormName!fldItemID & "

It just doesnt seem to work if I put it in the row source of the control.

Is this an Access bug?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
Access projects are very different animals from .mdb's so it wouldn't surprise me at all.  Since modifying the SQL string and replacing the RecordSource works, use that method.  With an .adp there is no advantage in attempting to use a queyrdef.
0
DbPioneerCommented:
Not sure about Projects but you can't reference a form in an Access SQL query (for your list box row source ) - has to be done in VBA.
Try the following approach:
set the list box row source query to: SELECT tblReport2.VesselName FROM tblReport2 WHERE tblitems.fldID = [TempVars]![NewID];
In your main form On Load event: TempVars.Add "tvNewID",0
In your textbox On Change event:
 TempVars!NewID = Me.(textbox Name).Text
Me.(List Box Name).Requery

It works at this end!
0
DbPioneerCommented:
Better correct myself here.
You can of course reference forms in the Access query builder.
You can't reference variables though - hence my use of a TempVar.
0
PatHartmanCommented:
Not sure about Projects but you can't reference a form in an Access SQL query (for your list box row source ) - has to be done in VBA.
Not true.  In an .mdb or .accdb, you can reference form fields using the format I showed earlier --- Forms!yourformname!yourcontrolname, in querydefs or in embedded SQL.  

The syntax for queries in Access projects (.adp) AND pass-through queries in .mdb/.accdb is T-SQL so standard VBA/Access functions and references are NOT allowed.  The only way you can use them is if you build the SQL string in code and concatenate the actual values as part of the string build.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmsjmsAuthor Commented:
"The syntax for queries in Access projects (.adp) AND pass-through queries in .mdb/.accdb is T-SQL so standard VBA/Access functions and references are NOT allowed.  The only way you can use them is if you build the SQL string in code and concatenate the actual values as part of the string build. "

I'm just wandering if it's a limitation with Access 2010. I could have sworn I managed to do it before with "select..... value= " & fieldnamereference  in the rowsource entered directly in the properties window but I can't seem to get it working in Access 2010.

I suppose I could setup a test access MDB and try that.  I'll give it a go.
0
jmsjmsAuthor Commented:
RIght

Access 2010, accdb format standard database.

This works fine in the row source.

SELECT [tblStaff].[ID], [tblStaff].[fldFirstName] FROM tblStaff WHERE fldCompany = forms!frmCompany!ID ORDER BY [fldFirstName];

I have to use a listboxName.requery in the current event of the parent form to updated it each time the main form content is changed but it works fine, shows the data and filters the results correctly.

Access Project [ADP] 2010 SQL 2012

I can put in a SQL statement OK, but as soon as I put in a reference a form field I get an error "Incorrect Syntax near '!'.  

Looks like Access is just dumping the whole line to SQL and not pre-parsing it to fill in the relevant field value.
0
jmsjmsAuthor Commented:
Looks like it's not possible to use a form field reference in a ADP rowsource via property viewer  unless you do it programmatically.

Thanks to all
0
PatHartmanCommented:
You have to build the SQL in VBA and then place it in the RowSource.  The SQL in the RowSource must be in T-SQL format in an .adp.  In an .mdb/.accdb then the Forms! reference works fine because the SQL is Access syntax.

Me.RowSource = "SELECT [tblStaff].[ID], [tblStaff].[fldFirstName] FROM tblStaff WHERE fldCompany = " & Me.ID  & " ORDER BY [fldFirstName];"
0
jmsjmsAuthor Commented:
Thanks Pat.

BTW, I normally take off the Square brackets as never have a space in the field/tables names.

CHeers
0
PatHartmanCommented:
I don't use them either for the same reason.  I just copied and pasted some code from the thread.
0
jmsjmsAuthor Commented:
OK. THanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.