Solved

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

Posted on 2014-09-08
14
3,848 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 36

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 36

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 36

Accepted Solution

by:
PatHartman earned 500 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 36

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 36

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 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