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
Solved

Textbox  controlsource is a query but not showing results jUST $ Name

Posted on 2014-07-21
5
424 Views
Last Modified: 2014-07-22
Access 2010
Useform with textbox , combobox(2 columns--bound to column2, query
What I need: Trying get the textbox to display a value from the query but a criteria is also a value from a combobox.

=DLookUp("CountOfProcrun","xaEmployee_Wwg_Searches","RACF=" & [Forms]![iData_Reporting]![combo5])
or
SELECT Count(dbo_iData_Desc_Search_Log.ProcRun) AS CountOfProcRun
FROM dbo_iData_Desc_Search_Log
GROUP BY dbo_iData_Desc_Search_Log.RACF, dbo_iData_Desc_Search_Log.ProcRun
HAVING (((dbo_iData_Desc_Search_Log.RACF)=[Forms]![iData_Reporting]![Combo5]) AND ((dbo_iData_Desc_Search_Log.ProcRun)="1"));



this is not working ?

Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 3
  • 2
5 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40210157
Is the textbox on Forms!iData_Reporting?

I assume that [Combo5] (really bad name for a control, BTW)  bound to a data field on the form, or us just used for this and other purposes?

If so, have you tried:

DLookUp("CountOfProcrun","xaEmployee_Wwg_Searches","RACF=" & [Forms]![iData_Reporting]![combo5])

Have you considered just putting the code in the afterupdate event of the combo box?  That way, you could test to see whether it is a valid entry, or is empty, and then handle the situation appropriately.

Private Sub combo5_AfterUpdate

    dim strCriteria as string

    if TRIM(me.combo5 & "") <> "" Then

        'if the combo bound column is numeric, use this
        strCriteria = "RACF=" & me.[combo5]

        'if the combo bound column is text, use this
        strCriteria = "RACF='" & me.[combo5] & "'"
   
        me.SomeOtherControl = DLOOKUP("CountOfProcrun","xaEmployee_Wwg_Searches",strCriteria)

    end if

End Sub

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
ID: 40210181
FYED,  [Combo5]  is bound a table with 2 columns... But the value i need the query is the 2 nd  column.

BILL THOMAS |  BR2QW2

so if i select "BILL THOMAS"  I NEED THE QUERY TO USE  "BR2QW2"
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40210211
The syntax for that is either:

me.combo5.column(1)

OR, if it is not on the same form as the other data:

[Forms]![iData_Reporting].[Combo5].column(1)

The column property of the combo box is zero based, so the first column is referred to using column(0).

My personal preference is to put the bound column as 1 (not zero based) and hide that column from the user, then display what you want the user to see "Bill Thomas" in column 2 of the query.  Then you set the column widths to 0;1.5"

With it configured this way, you could simply refer to [Combo5], since the bound column will contain the value "BR2QW2"
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 40210821
Thanks for the information
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40210946
glad to help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

860 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