Solved

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

Posted on 2014-09-08
14
3,794 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
  • 7
  • 5
  • 2
14 Comments
 
LVL 35

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 35

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 35

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 35

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 35

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

839 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