Solved

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

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

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
 
LVL 34

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 34

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 34

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 34

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now