?
Solved

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

Posted on 2014-09-08
14
Medium Priority
?
4,070 Views
1 Endorsement
Last Modified: 2014-09-10
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!
1
Comment
Question by:jmsjms
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 
LVL 38

Expert Comment

by:PatHartman
ID: 40309984
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
 

Author Comment

by:jmsjms
ID: 40310005
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
 

Author Comment

by:jmsjms
ID: 40310073
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 38

Expert Comment

by:PatHartman
ID: 40310114
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
 

Expert Comment

by:DbPioneer
ID: 40310134
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
 

Expert Comment

by:DbPioneer
ID: 40310180
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
 
LVL 38

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40310477
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
 

Author Comment

by:jmsjms
ID: 40311499
"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
 

Author Comment

by:jmsjms
ID: 40311668
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
 

Author Closing Comment

by:jmsjms
ID: 40311676
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
 
LVL 38

Expert Comment

by:PatHartman
ID: 40312309
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
 

Author Comment

by:jmsjms
ID: 40314105
Thanks Pat.

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

CHeers
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40314510
I don't use them either for the same reason.  I just copied and pasted some code from the thread.
0
 

Author Comment

by:jmsjms
ID: 40314590
OK. THanks again.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question