Solved

DLookup to show multiple records on continuous form

Posted on 2011-03-07
11
464 Views
Last Modified: 2012-05-11
Hi,

i am using the dlookup function in a text box and its giving me a one value. Can someone help me to modify the function which would give the result as multiple values.

attached is a copy of the database..


test1.mdb
0
Comment
Question by:Saichand
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
DLookup can only return a single value.

From Microsoft's website (http://support.microsoft.com/kb/208786):
The DLookup() function returns one value from a single field even if more than one record satisfies the criteria. If no record satisfies the criteria, or if the domain contains no records, DLookup() returns a Null.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Try this expression:

=DLookUp("Account","Query1","[State]=" & Chr(34) & [cmbmstate] & " and [juris]= " & Chr(34) & [cmbmjuris] & Chr(34))

mx
0
 

Author Comment

by:Saichand
Comment Utility
Do you know or have any custom function?
0
 

Author Comment

by:Saichand
Comment Utility
i was reading through the below link and the code wasnt working.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23375131.html

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Also ... how do you get your form to populate with data ?

mx
0
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!

 

Author Comment

by:Saichand
Comment Utility
i didnt get your question DatabaseMX
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
When the form opens, no records are showing.  What are the steps to get records showing ?

mx
0
 

Author Comment

by:Saichand
Comment Utility
select the state and jurisdiction in the drop down..
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

you can of course use a recordset and assemble the wanted account numbers to insert it into a textbox. See the attached file for that.
test1.zip
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
Comment Utility
...should not enter a "brief description" with enter key...:-)

I wanted to add that you should better use a subform and then show the wanted records instead. As you already have a checkbox field in the table you can add this so the user can check or uncheck any row or you can use your UPDATE command to check/uncheck them. The query needed for the subform would be your Query1. No problems with DLookup or VBA code to assemble a string with a recordset.

Cheers,

Christian
0
 

Author Closing Comment

by:Saichand
Comment Utility
Not a complete answer i was looking for... but suggestion did help
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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

10 Experts available now in Live!

Get 1:1 Help Now